Details
-
Bug
-
Status: Open (View Workflow)
-
Minor
-
Resolution: Unresolved
-
10.4.12
-
mariadb:10.4 Docker Image
Description
This bug report is effectively a duplicate of a MySQL bug I just reported in Oracle's bug tracker: https://bugs.mysql.com/bug.php?id=98976. I was investigating whether MariaDB 10.4 would allow the customer to fix their database dump. I'm copying over the description from there.
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 [MariaDB note: I attached it as a file]. 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 `a`.
=======
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.
Attachments
Issue Links
- relates to
-
MDEV-16417 Store Foreign Key metadata outside of InnoDB
-
- In Review
-