MySQL Bugs: #72751: Creating foreign key fails on case-insensitive filesystem in some cases

Bug #72751	Creating foreign key fails on case-insensitive filesystem in some cases
Submitted:	26 May 2014 13:26	Modified:	4 Jul 2014 9:43
Reporter:	Vincent Huisman	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S3 (Non-critical)
Version:	5.6.17, 5.6.20	OS:	MacOS
Assigned to:		CPU Architecture:	Any

Description:
I haven't been able to test it properly on other systems and versions but I guess this behaviour is present on any instance running on a case-insensitive filesystem, so it's probable that other versions and operating systems are affected as well.

This behaviour was observed when trying to ALTER a table with a mixed-case name with existing foreign keys, which was imported from another instance with a case-sensitive filesystem, to add an extra foreign key. What happens, I think, is that when adding a foreign key without a name, one is auto-generated and then checked against the current foreign keys. This check is probably case-sensitive, so it fails to detect the ones that were already present with an upper-case name. However, when actually ALTERing the table, adding the constraint fails because the names collide when compared case-insensitively. This gives a very descriptive error: ERROR 1050 (42S01): Table './test/test1' already exists

How to repeat:
mysql> CREATE TABLE Test1 (a INT, b INT, KEY(a), KEY(b)); CREATE TABLE Test2 LIKE Test1;

mysql> ALTER TABLE Test1 ADD CONSTRAINT Test1_ibfk_1 FOREIGN KEY (a) REFERENCES Test2 (a) ON DELETE RESTRICT ON UPDATE RESTRICT;

mysql> ALTER TABLE Test1 ADD FOREIGN KEY (b) REFERENCES Test2 (b) ON DELETE RESTRICT ON UPDATE RESTRICT;
ERROR 1050 (42S01): Table './test/test1' already exists

