MySQL Bugs: #13942: automatic FK names > 64 chars long, truncated in information schema

Bug #13942	automatic FK names > 64 chars long, truncated in information schema
Submitted:	12 Oct 2005 0:02	Modified:	16 Apr 2019 17:17
Reporter:	Roland Bouman	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S3 (Non-critical)
Version:	5.0.13-rc-nt	OS:	Any (WinXP)
Assigned to:	Sunny Bains	CPU Architecture:	Any

Description:
Creating a foreign key constraint without specifying a name for it will automatically generate a name derived from the table name. The name is derived by appending "_ibfk_#" to the table name, where # is an integer. This naming scheme is used even if it results in a name that's longer than 64 characters, wich is the maximum length for most mysql identifiers. 

At least, this behaviour is inconsistent. When issuing a ALTER TABLE ADD CONSTRAINT <identifier> FOREIGN KEY, the statement fails if you try to use a a constraint_name longer than 64 characters.

Apart from that, the information_schema.table_constraints.constraint_name (among others) column is also exactly 64 characters long. Too long (automatically generated) constraint_names appear truncated.

How to repeat:
-- 64 char long tablename
create table _123456789112345678921234567893123456789412345678951234567896123(
    ref_id int unsigned
);

create table p(
    id int unsigned auto_increment primary key 
);

-- create foreign key, without specifying a name

alter table _123456789112345678921234567893123456789412345678951234567896123
add foreign key (
    ref_id
) references p (
    id
)
;

-- show constraint name with SHOW

show create table _123456789112345678921234567893123456789412345678951234567896123;

CREATE TABLE `_123456789112345678921234567893123456789412345678951234567896123` (
  `ref_id` int(10) unsigned default NULL,
  KEY `ref_id` (`ref_id`),
  CONSTRAINT `_123456789112345678921234567893123456789412345678951234567896123_ibfk_1` 
     FOREIGN KEY (`ref_id`) REFERENCES `p` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

-- show constraint name with information_schema

mysql> select *
    -> from   information_schema.table_constraints
    -> where  table_schema = schema()
    -> and    table_name = '_123456789112345678921234567893123456789412345678951234567896123'
    -> \G
*************************** 1. row ***************************
CONSTRAINT_CATALOG: NULL
 CONSTRAINT_SCHEMA: test
   CONSTRAINT_NAME: _123456789112345678921234567893123456789412345678951234567896123
      TABLE_SCHEMA: test
        TABLE_NAME: _123456789112345678921234567893123456789412345678951234567896123
   CONSTRAINT_TYPE: FOREIGN KEY

mysql> alter table _123456789112345678921234567893123456789412345678951234567896123 add constraint _123456789112345678921234567893123456789412345678951234567896
123_ib_fk1 foreign key(ref_id) references p(id)
    -> ;
ERROR 1059 (42000): Identifier name '_123456789112345678921234567893123456789412345678951234567896123_ib_fk1' is too long
mysql>

Suggested fix:
dissallow creation of the fk, and reoprt an error

Thanks for your bug report.  I was able to reproduce this problem using 5.0.13

Constraint name is renamed in InnoDB part of code
(see function ibool dict_table_rename_in_cache(), dict/dict0dict.c). 
We can't check the length of constraint name on handler level
(Previous suggested fix was wrong as it can't help in case
of test below).
Additional test case:
create table t1(ref_id int unsigned) ENGINE=innodb;
create table p1(id int unsigned auto_increment primary key) ENGINE=innodb;
alter table t1
add foreign key (ref_id) references p1 (id);
alter table t1 rename _123456789112345678921234567893123456789412345678951234567;

alter table rename should give an error.

Maybe anohter fix could be stretch the length of the CONSTAINT_NAME column in the information_schema database, so that you can at least have access to the full name. 

As I recall, dropping the constraint was not a problem, even though you need a name >64 chars to refer to it. It's just a nuisance that you cannot query the name from the information_schema, because that way, you cannot see the name of the constraint you want to drop.

Indeed you can drop, but not otherwise alter the constraint:

mysql> alter table _123456789112345678921234567893123456789412345678951234567896123 drop foreign key _123456789112345678921234567893123456789412345678951234567896123_ibfk_1;
Query OK, 0 rows affected (0.11 sec)
Records: 0  Duplicates: 0  Warnings: 0

Mark,

hmm... maybe we should truncate the table name in the constraint name, to avoid it from exceeding 64 bytes?

But why is the identifier max length only 64 bytes in MySQL? If it were longer, say 256 bytes, nobody would ever encounter this problem.

Regards,

Heikki

Hi, just want to mention (again) that no-one will ever have any problem when you stretch the length of the CONSTRAINT_NAME columns in the INFORMATION_SCHEMA database up to 
   64 
+ 6 (length of '_ibfk_') 
+ length of string representation of max. num. of fks per table (say, 2 digits, 99 foreign keys surely exceeds the maximum?). 

That is, provided you can use 72 char long identifiers in ALTER and DROP statements, this would work out fine.

The only problem now is that you cannot see what constraint you would wanna ALTER or DROP, because the name is truncated in the information_schema constraint_name columns.

Hi!

the problems are:

1) truncating the table name in the generated foreign key constraint name can lead to annoying name collision problems;
2) not truncating the table name makes a constraint name id > 64 bytes long, which can make a table dump impossible to import.

