Bug #94400 Imported Foreign Keys don't work if defined before the referenced table
Submitted: 19 Feb 23:22 Modified: 1 Apr 17:56
Reporter: Tim Düsterhus Email Updates:
Status: Closed Impact on me:
None 
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 23:22] Tim Düsterhus
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.
[19 Feb 23:23] Tim Düsterhus
dump.sql reproducing the issue

Attachment: dump.sql (application/sql, text), 524 bytes.

[19 Feb 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 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 23:45] Miguel Solorzano
Thank you for the bug report.
[11 Mar 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 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 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 17:56] Paul Dubois
Posted by developer:
 
Fixed in 8.0.16.

Imported foreign keys did not work if defined before the referenced
table.