MySQL Bugs: #33846: UPDATE word:Wrong 'Changed rows' if InnoDB, unique key and no rows qualify WHERE

Bug #33846	UPDATE word:Wrong 'Changed rows' if InnoDB, unique key and no rows qualify WHERE
Submitted:	13 Jan 2008 16:38	Modified:	22 Jan 2008 15:00
Reporter:	Konstantin Osipov (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S3 (Non-critical)
Version:	5.1-BK	OS:	Any
Assigned to:	Vasil Dimov	CPU Architecture:	Any

Description:
If I create an InnoDB table with a UNIQUE key, and then run UPDATE with impossible WHERE, the row is updated nevertheless.
See 'How to repeat' for details.

Note, that there is also Bug #29157 UPDATE, changed rows incorrect.
I report it as a separate bug because my analysis suggests that the cause of the bug is different.

How to repeat:
mysql> create table t1 (a int unique);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t1 (a) values (2);
Query OK, 1 row affected (0.00 sec)

mysql> update t1 set a=2;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

Note the 'Changed' count.

InnoDB:

mysql> create table t1 (a int unique) engine=innodb;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t1 (a) values (2);
Query OK, 1 row affected (0.02 sec)

mysql> update t1 set a=2;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

-- Observe the wrong 'Changed' count.

Test case:

create table t1 (a int unique) engine=innodb;
insert into t1 (a) values (2);
update t1 set a=2;

Suggested fix:
table->write_set and table->read_set are not the same, so can_compare_record is
evaluated to 'false' in mysql_update.
Apparently InnoDB doesn not report the clustered key through which the table is being read in table->read_set.

Thank you for a problem report. Please, specify the exact version used. I was not able to repeat with 5.0.54, for example:

mysql> select version();
+------------------------------+
| version()                    |
+------------------------------+
| 5.0.54-enterprise-gpl-nt-log |
+------------------------------+
1 row in set (0.00 sec)

mysql> drop table t1;
Query OK, 0 rows affected (0.09 sec)

mysql> create table t1 (a int unique) engine=innodb;
Query OK, 0 rows affected (0.09 sec)

mysql> insert into t1 (a) values (2);
Query OK, 1 row affected (0.03 sec)

mysql> update t1 set a=2;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

5.1-bk

Verified just as described with latest 5.1-BK on Linux:

openxs@linux:~/dbs/5.1> 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 2
Server version: 5.1.23-rc Source distribution

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

mysql> drop table t1;
Query OK, 0 rows affected (0.01 sec)

mysql> create table t1 (a int unique) engine=InnoDB;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t1 (a) values (2);
Query OK, 1 row affected (0.00 sec)

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

IU think this is InnoDB bug then, not generic DML one. Please, correct me if I am wrong.

Vasil please look at this.
--Heikki

Konstantin, Valeriy,

I am unable to repeat this with the latest 5.1 BK:

mysql> create table t1 (a int unique) engine=innodb;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t1 (a) values (2);
Query OK, 1 row affected (0.00 sec)

mysql> update t1 set a=2;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

The fix for Bug#29157 has been applied on Jan 14 and has probably fixed this bug too - i.e. this is the same as Bug#29157.

Can you confirm that it does not happen with the latest BK (after Jan 14)?

Re-verified on lates 5.1-BK. Still the same problem:

openxs@linux:~/dbs/5.1> 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 1
Server version: 5.1.24-rc Source distribution

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

mysql> drop table t1;
Query OK, 0 rows affected (0.02 sec)

mysql> create table t1 (a int unique) engine=innodb;
Query OK, 0 rows affected (0.02 sec)

mysql> insert into t1 (a) values (2);
Query OK, 1 row affected (0.00 sec)

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

mysql> exit
Bye
openxs@linux:~/dbs/5.1> cd ~/work/mysql-5.1
openxs@linux:~/work/mysql-5.1> bk changes | head
ChangeSet@1.2651, 2008-01-19 12:39:49+01:00, dfischer@mysql.com +1 -0
  Raise version number after cloning 5.1.23-rc
...

Hmm... is the change of Jan 14, 2008 already in the official 5.1 tree?

Sorry for the confusion in my previous comment. I tested latest BK with latest InnoDB from the InnoDB SVN repo, assuming that the snapshot has been applied. Looks like it is not yet (we have to wait for Tim to do push?).

This is the patch that fixes Bug#29157:

--- cut ---
Index: handler/ha_innodb.cc
===================================================================
--- handler/ha_innodb.cc	(revision 2177)
+++ handler/ha_innodb.cc	(revision 2178)
@@ -3772,12 +3772,22 @@ ha_innobase::update_row(
 	}
 
 	innodb_srv_conc_exit_innodb(trx);
 
 	error = convert_error_code_to_mysql(error, user_thd);
 
+	if (error == 0 /* success */
+	    && uvect->n_fields == 0 /* no columns were updated */) {
+
+		/* This is the same as success, but instructs
+		MySQL that the row is not really updated and it
+		should not increase the count of updated rows.
+		This is fix for http://bugs.mysql.com/29157 */
+		error = HA_ERR_RECORD_IS_THE_SAME;
+	}
+
 	/* Tell InnoDB server that there might be work for
 	utility threads: */
 
 	innobase_active_small();
 
 	DBUG_RETURN(error);
--- cut ---

it is not present in the latest BK.

Latest BK without any modifications:

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

Latest BK with the above patch applied:

mysql> update t1 set a=2;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

Closing this bug as a duplicate of Bug#29157. The patch that fixes Bug#29157 also fixes the test case described in this bug report (see my previous comment).