The solution: make CREATE TABLE and ALTER TABLE to fail if the generated foreign key constraint name would be >= 64 bytes long. The downside is yet another obscure foreign key error. A better solution would be to increase MySQL's max id len to 256 bytes. Then no one would bump into this problem.

Regards,

Heikki

Changing the category to Information Schema.

I wanted to close this bug out quickly.  Can all parties agree on Heikki's recommendation:

"A better solution would be to increase MySQL's max id
len to 256 bytes. Then no one would bump into this problem."

If so, I will put into progress and provide the fix.

Jay, 

I guess I'm not a party as I have no decisive power. However, I do agree that Heikki's suggestion would fix it. I believe the SQL standard requires a length of 128 characters for identifiers so this would be a really safe length to go by

I have just run into this exact problem when trying to restore a backup in which an auto generated FK was greater the 64.

Has this problem been fixed (hopefully using Heikki’s recommendations)? If so in which version?

This is a critical problem for us, as we have clients with backup files, which can not be reloaded.

David

Hi David, 

AFAIK this has not been fixed. Assuming your dump consists of SQL statement, my suggestion would be to change the fk identifiers. 

It's ugly but it will solve the problem for now.

The databases we are backing up and restoring or on our clients machines and contain proprietary data which we are not permitted. Hence, it is out of the question for them to send us a backup of their project in order for us to change the fk's.

Thus is a very critical bug, that if not fixed will require us to move to another DBMS.

Raising the maximum id length in MySQL to 255 or even longer would solve many problems with too long names.

Since MySQL devs do not have plans to raise it, we InnoDB devs need to come up with a solution like:

*) get the first 40 chars from the table name, and catenate a random 10 char string to make it (almost) sure there will not be name collisions. Then catename the _ibfk_... to the name.

Assigning this to Sunny. We may fix this in 6.0 or later.

Note that the upcoming server-level foreign key implementation in MySQL will presumably fix also this problem.

what will be fixed in 6.0?  the inconsistency?  or the 64 character limit?

microsoft's sql server and ibm's db2 both allow 128 byte identifiers.  SQL92 explicitly allows up to 128 byte identifiers.  i don't see why mysql should be short sighted here.  i agree with heikki; it should have been 256 to begin with.  starting out with these arbitrary limits is just strange.

is there a way to build mysql with limits > 64 bytes?  postgres has the same limit, but increasing it is as simple as modifying a def in a header.  this limit breaks our schema.  :/

Any progress on this? This limit is really a pain in the ass, and wouldn't it be really easy to increase the max length?

Tested with MySQL 5.6.19, it seems to allow constraints without truncating them:

