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

ERROR 1071: Specified key was too long; max key length is 1000/2000 bytes on CREATE TABLE ... UNIQUE KEY ... USING HASH

Details

    Description

      # First 3 succeed, last one fails with 'ERROR 1071 (42000): Specified key was too long; max key length is 2000 bytes'
      CREATE TABLE t1(c CHAR(1), UNIQUE KEY i(c) USING HASH) ENGINE=InnoDB;
      CREATE TABLE t2(c CHAR(1), UNIQUE KEY i(c) USING HASH) ENGINE=MyISAM;
      CREATE TABLE t3(c CHAR(1), UNIQUE KEY i(c) USING HASH) ENGINE=MEMORY;
      CREATE TABLE t4(c CHAR(1), UNIQUE KEY i(c) USING HASH) ENGINE=Aria;
      

      Leads to:

      10.5.6 1c587481966abc7a9ad5309d0a91ca920f7a5657 (Optimized)

      10.5.6>CREATE TABLE t1(c CHAR(1), UNIQUE KEY i(c) USING HASH) ENGINE=InnoDB;
      Query OK, 0 rows affected (0.013 sec)
      10.5.6>CREATE TABLE t2(c CHAR(1), UNIQUE KEY i(c) USING HASH) ENGINE=MyISAM;
      Query OK, 0 rows affected (0.002 sec)
      10.5.6>CREATE TABLE t3(c CHAR(1), UNIQUE KEY i(c) USING HASH) ENGINE=MEMORY;
      Query OK, 0 rows affected (0.002 sec)
      10.5.6>CREATE TABLE t4(c CHAR(1), UNIQUE KEY i(c) USING HASH) ENGINE=Aria;
      ERROR 1071 (42000): Specified key was too long; max key length is 2000 bytes
      

      10.4.15 eae968f62d285de97ed607c87bc131cd863d5d03 (Optimized)

      10.4.15>CREATE TABLE t4(c CHAR(1), UNIQUE KEY i(c) USING HASH) ENGINE=Aria;
      ERROR 1071 (42000): Specified key was too long; max key length is 1000 bytes
      

      Also note the 1000 vs 2000 inconsistency between 10.4 and 10.5

      It is a regression:

      10.3.25 bafc5c1321a7dff5f2da292111bf98fed9d1658d (Optimized)

      10.3.25>CREATE TABLE t4(c CHAR(1), UNIQUE KEY i(c) USING HASH) ENGINE=Aria;
      Query OK, 0 rows affected (0.012 sec)
      

      10.2.34 (Optimized)

      10.2.34>CREATE TABLE t4(c CHAR(1), UNIQUE KEY i(c) USING HASH) ENGINE=Aria;
      Query OK, 0 rows affected (0.01 sec)
      

      10.1.47 (Optimized)

      10.1.47>CREATE TABLE t4(c CHAR(1), UNIQUE KEY i(c) USING HASH) ENGINE=Aria;
      Query OK, 0 rows affected (0.01 sec)
      

      Attachments

        Issue Links

          Activity

            Roel Roel Van de Paar added a comment - - edited

            May also want to review MDEV-21624, (MDEV-17662,) and MDEV-22184 when looking into this. Though none of them seems directly related, the variability in this area may affect fixing strategy.

            Roel Roel Van de Paar added a comment - - edited May also want to review MDEV-21624 , ( MDEV-17662 ,) and MDEV-22184 when looking into this. Though none of them seems directly related, the variability in this area may affect fixing strategy.

            Reassigned to Monty, I was told this is related to recent doubling of Aria max key length.

            Roel Roel Van de Paar added a comment - Reassigned to Monty, I was told this is related to recent doubling of Aria max key length.
            midenok Aleksey Midenkov added a comment -

            The error occurred due to:

            536           if (!table_arg->field[field->field_index]->stored_in_db())
            537           {
            538             my_free(*recinfo_out);
            539             if (table_arg->s->long_unique_table)
            540             {
            541               my_error(ER_TOO_LONG_KEY, MYF(0), table_arg->file->max_key_length());
            542               DBUG_RETURN(HA_ERR_INDEX_COL_TOO_LONG);
            543             }
            544             my_error(ER_KEY_BASED_ON_GENERATED_VIRTUAL_COLUMN, MYF(0));
            545             DBUG_RETURN(HA_ERR_UNSUPPORTED);
            546           }
            

            The original error was ER_KEY_BASED_ON_GENERATED_VIRTUAL_COLUMN. It was changed by MDEV-18791 to HA_ERR_INDEX_COL_TOO_LONG for some reason I don't know. The original error seems to be more correct as this is engine limitation. I suggest to revert MDEV-18791.

            midenok Aleksey Midenkov added a comment - The error occurred due to: 536 if (!table_arg->field[field->field_index]->stored_in_db()) 537 { 538 my_free(*recinfo_out); 539 if (table_arg->s->long_unique_table) 540 { 541 my_error(ER_TOO_LONG_KEY, MYF(0), table_arg->file->max_key_length()); 542 DBUG_RETURN(HA_ERR_INDEX_COL_TOO_LONG); 543 } 544 my_error(ER_KEY_BASED_ON_GENERATED_VIRTUAL_COLUMN, MYF(0)); 545 DBUG_RETURN(HA_ERR_UNSUPPORTED); 546 } The original error was ER_KEY_BASED_ON_GENERATED_VIRTUAL_COLUMN. It was changed by MDEV-18791 to HA_ERR_INDEX_COL_TOO_LONG for some reason I don't know. The original error seems to be more correct as this is engine limitation. I suggest to revert MDEV-18791 .

            People

              serg Sergei Golubchik
              Roel Roel Van de Paar
              Votes:
              0 Vote for this issue
              Watchers:
              5 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

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