MySQL Bugs: #88082: ADD FOREIGN KEY fails with "table already exists" when lower_case_table

Bug #88082	ADD FOREIGN KEY fails with "table already exists" when lower_case_table_names=1
Submitted:	12 Oct 2017 19:42	Modified:	8 Apr 2019 19:34
Reporter:	Bradley Grainger (OCA)	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S3 (Non-critical)
Version:	5.6/5.7	OS:	Debian (Docker mysql:5.7)
Assigned to:		CPU Architecture:	Any

Description:
When lower_case_table_names=1, an automatically-generated constraint name that conflicts (case insensitively) with an existing constraint name will cause an "ALTER TABLE ADD FOREIGN KEY" statement to fail with the misleading message "Table 'x' already exists"

The error log does contain a clue as to the underlying problem, but still doesn't provide a real reason for why ALTER TABLE is failing:

[ERROR] InnoDB: Possible reasons:
[ERROR] InnoDB: (1) Table rename would cause two FOREIGN KEY constraints to have the same internal name in case-insensitive comparison.
[ERROR] InnoDB: (2) Table `testdb`.`test` exists in the InnoDB internal data dictionary though MySQL is trying to rename table `testdb`.`#sql-1_3` to it. Have you deleted the .frm file and not used DROP TABLE?
[Note] InnoDB: Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting.html for how to resolve the issue.
[ERROR] InnoDB: If table `testdb`.`test` is a temporary table #sql..., then it can be that there are still queries running on the table, and it will be dropped automatically when the queries end. You can drop the orphaned table inside InnoDB by creating an InnoDB table with the same name in another database and copying the .frm file to the current database. Then MySQL thinks the table exists, and DROP TABLE will succeed.

How to repeat:
Start a MySQL 5.7 instance with lower_case_table_names=1. For example, with Docker:

docker run --name lower_case_test -e MYSQL_ROOT_PASSWORD=test -e MYSQL_DATABASE=testdb -p 3306:3306 -d mysql:5.7 --lower-case-table-names=1

Connect to the server and execute the following SQL (for Docker: docker exec -it lower_case_test mysql -uroot -ptest)

/* ---------- */
USE testdb;
CREATE TABLE Foreign1(Foreign1Id INT NOT NULL AUTO_INCREMENT PRIMARY KEY);
CREATE TABLE Foreign2(Foreign2Id INT NOT NULL AUTO_INCREMENT PRIMARY KEY);

CREATE TABLE Test
(
  Foreign1Id INT,
  Foreign2Id INT,
  CONSTRAINT Test_ibfk_1 FOREIGN KEY (Foreign1Id) REFERENCES Foreign1 (Foreign1Id)
);

ALTER TABLE Test ADD FOREIGN KEY (Foreign2Id) REFERENCES Foreign2 (Foreign2Id);
/* ---------- */

The final SQL statement errors with:
ERROR 1050 (42S01): Table './testdb/test' already exists

Expected behaviour: the ALTER TABLE statement succeeds and adds a new FK constraint.

Workaround: Explicitly name the new constraint, e.g.,
ALTER TABLE Test ADD CONSTRAINT `Test_ibfk_2` FOREIGN KEY (Foreign2Id) REFERENCES Foreign2 (Foreign2Id);

Suggested fix:
When new constraint names are automatically generated, use case-insensitive comparison to find a unique name for the new constraint.

I tested with 8.0.3-rc-log (in Docker, mysql:8.0) and cannot reproduce this problem in that version.

The FK constraint is added with the auto-generated name "test_ibfk_2".

This may be a duplicate of #72751.

Link to possible duplicate case: https://bugs.mysql.com/bug.php?id=72751

miguel@tikal:~/dbs $ ./57c
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.21 Source distribution 2017-OCT-01

Copyright (c) 2000, 2017, 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 5.7 > create database testdb;
Query OK, 1 row affected (0.02 sec)

mysql 5.7 > use testdb
Database changed
mysql 5.7 > CREATE TABLE Foreign1(Foreign1Id INT NOT NULL AUTO_INCREMENT PRIMARY KEY);
Query OK, 0 rows affected (0.44 sec)

mysql 5.7 > CREATE TABLE Foreign2(Foreign2Id INT NOT NULL AUTO_INCREMENT PRIMARY KEY);
Query OK, 0 rows affected (0.31 sec)

