MySQL Bugs: #44541: InnoDB generates symbols that are too long, invalid DDL from show create table

Bug #44541	InnoDB generates symbols that are too long, invalid DDL from show create table
Submitted:	29 Apr 2009 14:12	Modified:	13 Mar 2013 19:19
Reporter:	Duncan Hutty	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S3 (Non-critical)
Version:	5.1.31, 5.1.34	OS:	Linux
Assigned to:		CPU Architecture:	Any
Tags:	identifier too long, innodb, mysqldump, SHOW CREATE TABLE

Description:
If the symbolic identifier for a foreign key constraint is not specified upon creation, innodb creates one automatically, based upon the table name and an ordinal per constraint in the table.

If the table name identifier contains non-alphanumeric characters, a unicode identifier is used in the symbol created, such as @0020 for a space. The unicode identifier may extend the symbol beyond the maximum permitted 64 characters.

Therefore, while this table can be created and used, the DDL produced by show create table is invalid; trying to (re)create a table using the output of show create table (and therefore mysqldump) fails with error 1509, identifier too long.

How to repeat:
CREATE TABLE `t1` (`f1` INT(10),`f2` INT(10));
CREATE TABLE `thistablemusthavealongname - with some non-alphanumeric chars` (`f1` INT(10),`f2` INT(10),KEY `f2` (`f2`), CONSTRAINT FOREIGN KEY (`f2`) REFERENCES `t1` (`k1`)) ENGINE=InnoDB;

mysql> show create table `thistablemusthavealongname - with some non-alphanumeric chars`\G
*************************** 1. row ***************************
       Table: thistablemusthavealongname - with some non-alphanumeric chars
Create Table: CREATE TABLE `thistablemusthavealongname - with some non-alphanumeric chars` (
  `f1` int(10) DEFAULT NULL,
  `f2` int(10) DEFAULT NULL,
  KEY `k1` (`f2`),
  CONSTRAINT `thistablemusthavealongname@0020@002d@0020with@0020some@0020non@002dalphanumeric@0020chars_ibfk_1` FOREIGN KEY (`f2`) REFERENCES `t1` (`f2`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Suggested fix:
CREATE TABLE should return an error if *any* of the identifiers in the table are over the limit of 64 characters, even the automatically generated ones such as the constraint symbol name created by InnoDB.

I think this is a duplicate of bug #13942. Please, check.

This is clearly related to bug #13942, but there are 2 issues. When InnoDB generates the constraint name:

1) It appends _ibfk_# which may take the length over the limit
2) It converts non-alphanumeric chars to unicodes which may take the length over the limit.

Raising the limit will not help.

I think the simple solution is that when InnoDB generates a name for a constraint (whether by alter table or create table), then if the generated name is greater than the limit, it should fail with error 1509.

Please, check with a newer version, 5.1.34. Looks like InnoDB already checks the limit:

valeriy-kravchuks-macbook-pro:5.1 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.1.34-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> drop table t1;
Query OK, 0 rows affected (0.07 sec)

mysql> CREATE TABLE `t1` (`f1` INT(10),`f2` INT(10));
Query OK, 0 rows affected (0.04 sec)

mysql> CREATE TABLE `thistablemusthavealongname - with some non-alphanumeric chars` (`f1`
    -> INT(10),`f2` INT(10),KEY `f2` (`f2`), CONSTRAINT FOREIGN KEY (`f2`) REFERENCES `t1`
    -> (`k1`)) ENGINE=InnoDB;
ERROR 1005 (HY000): Can't create table 'test.thistablemusthavealongname - with some non-alphanumeric chars' (errno: 150)

Sorry, my fault (and small mistake in your test case). Indeed, we should consider this a separate bug it seems:

mysql> CREATE TABLE `t1` (`f1` INT(10) primary key,`f2` INT(10)) engine=InnoDB;Query OK, 0 rows affected (0.06 sec)

mysql> CREATE TABLE `thistablemusthavealongname - with some non-alphanumeric chars` (`f1` INT(10),`f2` INT(10),KEY `f2` (`f2`), CONSTRAINT FOREIGN KEY (`f2`) REFERENCES `t1` (`f1`)) ENGINE=InnoDB;
Query OK, 0 rows affected (0.05 sec)

mysql> show create table `thistablemusthavealongname - with some non-alphanumeric chars`\G
*************************** 1. row ***************************
       Table: thistablemusthavealongname - with some non-alphanumeric chars
Create Table: CREATE TABLE `thistablemusthavealongname - with some non-alphanumeric chars` (
  `f1` int(10) DEFAULT NULL,
  `f2` int(10) DEFAULT NULL,
  KEY `f2` (`f2`),
  CONSTRAINT `thistablemusthavealongname@0020@002d@0020with@0020some@0020non@002dalphanumeric@0020chars_ibfk_1` FOREIGN KEY (`f2`) REFERENCES `t1` (`f1`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> select version();
+--------------+
| version()    |
+--------------+
| 5.1.34-debug | 
+--------------+
1 row in set (0.00 sec)

Added changelog entry for 5.1.69, 5.5.31, 5.6.11, 5.7.1.

The length of internally generated foreign key names was not checked. If internally generated foreign key names were over the 64 character limit, this resulted in invalid DDL from SHOW CREATE TABLE. This fix checks the length of internally generated foreign key names and reports an error message if the limit is exceeded.