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

          is caused by

          Task - A task that needs to be done. MDEV-371 Unique indexes for blobs

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed
          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-20194 Warnings inconsistently issued upon CHECK on table from older versions

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-23530 ERROR 1071: Specified key was too long; max key length is 1000/2000 bytes on CREATE TABLE ... UNIQUE KEY ... USING HASH

          • Major - Major loss of function.
          • Open

          Activity

            People

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

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.