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

Table definition gets corrupt after adding unique hash key

    XMLWordPrintable

Details

    • Q3/2025 Maintenance

    Description

      Note:
      The test case as such is not very realistic due to trying to create a foreign key on a blob on a MyISAM table (which is supposed to be ignored, but apparently not quite in this case, as I cannot replace it with just a prefixed key, the failure goes away).
      However, the effect is rather weird and drastic, so I don't think it can be dismissed lightly.

      10.5

      		 
      CREATE TABLE t (a TEXT, b INT, FOREIGN KEY(a) REFERENCES x(x)) ENGINE=MyISAM;
      		 
      SHOW CREATE TABLE t;
      		 
      ALTER TABLE t ADD UNIQUE(a), ADD KEY(a);
      		 
      SHOW CREATE TABLE t;
      		 
      ALTER TABLE t FORCE;
      		 
       
      		 
      DROP TABLE t;

      The initial table creation works as always, ending with

      10.5 5c92b27d5433df7558f41ac5718481f87bdfa544

      		 
      CREATE TABLE t (a TEXT, b INT, FOREIGN KEY(a) REFERENCES x(x)) ENGINE=MyISAM;
      		 
      Warnings:
      		 
      Note	1071	Specified key was too long; max key length is 1000 bytes
      		 
      SHOW CREATE TABLE t;
      		 
      Table	Create Table
      		 
      t	CREATE TABLE `t` (
      		 
        `a` text DEFAULT NULL,
      		 
        `b` int(11) DEFAULT NULL,
      		 
        KEY `a` (`a`(1000))
      		 
      ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci

      However, after adding the keys by ALTER, all goes wrong, see what happens with the column b, and note also <hash>(`a`) which makes the create statement invalid:

      ALTER TABLE t ADD UNIQUE(a), ADD KEY(a);
      		 
      Warnings:
      		 
      Note	1071	Specified key was too long; max key length is 1000 bytes
      		 
      SHOW CREATE TABLE t;
      		 
      Table	Create Table
      		 
      t	CREATE TABLE `t` (
      		 
        `a` text DEFAULT NULL,
      		 
        `b` int(11) GENERATED ALWAYS AS (<hash>(`a`)) VIRTUAL,
      		 
        UNIQUE KEY `a` (`a`) USING HASH,
      		 
        KEY `a_2` (`a`(1000))
      		 
      ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci

      The next ALTER just finishes it up, causing an assertion failure or a server crash:

      10.5 non-debug 5c92b27d5433df7558f41ac5718481f87bdfa544

      		 
      #2  <signal handler called>
      		 
      #3  0x00005556b8aa08c6 in re_setup_keyinfo_hash (key_info=key_info@entry=0x7faeac0f1780) at /data/bld/10.5-rel/sql/table.cc:9820
      		 
      #4  0x00005556b8a645f7 in mysql_prepare_alter_table (thd=thd@entry=0x7faeac000c68, table=table@entry=0x7faeac115318, create_info=create_info@entry=0x7faebe142500, alter_info=alter_info@entry=0x7faebe142410, alter_ctx=alter_ctx@entry=0x7faebe1418d0) at /data/bld/10.5-rel/sql/sql_table.cc:9461
      		 
      #5  0x00005556b8a6c0e0 in mysql_alter_table (thd=thd@entry=0x7faeac000c68, new_db=new_db@entry=0x7faeac005540, new_name=new_name@entry=0x7faeac0059a8, create_info=create_info@entry=0x7faebe142500, table_list=<optimized out>, table_list@entry=0x7faeac010730, recreate_info=recreate_info@entry=0x7faebe1423f0, alter_info=<optimized out>, order_num=<optimized out>, order=<optimized out>, ignore=<optimized out>, if_exists=<optimized out>) at /data/bld/10.5-rel/sql/sql_table.cc:10994
      		 
      #6  0x00005556b8ad0ccd in Sql_cmd_alter_table::execute (this=<optimized out>, thd=0x7faeac000c68) at /data/bld/10.5-rel/sql/sql_alter.cc:587
      		 
      #7  0x00005556b89b76a6 in mysql_execute_command (thd=thd@entry=0x7faeac000c68) at /data/bld/10.5-rel/sql/sql_parse.cc:6194
      		 
      #8  0x00005556b89bc58c in mysql_parse (thd=0x7faeac000c68, rawbuf=<optimized out>, length=<optimized out>, parser_state=<optimized out>, is_com_multi=<optimized out>, is_next_command=<optimized out>) at /data/bld/10.5-rel/sql/sql_parse.cc:8243
      		 
      #9  0x00005556b89bed45 in dispatch_command (command=command@entry=COM_QUERY, thd=thd@entry=0x7faeac000c68, packet=packet@entry=0x7faeac008109 "ALTER TABLE t FORCE", packet_length=packet_length@entry=19, is_com_multi=is_com_multi@entry=false, is_next_command=is_next_command@entry=false) at /data/bld/10.5-rel/sql/sql_parse.cc:1992
      		 
      #10 0x00005556b89c0a5d in do_command (thd=thd@entry=0x7faeac000c68) at /data/bld/10.5-rel/sql/sql_parse.cc:1375
      		 
      #11 0x00005556b8acc16d in do_handle_one_connection (connect=<optimized out>, put_in_cache=true) at /data/bld/10.5-rel/sql/sql_connect.cc:1386
      		 
      #12 0x00005556b8acc5d5 in handle_one_connection (arg=arg@entry=0x5556cebba5b8) at /data/bld/10.5-rel/sql/sql_connect.cc:1298
      		 
      #13 0x00005556b8e86114 in pfs_spawn_thread (arg=0x5556ceb4e728) at /data/bld/10.5-rel/storage/perfschema/pfs.cc:2201
      		 
      #14 0x00007faec40a81c4 in start_thread (arg=<optimized out>) at ./nptl/pthread_create.c:442
      		 
      #15 0x00007faec412885c in clone3 () at ../sysdeps/unix/sysv/linux/x86_64/clone3.S:81

      10.5 5c92b27d5433df7558f41ac5718481f87bdfa544 debug

      		 
      mariadbd: /data/bld/10.5-asan-ubsan/sql/table.cc:9804: void setup_keyinfo_hash(KEY*): Assertion `key_info->key_part->field->flags & (1<< 30)' failed.
      		 
      250521 14:06:53 [ERROR] /share8t/bld/10.5-asan-ubsan/sql/mariadbd got signal 6 ;
      		 
       
      		 
      #10 0x00007f9d20c53eb2 in __GI___assert_fail (assertion=0x5644795beba0 "key_info->key_part->field->flags & (1<< 30)", file=0x5644795b6600 "/data/bld/10.5-asan-ubsan/sql/table.cc", line=9804, function=0x5644795beb00 "void setup_keyinfo_hash(KEY*)") at ./assert/assert.c:101
      		 
      #11 0x000056447632ce1b in setup_keyinfo_hash (key_info=key_info@entry=0x6190000a2db8) at /data/bld/10.5-asan-ubsan/sql/table.cc:9804
      		 
      #12 0x00005644761ccfd8 in mysql_prepare_alter_table (thd=thd@entry=0x62b000069218, table=table@entry=0x6190000a2398, create_info=create_info@entry=0x7f9d16c05530, alter_info=alter_info@entry=0x7f9d16c05400, alter_ctx=alter_ctx@entry=0x7f9d16c04740) at /data/bld/10.5-asan-ubsan/sql/sql_table.cc:9285
      		 
      #13 0x00005644761f8ad3 in mysql_alter_table (thd=thd@entry=0x62b000069218, new_db=<optimized out>, new_name=new_name@entry=0x62b00006e118, create_info=create_info@entry=0x7f9d16c05530, table_list=table_list@entry=0x62b000038338, recreate_info=recreate_info@entry=0x7f9d16c05340, alter_info=<optimized out>, order_num=<optimized out>, order=<optimized out>, ignore=<optimized out>, if_exists=<optimized out>) at /data/bld/10.5-asan-ubsan/sql/sql_table.cc:10994
      		 
      #14 0x00005644764dc832 in Sql_cmd_alter_table::execute (this=<optimized out>, thd=0x62b000069218) at /data/bld/10.5-asan-ubsan/sql/sql_alter.cc:587
      		 
      #15 0x0000564475d6010e in mysql_execute_command (thd=thd@entry=0x62b000069218) at /data/bld/10.5-asan-ubsan/sql/sql_parse.cc:6194
      		 
      #16 0x0000564475d64fe5 in mysql_parse (thd=thd@entry=0x62b000069218, rawbuf=<optimized out>, length=<optimized out>, parser_state=parser_state@entry=0x7f9d16c06c60, is_com_multi=is_com_multi@entry=false, is_next_command=is_next_command@entry=false) at /data/bld/10.5-asan-ubsan/sql/sql_parse.cc:8243
      		 
      #17 0x0000564475d6e423 in dispatch_command (command=command@entry=COM_QUERY, thd=thd@entry=0x62b000069218, packet=packet@entry=0x629000235219 "ALTER TABLE t FORCE", packet_length=packet_length@entry=19, is_com_multi=is_com_multi@entry=false, is_next_command=is_next_command@entry=false) at /data/bld/10.5-asan-ubsan/sql/sql_parse.cc:1891
      		 
      #18 0x0000564475d7acf4 in do_command (thd=thd@entry=0x62b000069218) at /data/bld/10.5-asan-ubsan/sql/sql_parse.cc:1375
      		 
      #19 0x00005644764b087e in do_handle_one_connection (connect=<optimized out>, connect@entry=0x608000002db8, put_in_cache=put_in_cache@entry=true) at /data/bld/10.5-asan-ubsan/sql/sql_connect.cc:1386
      		 
      #20 0x00005644764b19db in handle_one_connection (arg=0x608000002db8) at /data/bld/10.5-asan-ubsan/sql/sql_connect.cc:1298
      		 
      #21 0x0000564477e3f5ee in pfs_spawn_thread (arg=0x615000006c18) at /data/bld/10.5-asan-ubsan/storage/perfschema/pfs.cc:2201
      		 
      #22 0x00007f9d20ca81c4 in start_thread (arg=<optimized out>) at ./nptl/pthread_create.c:442
      		 
      #23 0x00007f9d20d2885c in clone3 () at ../sysdeps/unix/sysv/linux/x86_64/clone3.S:81

      The failures started happening after this commit in 10.5.28:

      commit ecaedbe299fe11372c36319f9b732b81e9f54883
      		 
      Author: Nikita Malyavin
      		 
      Date:   Sat Oct 12 21:32:18 2024 +0200
      		 
       
      		 
          MDEV-33658 1/2 Refactoring: extract Key length initialization
      		 
          
          mysql_prepare_create_table: Extract a Key initialization part that

      Attachments

        Issue Links

          is caused by

          Bug - A problem which impairs or prevents the functions of the product. MDEV-33658 Cannot add a foreign key on a table with a long UNIQUE multi-column index, that contains a foreign key as a prefix.

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

          Activity

            People

              serg Sergei Golubchik
              elenst Elena Stepanova
              Votes:
              0 Vote for this issue
              Watchers:
              2 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.