Bug #95712 ERROR 1059 wrongly reported for a 64 characters table name
Submitted: 10 Jun 2019 15:01 Modified: 16 Oct 2019 14:39
Reporter: dave do Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: DDL Severity:S3 (Non-critical)
Version:8.0.16 OS:Any
Assigned to: CPU Architecture:Any

[10 Jun 2019 15:01] dave do
Description:
run the sqlcommand like below:
CREATE TABLE IF NOT EXISTS abcdefghijklmnoopqrstuvwxyz0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ0 (C_ID INT NULL AUTO_INCREMENT UNIQUE REFERENCES CREATE_TABLE_000 (C_ID,C_D_ID,C_W_ID) ON UPDATE CASCADE ,C_M_ID INT GENERATED ALWAYS AS ('qwe@#123' LIKE 'asd@213#') NOT NULL UNIQUE KEY , C_D_ID INTEGER NOT NULL,C_W_ID BIGINT NOT NULL,C_DOUBLE DOUBLE NOT NULL,C_DECIMAL DECIMAL NOT NULL,C_FIRST VARCHAR(64) NOT NULL,C_MIDDLE CHAR(2),C_LAST VARCHAR(64) NOT NULL,C_STREET_1 VARCHAR(20) NOT NULL,C_STREET_2 VARCHAR(20),C_CITY VARCHAR(64) NOT NULL,C_STATE CHAR(2) NOT NULL,C_ZIP CHAR(9) NOT NULL,C_PHONE CHAR(16) NOT NULL,C_SINCE TIMESTAMP,C_CREDIT CHAR(2) NOT NULL,C_CREDIT_LIM NUMERIC(12,2),C_DISCOUNT NUMERIC(4,4),C_BALANCE NUMERIC(12,2),C_YTD_PAYMENT REAL NOT NULL,C_PAYMENT_CNT FLOAT NOT NULL,C_DELIVERY_CNT BOOLEAN NOT NULL,C_END DATE NOT NULL,C_VCHAR VARCHAR(1000),C_DATA TEXT,C_TEXT BLOB,C_TINYTEXT TINYTEXT,C_MEDIUMBLOB MEDIUMBLOB,C_LONGBLOB LONGBLOB,C_LINESTRING LINESTRING not null, CONSTRAINT PRIMARY KEY USING HASH (C_D_ID) USING HASH, UNIQUE USING BTREE (C_ID,C_D_ID,C_W_ID DESC) , KEY index_CREATE_TABLE_049_taurus (C_ID DESC) COMMENT 'string', CHECK ('agdj56!#54'IS UNKNOWN)) AVG_ROW_LENGTH = 8, CHARACTER SET = utf8, CHECKSUM 1, COMMENT 'string', CONNECTION = 'connect_string', DELAY_KEY_WRITE = 1, ENCRYPTION 'N', INSERT_METHOD FIRST, KEY_BLOCK_SIZE = 0, MAX_ROWS 99, MIN_ROWS = 99, PACK_KEYS = 1, PASSWORD = 'string', ROW_FORMAT COMPRESSED, STATS_PERSISTENT 0, STATS_SAMPLE_PAGES 9

will get an error:
+ ERROR 1059 (42000) at line 19: Identifier name 'abcdefghijklmnoopqrstuvwxyz0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ0_chk_1' is too long

the Identifier name contains extra suffix '_chk_1' which may involved by CHECK constraints feature.

How to repeat:
RUN THE COMMAND IN A SELECTED DATABASE:

CREATE TABLE IF NOT EXISTS abcdefghijklmnoopqrstuvwxyz0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ0 (C_ID INT NULL AUTO_INCREMENT UNIQUE REFERENCES CREATE_TABLE_000 (C_ID,C_D_ID,C_W_ID) ON UPDATE CASCADE ,C_M_ID INT GENERATED ALWAYS AS ('qwe@#123' LIKE 'asd@213#') NOT NULL UNIQUE KEY , C_D_ID INTEGER NOT NULL,C_W_ID BIGINT NOT NULL,C_DOUBLE DOUBLE NOT NULL,C_DECIMAL DECIMAL NOT NULL,C_FIRST VARCHAR(64) NOT NULL,C_MIDDLE CHAR(2),C_LAST VARCHAR(64) NOT NULL,C_STREET_1 VARCHAR(20) NOT NULL,C_STREET_2 VARCHAR(20),C_CITY VARCHAR(64) NOT NULL,C_STATE CHAR(2) NOT NULL,C_ZIP CHAR(9) NOT NULL,C_PHONE CHAR(16) NOT NULL,C_SINCE TIMESTAMP,C_CREDIT CHAR(2) NOT NULL,C_CREDIT_LIM NUMERIC(12,2),C_DISCOUNT NUMERIC(4,4),C_BALANCE NUMERIC(12,2),C_YTD_PAYMENT REAL NOT NULL,C_PAYMENT_CNT FLOAT NOT NULL,C_DELIVERY_CNT BOOLEAN NOT NULL,C_END DATE NOT NULL,C_VCHAR VARCHAR(1000),C_DATA TEXT,C_TEXT BLOB,C_TINYTEXT TINYTEXT,C_MEDIUMBLOB MEDIUMBLOB,C_LONGBLOB LONGBLOB,C_LINESTRING LINESTRING not null, CONSTRAINT PRIMARY KEY USING HASH (C_D_ID) USING HASH, UNIQUE USING BTREE (C_ID,C_D_ID,C_W_ID DESC) , KEY index_CREATE_TABLE_049_taurus (C_ID DESC) COMMENT 'string', CHECK ('agdj56!#54'IS UNKNOWN)) AVG_ROW_LENGTH = 8, CHARACTER SET = utf8, CHECKSUM 1, COMMENT 'string', CONNECTION = 'connect_string', DELAY_KEY_WRITE = 1, ENCRYPTION 'N', INSERT_METHOD FIRST, KEY_BLOCK_SIZE = 0, MAX_ROWS 99, MIN_ROWS = 99, PACK_KEYS = 1, PASSWORD = 'string', ROW_FORMAT COMPRESSED, STATS_PERSISTENT 0, STATS_SAMPLE_PAGES 9 ;
[10 Jun 2019 17:15] MySQL Verification Team
Thank you for the bug report.
[16 Oct 2019 14:39] Paul DuBois
Posted by developer:
 
The same issue can occur for foreign key constraint names, too, if internally generated.

Added this note to
https://dev.mysql.com/doc/refman/8.0/en/identifier-length.html:

For constraint definitions that include no constraint name, the
server internally generates a name derived from the associated table
name. For example, internally generated foreign key and CHECK
constraint names consist of the table name plus _ibfk_ or _chk_ and a
number. If the table name is close to the length limit for constraint
names, the additional characters required for the constraint name may
cause that name to exceed the limit, resulting in an error.