#At file:///export/home/jl208045/mysql/mysql-trunk-55385/ based on revid:luis.soares@stripped
3641 Jorgen Loland 2011-02-14
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 an error.
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
@ 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 11:15:13 +0000
+++ b/mysql-test/r/multi_update.result 2011-02-14 14:51:47 +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-14 14:51:47 +0000
@@ -0,0 +1,25 @@
+#
+# 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=InnoDB;
+INSERT INTO t1 VALUES (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
+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'.
+
+# Should be (0,0)
+SELECT * FROM t1;
+pk a
+0 0
+DROP VIEW v1;
+DROP TABLE t1;
=== modified file 'mysql-test/r/partition.result'
--- a/mysql-test/r/partition.result 2011-01-10 16:37:47 +0000
+++ b/mysql-test/r/partition.result 2011-02-14 14:51:47 +0000
@@ -2263,3 +2263,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 (
+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;
+
+# Should be (1,1),(10,10)
+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: 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.col2 = 2, B.col1 = 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.col2 = 2 , B.col1 = 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.col2 = 2 , B.col1 = 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;
+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;
+
+# Should be (1,3),(10,3)
+SELECT * FROM t1_part;
+col1 col2
+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 11:15:13 +0000
+++ b/mysql-test/t/multi_update.test 2011-02-14 14:51:47 +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-14 14:51:47 +0000
@@ -0,0 +1,31 @@
+--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,
+ PRIMARY KEY (pk)
+) ENGINE=InnoDB;
+
+INSERT INTO t1 VALUES (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;
+
+--echo
+--echo # Should be (0,0)
+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 16:37:47 +0000
+++ b/mysql-test/t/partition.test 2011-02-14 14:51:47 +0000
@@ -2266,3 +2266,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 (
+ 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
+--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.col1 = 2, B.col2 = 3;
+--error ER_MULTI_UPDATE_KEY_CONFLICT
+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_KEY_CONFLICT
+UPDATE t1_part AS A NATURAL JOIN v1 as B SET A.col2 = 2 , B.col1 = 3;
+--error ER_MULTI_UPDATE_KEY_CONFLICT
+UPDATE v1 AS A NATURAL JOIN t1_part as B SET A.col2 = 2 , B.col1 = 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.col2 = 2 , B.col2 = 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:54:12 +0000
+++ b/sql/handler.cc 2011-02-14 14:51:47 +0000
@@ -2879,6 +2879,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-08 17:48:20 +0000
+++ b/sql/share/errmsg-utf8.txt 2011-02-14 14:51:47 +0000
@@ -6454,3 +6454,6 @@ 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_BINLOG_STMT_CACHE_SIZE_GREATER_THAN_MAX
eng "Option binlog_stmt_cache_size (%lu) is greater than max_binlog_stmt_cache_size (%lu); setting binlog_stmt_cache_size equal to max_binlog_stmt_cache_size."
+
+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:38:59 +0000
+++ b/sql/sql_update.cc 2011-02-14 14:51:47 +0000
@@ -999,6 +999,88 @@ 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)
+ {
+ 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 *table1= tl->table;
+ TABLE *table2= tl2->table;
+ if (table2->map & tables_for_update && table1->s == table2->s)
+ {
+ // A table is updated through two aliases
+#ifdef WITH_PARTITION_STORAGE_ENGINE
+ if (table1->part_info &&
+ (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;
+ }
+#endif
+ if ((table1->file->primary_key_is_clustered() &&
+ table1->s->primary_key != MAX_KEY) &&
+ (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
@@ -1078,10 +1160,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-20110214145147-w13okic3h9u46pb1.bundle
