Bug #96365 FK table name casing is omitted on MySQL 8 lower_case_table_names=2
Description:
This issue is very similar to that reported in https://bugs.mysql.com/bug.php?id=88718, however it affects the table name reported in the foreign key, not the column name, and this does appear to be a regression with MySQL 8.0 that does not occur in version 5.7. When calling SHOW CREATE TABLE, the table name reported in a foreign key constraint comes out as all lower case, when on a case-insensitive filesystem with lower_case_table_names=2. This does not occur in MySQL server 5.7.
How to repeat:
First establish that the database is on a case-insensitive filesystem with lower_case_table_names=2:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 30
Server version: 8.0.17 MySQL Community Server - GPL
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show variables like 'lower_case_table_names';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| lower_case_table_names | 2 |
+------------------------+-------+
1 row in set (0.00 sec)
Create two tables very similar to those mentioned in the fourth or so comment in https://bugs.mysql.com/bug.php?id=88718, except make the casing of the parent table name also mixed case:
CREATE TABLE `Table_One` (
`Id` INTEGER PRIMARY KEY
);
CREATE TABLE `table_two` (
`TABLE_ONE_ID` INTEGER,
FOREIGN KEY (`TABLE_ONE_ID`) REFERENCES `Table_One` (`Id`)
);
we can see that SHOW CREATE TABLE `table_two` reports the name of `Table_One` using all lower case:
mysql> SHOW CREATE TABLE `table_two`;
+-----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| table_two | CREATE TABLE `table_two` (
`TABLE_ONE_ID` int(11) DEFAULT NULL,
KEY `TABLE_ONE_ID` (`TABLE_ONE_ID`),
CONSTRAINT `table_two_ibfk_1` FOREIGN KEY (`TABLE_ONE_ID`) REFERENCES `table_one` (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
Running the identical steps / configuration for MySQL 5.7 yields the correct result:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.27 MySQL Community Server (GPL)
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> CREATE TABLE `Table_One` (
-> `Id` INTEGER PRIMARY KEY
-> );
Query OK, 0 rows affected (0.04 sec)
mysql>
mysql> CREATE TABLE `table_two` (
-> `TABLE_ONE_ID` INTEGER,
-> FOREIGN KEY (`TABLE_ONE_ID`) REFERENCES `Table_One` (`Id`)
-> );
Query OK, 0 rows affected (0.04 sec)
mysql> SHOW CREATE TABLE `table_two`;
+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| table_two | CREATE TABLE `table_two` (
`TABLE_ONE_ID` int(11) DEFAULT NULL,
KEY `TABLE_ONE_ID` (`TABLE_ONE_ID`),
CONSTRAINT `table_two_ibfk_1` FOREIGN KEY (`TABLE_ONE_ID`) REFERENCES `Table_One` (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
Portions of this website are copyright © 2001, 2002 The PHP Group
Page generated in 0.041 sec. using MySQL 8.4.7-u6-cloud
Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the
respective copyright holders.
It is not reviewed in advance
by Oracle and does not necessarily represent the opinion of
Oracle or any other party.