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

• Submitted: 25 Jul 2016 13:34
• Modified: 29 Jul 2016 12:20
• Reporter: Daniël van Eeden (OCA)
• Status: Verified
• Category: MySQL Server: Information schema
• Severity: S2 (Serious)
• Version: 5.6.27-log, 5.6.31
• OS: Any
• CPU Architecture: Any

[25 Jul 2016 13:34] Daniël van Eeden

Description:
There is a 64 chars limit on the table name.
https://dev.mysql.com/doc/refman/5.7/en/identifiers.html

The 64 chars is a rather low limit.

When I place a <string_of_65_chars>.{MYD,MYI} file in a db dir some I_S queries stop working.

Some I_S queries are known to give warnings with 4-byte UTF-8 characters, but at least they keep working.

I would like to be able to query on WHERE CHAR_LENGTH(TABLE_NAME) > 64 to find wrong table names.. but no such luck.

This happens if there is a long table name (<=64) and it is then renamed to something like 'to_drop_<table_name>' or '_<table_name>_new' (like pt-online-schema-change does)

How to repeat:
-- This works
SELECT  TABLE_SCHEMA, TABLE_NAME, ENGINE
FROM    INFORMATION_SCHEMA.TABLES
WHERE   TABLE_TYPE = 'BASE TABLE'
AND     ENGINE IN ( 'MyISAM', 'InnoDB' )

-- This fails
SELECT  TABLE_SCHEMA, TABLE_NAME, ENGINE
FROM    INFORMATION_SCHEMA.TABLES
WHERE   TABLE_TYPE = 'BASE TABLE'
AND     ENGINE IN ( 'MyISAM', 'InnoDB' )
AND     NOT ( ENGINE = 'MyISAM' AND ROW_FORMAT = 'Compressed' )

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

[26 Jul 2016 7:55] MySQL Verification Team

Hello Daniël,

Thank you for the report and feedback!

Thanks,
Umesh

[29 Jul 2016 12:20] Daniël van Eeden

A very similar case on 5.7.12:

mysql> CREATE TABLE `xxx` (id int) ENGINE=MYISAM;
mysql> \! mv data/test/xxx.frm data/test/@0000.frm
mysql> \! mv data/test/xxx.MYD data/test/@0000.MYD
mysql> \! mv data/test/xxx.MYI data/test/@0000.MYI
mysql> show table status\G
ERROR 1103 (42000): Incorrect table name ''
mysql [localhost] {msandbox} (test) > select * from information_schema.tables;
ERROR 1103 (42000): Incorrect table name ''