MySQL Bugs: #28904: ON UPDATE CURRENT_TIMESTAMP does not work properly with ON DUPLICATE KEY

Bug #28904	ON UPDATE CURRENT_TIMESTAMP does not work properly with ON DUPLICATE KEY
Submitted:	5 Jun 2007 19:04	Modified:	23 Jun 2007 8:44
Reporter:	Andrei Nazarenko	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S2 (Serious)
Version:	5.0.41	OS:	Linux (SuSE 10.1)
Assigned to:	Evgeny Potemkin	CPU Architecture:	Any
Tags:	ON DUPLICATE KEY, ON UPDATE CURRENT_TIMESTAMP, timestamp

Description:
Wrong TIMESTAMP values that have the ON UPDATE CURRENT_TIMESTAMP property are returned after executing repeating *identical* INSERT ... ON DUPLICATE KEY UPDATE statements.

Normally, if simple UPDATE tries set the field to the value that is already there, no update is performed. You get the message: "Rows matched: xx  Changed: 0  Warnings: 0". From this point of view, the TIMESTAMP fields with the "ON UPDATE CURRENT_TIMESTAMP" property should not be updated as well. This is true for simple UPDATE statements. However, during INSERT ... ON DUPLICATE KEY UPDATE the TIMESTAMP fields *do* get updated. What is much worse is that you can't see that easily! This happens if you had issued identical SELECT statements before and after the "INSERT .. ON DUPLICATE". It seems that the cached (older) values are returned for a repeating SELECT statement (tfor the SELECT that does not change the syntax). If SELECT statement changes in syntax (not in nature) then fresh data is returned.

Last note: it seems that the affected rows number is still returned wrong for the "INSERT ... ON DUPLICATE KEY UPDATE" statements.

How to repeat:
CREATE TABLE `test` (
`stamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
 `id` int(10) unsigned NOT NULL default '0',
 `data` varchar(255) NOT NULL,
 PRIMARY KEY  (`id`)
) ENGINE=MyISAM;

INSERT INTO `test` (`id`, `data`) VALUES (1, 'somedata')
   ON DUPLICATE KEY UPDATE `data` = 'somedata';

-- The result:  Query OK, 1 row affected (0.00 sec)  -- fine so far

SELECT * FROM `test`;

+---------------------+----+----------+
| stamp               | id | data     |
+---------------------+----+----------+
| 2007-06-05 20:58:02 |  1 | somedata |
+---------------------+----+----------+
1 row in set (0.00 sec)

-- Now repeat the same INSERT statement after a couple of seconds:

INSERT INTO `test` (`id`, `data`) VALUES (1, 'somedata')
   ON DUPLICATE KEY UPDATE `data` = 'somedata';

-- Result: Query OK, 0 rows affected (0.00 sec)  -- seems to be logical,
-- nothing should be updated... let's see:

SELECT * FROM `test`;
+---------------------+----+----------+
| stamp               | id | data     |
+---------------------+----+----------+
| 2007-06-05 20:58:02 |  1 | somedata |
+---------------------+----+----------+
1 row in set (0.00 sec)

-- Indeed... the same old data, the same old timestamp.
-- But how about this query? It shouldn't give different result, should it?

SELECT `stamp`, `id`, `data` FROM `test`;
+---------------------+----+----------+
| stamp               | id | data     |
+---------------------+----+----------+
| 2007-06-05 20:59:00 |  1 | somedata |
+---------------------+----+----------+
1 row in set (0.00 sec)

-- Now, suddenly, we are getting the fresh (updated) timestamp value!
-- Try executing the old SELECT once again... we get the old timestamp!!!

SELECT * FROM `test`;
+---------------------+----+----------+
| stamp               | id | data     |
+---------------------+----+----------+
| 2007-06-05 20:58:02 |  1 | somedata |
+---------------------+----+----------+
1 row in set (0.00 sec)

-- You could flush the table with the FLUSH TABLE statement
-- Then the results change even for the old SELECT:

FLUSH TABLE `test`;
Query OK, 0 rows affected (0.00 sec)

SELECT * FROM `test`;
+---------------------+----+----------+
| stamp               | id | data     |
+---------------------+----+----------+
| 2007-06-05 21:00:24 |  1 | somedata |
+---------------------+----+----------+
1 row in set (0.00 sec)

-- I hope this illustrates the point clearly. On the last point
-- The number of rows affected by this statement on our existing table:

INSERT INTO `test` (`id`, `data`) VALUES (1, 'somedata')
      ON DUPLICATE KEY UPDATE `data` = 'changeddata';
Query OK, 2 rows affected (0.00 sec)

-- Why is it *2* rows affected ? There was and still is only 1 row in my table. Is this a bug or a feature?

Suggested fix:
There is no 'self-made' fix for this, if you want to keep the behaviour of "INSERT ... ON DUPLCATE KEY UPDATE" consistent with that of simple UPDATE statement.

Thank you for the bug report. I was able to repeat the  2 rows affected
part when actually only one row exist, if is that the intended behavior
should be documented.

[miguel@light 5.0]$ bin/mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 5.0.44-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE TABLE `test` (
    -> `stamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
    ->  `id` int(10) unsigned NOT NULL default '0',
    ->  `data` varchar(255) NOT NULL,
    ->  PRIMARY KEY  (`id`)
    -> ) ENGINE=MyISAM;
