MySQL Bugs: #34765: auto generated FK names can be longer than 64 characters

Bug #34765	auto generated FK names can be longer than 64 characters
Submitted:	22 Feb 2008 20:41	Modified:	3 Jun 2008 0:15
Reporter:	Matthew Lord	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S2 (Serious)
Version:	5.0.54	OS:	Any
Assigned to:	Assigned Account	CPU Architecture:	Any
Tags:	fk, foreign key, innodb, v6

Description:
This is related to bug 13942.

mysql> CREATE TABLE other (id int PRIMARY KEY) engine=innodb;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyz123456 (id int PRIMARY KEY, ref int, FOREIGN KEY (ref) REFERENCES other(id)) engine=innodb;
Query OK, 0 rows affected (0.02 sec)

mysql> show create table abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyz123456\G
*************************** 1. row ***************************
Table: abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyz123456
Create Table: CREATE TABLE `abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyz123456` (
`id` int(11) NOT NULL,
`ref` int(11) default NULL,
PRIMARY KEY (`id`),
KEY `ref` (`ref`),
CONSTRAINT `abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyz123456_ibfk_1` FOREIGN KEY (`ref`) REFERENCES `other` (`id`)
) ENGINE=InnoDB
1 row in set (0.00 sec)

The problem comes when you try to dump and reload this table as the SQL layer will reject the FK name as too long when creating the table.

How to repeat:
use test;

CREATE TABLE other (id int PRIMARY KEY) engine=innodb;

CREATE TABLE abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyz123456 (id int PRIMARY KEY, ref int, FOREIGN KEY (ref) REFERENCES other(id)) engine=innodb;

show create table abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyz123456;

CREATE TABLE `test2` (`id` int(11) NOT NULL, `ref` int(11) default NULL, PRIMARY KEY (`id`), KEY `ref` (`ref`), CONSTRAINT `abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyz123456_ibfk_1` FOREIGN KEY (`ref`) REFERENCES `other` (`id`)) ENGINE=InnoDB; 

Suggested fix:
We should truncate the table name or find some other means of keeping the Constraint name <= 64 characters or we end up with failing CREATE TABLE 
statements in mysqldump backups.

Verified as described.

$ ./bin/mysql < mydump mydb
ERROR 1059 (42000) at line 25: Identifier name 'abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyz123456_ibfk_1' is too long

InnoDB could generate shorter foreign key names for extremely long table names. Though, then collisions are inevitable...

Assigning to Sunny.

Sunny has a large patch that fixes this in 6.0. We do not want to touch 5.1 any more.

See also Bug# 13942