#At file:///export/home/jl208045/mysql/mysql-5.5/ based on revid:john.embretsen@stripped
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.
modified:
mysql-test/r/partition.result
mysql-test/t/partition.test
sql/share/errmsg-utf8.txt
sql/sql_update.cc
=== 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-01-17 09:26:25 +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
+# BUG#57373: Multi update+InnoDB reports ER_KEY_NOT_FOUND if a
+# table is updated twice
+#
+CREATE TABLE t1_part (
+col1 int,
+col2 int
+) PARTITION BY LINEAR HASH(col1) PARTITIONS 3;
+INSERT INTO t1_part VALUES (1, 1) , (10, 10);
+CREATE VIEW v1 AS SELECT * FROM t1_part;
+
+SELECT * FROM t1_part;
+col1 col2
+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.col1 = 2, B.col2 = 3;
+ERROR HY000: Cannot do multi-table update because partitioned table 't1_part' is updated both by table/view 'A' and 'B'
+UPDATE t1_part AS A NATURAL JOIN t1_part B SET A.col2 = 2, B.col1 = 3;
+ERROR HY000: Cannot do multi-table update because partitioned table 't1_part' is updated both by table/view '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.col2 = 2 , B.col1 = 3;
+ERROR HY000: Cannot do multi-table update because partitioned table 't1_part' is updated both by table/view 'A' and 'B'
+UPDATE v1 AS A NATURAL JOIN t1_part as B SET A.col2 = 2 , B.col1 = 3;
+ERROR HY000: Cannot do multi-table update because partitioned table 't1_part' is updated both by table/view 'A' and 'B'
+
+SELECT * FROM t1_part;
+col1 col2
+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.col2 = 2 , B.col2 = 3;
+
+SELECT * FROM t1_part;
+col1 col2
+1 3
+10 3
+
+DROP VIEW v1;
+DROP TABLE t1_part;
=== 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-01-17 09:26:25 +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
+--echo # BUG#57373: Multi update+InnoDB reports ER_KEY_NOT_FOUND if a
+--echo # table is updated twice
+--echo #
+
+CREATE TABLE t1_part (
+ col1 int,
+ col2 int
+) PARTITION BY LINEAR HASH(col1) PARTITIONS 3;
+
+INSERT INTO t1_part VALUES (1, 1) , (10, 10);
+CREATE VIEW v1 AS SELECT * FROM t1_part;
+
+--echo
+SELECT * FROM t1_part;
+
+--echo
+--echo # Case 1
+--echo # Update is refused because partitioning key is updated
+--error ER_MULTI_UPDATE_SAME_PARTITION
+UPDATE t1_part AS A NATURAL JOIN t1_part B SET A.col1 = 2, B.col2 = 3;
+--error ER_MULTI_UPDATE_SAME_PARTITION
+UPDATE t1_part AS A NATURAL JOIN t1_part B SET A.col2 = 2, B.col1 = 3;
+
+--echo
+--echo # Case 2
+--echo # Like 1, but partition accessed through a view
+--error ER_MULTI_UPDATE_SAME_PARTITION
+UPDATE t1_part AS A NATURAL JOIN v1 as B SET A.col2 = 2 , B.col1 = 3;
+--error ER_MULTI_UPDATE_SAME_PARTITION
+UPDATE v1 AS A NATURAL JOIN t1_part as B SET A.col2 = 2 , B.col1 = 3;
+
+--echo
+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.col2 = 2 , B.col2 = 3;
+
+--echo
+SELECT * FROM t1_part;
+
+--echo
+# Cleanup
+DROP VIEW v1;
+DROP TABLE t1_part;
=== modified file 'sql/share/errmsg-utf8.txt'
--- a/sql/share/errmsg-utf8.txt 2010-11-05 17:42:37 +0000
+++ b/sql/share/errmsg-utf8.txt 2011-01-17 09:26:25 +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_SAME_PARTITION
+ eng "Cannot do multi-table update because partitioned table '%-.192s' is updated both by table/view '%-.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-01-17 09:26:25 +0000
@@ -1077,10 +1077,42 @@ 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;
+#ifdef WITH_PARTITION_STORAGE_ENGINE
+
+ /*
+ Check if the same partitioned table is updated more than once and
+ if so issue error if the partitioning key is updated (BUG#55385)
+ */
+ for (tl= leaves; tl ; tl= tl->next_leaf)
+ {
+ if (tl->table->map & tables_for_update &&
+ tl->table->part_info)
+ {
+ // tl is a partitioned table that will be updated
+ for (TABLE_LIST* tl2= tl->next_leaf; tl2 ; tl2= tl2->next_leaf)
+ {
+ if (tl2->table->map & tables_for_update && // tl2 is also updated
+ tl->table->s == tl2->table->s && // tl and tl2 is same table
+ (partition_key_modified(tl->table, tl->table->write_set) ||
+ partition_key_modified(tl2->table, tl2->table->write_set))
+ )
+ {
+ my_error(ER_MULTI_UPDATE_SAME_PARTITION, MYF(0),
+ tl->table_name,
+ tl->belong_to_view ? tl->belong_to_view->alias : tl->alias,
+ tl2->belong_to_view ?
+ tl2->belong_to_view->alias : tl2->alias);
+ DBUG_RETURN(TRUE);
+ }
+ }
+ }
+ }
+#endif
+
/*
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-20110117092625-d4r3y8xhvarkjsal.bundle
