MySQL 5.5.21 ON UPDATE CURRENT_TIMESTAMP incorrect

MySQLrow-modification-timetimestamp

As the title says, I'm having a few issues with 'ON UPDATE CURRENT_TIMESTAMP'. Here is the SQL definition for the table:

CREATE TABLE `judgements` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `parent_id` bigint(20) NOT NULL DEFAULT '0',
  `entry_id` bigint(20) NOT NULL,
  `group_id` bigint(20) NOT NULL,
  `comments` longtext,
  `rating` int(11) DEFAULT '0',
  `status` int(11) NOT NULL DEFAULT '1',
  `modified` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`,`entry_id`,`group_id`),
  KEY `group_id` (`group_id`),
  KEY `entry_id` (`entry_id`),
  KEY `status_id` (`status`),
  CONSTRAINT `entry_id` FOREIGN KEY (`entry_id`) REFERENCES `entries` (`id`),
  CONSTRAINT `group_id` FOREIGN KEY (`group_id`) REFERENCES `groups` (`id`),
  CONSTRAINT `status_id` FOREIGN KEY (`status`) REFERENCES `status` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9082 DEFAULT CHARSET=utf8;

Records are inserted using the WordPress database class ( $wpdb->insert ). All fields except id and modified are passed in an associative array to the function. This works as expected, the record is inserted and a timestamp is applied. The problem is that this timestamp is not always correct.
In seemingly random instances (around 15% of inserts) the timestamp will be anywhere up to 30 minutes in the future… I couldn't find any sort of pattern to this behaviour which makes the problem difficult to track down.
If it is relevant, this table experiences quite a high volume of inserts for several hours a day.
After searching Google and this site I am no closer to tracking down the problem. I did manage to find one forum post, from several years ago, where someone had a similar problem but the only reply was to file a bug report.
Has anyone else experienced this or have a theory as to why it is happening?

Thanks.

Best Answer

I don't think you are supposed to use DEFAULT (constant) with ON UPDATE.

According to the MySQL Docuementation

With an ON UPDATE CURRENT_TIMESTAMP clause and a constant DEFAULT clause, the column is automatically updated to the current timestamp and has the given constant default value.

CREATE TABLE t1
(
    ts TIMESTAMP DEFAULT 0 ON UPDATE CURRENT_TIMESTAMP
);

This might just be stomping over with the current datetime.

On the same page, it says

With an ON UPDATE CURRENT_TIMESTAMP clause but no DEFAULT clause, the column is automatically updated to the current timestamp. The default is 0 unless the column is defined with the NULL attribute, in which case the default is NULL. 

CREATE TABLE t1
 (
     ts TIMESTAMP ON UPDATE CURRENT_TIMESTAMP      -- default 0
 );
 CREATE TABLE t2
 (
     ts TIMESTAMP NULL ON UPDATE CURRENT_TIMESTAMP -- default NULL
 );

I suggest just removing the DEFAULT '0000-00-00 00:00:00'

Give it a Try !!!

Related Question