MySQL Bugs: #29157: UPDATE, changed rows incorrect

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

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

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.

Should be 5.1.19

Thank you for the report.

Verified as described.

This may be a MySQL Server bug: in table 'i', it updates row (2, 2), though the value of 'j' does not actually change!

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.

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.

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.

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.

Pushed into 5.1.21-beta

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.

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.

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}

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.

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

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.

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.

Pushed into 5.1.24-rc

Pushed into 6.0.5-alpha

Noted in 5.1.24, 6.0.5 changelogs.

InnoDB could return an incorrect rows-updated value for UPDATE
statements.

Fix also noted in the 5.1.23-ndb-6.3.11 changelog.

The patch for this bug is not in 6.0

Merged into 6.0.6-alpha, according to Tim.

Noted in 6.0.6 (not 6.0.5) changelog.

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)

Push resulted from incorporation of InnoDB tree. No changes pertinent to this bug. Re-closing.

Pushed into mysql-next-mr (revid:alik@sun.com-20100524190136-egaq7e8zgkwb9aqi) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (pib:16)

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)

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)

Push resulted from incorporation of InnoDB tree. No changes pertinent to this bug.
Re-closing.

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)

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)

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)