| Bug #94400 | Imported Foreign Keys don't work if defined before the referenced table | ||
|---|---|---|---|
| Submitted: | 19 Feb 2019 23:22 | Modified: | 1 Apr 2019 17:56 |
| Reporter: | Tim Düsterhus | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: DML | Severity: | S2 (Serious) |
| Version: | 8.0.15 | OS: | Debian (mysql:8 Docker Container) |
| Assigned to: | CPU Architecture: | x86 (amd64) | |
[19 Feb 2019 23:23]
Tim Düsterhus
dump.sql reproducing the issue
Attachment: dump.sql (application/sql, text), 524 bytes.
[19 Feb 2019 23:23]
Tim Düsterhus
Transcript of MySQL 5.7.25 showing no issue.
Attachment: mysql-5.txt (text/plain), 1.82 KiB.
[19 Feb 2019 23:23]
Tim Düsterhus
Transcript of MySQL 8.0.15 showing the issue.
Attachment: mysql-8.txt (text/plain), 1.86 KiB.
[19 Feb 2019 23:45]
MySQL Verification Team
Thank you for the bug report.
[11 Mar 2019 14:12]
Tim Düsterhus
I investigated a bit more and it appears that this is a regression within patch releases the MySQL 8 series: - It works fine with 8.0.13. - It does not work with 8.0.14.
[11 Mar 2019 14:13]
Tim Düsterhus
Transcript of MySQL 8.0.13 showing no issue.
Attachment: mysql-8.0.13.txt (text/plain), 1.86 KiB.
[11 Mar 2019 14:13]
Tim Düsterhus
Transcript of MySQL 8.0.14 showing the issue.
Attachment: mysql-8.0.14.txt (text/plain), 1.86 KiB.
[1 Apr 2019 17:56]
Paul DuBois
Posted by developer: Fixed in 8.0.16. Imported foreign keys did not work if defined before the referenced table.
Description: I have two tables `a` and `b` with `a` referencing a column in `b` with `ON DELETE CASCADE`. When I import a database dump, created using `mysqldump` with `FOREIGN_KEY_CHECKS=0` and `a` appearing before `b` then the `FOREIGN KEY` does not work: ``` mysql> SHOW CREATE TABLE a \G SELECT * FROM a; DELETE FROM b WHERE id = 11; SELECT * FROM a; DROP DATABASE test; CREATE DATABASE test; *************************** 1. row *************************** Table: a Create Table: CREATE TABLE `a` ( `id` int(10) NOT NULL, PRIMARY KEY (`id`), CONSTRAINT `fk` FOREIGN KEY (`id`) REFERENCES `b` (`id`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 1 row in set (0.00 sec) +----+ | id | +----+ | 1 | | 11 | +----+ 2 rows in set (0.00 sec) Query OK, 1 row affected (0.01 sec) +----+ | id | +----+ | 1 | | 11 | +----+ 2 rows in set (0.00 sec) Query OK, 2 rows affected (0.07 sec) Query OK, 1 row affected (0.01 sec) mysql> ``` It worked fine in a MySQL 5.7.25 Docker Container: ``` mysql> SHOW CREATE TABLE a \G SELECT * FROM a; DELETE FROM b WHERE id = 11; SELECT * FROM a; DROP DATABASE test; CREATE DATABASE test; *************************** 1. row *************************** Table: a Create Table: CREATE TABLE `a` ( `id` int(10) NOT NULL, PRIMARY KEY (`id`), CONSTRAINT `fk` FOREIGN KEY (`id`) REFERENCES `b` (`id`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row in set (0.00 sec) +----+ | id | +----+ | 1 | | 11 | +----+ 2 rows in set (0.00 sec) Query OK, 1 row affected (0.00 sec) +----+ | id | +----+ | 1 | +----+ 1 row in set (0.00 sec) Query OK, 2 rows affected (0.04 sec) Query OK, 1 row affected (0.00 sec) ``` If I run `ALTER TABLE a ENGINE=InnoDB` after doing the import it works as expected (as with MySQL 5.7.25). I will be attaching: - dump.sql : SQL dump with a reproducer - mysql-8.txt: Transcript of my session with MySQL 8.0.15, showing the bug. - mysql-5.txt: Transcript of my session with MySQL 5.7.25, showing that it works. Both transcripts have been executed with a fresh container: `docker run -it -e MYSQL_ROOT_PASSWORD=root --name mysql8 --rm mysql:8` How to repeat: 1. docker run -it -e MYSQL_ROOT_PASSWORD=root --name mysql8 --rm mysql:8 2. Create database. 3. Import dump.sql 4. Run: DELETE FROM b WHERE id = 11; 5. Run: SELECT * FROM a; 6. Notice that the record with id 11 is still there, despite the FOREIGN KEY existing.