CakePHP Model’s created / modified gotcha!

By on June 15, 2007 in CakePHP, MySQL, PHP

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

PLAIN TEXT
SQL:
  1. ALTER TABLE `XXXX` CHANGE `created` `created` DATETIME NULL;

Phew! from then onwards everything worked fine.

About Abbas Ali

Abbas Ali is a Mechanical Engineer by education. He turned to programming and took it as a profession just after finishing his studies. He is fascinated equally by both machines and computers. He leads the team of dynamic programmers at SANIsoft and works as a Technology Manager. He is also an active developer on the Coppermine Picture Gallery team.
View all posts by Abbas Ali →

7 Responses to CakePHP Model’s created / modified gotcha!

  1. AndreEncinas May 23, 2008 at 7:28 pm #

    thanks for post,
    Andre

    Reply
  2. Ruben Ascencio November 17, 2008 at 10:30 am #

    The problem persisted even when I altered my table, I had to empty tmp/models and tmp/persistent in order to get it to work.

    hope this help someone else.

    Thanks for the post!

    Reply
  3. Abbas Ali November 21, 2008 at 5:20 pm #

    @Ruben: Cakephp stores the table structure in cache (so that a query is not needed every time to describe the table). If one alters the table, cakephp still takes the structure from cache and hence it is necessary to clear the cache from tmp/models for the table changes to take effect.

    However if you are running the application in debug level 2 then model cache is automatically cleared on each page load.

    Reply
  4. jctim January 28, 2009 at 8:35 pm #

    Thank you very mutch
    I have production mysql4 too
    you have been helped me :)

    Reply
  5. Daniel September 30, 2009 at 7:50 pm #

    Thank you for this tip, you've saved me a lot of potential headaches :)

    Reply
  6. jeremy December 3, 2009 at 2:47 pm #

    Thanks man,

    saved me a lot of time!

    Reply

Trackbacks/Pingbacks

  1. [PRONIQUE] CakePHP Developer Links - July 23, 2009

    [...] SANIsoft – PHP for E Biz» Blog Archive » CakePHP Model’s created / modified gotcha! [...]

Leave a Reply