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
Triage: Triaged: D2 (Serious) / R3 (Medium) / E3 (Medium)

[11 Oct 2010 14:14] Matthias Leich
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
[11 Oct 2010 14:20] Matthias Leich
The same problem could be observed with
mysql-5.5 revno: 3088 2010-10-02
(5.5.7-rc-debug-log)
[18 Oct 2010 13:52] Konstantin Osipov
A multi-update bug.
Need the core file.
[2 Nov 2010 15:43] Jon Olav Hauglid
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;
[4 Nov 2010 8:57] Jon Olav Hauglid
With the test case above, the bug is repeatable in 5.1 as well.
[6 Jan 2011 9:01] Jørgen Løland
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.
[17 Jan 2011 9:26] 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/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.
[2 Mar 2011 15:25] Paul Dubois
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