Bug #93587 Error when creating a table with long partition names
Submitted: 13 Dec 2018 9:56 Modified: 15 Feb 2019 15:47
Reporter: Sergei Glushchenko Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Data Dictionary Severity:S3 (Non-critical)
Version:8.0.13 OS:Any
Assigned to: CPU Architecture:Any

[13 Dec 2018 9:56] Sergei Glushchenko
Description:
According to the https://dev.mysql.com/doc/refman/8.0/en/identifiers.html maximum length of certain identifier types (such as table and database name) is 64 characters. It was true for partition names as well at least in MySQL 5.7. This is no longer a case for MySQL 8.

How to repeat:
Run following in MySQL 5.7 (each identifier is 64 characters long). It will succeed:

CREATE DATABASE MC5NOGLQ9OFY7YM76Z1T758ZTPTJ6IPLVSLDHMSEXT63MLVHCPEW4DNU2OPQDRRE;
USE MC5NOGLQ9OFY7YM76Z1T758ZTPTJ6IPLVSLDHMSEXT63MLVHCPEW4DNU2OPQDRRE;

CREATE TABLE TH6EDXFX5D1U5BLB3I50LN5DFO415JIRP9XKUC0H9O2IONKQL3IOMFYW4ZVOCFPP (a INT NOT NULL, b INT)
    PARTITION BY RANGE (a) PARTITIONS 3 SUBPARTITION BY KEY (b) (
        PARTITION O8W7066AGXADOMYWHT89TWMBJOMTFDMDC74WJ7IUPKD75LVU1ENOV1J008SJBKKF VALUES LESS THAN (200) (
            SUBPARTITION YWKQ987ZTKDJ33ZBMLW526153X86VXL4X44R15SPF8JQS92665MT0QI6BSNKAZY5,
            SUBPARTITION OSYA45V7KKPJ840E4CZ7CKFXDT3J1NNM8QTT9BQOF896CAZVWFY4K236VHYD1WXN,
            SUBPARTITION M6YGLCVSKPSVF1RYZA4XJNP7HP9P7OKBP0268T2HKJ0005BW3LLSALQJ94UE5ZSV),
        PARTITION F3HRUC798U6YIBQFSC9BDKOAUH2SD6B0A3IA7J4P2V8M5U84AAVCR27NNQGM8NI3 VALUES LESS THAN (600) (
            SUBPARTITION YKWIEPEMRMG097FJ0D8WBJC9TF93GW7GVSU8H0MXYSX940JYO0RNVR4W7YYEZDSF,
            SUBPARTITION WDJZ36D1IMDRB4ZABLZXWE7J00OP1WR028V1PREZN46PK9L3Y3ERVTWKXYTMC08W,
            SUBPARTITION KRR0PI4ZBU50X4YVWKDA65PFYRNB69EV5LMM3CMBUVUEIHN2MPY30O8J8WEOOG2Q),
        PARTITION UYZ1675BEXI942ED7EUYLK03GM90QG3ZGYBLBKGLNWVYNVYUZ70J78BJWYLGS6CV VALUES LESS THAN (1800) (
            SUBPARTITION XIYZDGAOHPZH1R6RTKURMSUENS8VIN1U1CL7T2594FWR1ELS55UDVDWVX65K3WPE,
            SUBPARTITION J64S0S338J2AV273XIHW1QHFRK9ZVLSIGSXFF4E22RYCLAG2J3H04PN6M70OB39Y,
            SUBPARTITION XRL60IN1WNLR4YQAIILJTB9XCDP7Z4CUXRCY9Y2ES55W6UQERG51QV1UYTMZW673));

But MySQL 8.0 will return following error:

ERROR 3507 (HY000): Failed to update tablespaces dictionary object.

Remove 5 characters from each subpartition name:

CREATE TABLE TH6EDXFX5D1U5BLB3I50LN5DFO415JIRP9XKUC0H9O2IONKQL3IOMFYW4ZVOCFPP (a INT NOT NULL, b INT)
    PARTITION BY RANGE (a) PARTITIONS 3 SUBPARTITION BY KEY (b) (
        PARTITION O8W7066AGXADOMYWHT89TWMBJOMTFDMDC74WJ7IUPKD75LVU1ENOV1J008SJBKKF VALUES LESS THAN (200) (
            SUBPARTITION YWKQ987ZTKDJ33ZBMLW526153X86VXL4X44R15SPF8JQS92665MT0QI6BSN,
            SUBPARTITION OSYA45V7KKPJ840E4CZ7CKFXDT3J1NNM8QTT9BQOF896CAZVWFY4K236VHY,
            SUBPARTITION M6YGLCVSKPSVF1RYZA4XJNP7HP9P7OKBP0268T2HKJ0005BW3LLSALQJ94U),
        PARTITION F3HRUC798U6YIBQFSC9BDKOAUH2SD6B0A3IA7J4P2V8M5U84AAVCR27NNQGM8NI3 VALUES LESS THAN (600) (
            SUBPARTITION YKWIEPEMRMG097FJ0D8WBJC9TF93GW7GVSU8H0MXYSX940JYO0RNVR4W7YY,
            SUBPARTITION WDJZ36D1IMDRB4ZABLZXWE7J00OP1WR028V1PREZN46PK9L3Y3ERVTWKXYT,
            SUBPARTITION KRR0PI4ZBU50X4YVWKDA65PFYRNB69EV5LMM3CMBUVUEIHN2MPY30O8J8WE),
        PARTITION UYZ1675BEXI942ED7EUYLK03GM90QG3ZGYBLBKGLNWVYNVYUZ70J78BJWYLGS6CV VALUES LESS THAN (1800) (
            SUBPARTITION XIYZDGAOHPZH1R6RTKURMSUENS8VIN1U1CL7T2594FWR1ELS55UDVDWVX65,
            SUBPARTITION J64S0S338J2AV273XIHW1QHFRK9ZVLSIGSXFF4E22RYCLAG2J3H04PN6M70,
            SUBPARTITION XRL60IN1WNLR4YQAIILJTB9XCDP7Z4CUXRCY9Y2ES55W6UQERG51QV1UYTM));

Success now.
[13 Dec 2018 11:02] MySQL Verification Team
Hello Sergei,

Thank you for the report and feedback!

regards,
Umesh
[15 Feb 2019 15:47] Daniel Price
Posted by developer:
 
Fixed as of the upcoming 8.0.16 release, and here's the changelog entry:

The mysql.tablespaces.name column limit was 259 bytes, which was less
than required for permitted identifier lengths. The column limit was
raised to 268 bytes.