CODEWITHSUNDEEP
mysqldatabaseamazon-web-servicesrds
user1427661
user1427661

Reputation: 11774

Error 1059: Identifier Name Too Long on Foreign Key Constraints from Existing Table

I have some pre-existing tables in a production database. When I run show create table existing_table it shows the SQL required to create the table, which involves several CONSTRAINT FOREIGN KEY REFERENCES involving constraints with names like:

_xxxxxxxxxxx_xxxxxxxxxxx_xx_f87560e38ebd0f6_fk_customer_customer_id

This seems normal, but when I try to create a tmp table like new_existing_table that runs the same SQL as shown in the create table query, I get the following error:

ERROR 1059 (42000): Identifier name '_xxxxxxxxxxxx_xxxxxxx_xxxxxx_xx_7435260ed6a242b0_fk_customer_customer_id' is too long

If this was the SQL that generated the original table, how is it failing when I create the new table? Even if I make the table name really short, like tmp, I get the same error.

I'm using MySQL, InnoDB on Amazon RDS.

Upvotes: 2

Views: 11513

Answers (1)

Michael - sqlbot
Michael - sqlbot

Reputation: 179124

This looks like a problem related to MySQL Bug #44541, which was fixed in MySQL Server versions 5.1.69, 5.5.31, 5.6.11, and 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.

The problem is, the bug allowed tables with invalid definitions to be created, so the server version that's important here isn't the server version you're trying to load onto -- it's the server version that was running when and where the table was created. The fix doesn't do anything other than prevent newly created tables from having invalid auto-generated identifiers.

The identifier is the constraint identifier, and its value isn't particularly important -- it's only used to tell you, in an error message, the foreign key constraint that was about to be violated... so you can just remove it. The only time you need to explicitly declare it is when the auto-generated one would be too large.

From http://dev.mysql.com/doc/refman/5.6/en/create-table-foreign-keys.html, you'll notice that the keyword CONSTRAINT is optional, and even if supplied, the symbol following it is still optional. The symbol is the element that's giving you a problem. It's limited to 64 characters. It always has been, but due to a bug, the limit wasn't properly imposed.

[CONSTRAINT [symbol]] FOREIGN KEY
[index_name] (index_col_name, ...)
REFERENCES tbl_name (index_col_name,...)
[ON DELETE reference_option]
[ON UPDATE reference_option]

Upvotes: 4

Related Questions