MySQL Bugs: #86120: Upgrading a MySQL instance with a table name of >64 chars results in breakage

Bug #86120	Upgrading a MySQL instance with a table name of >64 chars results in breakage
Submitted:	27 Apr 2017 20:22	Modified:	24 May 2017 14:19
Reporter:	Daniël van Eeden (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Data Dictionary	Severity:	S3 (Non-critical)
Version:	8.0.1	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
MySQL has a 64 char limit on table names. However with MyISAM, Blackhole, CSV the frm etc. files can be renamed on the filesystem.

On upgrade to 8.0.1 this happens:
2017-04-27T20:06:17.324682Z 1 [Note] Created Data Dictionary for upgrade
.2017-04-27T20:06:18.942663Z 3 [ERROR] /foo/bin/mysqld: Failed to update tables dictionary object.
2017-04-27T20:06:18.942701Z 3 [ERROR] Error in Creating DD entry for test.xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
…
2017-04-27T20:06:19.334532Z 3 [ERROR] Unable to drop the DD tables during clean up after upgrade failure
2017-04-27T20:06:19.340191Z 0 [ERROR] Failed to Populate DD tables.
2017-04-27T20:06:19.340303Z 0 [ERROR] Aborting

After removing the data/test/xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx.frm file the upgrade fails:
2017-04-27T20:10:11.514581Z 1 [ERROR] InnoDB: Operating system error number 17 in a file operation.
2017-04-27T20:10:11.514627Z 1 [ERROR] InnoDB: Error number 17 means 'File exists'
2017-04-27T20:10:11.514638Z 1 [Note] InnoDB: Some operating system error numbers are described at http://dev.mysql.com/doc/refman/8.0/en/operating-system-error-codes.html
2017-04-27T20:10:11.514647Z 1 [ERROR] InnoDB: Cannot create file './mysql/table_stats.ibd'
2017-04-27T20:10:11.514654Z 1 [ERROR] InnoDB: The file './mysql/table_stats.ibd' already exists though the corresponding table did not exist in the InnoDB data dictionary. Have you moved InnoDB .ibd files around without using the SQL commands DISCARD TABLESPACE and IMPORT TABLESPACE, or did mysqld crash in the middle of CREATE TABLE? You can resolve the problem by removing the file './mysql/table_stats.ibd' under the 'datadir' of MySQL.
2017-04-27T20:10:11.514686Z 1 [ERROR] /foo/bin/mysqld: Tablespace '`mysql`.`table_stats`' exists.
2017-04-27T20:10:11.514700Z 1 [ERROR] /foo/bin/mysqld: Got error 184 - 'Tablespace already exists' from storage engine
2017-04-27T20:10:11.647394Z 0 [ERROR] Data Dictionary initialization failed.
2017-04-27T20:10:11.647423Z 0 [ERROR] Aborting

Going back to 5.7.18 will also likely cause issues.

Related:
Bug #82335 Table name with >64 chars breaks information schema queries

How to repeat:
On 5.7:
Create `test`.`x` with engine=blackhole
Shutdown server
Rename x.frm to xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx.frm
Start server
Verify access to the table
Shutdown the server

Now try to upgrade to 8.0

Suggested fix:
1. Test for tables with >64 chars and other critical issues before actually touching the data. This allows you to start 5.7.x again and fix issues before upgrading.

2. Ignore tables with >64 chars on DD creation and return a warning.

Hello Daniël,

Thank you for the report.

Thanks,
Umesh

test results

Attachment: 86120.results (application/octet-stream, text), 6.11 KiB.

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

Upgrading from MySQL 5.7 to MySQL 8.0 failed during data dictionary
creation due to table names that exceeded the 64 character length limit.