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

ALTER TABLE fails to rename a column in SYS_FIELDS

Details

    Description

      --source include/have_innodb.inc
      CREATE TABLE t3 ( col1 INT, col_int INTEGER, col_text TEXT ) ENGINE = InnoDB;
      ALTER TABLE t3 ADD PRIMARY KEY ( col_text(9), col_int ) ;
      ALTER TABLE t3 ADD INDEX ( col_int, col1 ) ;
      ALTER TABLE t3 ADD COLUMN col_int_g_copy INTEGER GENERATED ALWAYS AS (col_int)  ;
      ALTER TABLE t3 CHANGE COLUMN IF EXISTS col1 otto INT ;
      # The last statement leads to crash.
      DROP TABLE t3;
       
      Version: '10.6.0-MariaDB-debug-log'  socket: '/home/mleich/Server_bin/10.6_debug/mysql-test/var/tmp/mysqld.1.sock'  port: 16000  Source distribution
      2021-02-02 12:58:25 4 [ERROR] InnoDB: No matching column for `col1` in index `col_int` of table `test`.`t3`
      210202 12:58:25 [ERROR] mysqld got signal 11 ;
      Query (0x7f4e54012dc0): ALTER TABLE t3 CHANGE COLUMN IF EXISTS col1 otto INT
       
      Connection ID (thread ID): 4
      Status: NOT_KILLED
       
      #0  __pthread_kill (threadid=<optimized out>, signo=11) at ../sysdeps/unix/sysv/linux/pthread_kill.c:57
      #1  0x0000556d35791e13 in my_write_core (sig=11) at /home/mleich/Server/10.6/mysys/stacktrace.c:424
      #2  0x0000556d34e83b08 in handle_fatal_signal (sig=11) at /home/mleich/Server/10.6/sql/signal_handler.cc:330
      #3  <signal handler called>
      #4  0x0000556d3533ea7c in dict_stats_try_drop_table (thd=0x7f4e54000cf8, name=..., table_name=...) at /home/mleich/Server/10.6/storage/innobase/handler/handler0alter.cc:10096
      #5  0x0000556d3533ebf7 in innobase_reload_table (thd=0x7f4e54000cf8, table=0x0, table_name=..., ctx=...) at /home/mleich/Server/10.6/storage/innobase/handler/handler0alter.cc:10133
      #6  0x0000556d353417fc in ha_innobase::commit_inplace_alter_table (this=0x7f4e541c87d0, altered_table=0x7f4e7d62d960, ha_alter_info=0x7f4e7d62d8c0, commit=true) at /home/mleich/Server/10.6/storage/innobase/handler/handler0alter.cc:11263
      #7  0x0000556d34e94a3e in handler::ha_commit_inplace_alter_table (this=0x7f4e541c87d0, altered_table=0x7f4e7d62d960, ha_alter_info=0x7f4e7d62d8c0, commit=true) at /home/mleich/Server/10.6/sql/handler.cc:4855
      #8  0x0000556d34c27462 in mysql_inplace_alter_table (thd=0x7f4e54000cf8, table_list=0x7f4e54012ed0, table=0x7f4e5419c2a8, altered_table=0x7f4e7d62d960, ha_alter_info=0x7f4e7d62d8c0, target_mdl_request=0x7f4e7d62dd30, alter_ctx=0x7f4e7d62e880) at /home/mleich/Server/10.6/sql/sql_table.cc:8138
      #9  0x0000556d34c2ebd8 in mysql_alter_table (thd=0x7f4e54000cf8, new_db=0x7f4e54005768, new_name=0x7f4e54005b68, create_info=0x7f4e7d62f490, table_list=0x7f4e54012ed0, alter_info=0x7f4e7d62f3c0, order_num=0, order=0x0, ignore=false, if_exists=false) at /home/mleich/Server/10.6/sql/sql_table.cc:10683
      #10 0x0000556d34cd4d71 in Sql_cmd_alter_table::execute (this=0x7f4e540136c0, thd=0x7f4e54000cf8) at /home/mleich/Server/10.6/sql/sql_alter.cc:539
      #11 0x0000556d34b3034d in mysql_execute_command (thd=0x7f4e54000cf8) at /home/mleich/Server/10.6/sql/sql_parse.cc:5880
      #12 0x0000556d34b365e1 in mysql_parse (thd=0x7f4e54000cf8, rawbuf=0x7f4e54012dc0 "ALTER TABLE t3 CHANGE COLUMN IF EXISTS col1 otto INT", length=52, parser_state=0x7f4e7d630520) at /home/mleich/Server/10.6/sql/sql_parse.cc:7906
      #13 0x0000556d34b22af5 in dispatch_command (command=COM_QUERY, thd=0x7f4e54000cf8, packet=0x7f4e540087b9 "ALTER TABLE t3 CHANGE COLUMN IF EXISTS col1 otto INT ", packet_length=53) at /home/mleich/Server/10.6/sql/sql_parse.cc:1833
      #14 0x0000556d34b2143d in do_command (thd=0x7f4e54000cf8) at /home/mleich/Server/10.6/sql/sql_parse.cc:1365
      #15 0x0000556d34cca462 in do_handle_one_connection (connect=0x556d37f62df8, put_in_cache=true) at /home/mleich/Server/10.6/sql/sql_connect.cc:1410
      #16 0x0000556d34cca1d0 in handle_one_connection (arg=0x556d37f50f08) at /home/mleich/Server/10.6/sql/sql_connect.cc:1312
      #17 0x0000556d3520e213 in pfs_spawn_thread (arg=0x556d37ebab18) at /home/mleich/Server/10.6/storage/perfschema/pfs.cc:2201
      #18 0x00007f4e88f8d7fc in start_thread (arg=0x7f4e7d631700) at pthread_create.c:465
      #19 0x00007f4e881c3b5f in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:95
       
      10.6 8a495d7f90f64566d083d9ccd04cd95023a40931 2021-01-30T10:50:14+03:00
      origin/10.5 7c052cdf0bc53f8ee3387186993710bd9754b65d 2021-01-17T13:21:55+01:00
      origin/10.4 542d769ea1a22a7a6a87c9fe76ff911a162ade44 2021-01-28T07:39:34+02:00
      origin/10.3 df1eefb2ad138846269d40372678af805589700a 2021-01-07T17:53:04+01:00
      no replay on some 10.2 development tree mid of January
      

      Attachments

        Activity

          The failure started happening on 10.3 after this commit:

          commit 9a645dae9e59ec398cfda33529c44002625ddc87
          Author: Nikita Malyavin
          Date:   Mon Dec 21 22:54:27 2020 +1000
           
              MDEV-23632 ALTER TABLE...ADD KEY creates corrupted index on virtual column
          

          elenst Elena Stepanova added a comment - The failure started happening on 10.3 after this commit: commit 9a645dae9e59ec398cfda33529c44002625ddc87 Author: Nikita Malyavin Date: Mon Dec 21 22:54:27 2020 +1000   MDEV-23632 ALTER TABLE...ADD KEY creates corrupted index on virtual column

          I believe that the root cause of this problem may affect 10.2 as well, but I am not sure of that yet. The provided test case does depend on MDEV-11369 (instant ADD COLUMN), which is only present starting with 10.3.

          The root cause why the table was not found is that innobase_rename_column_try() is looking for SYS_FIELDS.POS=4<<16 instead of SYS_FIELDS.POS=1<<16. Hence, the column was not renamed.

          Debugging this with INFORMATION_SCHEMA.INNODB_SYS_FIELDS is not easy, because it will not display the actual value of the POS column unless the following patch is applied:

          diff --git a/storage/innobase/dict/dict0load.cc b/storage/innobase/dict/dict0load.cc
          index 386b99bcaad..ce77b04a32b 100644
          --- a/storage/innobase/dict/dict0load.cc
          +++ b/storage/innobase/dict/dict0load.cc
          @@ -2072,8 +2072,9 @@ dict_load_field_low(
           		return("SYS_FIELDS.POS mismatch");
           	}
           
          +	prefix_len = pos_and_prefix_len & 0xFFFFUL;
          +	if (!index) position = pos_and_prefix_len; else
           	if (first_field || pos_and_prefix_len > 0xFFFFUL) {
          -		prefix_len = pos_and_prefix_len & 0xFFFFUL;
           		position = (pos_and_prefix_len & 0xFFFF0000UL)  >> 16;
           	} else {
           		prefix_len = 0;
          

          The prefix_len is not being displayed at all.

          I am working on trying to find out why we are trying to look up the wrong field offset, and how to provide some more robust error handling for a dictionary corruption scenario.

          marko Marko Mäkelä added a comment - I believe that the root cause of this problem may affect 10.2 as well, but I am not sure of that yet. The provided test case does depend on MDEV-11369 (instant ADD COLUMN ), which is only present starting with 10.3. The root cause why the table was not found is that innobase_rename_column_try() is looking for SYS_FIELDS.POS=4<<16 instead of SYS_FIELDS.POS=1<<16 . Hence, the column was not renamed. Debugging this with INFORMATION_SCHEMA.INNODB_SYS_FIELDS is not easy, because it will not display the actual value of the POS column unless the following patch is applied: diff --git a/storage/innobase/dict/dict0load.cc b/storage/innobase/dict/dict0load.cc index 386b99bcaad..ce77b04a32b 100644 --- a/storage/innobase/dict/dict0load.cc +++ b/storage/innobase/dict/dict0load.cc @@ -2072,8 +2072,9 @@ dict_load_field_low( return("SYS_FIELDS.POS mismatch"); } + prefix_len = pos_and_prefix_len & 0xFFFFUL; + if (!index) position = pos_and_prefix_len; else if (first_field || pos_and_prefix_len > 0xFFFFUL) { - prefix_len = pos_and_prefix_len & 0xFFFFUL; position = (pos_and_prefix_len & 0xFFFF0000UL) >> 16; } else { prefix_len = 0; The prefix_len is not being displayed at all. I am working on trying to find out why we are trying to look up the wrong field offset, and how to provide some more robust error handling for a dictionary corruption scenario.

          I do not think that we can easily fix the consequences of the bug. Even if innobase_reload_table() checked for the null pointer, we would crash in various places later due to m_prebuilt->table==NULL. The root cause is in innobase_rename_column_try(), and I think that it should affect MariaDB 10.0, 10.1, MySQL 5.6, 5.7 as well. I am working on a test case for 10.2.

          marko Marko Mäkelä added a comment - I do not think that we can easily fix the consequences of the bug. Even if innobase_reload_table() checked for the null pointer, we would crash in various places later due to m_prebuilt->table==NULL . The root cause is in innobase_rename_column_try() , and I think that it should affect MariaDB 10.0, 10.1, MySQL 5.6, 5.7 as well. I am working on a test case for 10.2.

          I repeated the root cause on 10.2:

          --source include/have_innodb.inc
          CREATE TABLE t1 (a INT, b TEXT, c INT, PRIMARY KEY(b(9)), INDEX(c,a))
          ENGINE=InnoDB;
          ALTER TABLE t1 CHANGE COLUMN a u INT;
          SELECT * FROM information_schema.innodb_sys_fields where name='a';
          DROP TABLE t1;
          

          Without the fix, the SELECT will wrongly return one row for the old column name a. The column was supposed to be renamed from a to u, but because it is not the first field in the secondary index, and because the primary key (but not the secondary index) contains a column prefix, the UPDATE SYS_FIELDS will fail to find the record with the wrong search key (INDEX_ID,POS).

          marko Marko Mäkelä added a comment - I repeated the root cause on 10.2: --source include/have_innodb.inc CREATE TABLE t1 (a INT , b TEXT, c INT , PRIMARY KEY (b(9)), INDEX (c,a)) ENGINE=InnoDB; ALTER TABLE t1 CHANGE COLUMN a u INT ; SELECT * FROM information_schema.innodb_sys_fields where name = 'a' ; DROP TABLE t1; Without the fix, the SELECT will wrongly return one row for the old column name a . The column was supposed to be renamed from a to u , but because it is not the first field in the secondary index, and because the primary key (but not the secondary index) contains a column prefix, the UPDATE SYS_FIELDS will fail to find the record with the wrong search key (INDEX_ID,POS) .

          Results of RQG testing on
          origin/10.3 59eda73eff1a22ac0373d818bc802c05e82b5449 2021-02-01T13:17:17+02:00
          including a patch 
          Test battery for broad range coverage:  No new bad effects.
          

          mleich Matthias Leich added a comment - Results of RQG testing on origin/10.3 59eda73eff1a22ac0373d818bc802c05e82b5449 2021-02-01T13:17:17+02:00 including a patch Test battery for broad range coverage: No new bad effects.

          People

            marko Marko Mäkelä
            mleich Matthias Leich
            Votes:
            0 Vote for this issue
            Watchers:
            4 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.