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: | |
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 |
[5 Aug 2010 16:07]
Peter Gulutzan
[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.