List:Commits« Previous MessageNext Message »
From:Jorgen Loland Date:January 17 2011 9:26am
Subject:bzr commit into mysql-5.5 branch (jorgen.loland:3256) Bug#55385 Bug#57373
View as plain text  
#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
Thread
bzr commit into mysql-5.5 branch (jorgen.loland:3256) Bug#55385 Bug#57373Jorgen Loland17 Jan
  • Re: bzr commit into mysql-5.5 branch (jorgen.loland:3256) Bug#55385Bug#57373Jon Olav Hauglid17 Jan
    • Re: bzr commit into mysql-5.5 branch (jorgen.loland:3256) Bug#55385Bug#57373Jorgen Loland18 Jan
  • Re: bzr commit into mysql-5.5 branch (jorgen.loland:3256) Bug#55385Bug#57373Mattias Jonsson22 Jan
    • Re: bzr commit into mysql-5.5 branch (jorgen.loland:3256) Bug#55385Bug#57373Jon Olav Hauglid11 Feb
      • Re: bzr commit into mysql-5.5 branch (jorgen.loland:3256) Bug#55385Bug#57373Mattias Jonsson11 Feb
        • Re: bzr commit into mysql-5.5 branch (jorgen.loland:3256) Bug#55385Bug#57373Jorgen Loland11 Feb
        • Re: bzr commit into mysql-5.5 branch (jorgen.loland:3256) Bug#55385Bug#57373Jorgen Loland14 Feb