MySQL Bugs: #57373: Multi update+InnoDB reports ER_KEY_NOT

Bug #57373	Multi update+InnoDB reports ER_KEY_NOT_FOUND if a table is updated twice
Submitted:	11 Oct 2010 14:14	Modified:	2 Mar 2011 15:25
Reporter:	Matthias Leich	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:	5.1, 5.5	OS:	Any
Assigned to:	Jørgen Løland	CPU Architecture:	Any
Tags:	RQG

Description:
The problem was found when testing with RQG
- 32 threads
- derivate of the WL5004_* grammar
  heavy concurrent DML and DDL

Result on mysql-5.5-runtime last pull 2010-10-11
+ patches for avoiding
  Bug#57345 btr_pcur_store_position abort for
  load with concurrent lock/unlock tables
(problem was already visible without bug#57345 patch)
-----------------------------------------------------
frequent
 Query: UPDATE t1 AS A NATURAL JOIN t1 B SET A. `pk` = 7 , B. `pk` = 1 failed: 1032 Can't find record in 't1'

RQG SQL grammar:
----------------
query:
   # This command is harmless UPDATE t1 AS A SET A. `pk` = _digit |
   UPDATE t1 AS A NATURAL JOIN t1 B SET A. `pk` = _digit , B. `pk` = _digit |
   CREATE TABLE IF NOT EXISTS t1 ( pk INT, col_int INT, PRIMARY KEY (pk)) ENGINE = InnoDB ; ALTER TABLE t1 COMMENT = 'Hello' ; INSERT INTO t1 SELECT _digit , _digit ; COMMIT; SELECT SLEEP(RAND()); DROP TABLE t1;

The essential seem to be that
- the UPDATE modifies the same table twice
- concurrent ALTER table happen
- InnoDB is involved

How to repeat:
perl runall.pl --gendata=conf/runtime/WL5004_data.zz \
--duration=900 --queries=3000 \
--reporter=Deadlock,Backtrace,Shutdown \
--threads=32 --basedir=<patch to MySQL root> \
--mysqld=--lock-wait-timeout=1 --mysqld=--innodb-lock-wait-timeout=1 \
--mysqld=--log-output=file --grammar=./3.yy

The same problem could be observed with
mysql-5.5 revno: 3088 2010-10-02
(5.5.7-rc-debug-log)

A multi-update bug.
Need the core file.

MTR test case:

CREATE TABLE t1(pk INT, PRIMARY KEY (pk)) engine=innodb;
INSERT INTO t1 VALUES (0);
UPDATE t1 AS A, t1 AS B SET A.pk = 1, B.pk = 2;

With the test case above, the bug is repeatable in 5.1 as well.

Same cause as BUG#55385: The first update ("SET A.pk = 1") moves a record to another partition, the second update ("B.pk = 2") therefore fails to find the record in the partition it expects it to be.

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/128909

3256 Jorgen Loland 2011-01-17
Bug#55385: UPDATE statement throws an error, but still updates the
table entries
Bug#57373: Multi update+InnoDB reports ER_KEY_NOT_FOUND if a table
is updated twice

Partitioning fails if multi-update updates the same partitioned
table twice and the partitioning key is updated. This is
because updates on the first table may move records from one
partition to another, and update on the second table will fail
to locate the records to update due to this.

In InnoDB (BUG#57373), the result was that updates on the
first table were performed. The transaction was then aborted
once updates on the second table failed to locate records.
Error ER_KEY_NOT_FOUND was returned. Problem: unneccessary work
was performed (update + abort) and misleading error message
returned.

In MyISAM (BUG#55385), the result was that updates on the first
table were performed. The transaction was then stopped once
updates on the second table failed to locate records. Error
"Got error 134 from storage engine" was returned. However,
since MyISAM is unable to abort, the updates on the first
table were still in effect. Problem: misleading error message
and half-performed transaction.

The fix is to chech if multi-table update will
a) update the same partition twice, and
b) at least one of these will update the partitioning key
and thereby risk moving records to another partition.
If this is the case, a meaningful error message is issued
before any update work has been done.
@ mysql-test/r/partition.result
Add test for bugs 55385 and 57373.
@ mysql-test/t/partition.test
Add test for bugs 55385 and 57373.
@ sql/share/errmsg-utf8.txt
New error message for multi-table update where the same partition is updated twice.
@ sql/sql_update.cc
Check if multi-table update is about to update the same partitioned table twice and issue error.

Noted in 5.5.11, 5.6.2 changelogs.

If a multiple-table update updated a row through two aliases and the
first update physically moved the row, the second update failed to
locate the row. This resulted in different errors depending on 
storage engine, although these errors did not accurately describe the
problem: 

* MyISAM: Got error 134 from storage engine
* InnoDB: Can't find record in 'tbl'
      
For MyISAM, which is nontransactional, the update executed first was
performed but the second was not. In addition, for two equal
multiple-table update statements, one could succeed and the other
fail depending on whether the record actually moved, which is
inconsistent.
      
Now such an update returns an error if it will update a table through
multiple aliases, and perform an update that may physically more the
row in at least one of these aliases.

CHANGESET - http://lists.mysql.com/commits/131836