bzr commit into mysql-trunk-bugfixing branch (mattias.jonsson:3237) Bug#55784

[Mattias Jonsson]

#At file:///Users/mattiasj/mysql-bzr/b55784-trunk-bf/ based on revid:holyfoot@stripped

 3237 Mattias Jonsson	2010-08-25
      Bug#55784: Foreign key integrity broken by alter table
      
      By using EXCHANGE PARTITION WITH TABLE
      it was possible to break foreign key integrity.
      
      EXCHANGE PARTITION did not verify that the table
      to exchange with was not referenced by other
      tables foreign keys.
      
      Solved by checking this with ::can_switch_engine
     @ mysql-test/r/partition_exchange.result
        updated result
     @ mysql-test/t/partition_exchange.test
        added test
     @ sql/share/errmsg-utf8.txt
        Added error message for foreign key in table to
        exchange with.
     @ sql/sql_partition_admin.cc
        Added check for foreign key referencing the table.

    modified:
      mysql-test/r/partition_exchange.result
      mysql-test/t/partition_exchange.test
      sql/share/errmsg-utf8.txt
      sql/sql_partition_admin.cc
=== modified file 'mysql-test/r/partition_exchange.result'
--- a/mysql-test/r/partition_exchange.result	2010-08-04 11:53:45 +0000
+++ b/mysql-test/r/partition_exchange.result	2010-08-25 14:00:02 +0000
@@ -1,3 +1,16 @@
+DROP TABLE IF EXISTS t1, t2, t3, t, tp, tsp, tmp;
+CREATE TABLE t1 (s1 INT PRIMARY KEY) engine=innodb;
+CREATE TABLE t2 (s1 INT, FOREIGN KEY (s1) REFERENCES t1 (s1)) engine=innodb;
+INSERT INTO t1 VALUES (1),(2),(3);
+INSERT INTO t2 VALUES (1),(2),(3);
+CREATE TABLE t3 (s1 INT PRIMARY KEY)
+engine=innodb
+PARTITION BY LIST (s1)
+(PARTITION p1 VALUES IN (1,2,3));
+ALTER TABLE t3 EXCHANGE PARTITION p1 WITH TABLE t1;
+ERROR HY000: Table to exchange with partition have foreign key references: 't1'
+DROP TABLE t2, t1, t3;
+# Tests for WL#4445
 CREATE TABLE t (a INT,
 b VARCHAR(55),
 PRIMARY KEY (a))

=== modified file 'mysql-test/t/partition_exchange.test'
--- a/mysql-test/t/partition_exchange.test	2010-08-04 11:53:45 +0000
+++ b/mysql-test/t/partition_exchange.test	2010-08-25 14:00:02 +0000
@@ -1,5 +1,29 @@
 --source include/have_innodb.inc
 --source include/have_partition.inc
+
+--disable_warnings
+DROP TABLE IF EXISTS t1, t2, t3, t, tp, tsp, tmp;
+--enable_warnings
+
+CREATE TABLE t1 (s1 INT PRIMARY KEY) engine=innodb;
+
+CREATE TABLE t2 (s1 INT, FOREIGN KEY (s1) REFERENCES t1 (s1)) engine=innodb;
+
+INSERT INTO t1 VALUES (1),(2),(3);
+
+INSERT INTO t2 VALUES (1),(2),(3);
+
+CREATE TABLE t3 (s1 INT PRIMARY KEY)
+  engine=innodb
+  PARTITION BY LIST (s1)
+  (PARTITION p1 VALUES IN (1,2,3));
+
+--error ER_PARTITION_EXCHANGE_FOREIGN_KEY
+ALTER TABLE t3 EXCHANGE PARTITION p1 WITH TABLE t1;
+
+DROP TABLE t2, t1, t3;
+
+--echo # Tests for WL#4445
 CREATE TABLE t (a INT,
   b VARCHAR(55),
   PRIMARY KEY (a))

=== modified file 'sql/share/errmsg-utf8.txt'
--- a/sql/share/errmsg-utf8.txt	2010-07-29 14:15:38 +0000
+++ b/sql/share/errmsg-utf8.txt	2010-08-25 14:00:02 +0000
@@ -6387,3 +6387,6 @@ ER_TABLES_DIFFERENT_METADATA
 ER_ROW_DOES_NOT_MATCH_PARTITION
   eng "Found row that does not match the partition"
   swe "Hittade rad som inte passar i partitionen"
+ER_PARTITION_EXCHANGE_FOREIGN_KEY
+  eng "Table to exchange with partition have foreign key references: '%-.64s'"
+  swe "Tabellen att byta ut mot partition har foreign key referenser: '%-.64s'"

=== modified file 'sql/sql_partition_admin.cc'
--- a/sql/sql_partition_admin.cc	2010-08-16 15:07:53 +0000
+++ b/sql/sql_partition_admin.cc	2010-08-25 14:00:02 +0000
@@ -145,6 +145,14 @@ static bool check_exchange_partition(TAB
              table->s->table_name.str);
     DBUG_RETURN(TRUE);
   }
+
+  /* The table cannot have foreign keys constraints or be referenced */
+  if(!table->file->can_switch_engines())
+  {
+    my_error(ER_PARTITION_EXCHANGE_FOREIGN_KEY, MYF(0),
+             table->s->table_name.str);
+    DBUG_RETURN(TRUE);
+  }
   DBUG_RETURN(FALSE);
 }
 


Attachment: [text/bzr-bundle] bzr/[e-mail]