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

        1. bug.sql
          0.5 kB
          Tim Düsterhus

        Issue Links

          Activity

            People

              midenok Aleksey Midenkov
              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.