Bug #84709 Renaming a table with ALTER TABLE ... COPY removes foreign keys

• Submitted: 30 Jan 2017 8:14
• Modified: 8 Apr 2019 8:23
• Reporter: Jon Olav Hauglid
• Status: Duplicate
• Category: MySQL Server: InnoDB storage engine
• Severity: S2 (Serious)
• Version: 5.6
• OS: Any
• CPU Architecture: Any

[30 Jan 2017 8:14] Jon Olav Hauglid

Description:
If a table is renamed with ALTER TABLE ... RENAME TO ... ALGORITHM=COPY,
any foreign keys in the table are lost.

How to repeat:
CREATE TABLE t1(c1 INT PRIMARY KEY) ENGINE=INNODB;
CREATE TABLE t2(c1 INT, FOREIGN KEY (c1) REFERENCES t1(c1)) ENGINE=INNODB;
SHOW CREATE TABLE t2;

ALTER TABLE t2 RENAME TO t3, ALGORITHM= COPY;
SHOW CREATE TABLE t3;
# Note that the FK is now gone 
SELECT * FROM information_schema.table_constraints WHERE table_name='t3';
# also gone from I_S.

INSERT INTO t3 VALUES(1);
# And values can be inserted in violation of the foreign key constraint.

DROP TABLE t3, t1;

[8 Apr 2019 8:22] Dmitry Lenev

Posted by developer:
 
Hello!

This bug has the same source as bug #11756183 / #48070 "FOREIGN KEYS MAY
DISAPPEAR AFTER ALTER TABLE RENAME". So I am closing it as a duplicate of
the latter bug.