List:Commits« Previous MessageNext Message »
From:Jorgen Loland Date:June 16 2011 6:32am
Subject:bzr push into mysql-5.5 branch (jorgen.loland:3453 to 3454) Bug#11882110
View as plain text  
 3454 Jorgen Loland	2011-06-16
      BUG#11882110: UPDATE REPORTS ER_KEY_NOT_FOUND IF TABLE IS 
                    UPDATED TWICE
      
      For multi update it is not allowed to update a column
      of a table if that table is accessed through multiple aliases
      and either
      1) the updated column is used as partitioning key
      2) the updated column is part of the primary key 
         and the primary key is clustered
      
      This check is done in unsafe_key_update().
      
      The bug was that for case 2), it was checked whether
      updated_column_number == table_share->primary_key 
      However, the primary_key variable is the index number of the 
      primary key, not a column number.
      
      Prior to this bugfix, the first column was wrongly believed to be
      the primary key. The columns covered by an index is found in
      table->key_info[idx_number]->key_part. The bugfix is to check if
      any of the columns in the keyparts of the primary key are
      updated.
      
      The user-visible effect is that for storage engines with
      clustered primary key (e.g. InnoDB but not MyISAM) queries
      like 
      "UPDATE t1 AS A JOIN t2 AS B SET A.primkey=..."
      will now error with 
      "ERROR HY000: Primary key/partition key update is not allowed 
      since the table is updated both as 'A' and 'B'." 
      instead of 
      "ERROR 1032 (HY000): Can't find record in 't1_tb'"
      even if primkey is not the first column in the table. This 
      was the intended behavior of bugfix 11764529.
     @ mysql-test/r/multi_update.result
        Add test for bug#11882110
     @ mysql-test/r/multi_update_innodb.result
        Add test for bug#11882110
     @ mysql-test/t/multi_update.test
        Add test for bug#11882110
     @ mysql-test/t/multi_update_innodb.test
        Add test for bug#11882110
     @ sql/sql_update.cc
        unsafe_key_update() wrongly checked if the primary key index
        number was the same as updated column number. Now it is checked
        whether any of the columns making up the primary key is updated.
     @ sql/table.h
        Fix comment on TABLE_SHARE::primary_key. Incorrect comment
        was introduced by an earlier merge conflict (as per dlenev)

    modified:
      mysql-test/r/multi_update.result
      mysql-test/r/multi_update_innodb.result
      mysql-test/t/multi_update.test
      mysql-test/t/multi_update_innodb.test
      sql/sql_update.cc
      sql/table.h
 3453 Vinay Fisrekar	2011-06-16
      Skip test run with valgrind as test has restarts 

    modified:
      mysql-test/suite/innodb/t/innodb_prefix_index_restart_server.test
=== modified file 'mysql-test/r/multi_update.result'
--- a/mysql-test/r/multi_update.result	2011-03-08 17:39:25 +0000
+++ b/mysql-test/r/multi_update.result	2011-06-16 06:24:00 +0000
@@ -697,4 +697,40 @@ SELECT * FROM t1;
 pk	a
 1	2
 DROP TABLE t1;
-end of tests
+#
+# BUG#11882110: UPDATE REPORTS ER_KEY_NOT_FOUND IF TABLE IS 
+#               UPDATED TWICE
+#
+CREATE TABLE t1 ( 
+col_int_key int, 
+pk int, 
+col_int int, 
+key(col_int_key), 
+primary key (pk)
+) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (1,2,3);
+
+CREATE TABLE t2 ( 
+col_int_key int, 
+pk_1 int, 
+pk_2 int, 
+col_int int, 
+key(col_int_key), 
+primary key (pk_1,pk_2)
+) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (1,2,3,4);
+
+UPDATE t1 AS A NATURAL JOIN t1 B SET A.pk=5,B.pk=7;
+
+SELECT * FROM t1;
+col_int_key	pk	col_int
+1	7	3
+
+UPDATE t2 AS A NATURAL JOIN t2 B SET A.pk_1=5,B.pk_1=7;
+
+UPDATE t2 AS A NATURAL JOIN t2 B SET A.pk_2=10,B.pk_2=11;
+
+SELECT * FROM t2;
+col_int_key	pk_1	pk_2	col_int
+1	7	11	4
+DROP TABLE t1,t2;

