Bug #55385 UPDATE statement throws an error, but still updates the table entries
Submitted: 20 Jul 2010 4:51 Modified: 2 Mar 2011 15:24
Reporter: Anitha Gopi Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:mysql-next-mr-wl5136-stage, 5.1 OS:Any
Assigned to: Jørgen Løland CPU Architecture:Any

[20 Jul 2010 4:51] Anitha Gopi
Description:
This was discovered on running RQG grammer WL5004_sql.yy against mysql-next-mr-wl5136-stage tree

Problem can be reproduced using the simplified MTR script

 --connect (connection_1, localhost, root, , test)
CREATE TABLE `table10_int` ( `col_int` int, pk int, `col_int_key` int, /*Indices*/ primary key (pk), key (`col_int_key` ));
INSERT /*! IGNORE */ INTO table10_int VALUES  (-1398603776, 1, 4) ,  (NULL, 2, 1) ,  (NULL, 3, 0) ,  (6, 4, NULL) ,  (NULL, 5, NULL) ,  (NULL, 6, 1770389504) ,  (NULL, 7, NULL) ,  (NULL, 8, NULL) ,  (NULL, 9, 7) ,  (-850853888, 10, 5);
CREATE SCHEMA IF NOT EXISTS testdb_N  COLLATE = utf8_bin;
CREATE TABLE IF NOT EXISTS testdb_N . t1_part1_N  ENGINE = MyISAM PARTITION BY LINEAR HASH(pk) PARTITIONS 3 AS SELECT * FROM test.table10_int;
UPDATE   testdb_N . t1_part1_N  AS A NATURAL JOIN table10_int  B SET A. `pk` = 2 , B. `pk` = 2;

The last UPDATE statement throws an error "ERROR 1030 (HY000): Got error 134 from storage engine". From a manual run of the above steps I could see that the table is actually getting updated

mysql> CREATE TABLE IF NOT EXISTS testdb_N . t1_part1_N  ENGINE = MyISAM PARTITION BY LINEAR HASH(pk) PARTITIONS 3 AS SELECT * FROM test.table10_int;
Query OK, 10 rows affected (0.19 sec)
Records: 10  Duplicates: 0  Warnings: 0

mysql> select * from testdb_N . t1_part1_N;
+-------------+----+-------------+
| col_int     | pk | col_int_key |
+-------------+----+-------------+
|           6 |  4 |        NULL |
|        NULL |  8 |        NULL |
| -1398603776 |  1 |           4 |
|        NULL |  3 |           0 |
|        NULL |  5 |        NULL |
|        NULL |  7 |        NULL |
|        NULL |  9 |           7 |
|        NULL |  2 |           1 |
|        NULL |  6 |  1770389504 |
|  -850853888 | 10 |           5 |
+-------------+----+-------------+
10 rows in set (0.01 sec)

mysql> UPDATE   testdb_N . t1_part1_N  AS A NATURAL JOIN testdb_N . t1_part1_N  B SET A. `pk` = 2 , B. `pk` = 2;
ERROR 1030 (HY000): Got error 134 from storage engine
mysql> select * from testdb_N . t1_part1_N;
+-------------+----+-------------+
| col_int     | pk | col_int_key |
+-------------+----+-------------+
|           6 |  4 |        NULL |
|        NULL |  8 |        NULL |
|        NULL |  3 |           0 |
|        NULL |  5 |        NULL |
|        NULL |  7 |        NULL |
|        NULL |  9 |           7 |
|        NULL |  2 |           1 |
|        NULL |  6 |  1770389504 |
|  -850853888 |  2 |           5 |
| -1398603776 |  2 |           4 |
+-------------+----+-------------+
10 rows in set (0.00 sec)

Note that pk has got updated to 2 for the rows that were included in the SELF JOIN result

Behavior is different on using InnoDB. Here the UPDATE throws a different error and is not successful
mysql> CREATE TABLE IF NOT EXISTS testdb_N . t1_part1_N  ENGINE = InnoDB PARTITION BY LINEAR HASH(pk) PARTITIONS 3 AS SELECT * FROM test.table10_int;
Query OK, 10 rows affected (0.10 sec)
Records: 10  Duplicates: 0  Warnings: 0

mysql> select * from testdb_N . t1_part1_N;
+-------------+----+-------------+
| col_int     | pk | col_int_key |
+-------------+----+-------------+
|           6 |  4 |        NULL |
|        NULL |  8 |        NULL |
| -1398603776 |  1 |           4 |
|        NULL |  3 |           0 |
|        NULL |  5 |        NULL |
|        NULL |  7 |        NULL |
|        NULL |  9 |           7 |
|        NULL |  2 |           1 |
|        NULL |  6 |  1770389504 |
|  -850853888 | 10 |           5 |
+-------------+----+-------------+
10 rows in set (0.00 sec)