mysql> CREATE TABLE `t234567890123456789012345678901234567890123456789012345678901234` (
  `id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1

mysql> ALTER TABLE `t234567890123456789012345678901234567890123456789012345678901234` 
ADD FOREIGN KEY (id) REFERENCES foo(id);

mysql> SHOW CREATE TABLE `t234567890123456789012345678901234567890123456789012345678901234`\G
*************************** 1. row ***************************
       Table: t234567890123456789012345678901234567890123456789012345678901234
Create Table: CREATE TABLE `t234567890123456789012345678901234567890123456789012345678901234` (
  `id` int(11) DEFAULT NULL,
  KEY `id` (`id`),
  CONSTRAINT `t234567890123456789012345678901234567890123456789012345678901234_ibfk_1` FOREIGN KEY (`id`) REFERENCES `foo` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

mysql> SELECT LENGTH('t234567890123456789012345678901234567890123456789012345678901234_ibfk_1') AS length;
+--------+
| length |
+--------+
|     71 |
+--------+

But interestingly, names in I_S have not been expanded:

mysql> desc INFORMATION_SCHEMA.TABLE_CONSTRAINTS;
+--------------------+--------------+------+-----+---------+-------+
| Field              | Type         | Null | Key | Default | Extra |
+--------------------+--------------+------+-----+---------+-------+
| CONSTRAINT_NAME    | varchar(64)  | NO   |     |         |       |
. . .

mysql> SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE TABLE_SCHEMA='test';
. . .
| def                | test              | t234567890123456789012345678901234567890123456789012345678901234 | test         | t234567890123456789012345678901234567890123456789012345678901234 | FOREIGN KEY     |

Also in 8.0.3.

For the following reason, I think that this is a critical bug:

mysql> drop table _123456789112345678921234567893123456789412345678951234567896123_ibfk_1;
ERROR 1059 (42000): Identifier name '_123456789112345678921234567893123456789412345678951234567896123_ibfk_1' is too long

MySQL 5.7.27-git still allows creation of foreign keys with too
long names and is still affected by problem with I_S:

CREATE TABLE
_123456789112345678921234567893123456789412345678951234567896123(ref_id INT);
CREATE TABLE p(id INT PRIMARY KEY);
ALTER TABLE _123456789112345678921234567893123456789412345678951234567896123
ADD FOREIGN KEY (ref_id) REFERENCES p (id);
SHOW CREATE TABLE
_123456789112345678921234567893123456789412345678951234567896123;
# Table  Create Table
# _123456789112345678921234567893123456789412345678951234567896123
CREATE TABLE
`_123456789112345678921234567893123456789412345678951234567896123` (
#  `ref_id` int(11) DEFAULT NULL,
#  KEY `ref_id` (`ref_id`),
#  CONSTRAINT
`_123456789112345678921234567893123456789412345678951234567896123_ibfk_1`
FOREIGN KEY (`ref_id`) REFERENCES `p` (`id`)
#) ENGINE=InnoDB DEFAULT CHARSET=latin1

# Notice that constraint name is truncated!
SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE TABLE_SCHEMA='test';
# CONSTRAINT_CATALOG     CONSTRAINT_SCHEMA       CONSTRAINT_NAME TABLE_SCHEMA
   TABLE_NAME      CONSTRAINT_TYPE
# def    test
_123456789112345678921234567893123456789412345678951234567896123        test
  _123456789112345678921234567893123456789412345678951234567896123
FOREIGN KEY
# def    test    PRIMARY test    p       PRIMARY KEY

MySQL 8.0 (I have used 8.0.17-git) takes different approach and prohibit
creation of foreign keys if generated name gets too long:

CREATE TABLE
_123456789112345678921234567893123456789412345678951234567896123(ref_id INT);
CREATE TABLE p(id INT PRIMARY KEY);
ALTER TABLE _123456789112345678921234567893123456789412345678951234567896123
ADD FOREIGN KEY (ref_id) REFERENCES p (id);
# The above ALTER fails with ERROR 42000: Identifier name
'_123456789112345678921234567893123456789412345678951234567896123_ibfk_1' is
too long
DROP TABLE _123456789112345678921234567893123456789412345678951234567896123;
CREATE TABLE c(ref_id INT, FOREIGN KEY (ref_id) REFERENCES p (id));
RENAME TABLE c TO
_123456789112345678921234567893123456789412345678951234567896123;
# The above RENAME fails with ERROR 42000: Identifier name
'_123456789112345678921234567893123456789412345678951234567896123_ibfk_1' is
too long

Since too long names are not allowed there is no problem with showing them
in INFORMATION_SCHEMA.

The scenario with RENAME TABLE has been addressed by fix for bug#24666169
"I_S.TABLE_CONSTRAINTS.CONSTRAINT_NAME IS NOT UPDATED AFTER RENAME TABLE"
in MySQL 8.0.1 (and CREATE TABLE scenario was addressed even before that).

Because of the above I am closing this bug as fixed in 8.0.1. Moving
it to Documenting state to let Documentation Team to decide if it makes
sense to add it to release notes.

Posted by developer:
 
Fixed as of the 8.0.1 release, and here's the changelog entry:

An automatically generated foreign key constraint identifier that
exceeded the 64 character limit appeared truncated in the
INFORMATION_SCHEMA.TABLE_CONSTRAINTS CONSTRAINT_NAME column. Automatically
generated foreign key constraint identifiers can no longer exceed the 64
character limit.