=== modified file 'mysql-test/r/multi_update_innodb.result'
--- a/mysql-test/r/multi_update_innodb.result	2011-02-21 15:49:03 +0000
+++ b/mysql-test/r/multi_update_innodb.result	2011-06-16 06:24:00 +0000
@@ -27,3 +27,43 @@ pk	a	b
 0	1	2
 DROP VIEW v1;
 DROP TABLE t1;
+#
+# BUG#11882110: UPDATE REPORTS ER_KEY_NOT_FOUND IF TABLE IS 
+#               UPDATED TWICE
+#
+CREATE TABLE t1 ( 
+col_int_key int, 
+pk int, 
+col_int int, 
+key(col_int_key), 
+primary key (pk)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (1,2,3);
+
+CREATE TABLE t2 ( 
+col_int_key int, 
+pk_1 int, 
+pk_2 int, 
+col_int int, 
+key(col_int_key), 
+primary key (pk_1,pk_2)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (1,2,3,4);
+
+UPDATE t1 AS A NATURAL JOIN t1 B SET A.pk=5,B.pk=7;
+ERROR HY000: Primary key/partition key update is not allowed since the table is updated both as 'A' and 'B'.
+
+SELECT * FROM t1;
+col_int_key	pk	col_int
+1	2	3
+
+UPDATE t2 AS A NATURAL JOIN t2 B SET A.pk_1=5,B.pk_1=7;
+ERROR HY000: Primary key/partition key update is not allowed since the table is updated both as 'A' and 'B'.
+
+UPDATE t2 AS A NATURAL JOIN t2 B SET A.pk_2=10,B.pk_2=11;
+ERROR HY000: Primary key/partition key update is not allowed since the table is updated both as 'A' and 'B'.
+
+SELECT * FROM t2;
+col_int_key	pk_1	pk_2	col_int
+1	2	3	4
+DROP TABLE t1,t2;

=== modified file 'mysql-test/t/multi_update.test'
--- a/mysql-test/t/multi_update.test	2011-03-08 17:39:25 +0000
+++ b/mysql-test/t/multi_update.test	2011-06-16 06:24:00 +0000
@@ -703,4 +703,50 @@ UPDATE t1 AS A, t1 AS B SET A.pk = 1, B.
 SELECT * FROM t1;
 DROP TABLE t1;
 
---echo end of tests
+--echo #
+--echo # BUG#11882110: UPDATE REPORTS ER_KEY_NOT_FOUND IF TABLE IS 
+--echo #               UPDATED TWICE
+--echo #
+
+# Results differ between storage engines. This test is to verify that
+# the bugfix did NOT change behavior for MyISAM.
+# See multi_update_innodb.test for the InnoDB variant of this test
+CREATE TABLE t1 ( 
+  col_int_key int, 
+  pk int, 
+  col_int int, 
+  key(col_int_key), 
+  primary key (pk)
+) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (1,2,3);
+
+--echo
+CREATE TABLE t2 ( 
+  col_int_key int, 
+  pk_1 int, 
+  pk_2 int, 
+  col_int int, 
+  key(col_int_key), 
+  primary key (pk_1,pk_2)
+) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (1,2,3,4);
+
+--echo
+UPDATE t1 AS A NATURAL JOIN t1 B SET A.pk=5,B.pk=7;
+
+--echo
+SELECT * FROM t1;
+
+--echo
+UPDATE t2 AS A NATURAL JOIN t2 B SET A.pk_1=5,B.pk_1=7;
+--echo
+UPDATE t2 AS A NATURAL JOIN t2 B SET A.pk_2=10,B.pk_2=11;
+
+--echo
+SELECT * FROM t2;
+
+DROP TABLE t1,t2;
+
+
+
+

=== modified file 'mysql-test/t/multi_update_innodb.test'
--- a/mysql-test/t/multi_update_innodb.test	2011-02-21 15:49:03 +0000
+++ b/mysql-test/t/multi_update_innodb.test	2011-06-16 06:24:00 +0000
@@ -31,3 +31,47 @@ SELECT * FROM t1;
 DROP VIEW v1;
 DROP TABLE t1;
 
+--echo #
+--echo # BUG#11882110: UPDATE REPORTS ER_KEY_NOT_FOUND IF TABLE IS 
+--echo #               UPDATED TWICE
+--echo #
+
+# Results differ between storage engines.
+# See multi_update.test for the MyISAM variant of this test
+CREATE TABLE t1 ( 
+  col_int_key int, 
+  pk int, 
+  col_int int, 
+  key(col_int_key), 
+  primary key (pk)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (1,2,3);
+
+--echo
+CREATE TABLE t2 ( 
+  col_int_key int, 
+  pk_1 int, 
+  pk_2 int, 
+  col_int int, 
+  key(col_int_key), 
+  primary key (pk_1,pk_2)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (1,2,3,4);
+
+--echo
+--error ER_MULTI_UPDATE_KEY_CONFLICT
+UPDATE t1 AS A NATURAL JOIN t1 B SET A.pk=5,B.pk=7;
+--echo
+SELECT * FROM t1;
+
+--echo
+--error ER_MULTI_UPDATE_KEY_CONFLICT
+UPDATE t2 AS A NATURAL JOIN t2 B SET A.pk_1=5,B.pk_1=7;
+--echo
+--error ER_MULTI_UPDATE_KEY_CONFLICT
+UPDATE t2 AS A NATURAL JOIN t2 B SET A.pk_2=10,B.pk_2=11;
+
+--echo
+SELECT * FROM t2;
+
+DROP TABLE t1,t2;

=== modified file 'sql/sql_update.cc'
--- a/sql/sql_update.cc	2011-02-21 15:49:03 +0000
+++ b/sql/sql_update.cc	2011-06-16 06:24:00 +0000
@@ -1071,17 +1071,27 @@ bool unsafe_key_update(TABLE_LIST *leave
             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)))
+          if (primkey_clustered)
           {
-            // 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;
+            // The primary key can cover multiple columns
+            KEY key_info= table1->key_info[table1->s->primary_key];
+            KEY_PART_INFO *key_part= key_info.key_part;
+            KEY_PART_INFO *key_part_end= key_part + key_info.key_parts;
+
+            for (;key_part != key_part_end; ++key_part)
+            {
+              if (bitmap_is_set(table1->write_set, key_part->fieldnr-1) ||
+                  bitmap_is_set(table2->write_set, key_part->fieldnr-1))
+              {
+                // 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;
+              }
+            }
           }
         }
       }

=== modified file 'sql/table.h'
--- a/sql/table.h	2011-03-25 09:06:07 +0000
+++ b/sql/table.h	2011-06-16 06:24:00 +0000
@@ -627,8 +627,8 @@ struct TABLE_SHARE
   uint db_options_in_use;		/* Options in use */
   uint db_record_offset;		/* if HA_REC_IN_SEQ */
   uint rowid_field_offset;		/* Field_nr +1 to rowid field */
-  /* Index of auto-updated TIMESTAMP field in field array */
-  uint primary_key;
+  /* Primary key index number, used in TABLE::key_info[] */
+  uint primary_key;                     
   uint next_number_index;               /* autoincrement key number */
   uint next_number_key_offset;          /* autoinc keypart offset in a key */
   uint next_number_keypart;             /* autoinc keypart number in a key */

No bundle (reason: useless for push emails).
Thread
bzr push into mysql-5.5 branch (jorgen.loland:3453 to 3454) Bug#11882110Jorgen Loland16 Jun