Bug #92317 Possible to add inconsistent parent to orphan foreign key if SE changed.

• Submitted: 6 Sep 2018 9:57
• Modified: 10 Oct 2018 18:38
• Reporter: Dmitry Lenev
• Status: Closed
• Category: MySQL Server: DDL
• Severity: S3 (Non-critical)
• Version: 8.0.14
• OS: Any
• CPU Architecture: Any

[6 Sep 2018 9:57] Dmitry Lenev

Description:
Normally attempt to add parent table to previously orphan foreign key
will lead to error if foreign key definition will be inconsistent as
result (e.g. missing parent column or key).

However it is possible to create such inconsistent foreign key by
adding parent table in different SE and then changing SE to the
the same storage engine as child table.

This is not supposed to happen in 8.0 branch. 

How to repeat:
SET FOREIGN_KEY_CHECKS=0;
CREATE TABLE child (fk INT, FOREIGN KEY (fk) REFERENCES parent (pk));
--error ER_FK_NO_INDEX_PARENT
CREATE TABLE parent (pk INT);
CREATE TABLE parent (pk INT) ENGINE=MyISAM;
--echo # Succeeds!
ALTER TABLE parent ENGINE=InnoDB;

DROP TABLE parent;
--error ER_FK_NO_COLUMN_PARENT
CREATE TABLE parent (a INT);
CREATE TABLE parent (a INT) ENGINE=MyISAM;
--echo # Succeeds!
ALTER TABLE parent ENGINE=InnoDB;

[10 Oct 2018 18:38] Paul DuBois

Posted by developer:
 
Fixed in 8.0.14.

It was possible to create an inconsistent foreign key by adding a
parent table with a different storage engine from the child table,
then changing the parent table to the same storage engine as the
child table.