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] MySQL Verification Team
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".

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.