Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-21624

Unique index length is able to exceed max key length

    XMLWordPrintable

    Details

      Description

      It appears that unique indexes are able to exceed the max key length in some cases.

      For example, if we tried to add the following primary key, then it would fail:

      CREATE OR REPLACE TABLE tab (
        col1 smallint(6) DEFAULT NULL,
        col2 smallint(6) DEFAULT NULL,
        col3 bigint(20) DEFAULT NULL,
        col4 bigint(20) DEFAULT NULL,
        col5 varchar(64) DEFAULT NULL,
        col6 varchar(64) DEFAULT NULL,
        col7 varchar(128) DEFAULT NULL,
        col8 varchar(20) DEFAULT NULL,
        col9 varchar(128) DEFAULT NULL,
        col10 varchar(128) DEFAULT NULL,
        col11 varchar(2000) DEFAULT NULL,
        col12 double DEFAULT NULL,
        col13 double DEFAULT NULL,
        col14 double DEFAULT NULL,
        col15 double DEFAULT NULL,
        col16 tinyint(4) DEFAULT NULL,
        col17 double DEFAULT NULL,
        col18 double DEFAULT NULL,
        KEY col4_idx (col4)
      ) DEFAULT CHARACTER SET = utf8mb4;
      ALTER TABLE tab
         ADD PRIMARY KEY
         (col1, col2, col3, col4, col5, col6, col7, col8, col9, col10, col11);
      

      And for example, if we tried to add the following non-unique secondary index, then it would also fail:

      CREATE OR REPLACE TABLE tab (
        col1 smallint(6) DEFAULT NULL,
        col2 smallint(6) DEFAULT NULL,
        col3 bigint(20) DEFAULT NULL,
        col4 bigint(20) DEFAULT NULL,
        col5 varchar(64) DEFAULT NULL,
        col6 varchar(64) DEFAULT NULL,
        col7 varchar(128) DEFAULT NULL,
        col8 varchar(20) DEFAULT NULL,
        col9 varchar(128) DEFAULT NULL,
        col10 varchar(128) DEFAULT NULL,
        col11 varchar(2000) DEFAULT NULL,
        col12 double DEFAULT NULL,
        col13 double DEFAULT NULL,
        col14 double DEFAULT NULL,
        col15 double DEFAULT NULL,
        col16 tinyint(4) DEFAULT NULL,
        col17 double DEFAULT NULL,
        col18 double DEFAULT NULL,
        KEY col4_idx (col4)
      ) DEFAULT CHARACTER SET = utf8mb4;
      CREATE INDEX tab_idx
         ON tab
         (col1, col2, col3, col4, col5, col6, col7, col8, col9, col10, col11);
      

      We see the following error in both cases:

      ERROR 1071 (42000): Specified key was too long; max key length is 3072 bytes
      

      However, for some reason, if we tried to add the following unique index, then it would succeed:

      CREATE OR REPLACE TABLE tab (
        col1 smallint(6) DEFAULT NULL,
        col2 smallint(6) DEFAULT NULL,
        col3 bigint(20) DEFAULT NULL,
        col4 bigint(20) DEFAULT NULL,
        col5 varchar(64) DEFAULT NULL,
        col6 varchar(64) DEFAULT NULL,
        col7 varchar(128) DEFAULT NULL,
        col8 varchar(20) DEFAULT NULL,
        col9 varchar(128) DEFAULT NULL,
        col10 varchar(128) DEFAULT NULL,
        col11 varchar(2000) DEFAULT NULL,
        col12 double DEFAULT NULL,
        col13 double DEFAULT NULL,
        col14 double DEFAULT NULL,
        col15 double DEFAULT NULL,
        col16 tinyint(4) DEFAULT NULL,
        col17 double DEFAULT NULL,
        col18 double DEFAULT NULL,
        KEY col4_idx (col4)
      ) DEFAULT CHARACTER SET = utf8mb4;
      CREATE UNIQUE INDEX tab_unq_idx
         ON tab
         (col1, col2, col3, col4, col5, col6, col7, col8, col9, col10, col11);
      

      Is it intentional for the unique index to have a different max key length?

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              sachin.setiya.007 Sachin Setiya
              Reporter:
              GeoffMontee Geoff Montee
              Votes:
              0 Vote for this issue
              Watchers:
              5 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved: