Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
With the definition you uhave, the timestamp column will not get the current timestamp on insertion. Only on updates. And what do you mean with "up to 30 minutes in the future"? Do some rows have timestamps that are ahead of the system clock? –  ypercube May 30 '13 at 11:10
    
@ypercube - It all seems so obvious when it is pointed out to you! As you said, the CURRENT_TIMESTAMP is only applied on update which is where I think the problem lies, in which case the problem has been mentioned in other threads on here regarding the default timestamp value. I hadn't noticed it before because a timestamp resembling the correct current time was being applied to the modified column instead of '0000-00-00 00:00:00'. The problem was that the time would vary quite dramatically - sometimes the correct time, other times it would be 10 minutes ahead of the system clock. Thanks. –  mbadger May 30 '13 at 11:56
add comment

1 Answer 1

up vote 0 down vote accepted

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 !!!

share|improve this answer
    
Now that is interesting! Thanks for taking the time to explain the problem. The solution you provided does seem to remedy the problem. –  mbadger Jun 12 '13 at 10:41
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.