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;

Holdback

Please post the folio table structure. Also, you have issues with your queries - no need to update all fields in on duplicate key section, only publication and productId need to be updated. And also - it is very bad idea to name your field as timestamp, because it is reserved word. – Magnific

@AndyW updated question with table structure – Holdback

@AndyW also didn't get you on "no need to update all fields in on duplicate key" perhaps your answer in the code will clear this up for me – Holdback

Are the values of publication and productID different than the corresponding columns in the duplicate row? Read the second comment under User Comments here: dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html "This does not work if nothing changes, presumably because MySQL doesn't run the dummy update at all". – Driedup

@Driedup that seems like the case, I've only publication and productId to be checked against and they are the only fields to be updated/inserted in the execute statement so that means it comes under dummy update, so as a workaround I'll use timestamp=NOW() in my query, please comment – Holdback

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.

Magnific

I'm afraid setting not null as well as renaming the field didn't work, also your point on providing all fields still not clear (your query is similar to mine, I'm only updating publication and productId p.s. both are composite unique keys) – Holdback

also I've used back ticks for mysql reserved word timestamp to get away with it, and it's working fine – Holdback

Did you try setting to NOT NULL or just guessing? I have a system with about 100 tables, most of them are using the auto-updatable timestamp columns - everything works like a charm. All those columns are NOT NULL, obviously, therefore it was the first thing I've noticed in your code. – Magnific

updated table structure after the Not Null, should I make the publication and productId NN as well? – Holdback

It all depends on your requirements to data model - setting some field to be NULL means you are declaring "this field sometimes can take unknown value and I'm OK with it". The auto-updatable timestamp field is a bit different story, as MySQL treats it 'magically', so some options like nullability could break the magic. – Magnific

Also, just came into my mind - check what data you pass to your execute call - if there are NULLs, the nullable fields will happily accept them, so you will have multiple records added instead the one record updated. – Magnific

execute call has only valid values, nulls are not allowed, the script only proceeds if the fields are not null, however, the timestamp is still not updating, I did update question with the changed structure (Update 2) – Holdback

I have updated the answer - pls see under bold UPDATE – Magnific

let's further refine, I won't need publication and productId to update because they are not changing, put simply ON DUPLICATE KEY UPDATE timestamp=CURRENT_TIMESTAMP – Holdback

cool, I'll accept the answer but before that, I didn't get on id=LAST_INSERT_ID(id) in the query, I assumed it's to return last insert id back to php in case if it was update only, but even after I've totally removed I still get last insert id's in my pdo regardless of insert or update. – Holdback

Seems you will need id=LAST_INSERT_ID(id) in order to populate last insert id when duplicate keys are inserted. You might want to check this answer on topic - #13623758 – Magnific

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

Driedup

It'll just add an unnecessary layer of definition, however, you got the answer on figuring it out that a dummy update was happening... – Holdback

Cool. I updated my response to make it easier for people to find that comment. – Driedup

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!

Narcho