Bug #29310 NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE updates with NO changes
Submitted: 22 Jun 2007 23:02 Modified: 27 Jul 2007 5:01
Reporter: Jacob Boswell Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.1 OS:Any
Assigned to: Evgeny Potemkin CPU Architecture:Any
Tags: bfsm_2007_07_19

[22 Jun 2007 23:02] Jacob Boswell
Description:
In 5.1 an InnoDB table with a timestamp column set to NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP will update the timestamp column even if an UPDATE does not actually result in a change. This is different functionality from 5.0. Tested on FreeBSD 6 and RHEL 4, MyISAM doesn't show same problem.

How to repeat:
mysql> show create table one
    -> ;
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                     |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| one   | CREATE TABLE `one` (
  `oneId` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `valcol` varchar(100) NOT NULL,
  PRIMARY KEY (`oneId`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=latin1 | 
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select * from one;
+-------+---------------------+--------+
| oneId | updated             | valcol |
+-------+---------------------+--------+
|     2 | 2007-06-22 16:55:18 | bla    | 
+-------+---------------------+--------+
1 row in set (0.00 sec)

mysql> update one set valcol = 'bla';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

mysql> select * from one;
+-------+---------------------+--------+
| oneId | updated             | valcol |
+-------+---------------------+--------+
|     2 | 2007-06-22 16:55:18 | bla    | 
+-------+---------------------+--------+
1 row in set (0.00 sec)

mysql> alter table one engine innodb;
Query OK, 1 row affected (0.04 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> select * from one;
+-------+---------------------+--------+
| oneId | updated             | valcol |
+-------+---------------------+--------+
|     2 | 2007-06-22 16:55:18 | bla    | 
+-------+---------------------+--------+
1 row in set (0.00 sec)

mysql> update one set valcol = 'bla';
Query OK, 1 row affected (0.03 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from one;
+-------+---------------------+--------+
| oneId | updated             | valcol |
+-------+---------------------+--------+
|     2 | 2007-06-22 17:01:47 | bla    | 
+-------+---------------------+--------+
1 row in set (0.00 sec)

Suggested fix:
Make InnoDB tables function the same as MyISAM, or have some kind of setting to choose the functionality.
[24 Jun 2007 12:09] Peter Laursen
This is a VERY CRITICAL bug for me!

Please tell the exact server version where you experience this!

I think severity should be raised to S1 or at least S2 as with certain schemas/applications this would totally spoil data integrity in an irreversible manner!

(I just verified with 6.0.0 and MyISAM, InnoDB and Falcon that it works as expected).  I have no option here to test with 5.1!

Peter
(non-mysql person)
[24 Jun 2007 14:11] Jacob Boswell
Tested on version 5.1.16 and 5.1.19
[25 Jun 2007 11:38] Hartmut Holzgraefe
Actual problem seems to be that in current 5.1 the UPDATE reports

  Rows matched: 1  Changed: 1  Warnings: 0

even though the actual row content did *not* change
whereas in 5.0 i get 

  Rows matched: 1  Changed: 0  Warnings: 0

as expected.

I also tested this with a table with only integer
columns to rule out charset and timestamp side effects
and got the same result.
[25 Jun 2007 13:52] Heikki Tuuri
This may be a MySQL Server bug. There is already another bug report that MySQL reports the changed rows as > 0 though no column value changed in an UPDATE.
[7 Jul 2007 18:35] Bugs System
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/30465

ChangeSet@1.2534, 2007-07-07 17:50:27+04:00, evgen@moonbone.local +4 -0
  Bug#29310: An InnoDB table was updated when the data wasn't actually changed.
  
  When a table is being updated it has two set of fields - fields required for
  checks of conditions and fields to be updated. A storage engine is allowed
  to not to retrieve columns marked for update. Due to this fact records can't
  be compared to see whether the data is changed or not. This makes the server
  always updating records independently of data change.
  
  Now when server sees that a handle isn't going to retrieve write-only fields
  then all of such fields are marked as read to force handler to retrieve
  them.
[7 Jul 2007 19:09] Peter Laursen
this changelog note is HORRIBLE in my opinion!  Nobody understands how this affects users.   This is writeen by a coder for coders only!

Changelogs should tell that the missing functionality in 5.1.x of a TIMESTAMP ... ON UPDATE CURRENT_TIMESTAMP was restored (and whatever other usablity features it may bhave affected) 

please answer: WAS the missing functionality in 5.1.x of a TIMESTAMP ... ON UPDATE CURRENT_TIMESTAMP restored  ??
[7 Jul 2007 19:15] Peter Laursen
And if you can confirm this and if can tell from which release this will be fixed, I would like to add the information here:
http://webyog.com/faq/35_153_en.html
[7 Jul 2007 19:17] Peter Laursen
And if you can confirm this and if can tell from which release this will be fixed, I would like to add the information here:
http://webyog.com/faq/35_153_en.html
[7 Jul 2007 20:02] Bugs System
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/30489

ChangeSet@1.2534, 2007-07-07 02:02:09+04:00, evgen@moonbone.local +4 -0
  Bug#29310: An InnoDB table was updated when the data wasn't actually changed.
  
  When a table is being updated it has two set of fields - fields required for
  checks of conditions and fields to be updated. A storage engine is allowed
  to not to retrieve columns marked for update. Due to this fact records can't
  be compared to see whether the data is changed or not. This makes the server
  always updating records independently of data change.
  
  Now when server sees that a handle isn't going to retrieve write-only fields
  then all of such fields are marked as read to force handler to retrieve
  them.
[7 Jul 2007 20:51] Bugs System
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/30491

ChangeSet@1.2534, 2007-07-08 00:17:35+04:00, evgen@moonbone.local +4 -0
  Bug#29310: An InnoDB table was updated when the data wasn't actually changed.
  
  When a table is being updated it has two set of fields - fields required for
  checks of conditions and fields to be updated. A storage engine is allowed
  not to retrieve columns marked for update. Due to this fact records can't
  be compared to see whether the data has been changed or not. This makes the
  server always update records independently of data change.
  
  Now when server sees that a handle isn't going to retrieve write-only fields
  then all of such fields are marked as to be read to force the handler to
  retrieve them.
[8 Jul 2007 14:16] Bugs System
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/30502

ChangeSet@1.2534, 2007-07-08 18:13:04+04:00, evgen@moonbone.local +4 -0
  Bug#29310: An InnoDB table was updated when the data wasn't actually changed.
  
  When a table is being updated it has two set of fields - fields required for
  checks of conditions and fields to be updated. A storage engine is allowed
  not to retrieve columns marked for update. Due to this fact records can't
  be compared to see whether the data has been changed or not. This makes the
  server always update records independently of data change.
  
  Now when an auto-updatable timestamp field is present and server sees that
  a table handle isn't going to retrieve write-only fields then all of such
  fields are marked as to be read to force the handler to retrieve them.
[20 Jul 2007 23:46] Bugs System
Pushed into 5.1.21-beta
[27 Jul 2007 5:01] Paul Dubois
Noted in 5.1.21 changelog.

For updates to InnoDB tables, a TIMESTAMP column with the ON UPDATE
CURRENT_TIMESTAMP attribute could be updated even when no values
actually changed.