#At file:///export/home/jl208045/mysql/mysql-5.5/ based on revid:magnus.blaudd@stripped
3352 Jorgen Loland 2011-02-21
BUG#11762751: UPDATE STATEMENT THROWS AN ERROR, BUT STILL
UPDATES THE TABLE ENTRIES (formerly 55385)
BUG#11764529: MULTI UPDATE+INNODB REPORTS ER_KEY_NOT_FOUND
IF A TABLE IS UPDATED TWICE (formerly 57373)
If multiple-table update updates a row through two aliases and
the first update physically moves the row, the second update will
fail to locate the row. This results in different errors
depending on storage engine:
* MyISAM: Got error 134 from storage engine
* InnoDB: Can't find record in 'tbl'
None of these errors accurately describe the problem.
Furthermore, since MyISAM is non-transactional, the update
executed first will be performed while the second will not.
In addition, for two equal multiple-table update statements,
one could succeed and the other fail based on whether or not
the record actually moved or not. This was inconsistent.
Two update operations may physically move a row:
1) Update of a column in a clustered primary key
2) Update of a column used to calculate which partition the
row belongs to
BUG#11764529 is about case 1) above, BUG#11762751 was about case 2).
The fix for these bugs is to return with an error if multiple-table
update is about to:
a) Update a table through multiple aliases, and
b) Perform an update that may physically more the row
in at least one of these aliases
This avoids
* partial updates as described for MyISAM above,
* provides the same error message that describes the actual problem
for all SEs
* inconsistent behavior where a statement fails or succeeds based on
e.g. the partitioning algorithm of the table.
@ mysql-test/r/multi_update.result
Add test for bug#57373
@ mysql-test/r/multi_update_innodb.result
Add test for bug#57373
@ mysql-test/r/partition.result
Add test for bug#55385
@ mysql-test/t/multi_update.test
Add test for bug#57373
@ mysql-test/t/multi_update_innodb.test
Add test for bug#57373
@ mysql-test/t/partition.test
Add test for bug#55385
@ sql/handler.cc
Translate handler error HA_ERR_RECORD_DELETED to server error
@ sql/share/errmsg-utf8.txt
New error message for multi-table update where the same table is updated multiple times.
@ sql/sql_update.cc
Add function unsafe_key_update()
added:
mysql-test/r/multi_update_innodb.result
mysql-test/t/multi_update_innodb.test
modified:
mysql-test/r/multi_update.result
mysql-test/r/partition.result
mysql-test/t/multi_update.test
mysql-test/t/partition.test
sql/handler.cc
sql/share/errmsg-utf8.txt
sql/sql_update.cc
=== modified file 'mysql-test/r/multi_update.result'
--- a/mysql-test/r/multi_update.result 2010-12-14 10:46:00 +0000
+++ b/mysql-test/r/multi_update.result 2011-02-21 15:49:03 +0000
@@ -680,4 +680,21 @@ Warnings:
Warning 1292 Truncated incorrect datetime value: '1'
DROP FUNCTION f1;
DROP TABLE t1;
+#
+# BUG#57373: Multi update+InnoDB reports ER_KEY_NOT_FOUND if a
+# table is updated twice
+#
+CREATE TABLE t1(
+pk INT,
+a INT,
+PRIMARY KEY (pk)
+) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (0,0);
+UPDATE t1 AS A, t1 AS B SET A.pk = 1, B.a = 2;
+
+# Should be (1,2)
+SELECT * FROM t1;
+pk a
+1 2
+DROP TABLE t1;
end of tests
=== added file 'mysql-test/r/multi_update_innodb.result'
--- a/mysql-test/r/multi_update_innodb.result 1970-01-01 00:00:00 +0000
+++ b/mysql-test/r/multi_update_innodb.result 2011-02-21 15:49:03 +0000
@@ -0,0 +1,29 @@
+#
+# BUG#57373: Multi update+InnoDB reports ER_KEY_NOT_FOUND if a
+# table is updated twice
+#
+CREATE TABLE t1(
+pk INT,
+a INT,
+b INT,
+PRIMARY KEY (pk)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (0,0,0);
+UPDATE t1 AS A, t1 AS B SET A.pk = 1, B.a = 2;
+ERROR HY000: Primary key/partition key update is not allowed since the table is updated both as 'A' and 'B'.
+SELECT * FROM t1;
+pk a b
+0 0 0
+CREATE VIEW v1 AS SELECT * FROM t1;
+UPDATE v1 AS A, t1 AS B SET A.pk = 1, B.a = 2;
+ERROR HY000: Primary key/partition key update is not allowed since the table is updated both as 'A' and 'B'.
+SELECT * FROM t1;
+pk a b
+0 0 0
+UPDATE t1 AS A, t1 AS B SET A.a = 1, B.b = 2;
+# Should be (0,1,2)
+SELECT * FROM t1;
+pk a b
+0 1 2
+DROP VIEW v1;
+DROP TABLE t1;
=== modified file 'mysql-test/r/partition.result'
--- a/mysql-test/r/partition.result 2011-01-10 14:08:31 +0000
+++ b/mysql-test/r/partition.result 2011-02-21 15:49:03 +0000
@@ -2264,3 +2264,51 @@ INSERT INTO t1 VALUES(0);
DROP TABLE t1;
SET GLOBAL myisam_use_mmap=default;
End of 5.1 tests
+#
+# BUG#55385: UPDATE statement throws an error, but still updates
+# the table entries
+CREATE TABLE t1_part (
+partkey int,
+nokey int
+) PARTITION BY LINEAR HASH(partkey) PARTITIONS 3;
+INSERT INTO t1_part VALUES (1, 1) , (10, 10);
+CREATE VIEW v1 AS SELECT * FROM t1_part;
+
+# Should be (1,1),(10,10)
+SELECT * FROM t1_part;
+partkey nokey
+1 1
+10 10
+
+# Case 1
+# Update is refused because partitioning key is updated
+UPDATE t1_part AS A NATURAL JOIN t1_part B SET A.partkey = 2, B.nokey = 3;
+ERROR HY000: Primary key/partition key update is not allowed since the table is updated both as 'A' and 'B'.
+UPDATE t1_part AS A NATURAL JOIN t1_part B SET A.nokey = 2, B.partkey = 3;
+ERROR HY000: Primary key/partition key update is not allowed since the table is updated both as 'A' and 'B'.
+
+# Case 2
+# Like 1, but partition accessed through a view
+UPDATE t1_part AS A NATURAL JOIN v1 as B SET A.nokey = 2 , B.partkey = 3;
+ERROR HY000: Primary key/partition key update is not allowed since the table is updated both as 'A' and 'B'.
+UPDATE v1 AS A NATURAL JOIN t1_part as B SET A.nokey = 2 , B.partkey = 3;
+ERROR HY000: Primary key/partition key update is not allowed since the table is updated both as 'A' and 'B'.
+
+# Should be (1,1),(10,10)
+SELECT * FROM t1_part;
+partkey nokey
+1 1
+10 10
+
+# Case 3
+# Update is accepted because partitioning key is not updated
+UPDATE t1_part AS A NATURAL JOIN t1_part B SET A.nokey = 2 , B.nokey = 3;
+
+# Should be (1,3),(10,3)
+SELECT * FROM t1_part;
+partkey nokey
+1 3
+10 3
+
+DROP VIEW v1;
+DROP TABLE t1_part;
=== modified file 'mysql-test/t/multi_update.test'
--- a/mysql-test/t/multi_update.test 2010-12-14 10:46:00 +0000
+++ b/mysql-test/t/multi_update.test 2011-02-21 15:49:03 +0000
@@ -683,4 +683,24 @@ UPDATE (SELECT 1 FROM t1 WHERE f1 = (SEL
DROP FUNCTION f1;
DROP TABLE t1;
+--echo #
+--echo # BUG#57373: Multi update+InnoDB reports ER_KEY_NOT_FOUND if a
+--echo # table is updated twice
+--echo #
+
+# Results differ between storage engines.
+# See multi_update_innodb.test for the InnoDB variant of this test
+CREATE TABLE t1(
+ pk INT,
+ a INT,
+ PRIMARY KEY (pk)
+) ENGINE=MyISAM;
+
+INSERT INTO t1 VALUES (0,0);
+UPDATE t1 AS A, t1 AS B SET A.pk = 1, B.a = 2;
+--echo
+--echo # Should be (1,2)
+SELECT * FROM t1;
+DROP TABLE t1;
+
--echo end of tests
=== added file 'mysql-test/t/multi_update_innodb.test'
--- a/mysql-test/t/multi_update_innodb.test 1970-01-01 00:00:00 +0000
+++ b/mysql-test/t/multi_update_innodb.test 2011-02-21 15:49:03 +0000
@@ -0,0 +1,33 @@
+--source include/have_innodb.inc
+
+--echo #
+--echo # BUG#57373: Multi update+InnoDB reports ER_KEY_NOT_FOUND if a
+--echo # table is updated twice
+--echo #
+
+# Results differ between storage engines.
+# See multi_update.test for the MyISAM variant of this test
+CREATE TABLE t1(
+ pk INT,
+ a INT,
+ b INT,
+ PRIMARY KEY (pk)
+) ENGINE=InnoDB;
+
+INSERT INTO t1 VALUES (0,0,0);
+--error ER_MULTI_UPDATE_KEY_CONFLICT
+UPDATE t1 AS A, t1 AS B SET A.pk = 1, B.a = 2;
+SELECT * FROM t1;
+
+CREATE VIEW v1 AS SELECT * FROM t1;
+--error ER_MULTI_UPDATE_KEY_CONFLICT
+UPDATE v1 AS A, t1 AS B SET A.pk = 1, B.a = 2;
+SELECT * FROM t1;
+
+UPDATE t1 AS A, t1 AS B SET A.a = 1, B.b = 2;
+--echo # Should be (0,1,2)
+SELECT * FROM t1;
+
+DROP VIEW v1;
+DROP TABLE t1;
+
=== modified file 'mysql-test/t/partition.test'
--- a/mysql-test/t/partition.test 2011-01-10 14:08:31 +0000
+++ b/mysql-test/t/partition.test 2011-02-21 15:49:03 +0000
@@ -2267,3 +2267,53 @@ DROP TABLE t1;
SET GLOBAL myisam_use_mmap=default;
--echo End of 5.1 tests
+
+--echo #
+--echo # BUG#55385: UPDATE statement throws an error, but still updates
+--echo # the table entries
+
+CREATE TABLE t1_part (
+ partkey int,
+ nokey int
+) PARTITION BY LINEAR HASH(partkey) PARTITIONS 3;
+
+INSERT INTO t1_part VALUES (1, 1) , (10, 10);
+CREATE VIEW v1 AS SELECT * FROM t1_part;
+
+--echo
+--echo # Should be (1,1),(10,10)
+SELECT * FROM t1_part;
+
+--echo
+--echo # Case 1
+--echo # Update is refused because partitioning key is updated
+--error ER_MULTI_UPDATE_KEY_CONFLICT
+UPDATE t1_part AS A NATURAL JOIN t1_part B SET A.partkey = 2, B.nokey = 3;
+--error ER_MULTI_UPDATE_KEY_CONFLICT
+UPDATE t1_part AS A NATURAL JOIN t1_part B SET A.nokey = 2, B.partkey = 3;
+
+--echo
+--echo # Case 2
+--echo # Like 1, but partition accessed through a view
+--error ER_MULTI_UPDATE_KEY_CONFLICT
+UPDATE t1_part AS A NATURAL JOIN v1 as B SET A.nokey = 2 , B.partkey = 3;
+--error ER_MULTI_UPDATE_KEY_CONFLICT
+UPDATE v1 AS A NATURAL JOIN t1_part as B SET A.nokey = 2 , B.partkey = 3;
+
+--echo
+--echo # Should be (1,1),(10,10)
+SELECT * FROM t1_part;
+
+--echo
+--echo # Case 3
+--echo # Update is accepted because partitioning key is not updated
+UPDATE t1_part AS A NATURAL JOIN t1_part B SET A.nokey = 2 , B.nokey = 3;
+
+--echo
+--echo # Should be (1,3),(10,3)
+SELECT * FROM t1_part;
+
+--echo
+# Cleanup
+DROP VIEW v1;
+DROP TABLE t1_part;
=== modified file 'sql/handler.cc'
--- a/sql/handler.cc 2011-02-08 15:47:33 +0000
+++ b/sql/handler.cc 2011-02-21 15:49:03 +0000
@@ -2700,6 +2700,7 @@ void handler::print_error(int error, myf
break;
case HA_ERR_KEY_NOT_FOUND:
case HA_ERR_NO_ACTIVE_RECORD:
+ case HA_ERR_RECORD_DELETED:
case HA_ERR_END_OF_FILE:
textno=ER_KEY_NOT_FOUND;
break;
=== modified file 'sql/share/errmsg-utf8.txt'
--- a/sql/share/errmsg-utf8.txt 2011-02-02 11:54:49 +0000
+++ b/sql/share/errmsg-utf8.txt 2011-02-21 15:49:03 +0000
@@ -6394,3 +6394,6 @@ ER_SLAVE_HEARTBEAT_VALUE_OUT_OF_RANGE_MA
ER_STMT_CACHE_FULL
eng "Multi-row statements required more than 'max_binlog_stmt_cache_size' bytes of storage; increase this mysqld variable and try again"
+
+ER_MULTI_UPDATE_KEY_CONFLICT
+ eng "Primary key/partition key update is not allowed since the table is updated both as '%-.192s' and '%-.192s'."
=== modified file 'sql/sql_update.cc'
--- a/sql/sql_update.cc 2010-12-29 00:26:31 +0000
+++ b/sql/sql_update.cc 2011-02-21 15:49:03 +0000
@@ -998,6 +998,98 @@ static table_map get_table_map(List<Item
return map;
}
+/**
+ If one row is updated through two different aliases and the first
+ update physically moves the row, the second update will error
+ because the row is no longer located where expected. This function
+ checks if the multiple-table update is about to do that and if so
+ returns with an error.
+
+ The following update operations physically moves rows:
+ 1) Update of a column in a clustered primary key
+ 2) Update of a column used to calculate which partition the row belongs to
+
+ This function returns with an error if both of the following are
+ true:
+
+ a) A table in the multiple-table update statement is updated
+ through multiple aliases (including views)
+ b) At least one of the updates on the table from a) may physically
+ moves the row. Note: Updating a column used to calculate which
+ partition a row belongs to does not necessarily mean that the
+ row is moved. The new value may or may not belong to the same
+ partition.
+
+ @param leaves First leaf table
+ @param tables_for_update Map of tables that are updated
+
+ @return
+ true if the update is unsafe, in which case an error message is also set,
+ false otherwise.
+*/
+static
+bool unsafe_key_update(TABLE_LIST *leaves, table_map tables_for_update)
+{
+ TABLE_LIST *tl= leaves;
+
+ for (tl= leaves; tl ; tl= tl->next_leaf)
+ {
+ if (tl->table->map & tables_for_update)
+ {
+ TABLE *table1= tl->table;
+ bool primkey_clustered= (table1->file->primary_key_is_clustered() &&
+ table1->s->primary_key != MAX_KEY);
+
+ bool table_partitioned= false;
+#ifdef WITH_PARTITION_STORAGE_ENGINE
+ table_partitioned= (table1->part_info != NULL);
+#endif
+
+ if (!table_partitioned && !primkey_clustered)
+ continue;
+
+ for (TABLE_LIST* tl2= tl->next_leaf; tl2 ; tl2= tl2->next_leaf)
+ {
+ /*
+ Look at "next" tables only since all previous tables have
+ already been checked
+ */
+ TABLE *table2= tl2->table;
+ if (table2->map & tables_for_update && table1->s == table2->s)
+ {
+ // A table is updated through two aliases
+ if (table_partitioned &&
+ (partition_key_modified(table1, table1->write_set) ||
+ partition_key_modified(table2, table2->write_set)))
+ {
+ // Partitioned key is updated
+ my_error(ER_MULTI_UPDATE_KEY_CONFLICT, MYF(0),
+ tl->belong_to_view ? tl->belong_to_view->alias
+ : tl->alias,
+ tl2->belong_to_view ? tl2->belong_to_view->alias
+ : tl2->alias);
+ return true;
+ }
+
+ if (primkey_clustered &&
+ (bitmap_is_set(table1->write_set, table1->s->primary_key) ||
+ bitmap_is_set(table2->write_set, table2->s->primary_key)))
+ {
+ // Clustered primary key is updated
+ my_error(ER_MULTI_UPDATE_KEY_CONFLICT, MYF(0),
+ tl->belong_to_view ? tl->belong_to_view->alias
+ : tl->alias,
+ tl2->belong_to_view ? tl2->belong_to_view->alias
+ : tl2->alias);
+ return true;
+ }
+ }
+ }
+ }
+ }
+ return false;
+}
+
/*
make update specific preparation and checks after opening tables
@@ -1077,10 +1169,14 @@ int mysql_multi_update_prepare(THD *thd)
thd->table_map_for_update= tables_for_update= get_table_map(fields);
+ leaves= lex->select_lex.leaf_tables;
+
+ if (unsafe_key_update(leaves, tables_for_update))
+ DBUG_RETURN(true);
+
/*
Setup timestamp handling and locking mode
*/
- leaves= lex->select_lex.leaf_tables;
for (tl= leaves; tl; tl= tl->next_leaf)
{
TABLE *table= tl->table;
Attachment: [text/bzr-bundle] bzr/jorgen.loland@oracle.com-20110221154903-lbyzzk52hiht7i5j.bundle
| Thread |
|---|
| • bzr commit into mysql-5.5 branch (jorgen.loland:3352) Bug#11762751Bug#11764529 | Jorgen Loland | 21 Feb |
