CakePHP Model’s created / modified gotcha!

OK! this post should more appropriately be named "Disparate behavior of DATETIME field between MySql versions" and is none of CakePHP's fault in fact no one is to blame other than perhaps MySQL and myself but such things happen to almost everyone once in a while.

CakePHP does a lot of things 'automagically' - one of those things is recording the DATETIME of record being 'created' and record being 'modified'. For this to happen all you need is to have a field called created and another field called modified in the table. Both these fields should be of the type DATETIME

Working on this new project I noticed something strange - while the necessary magic was happening on my local server the remote server was recording the fields as 0000-00-00 00:00:00 .

First thing I did was to check MySQL versions - Local mysql server version 5.0.33, Production server version 4.1.11. Oops! I had slipped up a bit on that one but I still did not understand what the problem was.

After some debugging and scratching my head I found the cause.

MySQL 4.1.11 adds a default value of 0000-00-00 00:00:00 to DATETIME field if it is defined NOT NULL, where as MySQL 5.0.33 does not do this and CakePHP relies on the field not having a default to automagically populate it. Having understood this the solution was easy - allow the created and modified fields to be NULL. In SQL this would be

Phew! from then onwards everything worked fine.