Description:
This bug report is related to #88718: https://bugs.mysql.com/bug.php?id=88718
We have customers that dumped their database with a MySQL version affected by #88718. Thus the dump contains an incorrect case for the referenced columns of some foreign keys. This incorrect case is not always corrected on a reimport into a fixed MySQL 8 version. Specifically the case will still be broken if the table is created before the referenced table is.
I have added a minimal reproducer dump to the "How to repeat" field. The foreign key appears to be fully functional after the import, but it will still show the wrong case. Find below a session within the `mysql` command line client.
mysql> CREATE DATABASE test;
Query OK, 1 row affected (0.01 sec)
mysql> use test;
Database changed
mysql> /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE TABLE a (
-> A_UPPERCASE_ID INT NOT NULL PRIMARY KEY,
-> b_ref INT NOT NULL,
->
-> FOREIGN KEY (b_ref) REFERENCES b (b_uppercase_id)
-> );
Query OK, 0 rows affected (0.08 sec)
mysql>
mysql> CREATE TABLE b (
-> B_UPPERCASE_ID INT NOT NULL PRIMARY KEY
-> );
Query OK, 0 rows affected (0.06 sec)
mysql>
mysql> CREATE TABLE c (
-> C_UPPERCASE_ID INT NOT NULL PRIMARY KEY,
-> b_ref INT NOT NULL,
->
-> FOREIGN KEY (b_ref) REFERENCES b (b_uppercase_id)
-> );
Query OK, 0 rows affected (0.09 sec)
mysql> /*!40014 SET FOREIGN_KEY_CHECKS=IF(@OLD_FOREIGN_KEY_CHECKS IS NULL, 1, @OLD_FOREIGN_KEY_CHECKS) */;
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW CREATE TABLE a \G SHOW CREATE TABLE b \G SHOW CREATE TABLE c \G*************************** 1. row ***************************
Table: a
Create Table: CREATE TABLE `a` (
`A_UPPERCASE_ID` int NOT NULL,
`b_ref` int NOT NULL,
PRIMARY KEY (`A_UPPERCASE_ID`),
KEY `b_ref` (`b_ref`),
CONSTRAINT `a_ibfk_1` FOREIGN KEY (`b_ref`) REFERENCES `b` (`b_uppercase_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)
*************************** 1. row ***************************
Table: b
Create Table: CREATE TABLE `b` (
`B_UPPERCASE_ID` int NOT NULL,
PRIMARY KEY (`B_UPPERCASE_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)
*************************** 1. row ***************************
Table: c
Create Table: CREATE TABLE `c` (
`C_UPPERCASE_ID` int NOT NULL,
`b_ref` int NOT NULL,
PRIMARY KEY (`C_UPPERCASE_ID`),
KEY `b_ref` (`b_ref`),
CONSTRAINT `c_ibfk_1` FOREIGN KEY (`b_ref`) REFERENCES `b` (`B_UPPERCASE_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.01 sec)
=======
=======> Note that the case of the foreign key has been corrected for table `c`, but not for table `b`.
=======
mysql> INSERT INTO b (B_UPPERCASE_ID) VALUES (1);
Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO a (A_UPPERCASE_ID, b_ref) VALUES (1, 1);
Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO a (A_UPPERCASE_ID, b_ref) VALUES (2, 2);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`a`, CONSTRAINT `a_ibfk_1` FOREIGN KEY (`b_ref`) REFERENCES `b` (`b_uppercase_id`))
=======
=======> Note how the foreign key is fully functional.
=======
The case will still be broken after dumping the database again:
root@6c3c3cc1c192:/# mysqldump -proot test |grep CONSTRAINT
mysqldump: [Warning] Using a password on the command line interface can be insecure.
CONSTRAINT `a_ibfk_1` FOREIGN KEY (`b_ref`) REFERENCES `b` (`b_uppercase_id`)
CONSTRAINT `c_ibfk_1` FOREIGN KEY (`b_ref`) REFERENCES `b` (`B_UPPERCASE_ID`)
Thus the customer is unable to fix the case of their foreign keys without manually editing the database dump.
How to repeat:
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
CREATE TABLE a (
A_UPPERCASE_ID INT NOT NULL PRIMARY KEY,
b_ref INT NOT NULL,
FOREIGN KEY (b_ref) REFERENCES b (b_uppercase_id)
);
CREATE TABLE b (
B_UPPERCASE_ID INT NOT NULL PRIMARY KEY
);
CREATE TABLE c (
C_UPPERCASE_ID INT NOT NULL PRIMARY KEY,
b_ref INT NOT NULL,
FOREIGN KEY (b_ref) REFERENCES b (b_uppercase_id)
);
/*!40014 SET FOREIGN_KEY_CHECKS=IF(@OLD_FOREIGN_KEY_CHECKS IS NULL, 1, @OLD_FOREIGN_KEY_CHECKS) */;
