Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-21964

Case of referenced column of foreign key not corrected on import

    XMLWordPrintable

    Details

      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

            Activity

              People

              Assignee:
              midenok Aleksey Midenkov
              Reporter:
              TimWolla Tim Düsterhus
              Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

                Dates

                Created:
                Updated:

                  Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.