Bug #100441 The Foreign Key constraint is not updated after renaming

• Submitted: 6 Aug 2020 10:38
• Modified: 2 Sep 2020 8:33
• Reporter: zhijun long
• Status: Not a Bug
• Category: MySQL Server: DDL
• Severity: S3 (Non-critical)
• Version: 8.0.21
• OS: Any
• CPU Architecture: Any
• Tags: regression

[6 Aug 2020 10:38] zhijun long

Description:
Hi, all

The Foreign Key constraint is not updated after renaming. 

SET FOREIGN_KEY_CHECKS=0;
CREATE TABLE child (fk INT, FOREIGN KEY(fk) REFERENCES parent (pk)) ENGINE=InnoDB;
CREATE TABLE parent (pk INT PRIMARY KEY) ENGINE=MyISAM;
ALTER TABLE parent ENGINE=InnoDB, RENAME TO parent0;
CREATE TABLE parent (pk INT PRIMARY KEY) ENGINE=MyISAM;
SHOW CREATE TABLE child;
Table   Create Table
child   CREATE TABLE `child` (
  `fk` int DEFAULT NULL,
  KEY `fk` (`fk`),
  CONSTRAINT `child_ibfk_1` FOREIGN KEY (`fk`) REFERENCES `parent0` (`pk`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
LOCK TABLES parent WRITE;
ALTER TABLE parent ENGINE=InnoDB;
UNLOCK TABLES;
DROP TABLES child, parent;
DROP TABLES parent0;

As can be seen from the above results, Table child references to table parent0 instead of table parent. The following compares the results of mysql-5.7.30.

SET FOREIGN_KEY_CHECKS=0;
CREATE TABLE child (fk INT, FOREIGN KEY(fk) REFERENCES parent (pk)) ENGINE=InnoDB;
CREATE TABLE parent (pk INT PRIMARY KEY) ENGINE=MyISAM;
ALTER TABLE parent ENGINE=InnoDB, RENAME TO parent0;
CREATE TABLE parent (pk INT PRIMARY KEY) ENGINE=MyISAM;
SHOW CREATE TABLE child;
Table   Create Table
child   CREATE TABLE `child` (
  `fk` int(11) DEFAULT NULL,
  KEY `fk` (`fk`),
  CONSTRAINT `child_ibfk_1` FOREIGN KEY (`fk`) REFERENCES `parent` (`pk`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
LOCK TABLES parent WRITE;
ALTER TABLE parent ENGINE=InnoDB;
UNLOCK TABLES;
DROP TABLES child, parent;
DROP TABLES parent0;

How to repeat:
SET FOREIGN_KEY_CHECKS=0;
CREATE TABLE child (fk INT, FOREIGN KEY(fk) REFERENCES parent (pk)) ENGINE=InnoDB;

CREATE TABLE parent (pk INT PRIMARY KEY) ENGINE=MyISAM;
ALTER TABLE parent ENGINE=InnoDB, RENAME TO parent0;
#RENAME TABLE parent0 TO parent;
#DROP TABLE parent;

CREATE TABLE parent (pk INT PRIMARY KEY) ENGINE=MyISAM;
SHOW CREATE TABLE child;
LOCK TABLES parent WRITE;
#--error ER_TABLE_NOT_LOCKED
ALTER TABLE parent ENGINE=InnoDB;
UNLOCK TABLES;
DROP TABLES child, parent;
DROP TABLES parent0;

[6 Aug 2020 10:53] MySQL Verification Team

Hello zhijun long,

Thank you for the report and test case.

regards,
Umesh

[2 Sep 2020 8:33] Dmitry Lenev

Posted by developer:
 
Hello!

What you observe is not a bug but intended and expected behavior change in 8.0. 

The "ALTER TABLE parent ENGINE=InnoDB, RENAME TO parent0" which is executed
in the test case is equivalent to combination of two statements:

ALTER TABLE parent ENGINE=InnoDB;

and

RENAME TABLE parent TO parent0;

The former makes the foreign key in question "valid" as after it both child
and parent table in relationship belong to InnoDB.
The latter renames the table and updates the foreign key definition that
references it to use new parent table name (this is how both RENAME TABLE
and ALTER TABLE ... RENAME behave for parent tables in FKs).

Hence, the SHOW CREATE TABLE output for child table in which foreign key
references 'parent0'.

Creation of table named 'parent' later doesn't matter, as at this point
foreign key already has different table as a parent.

In 8.0 we have switched to use new SQL-layer data-dictionary to store
information about FKsand thanks to this were able to move most of FK checks
in DDL to SQL-layer. As result several issues involving FKs and DDL were
fixed, specifically issues related to COPY-ing ALTER TABLE and RENAME
(see bug #11756183 / bug#48070 "FOREIGN KEYS MAY DISAPPEAR AFTER ALTER
TABLE RENAME", for example), i.e. exactly in the above case.
These changes resulted in the observed behavior change from 5.7 and early
versions of 8.0.

Also, I would like to advise against creation of foreign keys that reference
existing tables in another storage engine. This is only supported for
historical reasons (because it was easier to create table in wrong SE on
error in earlier versions of MySQL Server) and we might stop supporting
this functionality. Creation of foreign keys that reference non-existing
tables (with FOREIGN_KEY_CHECKS option set to 0) should be fine though.

Taking into account the above I am marking this report as Not a bug.