CakePHP + MySQL + Tinyint(1) = Confusion

Just a quick note to point out that CakePHP fakes a BOOLEAN field in MySQL by using tinyint(1). MySQL doesn’t have a native support for BOOLEAN.

Therefore if you attempt to save some value other than 0 or 1, CakePHP will not allow you to do that (instead it will just save 1). The easiest way to get around this is to make your field tinyint(2), as one option.

FYI, supporting ticket: https://trac.cakephp.org/ticket/3903

m4s0n501
  • http://loudbaking.myeasyscripts.com JadB

    You’re so damn quick!

    Thanks for the help on irc – was about to post the same thing ;)

  • teknoid

    @JadB

    Ha ha… thanks for the blog topic ;)

  • rafaelbandeira3

    @Teknoid: What do you mean by not having support for BOOLEAN type?
    You can set BOOLEAN type for a column.
    Maybe the problem is that isn’t CakePHP that fakes a BOOLEAN in tinyints. Maybe MySQL does.
    I’m I wrong?

  • teknoid

    @rafaelbandeira3

    There is simply no “real” boolean data type in mysql.
    http://dev.mysql.com/doc/refman/5.0/en/other-vendor-data-types.html

    So cake has to do something to automagiacally support one, and tinyint(1) is the best option it seems.

  • Silver Knight

    I’d always been told (and seen people get told) in irc://freenode/#mysql that the enum type http://dev.mysql.com/doc/refman/5.0/en/enum.html used with possible values of “Y/N”, “T/F”, “True/False” or some other such logical options is the best substitute for boolean. Is there some reason why enum wasn’t used for this purpose, I wonder?

  • teknoid

    @Silver Knight

    ENUM is not supported by cake. There are a few discussions as to why (which I mostly tend to agree with), but a quick search at the google group should give you plenty to digest.

  • Silver Knight

    Thank you for the quick response. After a bit of research, it appears the primary reason the enum type is not supported in CakePHP is that enum is specific to a particular database vendor (MySQL). However, the same research turned up several nifty snippets of code which did allow CakePHP to support the enum type. This causes me to wonder: why hack around the lack of a boolean type by misusing the tinyint type when it would be just as simple to hack in a small “fix” using enum only when it’s needed to replace the boolean? I’m probably not understanding something key to the issue, I’m guessing?

  • teknoid

    @Silver Knight

    No, you’ve got it just right. The ENUM type has been ruled out completely, that’s the bottom line…
    To me the deal with tinyint(1) seems worthwhile, it probably allows for easier adoption across various DB’s. And the benefit of using tinyint(1) for boolean automagic, far outweighs the inconvenience of having to make the field tinyint(2) if other values are needed to be stored.

  • Silver Knight

    Well, you are probably right on that one. After all, declaring tinyint(1) or tinyint(2) doesn’t actually change the max values a tinyint can store anyhow. Either way, it’s 0-255 unsigned or -128 to 127 signed any way you slice it. The (1), (2), or (3) only affects the output, not the storage capacity (according to http://dev.mysql.com/doc/refman/5.0/en/numeric-types.html afaict), so I guess it’s not really a big deal. ;-)

  • Kemal

    The deal is, if I want to store values from 0 up to 9, and I defined a TINYINT(1) UNSIGNED NOT NULL in MySQL, Cake will just assume I want to have a boolean field.

  • http://teknoid.wordpress.com teknoid

    @Kemal

    Yes, you are absolutely right… hence the point of the post and suggestion that you should use tinyint(2) instead, if you need values other than 1 or 0 for that field.

  • room34

    I came upon this situation today and was scratching my head. I had a field I had originally been using as a boolean, but I decided to make it store a numerical value 0 to 9 instead, so I left it as a tinyint(1) but CakePHP just wouldn’t save anything but 0 or 1!

    Now I understand why, of course. I decided for my purposes to switch it to a char(1) though instead of a tinyint(2). BUT… it still didn’t work. It occurred to me that CakePHP must cache model information, and it does. So, just a tip to anyone who might be in my shoes. If you change your database structure, be sure to head over to app/tmp/cache/models and delete the file for the table you’ve altered. Once I did that, it worked like a charm!

  • Pingback: Why oh why won’t CakePHP store my tinyint(1) data? | Room 34 Creative Services » Scott Anderson: Minneapolis Web Designer/Developer and Musician()

  • http://teknoid.wordpress.com teknoid

    @room34

    That’s an excellent advice. It is very important to remember to clear cache after any DB modification to save a few head/desk connections.

    Thank you for pointing that out.

  • Pingback: Field type tinyint(1) would not save values other than 0 and 1 in CakePHP | DevArticles.In()