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
Last activity: Jan 19, 2018, 04:45 AM