List:Commits« Previous MessageNext Message »
From:Jorgen Loland Date:June 9 2011 10:35am
Subject:bzr commit into mysql-trunk branch (jorgen.loland:3172) Bug#11882110
View as plain text  
#At file:///export/home/jl208045/mysql/mysql-trunk-11882110/ based on revid:alexander.nozdrin@stripped

 3172 Jorgen Loland	2011-06-09
      BUG#11882110: UPDATE REPORTS ER_KEY_NOT_FOUND IF TABLE IS 
                    UPDATED TWICE
      
      For multi update it is not allowed to update certain columns
      of a table if that table is accessed through multiple aliases
      if either
      1) one of the updated columns is used as partitioning key
      2) one of the updated columns 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.
      
      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 is 
      updated.
     @ 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
        Remove comment that has been introduced by a 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
=== modified file 'mysql-test/r/multi_update.result'
--- a/mysql-test/r/multi_update.result	2011-03-08 19:14:42 +0000
+++ b/mysql-test/r/multi_update.result	2011-06-09 10:35:26 +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:31:41 +0000
+++ b/mysql-test/r/multi_update_innodb.result	2011-06-09 10:35:26 +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 19:14:42 +0000
+++ b/mysql-test/t/multi_update.test	2011-06-09 10:35:26 +0000
@@ -703,4 +703,49 @@ 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.
+# 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:31:41 +0000
+++ b/mysql-test/t/multi_update_innodb.test	2011-06-09 10:35:26 +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-06-09 08:58:41 +0000
+++ b/sql/sql_update.cc	2011-06-09 10:35:26 +0000
@@ -1069,17 +1069,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)))
-          {
-            // 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;
+          if (primkey_clustered)
+          { 
+            // 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-05-26 15:20:09 +0000
+++ b/sql/table.h	2011-06-09 10:35:26 +0000
@@ -669,7 +669,6 @@ 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;
   uint next_number_index;               /* autoincrement key number */
   uint next_number_key_offset;          /* autoinc keypart offset in a key */


Attachment: [text/bzr-bundle] bzr/jorgen.loland@oracle.com-20110609103526-2j01hg1e5sg9k0pw.bundle
Thread
bzr commit into mysql-trunk branch (jorgen.loland:3172) Bug#11882110Jorgen Loland9 Jun
  • Re: bzr commit into mysql-trunk branch (jorgen.loland:3172) Bug#11882110Jon Olav Hauglid10 Jun