Query OK, 0 rows affected (0.00 sec)

mysql> 
mysql> INSERT INTO `test` (`id`, `data`) VALUES (1, 'somedata')
    ->    ON DUPLICATE KEY UPDATE `data` = 'somedata';
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM `test`;
+---------------------+----+----------+
| stamp               | id | data     |
+---------------------+----+----------+
| 2007-06-05 17:47:23 |  1 | somedata | 
+---------------------+----+----------+
1 row in set (0.00 sec)

mysql> INSERT INTO `test` (`id`, `data`) VALUES (1, 'somedata')
    ->    ON DUPLICATE KEY UPDATE `data` = 'somedata';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM `test`;
+---------------------+----+----------+
| stamp               | id | data     |
+---------------------+----+----------+
| 2007-06-05 17:47:51 |  1 | somedata | 
+---------------------+----+----------+
1 row in set (0.00 sec)

mysql> SELECT `stamp`, `id`, `data` FROM `test`;
+---------------------+----+----------+
| stamp               | id | data     |
+---------------------+----+----------+
| 2007-06-05 17:47:51 |  1 | somedata | 
+---------------------+----+----------+
1 row in set (0.00 sec)

mysql> SELECT * FROM `test`;
+---------------------+----+----------+
| stamp               | id | data     |
+---------------------+----+----------+
| 2007-06-05 17:47:51 |  1 | somedata | 
+---------------------+----+----------+
1 row in set (0.00 sec)

mysql> FLUSH TABLE `test`;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM `test`;
+---------------------+----+----------+
| stamp               | id | data     |
+---------------------+----+----------+
| 2007-06-05 17:47:51 |  1 | somedata | 
+---------------------+----+----------+
1 row in set (0.00 sec)

mysql> INSERT INTO `test` (`id`, `data`) VALUES (1, 'somedata')
    ->       ON DUPLICATE KEY UPDATE `data` = 'changeddata';
Query OK, 2 rows affected (0.00 sec)

So basically, you cannot recreate the test case with the wrong TIMESTAMP behaviour as far as I can see :-(

I wonder if that has been fixed somewhere between the current production 5.0.41 and your development version...

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/28531

ChangeSet@1.2529, 2007-06-12 00:42:39+04:00, evgen@moonbone.local +3 -0
  Bug#28904: INSERT .. ON DUPLICATE was silently updating rows when it shouldn't.
  
  When the INSERT .. ON DUPLICATE KEY UPDATE have to update a matched row but
  the new data is the very same as in the record then it returns as there is
  no row was inserted nor updated, but nevertheless the row was silently updated.
  This leads to a situation when zero updated rows reported but the data was
  actually changed.
  
  Now the write_record function updates a row only if new data differs from
  that in record.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/28539

ChangeSet@1.2529, 2007-06-12 01:29:28+04:00, evgen@moonbone.local +3 -0
  Bug#28904: INSERT .. ON DUPLICATE was silently updating rows when it shouldn't.
  
  When the INSERT .. ON DUPLICATE KEY UPDATE has to update a matched row but
  the new data is the same as in the record then it returns as if
  no rows were inserted nor updated. Nevertheless the row was silently
  updated. This leads to a situation when zero updated rows are reported 
  in the case when data has actually been changed.
  
  Now the write_record function updates a row only if new data differs from
  that in the record.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/28540

ChangeSet@1.2529, 2007-06-12 01:41:23+04:00, evgen@moonbone.local +3 -0
  Bug#28904: INSERT .. ON DUPLICATE was silently updating rows when it shouldn't.
  
  When the INSERT .. ON DUPLICATE KEY UPDATE has to update a matched row but
  the new data is the same as in the record then it returns as if
  no rows were inserted or updated. Nevertheless the row is silently
  updated. This leads to a situation when zero updated rows are reported 
  in the case when data has actually been changed.
  
  Now the write_record function updates a row only if new data differs from
  that in the record.

It should be noted somewhere in the manual that the INSERT .. UPDATE 
returns 1 for a row that was inserted and 2 for a row that was updated (data was actually changed).

Pushed into 5.1.20-beta

Pushed into 5.0.44

Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html

Documented bugfix in 5.0.44 and 5.1.20 changelogs.

See http://bugs.mysql.com/bug.php?id=60489.