MySQL Bugs: #55206: ERROR 1032 (HY000): Can't find record in table during update (INNODB, foreign ke

Bug #55206	ERROR 1032 (HY000): Can't find record in table during update (INNODB, foreign ke
Submitted:	13 Jul 2010 10:09	Modified:	13 Jul 2010 12:03
Reporter:	William Adjei	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Errors	Severity:	S2 (Serious)
Version:	5.1.23	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
I am trying to perform a multiple table update where the tables are joined by foreign key constraints (INNODB storage engine). The update is failing with the error ERROR 1032 (HY000): Can't find record in <table> when I attempt to update the column with the constraint in both tables.

I have reproduced this in versions 5.0.x and 5.1.x running on both Linux and Mac OS.

How to repeat:
-- test case for "ERROR 1032 (HY000): Can't find record in ..."
DROP TABLE IF EXISTS b; DROP TABLE IF EXISTS a;
CREATE TABLE a(id INT PRIMARY KEY NOT NULL, a VARCHAR(20)) ENGINE=INNODB;
CREATE TABLE b(id INT PRIMARY KEY NOT NULL, b VARCHAR(20)) ENGINE=INNODB;
ALTER TABLE b ADD FOREIGN KEY (id) REFERENCES a(id) ON UPDATE CASCADE ON DELETE CASCADE;
INSERT INTO a VALUES(1,'foo'); INSERT INTO b VALUES(1,'bar');

-- the following update command (modifying the primary keys) results in the error message.
UPDATE a, b SET a.id=2,b.id=2 WHERE a.id=b.id AND a.id=1;

This is easy to repeat:

valeriy-kravchuks-macbook-pro:5.1 openxs$ 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.49-debug Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> DROP TABLE IF EXISTS b; DROP TABLE IF EXISTS a;
Query OK, 0 rows affected (0.04 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE a(id INT PRIMARY KEY NOT NULL, a VARCHAR(20)) ENGINE=INNODB;Query OK, 0 rows affected (0.12 sec)

mysql> CREATE TABLE b(id INT PRIMARY KEY NOT NULL, b VARCHAR(20)) ENGINE=INNODB;Query OK, 0 rows affected (0.18 sec)

mysql> ALTER TABLE b ADD FOREIGN KEY (id) REFERENCES a(id) ON UPDATE CASCADE ON DELETE CASCADE;
Query OK, 0 rows affected (0.10 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> INSERT INTO a VALUES(1,'foo'); INSERT INTO b VALUES(1,'bar');
Query OK, 1 row affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

mysql> 
mysql> UPDATE a, b SET a.id=2,b.id=2 WHERE a.id=b.id AND a.id=1;
ERROR 1032 (HY000): Can't find record in 'b'
mysql> select * from a, b WHERE a.id=b.id AND a.id=1;
+----+------+----+------+
| id | a    | id | b    |
+----+------+----+------+
|  1 | foo  |  1 | bar  |
+----+------+----+------+
1 row in set (0.00 sec)

mysql> explain select * from a, b WHERE a.id=b.id AND a.id=1;
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref   | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | a     | const | PRIMARY       | PRIMARY | 4       | const |    1 |       |
|  1 | SIMPLE      | b     | const | PRIMARY       | PRIMARY | 4       | const |    1 |       |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
2 rows in set (0.00 sec)

But looks like this limitation is documented in http://dev.mysql.com/doc/refman/5.1/en/update.html 

"If you use a multiple-table UPDATE  statement involving InnoDB tables for which there are foreign key constraints, the MySQL optimizer might process tables in an order that differs from that of their parent/child relationship. In this case, the statement fails and rolls back. Instead, update a single table and rely on the ON UPDATE capabilities that InnoDB provides to cause the other tables to be modified accordingly."

and workaround suggested works:

mysql> UPDATE a SET a.id=2 WHERE a.id=1;
Query OK, 1 row affected (0.37 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from a, b WHERE a.id=b.id;
+----+------+----+------+
| id | a    | id | b    |
+----+------+----+------+
|  2 | foo  |  2 | bar  |
+----+------+----+------+
1 row in set (0.00 sec)

Agreed - in this case the cascade feature resolves the issue, so not a bug

Thanks