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 2:02	Modified:	23 Aug 2006 6:36
Reporter:	Roland Bouman
Status:	Verified
Category:	Server: InnoDB	Severity:	S3 (Non-critical)
Version:	5.0.13-rc-nt	OS:	Any (WinXP)
Assigned to:	Sunny Bains	Target Version:
Triage:	Triaged: D4 (Minor) / R2 (Low) / E2 (Low)

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.
 :/