mysql> SHOW CREATE TABLE Test1;
| Test1 | CREATE TABLE `Test1` (
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  KEY `a` (`a`),
  KEY `b` (`b`),
  CONSTRAINT `Test1_ibfk_1` FOREIGN KEY (`a`) REFERENCES `Test2` (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

mysql> ALTER TABLE Test1 DROP FOREIGN KEY Test1_ibfk_1;

mysql> ALTER TABLE Test1 ADD FOREIGN KEY (a) REFERENCES Test2 (a) ON DELETE RESTRICT ON UPDATE RESTRICT;

mysql> ALTER TABLE Test1 ADD FOREIGN KEY (b) REFERENCES Test2 (b) ON DELETE RESTRICT ON UPDATE RESTRICT;

mysql> SHOW CREATE TABLE Test1;
| Test1 | CREATE TABLE `Test1` (
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  KEY `a` (`a`),
  KEY `b` (`b`),
  CONSTRAINT `test1_ibfk_2` FOREIGN KEY (`b`) REFERENCES `Test2` (`b`),
  CONSTRAINT `test1_ibfk_1` FOREIGN KEY (`a`) REFERENCES `Test2` (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

mysql> ALTER TABLE Test1 DROP FOREIGN KEY test1_ibfk_1, DROP FOREIGN KEY test1_ibfk_2;

mysql> ALTER TABLE Test1 ADD CONSTRAINT test1_ibfk_1 FOREIGN KEY (a) REFERENCES Test2 (a) ON DELETE RESTRICT ON UPDATE RESTRICT;

mysql> ALTER TABLE Test1 ADD FOREIGN KEY (b) REFERENCES Test2 (b) ON DELETE RESTRICT ON UPDATE RESTRICT;

mysql> SHOW CREATE TABLE Test1;
| Test1 | CREATE TABLE `Test1` (
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  KEY `a` (`a`),
  KEY `b` (`b`),
  CONSTRAINT `test1_ibfk_2` FOREIGN KEY (`b`) REFERENCES `Test2` (`b`),
  CONSTRAINT `test1_ibfk_1` FOREIGN KEY (`a`) REFERENCES `Test2` (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

mysql> show variables like '%case%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| lower_case_file_system | ON    |
| lower_case_table_names | 2     |
+------------------------+-------+
2 rows in set (0.00 sec)

Suggested fix:
Check case-insensitively for existing foreign keys when auto-generating, or anything else that will result in logical and predictable behaviour. In my opinion, the table name should be used as is, even on case-insensitive systems, so the keys should have been named Test1_ibfk_1 and Test1_ibfk_2 in this case.

In any case, adding a valid foreign key without a name should always succeed.

Thank you for the report.

// MySQL started with
[root@cluster-repo mysql-advanced-5.6.20]# bin/mysqld_safe --defaults-file=./my.cnf --lower_case_table_names=1 --user=root &

[root@cluster-repo mysql-advanced-5.6.20]# more my.cnf

[mysqld_safe]
expire_logs_days=10
[mysqld]
log_bin=master-bin
binlog-format=STATEMENT
# These are commonly set, remove the # and set as required.
basedir = /data/ushastry/server/mysql-advanced-5.6.20
datadir = /data/ushastry/server/mysql-advanced-5.6.20/data
port = 3306
socket = /tmp/mysql_wb.sock
server_id=3302

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
expire_logs_days=100

//

[root@cluster-repo mysql-advanced-5.6.20]# bin/mysql -u root -p -S /tmp/mysql_wb.sock
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.20-enterprise-commercial-advanced-log MySQL Enterprise Server - Advanced Edition (Commercial)

Copyright (c) 2000, 2014, 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 '%version%';
+-------------------------+---------------------------------------------------------+
| Variable_name           | Value                                                   |
+-------------------------+---------------------------------------------------------+
| innodb_version          | 5.6.20                                                  |
| protocol_version        | 10                                                      |
| slave_type_conversions  |                                                         |
| version                 | 5.6.20-enterprise-commercial-advanced-log               |
| version_comment         | MySQL Enterprise Server - Advanced Edition (Commercial) |
| version_compile_machine | x86_64                                                  |
| version_compile_os      | Linux                                                   |
+-------------------------+---------------------------------------------------------+
7 rows in set (0.00 sec)

mysql> show variables like '%case%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| lower_case_file_system | OFF   |
| lower_case_table_names | 1     |
+------------------------+-------+
2 rows in set (0.00 sec)

mysql> drop database if exists test;
Query OK, 3 rows affected (0.59 sec)

mysql> create database test;
Query OK, 1 row affected (0.00 sec)

mysql> use test
Database changed
mysql>

mysql> CREATE TABLE Test1 (a INT, b INT, KEY(a), KEY(b)); CREATE TABLE Test2 LIKE Test1;
Query OK, 0 rows affected (0.45 sec)

Query OK, 0 rows affected (0.42 sec)

mysql> ALTER TABLE Test1 ADD CONSTRAINT Test1_ibfk_1 FOREIGN KEY (a) REFERENCES Test2 (a) ON DELETE RESTRICT ON UPDATE RESTRICT;
Query OK, 0 rows affected (1.11 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE Test1 ADD FOREIGN KEY (b) REFERENCES Test2 (b) ON DELETE RESTRICT ON UPDATE RESTRICT;
ERROR 1050 (42S01): Table './test/test1' already exists
mysql>
mysql> show errors;
+-------+------+------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                    |
+-------+------+------------------------------------------------------------------------------------------------------------+
| Error | 1050 | Table './test/test1' already exists                                                                        |
| Error | 1025 | Error on rename of './test/#sql-7fef_2' to './test/test1' (errno: -1 - Unknown error 18446744073709551615) |
+-------+------+------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> show create table Test1\G
*************************** 1. row ***************************
       Table: Test1
Create Table: CREATE TABLE `test1` (
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  KEY `a` (`a`),
  KEY `b` (`b`),
  CONSTRAINT `Test1_ibfk_1` FOREIGN KEY (`a`) REFERENCES `test2` (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> show create table Test2\G
*************************** 1. row ***************************
       Table: Test2
Create Table: CREATE TABLE `test2` (
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  KEY `a` (`a`),
  KEY `b` (`b`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

see https://bugs.mysql.com/bug.php?id=88082.

Posted by developer:
 
The problem is still repeatable with MySQL server 5.7.27-git and 8.0.17-git.

Slightly simplified test case:

SHOW VARIABLES LIKE '%case%';
#	Variable_name   Value
#	lower_case_file_system  ON
#	lower_case_table_names  2
CREATE TABLE Test1 (a INT, b INT, KEY(a), KEY(b));
CREATE TABLE Test2 LIKE Test1;
ALTER TABLE Test1 ADD CONSTRAINT Test1_ibfk_1 FOREIGN KEY (a) REFERENCES Test2 (a);
# The below statement still fails:
ALTER TABLE Test1 ADD FOREIGN KEY (b) REFERENCES Test2 (b);
# In 5.7 error message is "1050: Table './test/test1' already exists"
# In 8.0 error message is somewhat better "1826: Duplicate foreign key constraint name 'test1_ibfk_1'"

The underlying problem is the same in both these versions. Foreign key names are stored and compared
in InnoDB data-dictionary (in 5.7) or in New data-dictionary (in 8.0) in case-insensitive fashion.
While the code which generates names for foreign keys missing explicit name does binary pattern
matching with lowercased table name when determining what is the highest number used for automatically
generated foreign key name for the table. As result 'Test1_ibfk_1' is not considered as matching 
the pattern and thus new constraint gets 1 as its number component for the name (instead of 2).

Posted by developer:
 
Bug #26964577 / # 88082 "ADD FOREIGN KEY FAILS WITH "TABLE ALREADY EXISTS"
WHEN LOWER_CASE_TABLE_NAMES=1" has been marked duplicate of this one.

Posted by developer:
 
Bug #15868486/#67385 "ALTER TABLE TO ADD FK FAILS WHEN TABLE HAS SPECIFIC NAME"
has been marked as duplicate of this bug.