| 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: | |
| Category: | MySQL Server | Severity: | S3 (Non-critical) |
| Version: | 5.1 | OS: | Any |
| Assigned to: | Evgeny Potemkin | CPU Architecture: | Any |
| Tags: | bfsm_2007_07_19 | ||
[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.
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.