MySQL Bugs: #45307: changing foreign key's field case results in Duplicate key name error on restore

Bug #45307	changing foreign key's field case results in Duplicate key name error on restore
Submitted:	3 Jun 2009 14:56	Modified:	7 Oct 2009 21:13
Reporter:	Scott Noyes	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S3 (Non-critical)
Version:	5.0.82	OS:	Linux
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
Create a foreign key and explicitly name the constraint, but allow MySQL to create the index (it will use the same name). Alter the table and change the case of the column that is the foreign key. The foreign key definition will not change, but the column and index will. The resulting structure cannot be recreated from the SHOW CREATE TABLE or mysqldump output.

How to repeat:
USE test;
CREATE TABLE t1 (id int, index(id)) ENGINE=InnoDB;
CREATE TABLE t2 (ID int, CONSTRAINT `FK_ID` FOREIGN KEY (ID) REFERENCES t1 (id)) ENGINE=InnoDB;
ALTER TABLE t2 CHANGE ID id int;

-- At this point, t2 has a column named `id` and an index named `FK_ID` on column `id`, but a foreign key constraint named `FK_ID` referring to `ID`.

mysqldump test > backup.sql
mysql test < backup.sql

-- The import fails with "ERROR 1061 (42000) at line 97: Duplicate key name 'FK_ID'"

Suggested fix:
Since column names are not case sensitive, this should not cause an error. There are three obvious solutions:

- Allow different cases for the column name with no error.
- Cause an ALTER TABLE that changes only column case to also fix foreign key definitions
- Prevent altering the column name at all. It is already forbidden to change a foreign key column to a different name (you get an error trying ALTER TABLE t2 CHANGE ID someDifferentName int;)

Just to be clear, the comment regarding allowing automatic name creation is not important. You get the same effect if you just happen to specify the same name for the key and the constraint:

CREATE TABLE t1 (id int, index(id)) ENGINE=InnoDB;
CREATE TABLE t2 (id int, KEY `id` (id), CONSTRAINT `id` FOREIGN KEY (ID) REFERENCES t1 (`id`)) ENGINE=InnoDB; -- fails
CREATE TABLE t2 (id int, KEY `id` (id), CONSTRAINT `id` FOREIGN KEY (id) REFERENCES t1 (`id`)) ENGINE=InnoDB; -- works

Additional investigation is needed, but this bug might be caused by the same reason as bug #39932 "create table fails if column for FK is in different case than in corr index".

How can this be 'target 6.0 beta' when there will be no more 6.0 releases?

Verified fix from bug will resolve this problem:

bool Key_part_spec::operator==(const Key_part_spec& other) const
{
....
-  return length == other.length && !strcmp(field_name, other.field_name);
+  return length == other.length && !my_strcasecmp(system_charset_info,
+                                                field_name, other.field_name);
....
}

mysql>  CREATE TABLE t3 (id int, index(id)) ENGINE=InnoDB;
Query OK, 0 rows affected (0.00 sec)

mysql>  CREATE TABLE t4 (id int, KEY `id` (id), CONSTRAINT `id` FOREIGN KEY (ID) REFERENCES t3(`id`)) ENGINE=InnoDB;
Query OK, 0 rows affected (0.00 sec)

So does the repro script in the bug description (works with the fix).

Thanks
Jimmy

Mark this to be a dup of Bug #39932.

Both testcases seem indeed resolved in 5.1.52 (ref bug #39932)