MySQL Bugs: #55385: UPDATE statement throws an error, but still updates the table entries

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

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;

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;

Thank you for the bug report.

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.

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