mysql> UPDATE   testdb_N . t1_part1_N  AS A NATURAL JOIN testdb_N . t1_part1_N  B SET A. `pk` = 1 , B. `pk` = 1;
ERROR 1032 (HY000): Can't find record in 't1_part1_N'
mysql> select * from testdb_N . t1_part1_N;
+-------------+----+-------------+
| col_int     | pk | col_int_key |
+-------------+----+-------------+
|           6 |  4 |        NULL |
|        NULL |  8 |        NULL |
| -1398603776 |  1 |           4 |
|        NULL |  3 |           0 |
|        NULL |  5 |        NULL |
|        NULL |  7 |        NULL |
|        NULL |  9 |           7 |
|        NULL |  2 |           1 |
|        NULL |  6 |  1770389504 |
|  -850853888 | 10 |           5 |
+-------------+----+-------------+
10 rows in set (0.01 sec)

How to repeat:
Run the following MTR script

--connect (connection_1, localhost, root, , test)
CREATE TABLE `table10_int` ( `col_int` int, pk int, `col_int_key` int, /*Indices*/ primary key (pk), key (`col_int_key` ));
INSERT /*! IGNORE */ INTO table10_int VALUES  (-1398603776, 1, 4) ,  (NULL, 2, 1) ,  (NULL, 3, 0) ,  (6, 4, NULL) ,  (NULL, 5, NULL) ,  (NULL, 6, 1770389504) ,  (NULL, 7, NULL) ,  (NULL, 8, NULL) ,  (NULL, 9, 7) ,  (-850853888, 10, 5);
CREATE SCHEMA IF NOT EXISTS testdb_N  COLLATE = utf8_bin;
CREATE TABLE IF NOT EXISTS testdb_N . t1_part1_N  ENGINE = MyISAM PARTITION BY LINEAR HASH(pk) PARTITIONS 3 AS SELECT * FROM test.table10_int;
UPDATE   testdb_N . t1_part1_N  AS A NATURAL JOIN table10_int  B SET A. `pk` = 2 , B. `pk` = 2;
[20 Jul 2010 11:49] Anitha Gopi
Further investigation by Matthias shows that the problem is only if the update uses the same value for the column. See below

--disable_warnings
DROP TABLE IF EXISTS t1_myisam;
DROP TABLE IF EXISTS t1_part;
--enable_warnings

CREATE TABLE t1_myisam  ( col1 int) ENGINE = MyISAM;
CREATE TABLE t1_part    ( col1 int) ENGINE = MyISAM
PARTITION BY LINEAR HASH(col1) PARTITIONS 3;

INSERT INTO t1_myisam VALUES ( 1 ) , ( 10 );
INSERT INTO t1_part   VALUES ( 1 ) , ( 10 );

# "Harmless" (Partitioning is not involved)
UPDATE t1_myisam  AS A NATURAL JOIN t1_myisam B SET A. col1 = 2 , B.col1 = 2;

# "Harmless" (We set col1 to different values)
UPDATE t1_part  AS A NATURAL JOIN t1_part B SET A. col1 = 1 , B.col1 = 2;

# 1030: Got error 134 from storage engine
UPDATE t1_part  AS A NATURAL JOIN t1_part B SET A. col1 = 2 , B.col1 = 2;

# Cleanup
DROP TABLE t1_myisam, t1_part;
[28 Jul 2010 17:47] MySQL Verification Team
Thank you for the bug report.
[22 Dec 2010 8:33] Jørgen Løland
The problem is not that the update sets A.col1 and B.col1 to the same value, but that the update of A.col1 moves the record to another partition. Using the test case above we see this:

-----------------
# Original #records in the partitions
SELECT table_rows FROM information_schema.partitions 
WHERE table_schema = schema() and table_name = 't1_part';
table_rows
0
1
1

UPDATE t1_part  AS A NATURAL JOIN t1_part B SET A. col1 = 2 , B.col1 = 2;
ERROR HY000: Got error 134 from storage engine

# Updating the A-part moved a record to the third partition
SELECT table_rows FROM information_schema.partitions 
WHERE table_schema = schema() and table_name = 't1_part';
table_rows
0
0
2
-----------------

When a record is moved from one partition to another, it has to be deleted from one and inserted into the other. That's what error 134 is about: "Record was already deleted (or record file crashed)". 

So we get an error because one of the records is not where it should be according to the rowid we got when the join was performed. An error means the statement bails out. Innodb does the correct thing and aborts the transaction. MyISAM is incapable of performing abort. 

This is what happens when not using transactions. Workaround: use storage engine with transaction support. Suggest bug is closed as won't fix.
[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:24] 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