Bug #29157 UPDATE, changed rows incorrect
Submitted: 17 Jun 2007 5:49 Modified: 19 Jun 2010 18:05
Reporter: KimSeong Loh (Candidate Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S3 (Non-critical)
Version:6.0 OS:Any
Assigned to: CPU Architecture:Any
Tags: fixed in pre-v6, innodb, qc, UPDATE, v6

[17 Jun 2007 5:49] KimSeong Loh
Description:
The number of changed rows by UPDATE statement is reported wrongly with InnoDB storage engine.

Problem does not occur in MyISAM.
Problem does not occurs in version 5.0.41

How to repeat:
mysql> create table m (i int, j int) engine=myisam;
Query OK, 0 rows affected (0.08 sec)

mysql> insert m value (1,1),(2,2);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> update m set j=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 2  Changed: 1  Warnings: 0

mysql> create table i (i int, j int) engine=innodb;
Query OK, 0 rows affected (0.11 sec)

mysql> insert i value (1,1),(2,2);
Query OK, 2 rows affected (0.11 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> update i set j=2;
Query OK, 2 rows affected (0.09 sec)
Rows matched: 2  Changed: 2  Warnings: 0
[18 Jun 2007 5:57] Valeriy Kravchuk
Thank you for a problem report. Are you sure you had used version 5.1.9? If yes, please, check with a newer version, 5.1.19, and inform about the results.
[18 Jun 2007 6:00] KimSeong Loh
Should be 5.1.19
[18 Jun 2007 6:58] Sveta Smirnova
Thank you for the report.

Verified as described.
[18 Jun 2007 13:20] Heikki Tuuri
This may be a MySQL Server bug: in table 'i', it updates row (2, 2), though the value of 'j' does not actually change!
[28 Jun 2007 10:55] Georgi Kodinov
Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Whether the number of rows updated is different from the number of rows found is engine specific. 
Some storage engines are able to read just a subset of the columns when doing an update (InnoDB is one such engine). So the server can't reliably check if a row will not be updated. And because of that it will always have number of rows updated equal to the number of rows found.
The reason that this worked differently in 5.0 is the fact that in 5.1 the server passes information for the columns it needs to read to InnoDB and hence not all the columns are read anymore.
In 5.0 such information was not passed down to the InnoDB storage engine, so it always returned all the columns and a check wether a rows is updated was possible.
See bug #29310 for a related problem.
[28 Jun 2007 11:03] Georgi Kodinov
An easy way to work around this problem is by rewriting the update so it finds only the rows that actually need update :
update i set j=2 where j <> 2;

This will change the number of rows found, but the number of rows updated will be what it used to be in 5.0.
[28 Jun 2007 11:05] Sergei Golubchik
You're saying that a feature is removed, cannot be relied into anymore.
I don't think it's a good idea.
Instead, we need to push the check down to the storage engine. For example, let storage engine to return HA_ERR_RECORD_IS_THE_SAME in such a case, and don't increment the counter of updated rows.
[28 Jun 2007 13:08] 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/29856

ChangeSet@1.2519, 2007-06-28 16:07:55+03:00, gkodinov@magare.gmz +7 -0
  Bug #29157: UPDATE, changed rows incorrect
  
  Sometimes the number of really updated rows (with changed
  column values) cannot be determined at the server level
  alone (e.g. if the storage engine does not return enough
  column values to verify that). So the only dependable way
  in such cases is to let the storage engine return that
  information if possible.
  Fixed the bug at server level by providing a way for the 
  storage engine to return information about wether it 
  actually updated the row or the old and the new column 
  values are the same. It can do that by returning 
  HA_ERR_RECORD_IS_THE_SAME in ha_update_row().
  Note that each storage engine may choose not to try to
  return this status code, so this behaviour remains 
  storage engine specific.
[1 Jul 2007 19:58] Bugs System
Pushed into 5.1.21-beta
[2 Jul 2007 5:45] Georgi Kodinov
This bug cannot be effectively resolved in the server, because as of 5.1 not all the columns are read when reading an InnoDB row. Thus the server doesn't know whether a row is really updated or not.
That's why I'm moving back to "Verified" and reassigning to InnoDB : my fix only makes a provision for the storage engine to supply information if the row is really updated. 
If the row's new values are the same as the old ones then the storage engine must return error code HA_ERR_RECORD_IS_THE_SAME from the call to ha_update_row(). This is the same as success, but  instructs the server that the row is not really updated and it should not increase the count of updated rows.
[2 Jul 2007 13:48] Heikki Tuuri
Hmm... why does not MySQL-5.1 ask all updated columns from the engine? We might save some CPU time by NOT updating rows where the column does not change.

Of course, we can also do that comparison inside the engine. But it would be more efficient in the SQL interpreter: we know that a = a + 1 always does change the value, while a = 10 does not necessarily.
[2 Jul 2007 13:56] Sergei Golubchik
It doesn't ask all updated columns, in the case like

  UPDATE ... SET a=5 WHERE b=6

in this case, read_set={b}, write_set={a}
[6 Jul 2007 15:29] Sergei Golubchik
forgot to add: in cases when write_set is a subset of a read_set, MySQL will still check whether the row was changes, just as it was doing before. It'll rely on storage engine to check it only when read_set doesn't include all the necessary columns.
[11 Dec 2007 16:37] Heikki Tuuri
Vasil,

please look how hard this is to fix inside InnoDB.

How can we communicate to MySQL if some column value for a row inside InnoDB changes or not in an UPDATE?

Regards,

Heikki
[12 Dec 2007 18:45] Vasil Dimov
Thanks, Georgi!

I have just created a patch that makes InnoDB's ha_innobase::update_row() to return HA_ERR_RECORD_IS_THE_SAME if no columns have been updated.
[24 Jan 2008 8:52] Timothy Smith
Patch queued to 5.1-build.  NOT yet in 6.0 (5.1-snapshot is null merged into 6.0).  Please return to "Patch approved" after documenting until 6.0 snapshot is available.
[25 Feb 2008 16:00] Bugs System
Pushed into 5.1.24-rc
[25 Feb 2008 16:06] Bugs System
Pushed into 6.0.5-alpha
[20 Mar 2008 19:47] Paul DuBois
Noted in 5.1.24, 6.0.5 changelogs.

InnoDB could return an incorrect rows-updated value for UPDATE
statements.
[30 Mar 2008 9:37] Jon Stephens
Fix also noted in the 5.1.23-ndb-6.3.11 changelog.
[11 Jun 2008 19:14] Konstantin Osipov
The patch for this bug is not in 6.0
[24 Jun 2008 21:09] Calvin Sun
Merged into 6.0.6-alpha, according to Tim.
[25 Jun 2008 1:31] Paul DuBois
Noted in 6.0.6 (not 6.0.5) changelog.
[5 May 2010 15:16] Bugs System
Pushed into 5.1.47 (revid:joro@sun.com-20100505145753-ivlt4hclbrjy8eye) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[6 May 2010 3:01] Paul DuBois
Push resulted from incorporation of InnoDB tree. No changes pertinent to this bug. Re-closing.
[28 May 2010 5:56] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100524190136-egaq7e8zgkwb9aqi) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (pib:16)
[28 May 2010 6:25] Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20100524190941-nuudpx60if25wsvx) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[28 May 2010 6:53] Bugs System
Pushed into 5.5.5-m3 (revid:alik@sun.com-20100524185725-c8k5q7v60i5nix3t) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[29 May 2010 22:48] Paul DuBois
Push resulted from incorporation of InnoDB tree. No changes pertinent to this bug.
Re-closing.
[17 Jun 2010 11:58] Bugs System
Pushed into 5.1.47-ndb-7.0.16 (revid:martin.skold@mysql.com-20100617114014-bva0dy24yyd67697) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[17 Jun 2010 12:37] Bugs System
Pushed into 5.1.47-ndb-6.2.19 (revid:martin.skold@mysql.com-20100617115448-idrbic6gbki37h1c) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[17 Jun 2010 13:24] Bugs System
Pushed into 5.1.47-ndb-6.3.35 (revid:martin.skold@mysql.com-20100617114611-61aqbb52j752y116) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)