[#MDEV-21964] Case of referenced column of foreign key not corrected on import

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.

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`))

	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.

[MDEV-21964] Case of referenced column of foreign key not corrected on import Created: 2020-03-17 Updated: 2020-04-28
Status:	Open
Project:	MariaDB Server
Component/s:	Data Definition - Create Table, Information Schema
Affects Version/s:	10.4.12
Fix Version/s:	10.6

[MDEV-21964] Case of referenced column of foreign key not corrected on import Created: 2020-03-17 Updated: 2020-04-28