OK, 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):
- CakePHP will only store
0or1in atinyint(1)field. Period. - 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.
Comments
11 responses to “Why oh why won’t CakePHP store my tinyint(1) data?”
Another suggestion could be to opt for a tinyint(2) field and store 0, 10, 20, 30, 40, 50, 60, 70, 80, 90 for your access_levels. This will help later if you decide you need more access levels as you can insert values between the others. Just a thought anyway. :)
Yes, I suppose I could do that. But is 100 really enough? ;)
Right now I’ve only assigned 5 of the 10 available spaces anyway, and I’m still only really using three of those. For what it’s worth, here they are:
0 = No access
1 = Basic user (potentially a limited-access user, maybe read-only; I haven’t decided on a specific use for this yet)
2 = Standard user
8 = Administrator
9 = Root (essentially the same as administrator, but I might want to have a “super administrator” at some point)
So right now, it’s either 0, 2 or 8. Intuitive, right? :D
My biggest fear about your suggestion is that it looks too much like BASIC to me. ’80s flashback.
Sounds like this should be a foreign key to an roles table (or similar).
Back on track , I hadn’t spotted this behaviour before – thanks for pointing it out – but I’d be very surprised if you couldn’t override this somewhere.
The roles table is not a bad idea; I’ve typically done that in the past. These days I’m focusing, however, on making my database schema as simple — minimalist, really — as possible. That means both keeping the number of tables low and keeping the number of fields in those tables low. Finding the common denominators between different types of content and building the database so that as much of the structure of the data itself can be handled from within the application as possible. That also means avoiding separate lookup tables when a simple numeric value or enum type field will do. I would have considered using an enum for this, actually, but I opted instead for the numeric values and defining constants for each to use within the application code itself. (A nice benefit of the numbers is that you can essentially grant access to “anyone at or above this level”… which of course could be done by reading the keys for the enum values too but… well, this approach seemed simpler and that’s what it’s all about.)
As for the override… yes, I would think it is something you could override — both the handling of tinyint(1) fields and the automatic caching of database schema. But so far I haven’t found either… not that I’ve looked that hard.
Wow. If we’re hearing from even a fraction of 0.001% of you interested readership, you must have one heck of a sizable audience!
;-)
I’m currently working on my first Cake project, but one of the first things that I did, for exactly the reasons you mention, is disable the cache in my core.php. This is in my dev environment, of course. That may help for the purpose of trying to get things done.
That’s a good idea. I think the reason I ran into this problem is something I can’t quite explain… this is my second “big” CakePHP project (although I’ve been poking around with it for nearly a year), building on some of the key elements of the first one. When I started the first one, version 1.2 wasn’t released yet, but I’ve upgraded the project to it. This second one was started on 1.2 right from the get-go.
But… the model caching is just not happening on the first project. I didn’t even know it existed until I ran into this problem with the second project. I compared my core.php files between the two, and I don’t see any significant difference — the caching settings appear to be the same in both.
Is model caching a new addition to version 1.2? When I upgraded the first project to 1.2, I replaced the cake directory, but I didn’t replace any of the files inside the app directory. Did I miss something critical in the upgrade?
THANKS MAN, u have no idea how many hairs i have lost related to this fckuing tinyint bug. i tried everything except the “clear cache” thing to get it working. but nothing seemed to work.
then i found ur post and whola…it actually works after clearing the cache….so THANKS AGAIN :D
Thaaaank you! Nice blog btw!
I know this entry was done over a year ago, but i’m (and maybe google search indexing) slow like that.
I want to say in the current version (1.3) caching is turned off if the debug level is any higher than 0.
On the issue of you upgrading your user roles from 2 roles to around 10, it almost sounds like your doing an enum without the enum. Ignoring the fact that cake doesn’t deal well with enums (and for some good reasons), I would say the best fix for your issue would have been to create another table for your roles. You can give them a logical value (like a string) so that it’s easier to read in your code and schema, while keeping the ability to scale to any number of roles. I know this can be a bit cumbersome, but I really feel as though it’s the best route