MySQL Bugs: #67385: Alter table to add FK fails when table has specific name

Bug #67385	Alter table to add FK fails when table has specific name
Submitted:	26 Oct 2012 13:58	Modified:	12 Apr 2019 15:11
Reporter:	Jonathan Amend	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S3 (Non-critical)
Version:	5.5.28, 5.5.29, 5.7.0	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
After creating some InnoDB tables, including a relationship on varchar columns, with one of those tables named "agency", trying to alter the agency table to add another relationship fails.

It sounds like any number of common user errors like duplicate FK names, mixed case names, different data types/collations, etc., but I've tried this on new databases and even new instances with the same result.

Using a different name for the "agency" table like agence, agenc, agencie will work fine.

I've tested this on Gentoo Linux and Windows 7 (both 64-bit) with the same result.

How to repeat:
CREATE TABLE `paymenttype`( `name` VARCHAR(255) NOT NULL, PRIMARY KEY (`name`) ) DEFAULT CHARSET=utf8;

# OK

CREATE TABLE `agency` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `paymentTypeName` varchar(255) NOT NULL,
  `enforceUserList` tinyint(1) NOT NULL,
  `templatesRelativePath` varchar(255) NOT NULL,
  `siteName` varchar(100) NOT NULL,
  `supportsUSA` tinyint(1) NOT NULL,
  `supportsCAN` tinyint(1) NOT NULL,
  `defaultCountry` varchar(40) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `FK_Agency_PaymentType` (`paymentTypeName`),
  CONSTRAINT `Agency_ibfk_1` FOREIGN KEY (`paymentTypeName`) REFERENCES `paymenttype` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8;

# OK

CREATE TABLE `country` (
  `name` varchar(40) NOT NULL,
  `abbreviation` varchar(4) NOT NULL,
  `enabled` tinyint(1) NOT NULL,
  PRIMARY KEY (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

# OK

ALTER TABLE agency ADD FOREIGN KEY FK_agency_country_Name(defaultCountry) REFERENCES country(name);

####

Query: ALTER TABLE agency ADD FOREIGN KEY FK_agency_country_Name(defaultCountry) REFERENCES country(name)

Error Code: 1050
Table '.\test4\agency' already exists

Execution Time : 0 sec
Transfer Time  : 0 sec
Total Time     : 0.011 sec

#### error log:
121026  9:38:32  InnoDB: Error; possible reasons:
InnoDB: 1) Table rename would cause two FOREIGN KEY constraints
InnoDB: to have the same internal name in case-insensitive comparison.
InnoDB: 2) table `test4`.`agency` exists in the InnoDB internal data
InnoDB: dictionary though MySQL is trying to rename table `test4`.`#sql-690_3` to it.
InnoDB: Have you deleted the .frm file and not used DROP TABLE?
InnoDB: You can look for further help from
InnoDB: http://dev.mysql.com/doc/refman/5.5/en/innodb-troubleshooting.html
InnoDB: If table `test4`.`agency` is a temporary table #sql..., then it can be that
InnoDB: there are still queries running on the table, and it will be
InnoDB: dropped automatically when the queries end.
InnoDB: You can drop the orphaned table inside InnoDB by
InnoDB: creating an InnoDB table with the same name in another
InnoDB: database and copying the .frm file to the current database.
InnoDB: Then MySQL thinks the table exists, and DROP TABLE will
InnoDB: succeed.

#### SHOW ENGINE INNODB STATUS
------------------------
LATEST FOREIGN KEY ERROR
------------------------
InnoDB: Renaming table `test4`.`#sql-3f4_1` to `test4`.`agency` failed!

(that's it, no explanation)

Repeatable for me on Win7.

Replacing table identifier 'agency' with 'agent' resolves the problem.

Peter
(not a MySQL/Oracle person)

Thank you for the report.

Verified as described.

The "ALTER TABLE agency ADD FOREIGN KEY FK_agency_country_Name(defaultCountry)
REFERENCES country(name);" will generate a constraint ID of "test/agency_ibfk_1", which conflicts with the constraint name already existed in table agency. This is partly explained in:

121026  9:38:32  InnoDB: Error; possible reasons:
InnoDB: 1) Table rename would cause two FOREIGN KEY constraints
InnoDB: to have the same internal name in case-insensitive comparison.

Posted by developer:
 
Hello!

After investigation I think that this bug has the same root as bug #19156435 / #72751
"CREATING FOREIGN KEY FAILS ON CASE-INSENSITIVE FILESYSTEM IN SOME CASES".
Since the latter bug has a bit more explanations about why the problem occurs
as well as another duplicate report associated with it I marking this bug as
a duplicate of bug #19156435.