MySQL Bugs: #23561: timestamp is automatically updated when nothing changed through INSERT...ON DUP

Bug #23561	timestamp is automatically updated when nothing changed through INSERT...ON DUP
Submitted:	23 Oct 2006 22:32	Modified:	7 Nov 2007 17:13
Reporter:	Joseph N.	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.28-BK, 5.0.18-max-log	OS:	Linux (Linux, RedHat/RHEL3)
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
This bug is related to bug id 19978 (http://bugs.mysql.com/bug.php?id=19978), but wanted to highlight the impact on tables with timestamps.

Consider a table with a timestamp (configured to be automatically updated on insert/updates), if an update is triggered through INSERT ... ON DUPLICATE KEY UPDATE, then the timestamp is always updated even when the underlying data has not changed. This behaviour is different when the UPDATE statement is used.

How to repeat:
Use same example in bug id 19978 but add a new column to the table; For example,

last_update   TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

Suggested fix:
The state of affected records must be the same whether UPDATE or INSERT...ON DUPLICATE KEY UPDATE is used. The latter statement should not update records that have no resulting change to its updated columns.

Thank you for a bug report. Verified just as described with 5.0.28-BK on Linux:

openxs@suse:~/dbs/5.0> bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.0.28-debug Source distribution

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

mysql> CREATE TABLE `price` (
    ->   `assetid` int(11) NOT NULL default '0',
    ->   `date` date NOT NULL default '0000-00-00',
    ->   `open` double default NULL,
    ->   `high` double default NULL,
    ->   `low` double default NULL,
    ->   `close` double default NULL,
    ->   `volume` bigint(20) default NULL,
    -> last_update   TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    -> PRIMARY KEY  (`assetid`,`date`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.12 sec)

mysql> INSERT INTO price (assetid,date,open,high,low,close,volume) VALUES
    -> (202690,'2006-05-18','334.25','334.25','334.25','334.25','1') ON DUPLICATE
    -> KEY UPDATE
    -> open='334.25',high='334.25',low='334.25',close='334.25',volume='1';
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO price (assetid,date,open,high,low,close,volume) VALUES  (202
690,'2006-05-18','334.25','334.25','334.25','334.25','1') ON DUPLICATE KEY UPDATE open='334.25',high='334.25',low='334.25',close='334.25',volume='1';
Query OK, 2 rows affected (0.01 sec)

mysql> select * from price\G
*************************** 1. row ***************************
    assetid: 202690
       date: 2006-05-18
       open: 334.25
       high: 334.25
        low: 334.25
      close: 334.25
     volume: 1
last_update: 2006-10-24 10:47:35
1 row in set (0.00 sec)

mysql> select now(), version();
+---------------------+--------------+
| now()               | version()    |
+---------------------+--------------+
| 2006-10-24 10:47:50 | 5.0.28-debug |
+---------------------+--------------+
1 row in set (0.00 sec)

mysql> UPDATE price SET
    -> open='334.25',high='334.25',low='334.25',close='334.25',volume='1' WHERE
    -> assetid=202690 AND date='2006-05-18';
Query OK, 0 rows affected (0.01 sec)
Rows matched: 1  Changed: 0  Warnings: 0

mysql> select now(), version();
+---------------------+--------------+
| now()               | version()    |
+---------------------+--------------+
| 2006-10-24 10:48:08 | 5.0.28-debug |
+---------------------+--------------+
1 row in set (0.00 sec)

mysql> select * from price\G
*************************** 1. row ***************************
    assetid: 202690
       date: 2006-05-18
       open: 334.25
       high: 334.25
        low: 334.25
      close: 334.25
     volume: 1
last_update: 2006-10-24 10:47:35
1 row in set (0.00 sec)

Test case:

CREATE TABLE `price` (
`assetid` int(11) NOT NULL default '0',
`date` date NOT NULL default '0000-00-00',
`open` double default NULL,
`high` double default NULL,
`low` double default NULL,
`close` double default NULL,
`volume` bigint(20) default NULL,
`last_update` TIMESTAMP DEFAULT CURRENT_TIMESTAMP 
  ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY  (`assetid`,`date`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO price (assetid,date,open,high,low,close,volume) 
VALUES (202690,'2006-05-18','334.25','334.25','334.25','334.25','1') 
ON DUPLICATE KEY UPDATE
  open='334.25',high='334.25',low='334.25',close='334.25',volume='1';

SELECT now();
SELECT * FROM price; 

INSERT INTO price (assetid,date,open,high,low,close,volume) 
VALUES (202690,'2006-05-18','334.25','334.25','334.25','334.25','1') 
ON DUPLICATE KEY UPDATE
  open='334.25',high='334.25',low='334.25',close='334.25',volume='1';

SELECT now();
SELECT * FROM price;

UPDATE price 
SET open='334.25',high='334.25',low='334.25',close='334.25',volume='1' 
WHERE assetid=202690 AND date='2006-05-18';

SELECT now();
SELECT * FROM price;

Most likely a duplicate of bug #19978, not reproducible on recent 5.0/5.1 trees. 

Note that the verifier used 5.0.28-BK whereas the fix for bug #19978 appeared only in 5.0.36.