MySQL Bugs: #55784: Foreign key integrity broken by alter table

Bug #55784	Foreign key integrity broken by alter table
Submitted:	5 Aug 2010 16:07	Modified:	16 Nov 2010 13:49
Reporter:	Peter Gulutzan	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Partitions	Severity:	S3 (Non-critical)
Version:	5.6.0-m4	OS:	Linux (SUSE 64-bit)
Assigned to:	Mattias Jonsson	CPU Architecture:	Any

Description:
I'm using the new (WL#4445) statement
ALTER TABLE ... EXCHANGE PARTITION ... WITH TABLE ...;

If table t2 has a foreign key that references table t1,
I can remove everything from t1, leaving t2 with rows
that reference nothing.

That's a foreign-key integrity violation.

How to repeat:
DROP TABLE IF EXISTS t1,t2;
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 ;
SELECT * FROM t1;
SELECT * FROM t2;

What exact source code tree you had used?

mysql-trunk, pulled yesterday
built with
BUILD/compile-pentium-max

Verified just as described with current mysql-trunk on Mac OS X:

macbook-pro:trunk openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.6.0-m4-debug Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> DROP TABLE IF EXISTS t1,t2;
Query OK, 0 rows affected, 2 warnings (0.00 sec)

mysql> CREATE TABLE t1 (s1 INT PRIMARY KEY) engine=innodb;
Query OK, 0 rows affected (0.08 sec)

mysql> CREATE TABLE t2 (s1 INT, FOREIGN KEY (s1) REFERENCES t1 (s1)) engine=innodb;
Query OK, 0 rows affected (0.14 sec)

mysql> INSERT INTO t1 VALUES (1),(2),(3);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> INSERT INTO t2 VALUES (1),(2),(3);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> CREATE TABLE t3 (s1 INT PRIMARY KEY) engine=innodb PARTITION BY LIST (s1)    -> (PARTITION p1 VALUES IN (1,2,3));
Query OK, 0 rows affected (0.16 sec)

mysql> ALTER TABLE t3 EXCHANGE PARTITION p1 WITH TABLE t1 ;
Query OK, 0 rows affected (0.62 sec)

mysql> SELECT * FROM t1;
Empty set (0.00 sec)

mysql> SELECT * FROM t2;
+------+
| s1   |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.00 sec)

This is the same behavior as when doing:
CREATE TABLE t4 (s1 INT PRIMARY KEY) engine=innodb;
RENAME TABLE t1 to t1tmp, t4 to t1, t1tmp to t4;
SELECT * FROM t1;
s1
SELECT * FROM t2;
s1
1
2
3

But that may be a bug as well.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/116748

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.

pushed into mysql-trunk-bugfixing and mysql-next-mr-bugfixing

Pushed into mysql-trunk 5.6.99-m5 (revid:alexander.nozdrin@oracle.com-20101113155825-czmva9kg4n31anmu) (version source revid:jimmy.yang@oracle.com-20100804103744-vbpeghipkz6pyc9z) (merge vers: 5.1.51) (pib:21)

Pushed into mysql-next-mr (revid:alexander.nozdrin@oracle.com-20101113160336-atmtmfb3mzm4pz4i) (version source revid:jimmy.yang@oracle.com-20100804103744-vbpeghipkz6pyc9z) (pib:21)

Does not appear in any release: no changelog entry required. Closed.