Bug #96365 FK table name casing is omitted on MySQL 8 lower_case_table_names=2
Submitted: 29 Jul 2019 17:33 Modified: 29 Jul 2019 20:03
Reporter: Mike Bayer Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DDL Severity:S3 (Non-critical)
Version:8.0.17 OS:Any
Assigned to: CPU Architecture:Any
Tags: foreign key, lower case

[29 Jul 2019 17:33] Mike Bayer
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)
[29 Jul 2019 20:03] Miguel Solorzano
Thank you for the bug report.
[26 Aug 2019 19:24] Dmitry Lenev
Posted by developer:
 
Hello!

The problem described in this report is the same as issue
described in bug #25583288 "DO NOT USE HA_INNOBASE::GET_FOREIGN_KEY_CREATE_INFO() FOR SHOW CREATE TABLE FK".
The latter bug has been erroneously closed as fixed earlier,
but was re-opened today and updated accordingly.

Taking into account the above I am marking this bug as Duplicate
of bug #25583288 "DO NOT USE HA_INNOBASE::GET_FOREIGN_KEY_CREATE_INFO() FOR SHOW CREATE TABLE FK".