timestamp autoupdate not working with ON DUPLICATE KEY UPDATE (PDO)

Question

I've a table folio with timestamp set to auto update.

CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

PDO statement in PHP is not causing the timestamp to update.

$statement = $this->connection->prepare("
INSERT INTO folio(publication, productId) 
VALUES(:publication, :productId) 
ON DUPLICATE KEY UPDATE 
id=LAST_INSERT_ID(id), publication=:publication, productId=:productId");

following manual approach works but is not desirable.

$statement = $this->connection->prepare(
"INSERT INTO folio(publication, productId) 
VALUES(:publication, :productId) 
ON DUPLICATE KEY UPDATE 
id=LAST_INSERT_ID(id), publication=:publication, productId=:productId, timestamp=NOW()");

Update: Here's my folio table structure

CREATE TABLE `folio` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `publication` varchar(255) DEFAULT NULL,
  `productId` varchar(255) DEFAULT NULL,
  `timestamp` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_folio` (`publication`,`productId`)
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8;

Update 2: Table structure after setting timestamp to not null

CREATE TABLE `folio` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `publication` varchar(255) DEFAULT NULL,
  `productId` varchar(255) DEFAULT NULL,
  `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_folio` (`publication`,`productId`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

Answer 1

As far as I can see, the problem with your queries might be because you made timestamp field nullable

`timestamp` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

Try making it NOT NULL - since you have valid default value for it, MySQL won't complain you have not provided the value in the query:

`timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

Also, try renaming timestamp field to something more sane, e.g.:

`changed_timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

Also, as mentioned in my comments - you don't need to provide all fields in ON DUPLICATE KEY section, but only data fields:

INSERT INTO folio(publication, productId) 
VALUES(:publication, :productId) 
ON DUPLICATE KEY UPDATE 
    publication=:publication, 
    productId=:productId

It is because if MySQL detects you have a duplicate key condition, it won't insert new row, but update the existing one, therefore id column must be left intact.

UPDATE

Seems that not updating the timestamp column is a documented behavior - MySQL manual for TIMESTAMP columns

Citing the needed paragraph:

If the column is auto-updated, it is automatically updated to the current timestamp when the value of any other column in the row is changed from its current value. The column remains unchanged if all other columns are set to their current values. To prevent the column from updating when other columns change, explicitly set it to its current value. To update the column even when other columns do not change, explicitly set it to the value it should have (for example, set it to CURRENT_TIMESTAMP).

So, you are meeting all conditions :) - when you insert the record the timestamp should be populated correctly. But when you provide the duplicate values on order to update the timestamp, MySQL sees you set the values that already exist in the row (otherwise it wouldn't be duplicate), therefore it does not update the timestamp column.

So, the solution is straightforward and already found by you - update the timestamp column explicitly whenever you provide the duplicate values, e.g.:

INSERT INTO folio(publication, productId) 
VALUES(:publication, :productId) 
ON DUPLICATE KEY UPDATE 
    `timestamp` = NOW()

Anyway, making timestamp NOT NULL won't hurt.

Answer 2

If the new INSERT values are the same as the old values in the duplicate row then apparently MySQL does not perform the UPDATE, therefore the ON UPDATE CURRENT_TIMESTAMP is not fired. :(

So, as an awkward workaround, you can add a dummy field to your table, forcing the UPDATE to occur (in the case of a duplicate id):

$statement = $this->connection->prepare("
    INSERT INTO folio(publication, productId) 
      VALUES(:publication, :productId) 
      ON DUPLICATE KEY UPDATE 
      id=LAST_INSERT_ID(id), publication=:publication, productId=:productId,
      dummy = NOT dummy
");

...as suggested in the User Comments here: http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html

Answer 3

it's just

insert into your_table (a, b) values (?, ?) on duplicate key update created = current_timestamp

For better or worse, MySQL does not , in any way, default-update timestamps in the case of insert-uniquely type calls.

• it does not matter which of the three, insert-on-dupe, replace, insert-ignore you are using

• it does not make any difference whatsoever which settings or qualities you use on the fields, table or anything else

It just has to be done manually by adding created = current_timestamp on the end.

Unfortunately that's it!