List:Commits« Previous MessageNext Message »
From:Jorgen Loland Date:February 14 2011 2:51pm
Subject:bzr commit into mysql-trunk branch (jorgen.loland:3641) Bug#11762751
Bug#11764529
View as plain text  
#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
Thread
bzr commit into mysql-trunk branch (jorgen.loland:3641) Bug#11762751Bug#11764529Jorgen Loland14 Feb
  • Re: bzr commit into mysql-trunk branch (jorgen.loland:3641) Bug#11762751Bug#11764529Jon Olav Hauglid15 Feb
    • Re: bzr commit into mysql-trunk branch (jorgen.loland:3641) Bug#11762751Bug#11764529Jorgen Loland15 Feb
      • Re: bzr commit into mysql-trunk branch (jorgen.loland:3641) Bug#11762751Bug#11764529Jon Olav Hauglid15 Feb
        • Re: bzr commit into mysql-trunk branch (jorgen.loland:3641) Bug#11762751Bug#11764529Jorgen Loland17 Feb
          • Re: bzr commit into mysql-trunk branch (jorgen.loland:3641) Bug#11762751Bug#11764529MATTIAS JONSSON17 Feb
            • Re: bzr commit into mysql-trunk branch (jorgen.loland:3641) Bug#11762751Bug#11764529Jorgen Loland17 Feb