Sample Header Ad - 728x90

MySQL 5.5.21 ON UPDATE CURRENT_TIMESTAMP incorrect

6 votes
1 answer
8610 views
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.
Asked by mbadger (63 rep)
May 30, 2013, 11:03 AM
Last activity: Jan 19, 2018, 04:45 AM