Why oh why won’t CakePHP store my tinyint(1) data?

CakePHPOK, I actually know the answer to the question posed in the subject line. Despite the palpable suspense, I am sure 99.9% of my audience can tune out now. That leaves the remaining 0.001 readers to dive into this problem with me.

I’m doing a lot of CakePHP development these days, and I’m loving it. (What? The McDonald’s legal team is on its way over here? Damn. Fine, McDonalds. Have it your way. What, Burger King too? I guess I better make a run for the border. OK, joke’s over.)

Of course, plunging into a sea of someone else’s code is always fraught with a little peril, and today I found some. Here’s the scenario:

MySQL is a pretty cool database. I’m very loyal to it. But there are some things it should do, but it just doesn’t. One of those things is support a boolean data type. So, we make do. A common way to make do, and the way preferred by CakePHP, is to use a tinyint(1) field and just store 0 or 1 in it. In fact, CakePHP loves this approach so much that whenever it sees a data field that’s a tinyint(1), it “automagically” refuses to accept any values for that field other than 0 or 1.

That’s super-dee-duper. If that’s what you want. But I have a data table, my users table, as it happens, and in that table I used to have a field called admin, a boolean value. Either the user’s an admin or not. Great. But I decided to upgrade this to allow more access levels than just 0 or 1 (and while I was at it, changing the field name, shockingly, to access_level). I wanted to be able to support up to ten levels. Well, great! A tinyint(1) will nicely store values from 0 through 9, so I’m set!

Except… it didn’t work. Every time I tried to save a value greater than 1, I’d find that it had saved 1 as the value. I could change it to 0 just fine, but anything else became 1.

I checked the documentation and found ample evidence for the “automagic” behavior, so I figured there were a couple of possible changes I could make that would fix the problem: I could change it to a tinyint(2), or I could change it to a char(1). Since I decided I’d rather (in theory) allow letters in the field than double-digit numbers, I went with char(1).

Only it still didn’t work.

I did some more research, found that I seemed to be on the right track, but I was still confounded.

Then it occurred to me. I knew I had seen a cache directory somewhere in the labyrinth of directories and subdirectories and sub-sub-sub-sub-subdirectories in the CakePHP package. And I also knew that a framework as developed as CakePHP probably wouldn’t hit up the database for schema information constantly, so maybe… just maybe… it was cached.

I burrowed down in my application to the app/tmp/cache/models directory, and sure enough… there’s a cache file for each data table, with the schema in a serialized form. Well, it’s a cache, right? Nothin’ to lose. Trash can, here we come! I refreshed my page, and voilà! Success!

So… word to the wise (or, not so wise, like me… otherwise you’d probably already know):

  1. CakePHP will only store 0 or 1 in a tinyint(1) field. Period.
  2. If you change the schema for any of your data tables, and CakePHP acts like you didn’t… dump that cache!

We now return you to our regular, slightly less geeky programming.