Bug #88118 Upgrading to 8.0.3 fails if foreign keys of exactly 64 characters are present
Submitted: 17 Oct 2017 9:50 Modified: 23 Oct 2017 14:42
Reporter: Daniël van Eeden (OCA) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S2 (Serious)
Version:8.0.3 OS:Any
Assigned to: CPU Architecture:Any
Tags: compatibility, foreign keys, upgrade

[17 Oct 2017 9:50] Daniël van Eeden
Description:
On https://dev.mysql.com/doc/refman/8.0/en/upgrading.html it says:
==============================
There must be no tables that have foreign key constraint names longer than 64 characters. To identify tables with too-long constraint names, execute this query:

SELECT CONSTRAINT_SCHEMA, TABLE_NAME, CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
WHERE LENGTH(CONSTRAINT_NAME) > 64;

Any tables reported by the query must be altered to have constraint names no longer than 64 characters (use ALTER TABLE). 
==============================

For a machine where this query doesn't report anything I tried to upgrade. But then mysqld failed with:
2017-10-17T09:07:26.789374Z 2 [ERROR] [001059] /usr/sbin/mysqld: Identifier name 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx' is too long
2017-10-17T09:07:26.789433Z 2 [ERROR] [000000] InnoDB: Foreign key name:foobar/xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx is too long, for the table:foo_bar_baz. Please ALTER the foreign key name to use less than 64 characters and try upgrade again.

2017-10-17T09:07:26.789466Z 2 [ERROR] [004390] Error in fixing SE data for foobar.foo_bar_baz

So foreign keys of exactly 64 characters seems to break the upgrade but are not found by the query from the docs.

How to repeat:
Upgrade 5.7.19 with a table with a 64 char foreign key.

Suggested fix:
1. Allow upgrade of tables with 64 char foreign keys
2. If 1 is not possible: update the query in the docs (change > to >= )
[17 Oct 2017 9:56] Daniël van Eeden
The django backend for mysql defines the maximum length as 64.

https://github.com/django/django/blob/master/django/db/backends/mysql/operations.py#L194
[17 Oct 2017 10:24] MySQL Verification Team
Hello Daniël,

Thank you for the report.
Verified as described.

Thanks,
Umesh
[17 Oct 2017 10:26] MySQL Verification Team
test results

Attachment: 88118.results (application/octet-stream, text), 6.55 KiB.

[19 Oct 2017 12:38] Naga Satyanarayana Bodapati
Posted by developer:
 
Not possible to change the limit.

Please fix the docs. the query should use ">=" instead of ">".
[23 Oct 2017 14:42] Daniel Price
Posted by developer:
 
The documentation has been revised to state that there must be no tables that have foreign key constraint names longer than or equal to 64 characters.
https://dev.mysql.com/doc/refman/8.0/en/upgrading.html

Thank you for the bug report.
[24 Oct 2017 6:38] MySQL Verification Team
See https://bugs.mysql.com/bug.php?id=88196