mysql 5.7 > 
mysql 5.7 > CREATE TABLE Test
    -> (
    ->   Foreign1Id INT,
    ->   Foreign2Id INT,
    ->   CONSTRAINT Test_ibfk_1 FOREIGN KEY (Foreign1Id) REFERENCES Foreign1 (Foreign1Id)
    -> );
Query OK, 0 rows affected (0.36 sec)

mysql 5.7 > 
mysql 5.7 > ALTER TABLE Test ADD FOREIGN KEY (Foreign2Id) REFERENCES Foreign2 (Foreign2Id);
ERROR 1050 (42S01): Table './testdb/test' already exists
mysql 5.7 > 
-----------------------------------------------------------------------------------------------
miguel@tikal:~/dbs $ ./56c
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.39 Source distribution 2017-OCT-01

Copyright (c) 2000, 2017, 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 5.6 > create database testdb;
Query OK, 1 row affected (0.01 sec)

mysql 5.6 > USE testdb;
Database changed
mysql 5.6 > CREATE TABLE Foreign1(Foreign1Id INT NOT NULL AUTO_INCREMENT PRIMARY KEY);
Query OK, 0 rows affected (0.45 sec)

mysql 5.6 > CREATE TABLE Foreign2(Foreign2Id INT NOT NULL AUTO_INCREMENT PRIMARY KEY);
Query OK, 0 rows affected (0.29 sec)

mysql 5.6 > 
mysql 5.6 > CREATE TABLE Test
    -> (
    ->   Foreign1Id INT,
    ->   Foreign2Id INT,
    ->   CONSTRAINT Test_ibfk_1 FOREIGN KEY (Foreign1Id) REFERENCES Foreign1 (Foreign1Id)
    -> );
Query OK, 0 rows affected (0.33 sec)

mysql 5.6 > 
mysql 5.6 > ALTER TABLE Test ADD FOREIGN KEY (Foreign2Id) REFERENCES Foreign2 (Foreign2Id);
ERROR 1050 (42S01): Table './testdb/test' already exists
mysql 5.6 > show variables like "%lower%";
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| lower_case_file_system | OFF   |
| lower_case_table_names | 1     |
+------------------------+-------+
2 rows in set (0.00 sec)

mysql 5.6 > 

--------------------------------------------------------------------------------------------
miguel@tikal:~/dbs $ ./80c
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.4-rc-log Source distribution 2017-OCT-01

Copyright (c) 2000, 2017, 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 8.0 > create database testdb;
Query OK, 1 row affected (0.11 sec)

mysql 8.0 > USE testdb;
Database changed
mysql 8.0 > CREATE TABLE Foreign1(Foreign1Id INT NOT NULL AUTO_INCREMENT PRIMARY KEY);
Query OK, 0 rows affected (0.50 sec)

mysql 8.0 > CREATE TABLE Foreign2(Foreign2Id INT NOT NULL AUTO_INCREMENT PRIMARY KEY);
Query OK, 0 rows affected (0.49 sec)

mysql 8.0 > 
mysql 8.0 > CREATE TABLE Test
    -> (
    ->   Foreign1Id INT,
    ->   Foreign2Id INT,
    ->   CONSTRAINT Test_ibfk_1 FOREIGN KEY (Foreign1Id) REFERENCES Foreign1 (Foreign1Id)
    -> );
Query OK, 0 rows affected (0.58 sec)

mysql 8.0 > 
mysql 8.0 > ALTER TABLE Test ADD FOREIGN KEY (Foreign2Id) REFERENCES Foreign2 (Foreign2Id);
Query OK, 0 rows affected (1.41 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql 8.0 > show variables like "%lower%";
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| lower_case_file_system | OFF   |
| lower_case_table_names | 1     |
+------------------------+-------+
2 rows in set (0.03 sec)

Thank you for the bug report. Verified on CentOS 7.

Posted by developer:
 
This bug report describes the same issue as one described in bug #19156435
"CREATING FOREIGN KEY FAILS ON CASE-INSENSITIVE FILESYSTEM IN SOME CASES"
(the only difference is that in the latter case --lower-case-table-names > 0
 is induced by filesystem type).

So I am closing this bug report as a duplicate.

The bugs.mysql.com bug number for bug #19156435 "CREATING FOREIGN KEY FAILS ON CASE-INSENSITIVE FILESYSTEM IN SOME CASES" is bug#72751.