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
Triage: Triaged: D2 (Serious)

[5 Aug 2010 16:07] Peter Gulutzan
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;
[5 Aug 2010 16:19] Valeriy Kravchuk
What exact source code tree you had used?
[5 Aug 2010 16:27] Peter Gulutzan
mysql-trunk, pulled yesterday
built with
BUILD/compile-pentium-max
[5 Aug 2010 17:25] Valeriy Kravchuk
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)
[9 Aug 2010 9:24] Mattias Jonsson
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.
[25 Aug 2010 14:00] Bugs System
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.
[5 Oct 2010 10:58] Mattias Jonsson
pushed into mysql-trunk-bugfixing and mysql-next-mr-bugfixing
[13 Nov 2010 16:08] Bugs System
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)
[13 Nov 2010 16:38] Bugs System
Pushed into mysql-next-mr (revid:alexander.nozdrin@oracle.com-20101113160336-atmtmfb3mzm4pz4i) (version source revid:jimmy.yang@oracle.com-20100804103744-vbpeghipkz6pyc9z) (pib:21)
[16 Nov 2010 13:49] Jon Stephens
Does not appear in any release: no changelog entry required. Closed.