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

Table corruption ER_NO_SUCH_TABLE_IN_ENGINE or ER_CRASHED_ON_USAGE after ALTER on table with foreign key

Details

    Description

      --source include/have_innodb.inc
       
      CREATE TABLE t1 (a INT, b INT, PRIMARY KEY (a)) ENGINE=InnoDB;
      ALTER TABLE t1 ADD FOREIGN KEY (b) REFERENCES t1 (a) ON UPDATE CASCADE;
      LOCK TABLE t1 WRITE;
      TRUNCATE TABLE t1;
      ALTER TABLE t1 ADD c INT;
       
      # Cleanup
      UNLOCK TABLES;
      DROP TABLE t1;
      

      10.2 51b7438d debug and non-debug alike

      mysqltest: At line 7: query 'ALTER TABLE t1 ADD c INT' failed: 1932: Table 'test.t1' doesn't exist in engine
      

      2021-01-06  1:44:39 140509058860800 [Warning] InnoDB: Table test/t1 contains 2 user defined columns in InnoDB, but 3 columns in MariaDB. Please check INFORMATION_SCHEMA.INNODB_SYS_COLUMNS and https://mariadb.com/kb/en/innodb-data-dictionary-troubleshooting/ for how to resolve the issue.
      

      10.5 20727387 non-debug

      mysqltest: At line 7: query 'ALTER TABLE t1 ADD c INT' failed: 1194: Table 't1' is marked as crashed and should be repaired
      

      2021-01-06  1:45:53 4 [Warning] InnoDB: Table test/t1 contains 2 user defined columns in InnoDB, but 3 columns in MariaDB. Please check INFORMATION_SCHEMA.INNODB_SYS_COLUMNS and https://mariadb.com/kb/en/innodb-data-dictionary-troubleshooting/ for how to resolve the issue.
      2021-01-06  1:45:53 4 [ERROR] mariadbd: Table 't1' is marked as crashed and should be repaired
      

      10.5 20727387 debug

      2021-01-06  1:46:48 4 [Warning] InnoDB: Table test/t1 contains 2 user defined columns in InnoDB, but 3 columns in MariaDB. Please check INFORMATION_SCHEMA.INNODB_SYS_COLUMNS and https://mariadb.com/kb/en/innodb-data-dictionary-troubleshooting/ for how to resolve the issue.
      2021-01-06  1:46:48 4 [ERROR] mariadbd: Table 't1' is marked as crashed and should be repaired
      mariadbd: /data/src/10.5/sql/sql_base.cc:2375: int Locked_tables_list::unlock_locked_tables(THD*): Assertion `thd->transaction->stmt.is_empty()' failed.
      210106  1:46:48 [ERROR] mysqld got signal 6 ;
       
      #7  0x00007fe54d198f36 in __GI___assert_fail (assertion=0x56169620e3c0 "thd->transaction->stmt.is_empty()", file=0x56169620d7c0 "/data/src/10.5/sql/sql_base.cc", line=2375, function=0x56169620e388 "int Locked_tables_list::unlock_locked_tables(THD*)") at assert.c:101
      #8  0x00005616953ab046 in Locked_tables_list::unlock_locked_tables (this=0x7fe510004d90, thd=0x7fe510000db8) at /data/src/10.5/sql/sql_base.cc:2375
      #9  0x00005616953ab4b6 in Locked_tables_list::unlink_all_closed_tables (this=0x7fe510004d90, thd=0x7fe510000db8, lock=0x0, reopen_count=0) at /data/src/10.5/sql/sql_base.cc:2557
      #10 0x00005616953aba61 in Locked_tables_list::reopen_tables (this=0x7fe510004d90, thd=0x7fe510000db8, need_reopen=false) at /data/src/10.5/sql/sql_base.cc:2654
      #11 0x0000561695561cfc in mysql_alter_table (thd=0x7fe510000db8, new_db=0x7fe510005800, new_name=0x7fe510005c00, create_info=0x7fe548142420, table_list=0x7fe510014010, alter_info=0x7fe548142350, order_num=0, order=0x0, ignore=false, if_exists=false) at /data/src/10.5/sql/sql_table.cc:11044
      #12 0x0000561695608adc in Sql_cmd_alter_table::execute (this=0x7fe5100147f8, thd=0x7fe510000db8) at /data/src/10.5/sql/sql_alter.cc:539
      #13 0x000056169545f9cc in mysql_execute_command (thd=0x7fe510000db8) at /data/src/10.5/sql/sql_parse.cc:6006
      #14 0x0000561695465d88 in mysql_parse (thd=0x7fe510000db8, rawbuf=0x7fe510013f30 "ALTER TABLE t1 ADD c INT", length=24, parser_state=0x7fe548143510, is_com_multi=false, is_next_command=false) at /data/src/10.5/sql/sql_parse.cc:8042
      #15 0x0000561695451d6b in dispatch_command (command=COM_QUERY, thd=0x7fe510000db8, packet=0x7fe5100090a9 "ALTER TABLE t1 ADD c INT", packet_length=24, is_com_multi=false, is_next_command=false) at /data/src/10.5/sql/sql_parse.cc:1872
      #16 0x000056169545055f in do_command (thd=0x7fe510000db8) at /data/src/10.5/sql/sql_parse.cc:1353
      #17 0x00005616955fde9f in do_handle_one_connection (connect=0x5616984b58a8, put_in_cache=true) at /data/src/10.5/sql/sql_connect.cc:1410
      #18 0x00005616955fdc02 in handle_one_connection (arg=0x5616984cf308) at /data/src/10.5/sql/sql_connect.cc:1312
      #19 0x0000561695b5c74d in pfs_spawn_thread (arg=0x5616984b54e8) at /data/src/10.5/storage/perfschema/pfs.cc:2201
      #20 0x00007fe54d6b0609 in start_thread (arg=<optimized out>) at pthread_create.c:477
      #21 0x00007fe54d284293 in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:95
      

      Reproducible on 10.2-10.6 with slight differences as described above.
      Not reproducible on 10.1.
      Not reproducible on MySQL 5.7 or 8.0.

      Attachments

        Issue Links

          Activity

            For an extra note, this

            InnoDB: Table test/t1 contains 2 user defined columns in InnoDB, but 3 columns in MariaDB. Please check INFORMATION_SCHEMA.INNODB_SYS_COLUMNS and https://mariadb.com/kb/en/innodb-data-dictionary-troubleshooting/ for how to resolve the issue.
            

            should probably be an ERROR and not a warning. Also, the page at the provided link doesn't offer any advice for resolving this issue.

            elenst Elena Stepanova added a comment - For an extra note, this InnoDB: Table test/t1 contains 2 user defined columns in InnoDB, but 3 columns in MariaDB. Please check INFORMATION_SCHEMA.INNODB_SYS_COLUMNS and https://mariadb.com/kb/en/innodb-data-dictionary-troubleshooting/ for how to resolve the issue. should probably be an ERROR and not a warning. Also, the page at the provided link doesn't offer any advice for resolving this issue.

            Upon marko's request, tried to run on 10.2 with innodb_safe_truncate=off. It helps, the test case does not fail then.
            10.3+ does not have the option.

            elenst Elena Stepanova added a comment - Upon marko 's request, tried to run on 10.2 with innodb_safe_truncate=off . It helps, the test case does not fail then. 10.3+ does not have the option.

            In MDEV-13564, we changed ha_innobase::truncate() to internally perform RENAME, CREATE and DROP.

            The LOCK TABLE is apparently interfering with the following DDL statements. As far as I can tell, the statement

            ALTER TABLE t1 ADD c INT;
            

            completed just fine inside InnoDB (in 10.2, or without the MDEV-20590 SET GLOBAL innodb_instant_alter_column_allowed=never, it will rebuild the table).

            The immediate cause for the failure appears to be that HA_ERR_NO_SUCH_TABLE is being returned by ha_innobase::open() upon the completion of the operation:

            10.2 5ecaf52d42a1e464c71515f35be97855072bcafe

            #0  0x0000564145903824 in handler::ha_open (this=0x7ffae4037420, table_arg=0x7ffae4042128, name=0x7ffae4023190 "./test/t1", mode=2, test_if_locked=18) at /mariadb/10.2o/sql/handler.cc:2592
            #1  0x00005641457fc6ab in open_table_from_share (thd=thd@entry=0x7ffae4008f48, share=<optimized out>, share@entry=0x7ffae4022c80, alias=<optimized out>, db_stat=<optimized out>, db_stat@entry=33, 
                prgflag=<optimized out>, prgflag@entry=8, ha_open_flags=<optimized out>, outparam=<optimized out>, is_create_table=<optimized out>) at /mariadb/10.2o/sql/table.cc:3422
            #2  0x00005641456ed720 in open_table (thd=thd@entry=0x7ffae4008f48, table_list=table_list@entry=0x7ffae401bc88, ot_ctx=ot_ctx@entry=0x7ffae80f24b0) at /mariadb/10.2o/sql/sql_base.cc:1934
            #3  0x00005641457db599 in mysql_inplace_alter_table (thd=0x7ffae4008f48, table_list=0x7ffae401bc88, table=0x0, altered_table=0x7ffae404a258, ha_alter_info=0x7ffae80f27d0, 
                inplace_supported=HA_ALTER_INPLACE_NO_LOCK_AFTER_PREPARE, target_mdl_request=<optimized out>, alter_ctx=0x7ffae80f1a00) at /mariadb/10.2o/sql/sql_table.cc:7522
            #4  mysql_alter_table (thd=<optimized out>, thd@entry=0x7ffae4008f48, new_db=<optimized out>, new_name=<optimized out>, create_info=<optimized out>, create_info@entry=0x7ffae80f2e68, 
                table_list=table_list@entry=0x7ffae401bc88, alter_info=<optimized out>, alter_info@entry=0x7ffae80f35b8, order_num=<optimized out>, order=<optimized out>, ignore=<optimized out>)
                at /mariadb/10.2o/sql/sql_table.cc:9627
            #5  0x000056414582ea72 in Sql_cmd_alter_table::execute (this=<optimized out>, thd=0x7ffae4008f48) at /mariadb/10.2o/sql/sql_alter.cc:333
            #6  0x000056414573f575 in mysql_execute_command (thd=<optimized out>, thd@entry=0x7ffae4008f48) at /mariadb/10.2o/sql/sql_parse.cc:5994
            #7  0x000056414573cd27 in mysql_parse (thd=thd@entry=0x7ffae4008f48, rawbuf=0x7ffae401bbb0 "ALTER TABLE t1 ADD c INT", length=<optimized out>, parser_state=parser_state@entry=0x7ffae80f46f0, 
                is_com_multi=false, is_next_command=false) at /mariadb/10.2o/sql/sql_parse.cc:7763
            

            The reason for this appears to be that ha_innobase::open() is being invoked with the wrong TABLE_SHARE (corresponding to the old table definition, before the ADD COLUMN):

            	if (UNIV_UNLIKELY(n_cols != n_fields)) {
            		ib::warn() << "Table " << norm_name << " contains "
            			<< n_cols << " user"
            			" defined columns in InnoDB, but " << n_fields
            			<< " columns in MariaDB. Please check"
            			" INFORMATION_SCHEMA.INNODB_SYS_COLUMNS and"
            			" https://mariadb.com/kb/en/innodb-data-dictionary-troubleshooting/"
            			" for how to resolve the issue.";
             
            		/* Mark this table as corrupted, so the drop table
            		or force recovery can still use it, but not others. */
            		ib_table->file_unreadable = true;
            		ib_table->corrupted = true;
            		dict_table_close(ib_table, FALSE, FALSE);
            		goto no_such_table;
            	}
            

            I suspect that this bug could occur independent of the MDEV-13564 change, in the highly unlikely case that the table definition had been evicted from the InnoDB data dictionary cache during this.

            I hope that monty can shed some light on this. He recently helped fix MDEV-24564, which is a little similar.

            marko Marko Mäkelä added a comment - In MDEV-13564 , we changed ha_innobase::truncate() to internally perform RENAME , CREATE and DROP . The LOCK TABLE is apparently interfering with the following DDL statements. As far as I can tell, the statement ALTER TABLE t1 ADD c INT ; completed just fine inside InnoDB (in 10.2, or without the MDEV-20590 SET GLOBAL innodb_instant_alter_column_allowed=never , it will rebuild the table). The immediate cause for the failure appears to be that HA_ERR_NO_SUCH_TABLE is being returned by ha_innobase::open() upon the completion of the operation: 10.2 5ecaf52d42a1e464c71515f35be97855072bcafe #0 0x0000564145903824 in handler::ha_open (this=0x7ffae4037420, table_arg=0x7ffae4042128, name=0x7ffae4023190 "./test/t1", mode=2, test_if_locked=18) at /mariadb/10.2o/sql/handler.cc:2592 #1 0x00005641457fc6ab in open_table_from_share (thd=thd@entry=0x7ffae4008f48, share=<optimized out>, share@entry=0x7ffae4022c80, alias=<optimized out>, db_stat=<optimized out>, db_stat@entry=33, prgflag=<optimized out>, prgflag@entry=8, ha_open_flags=<optimized out>, outparam=<optimized out>, is_create_table=<optimized out>) at /mariadb/10.2o/sql/table.cc:3422 #2 0x00005641456ed720 in open_table (thd=thd@entry=0x7ffae4008f48, table_list=table_list@entry=0x7ffae401bc88, ot_ctx=ot_ctx@entry=0x7ffae80f24b0) at /mariadb/10.2o/sql/sql_base.cc:1934 #3 0x00005641457db599 in mysql_inplace_alter_table (thd=0x7ffae4008f48, table_list=0x7ffae401bc88, table=0x0, altered_table=0x7ffae404a258, ha_alter_info=0x7ffae80f27d0, inplace_supported=HA_ALTER_INPLACE_NO_LOCK_AFTER_PREPARE, target_mdl_request=<optimized out>, alter_ctx=0x7ffae80f1a00) at /mariadb/10.2o/sql/sql_table.cc:7522 #4 mysql_alter_table (thd=<optimized out>, thd@entry=0x7ffae4008f48, new_db=<optimized out>, new_name=<optimized out>, create_info=<optimized out>, create_info@entry=0x7ffae80f2e68, table_list=table_list@entry=0x7ffae401bc88, alter_info=<optimized out>, alter_info@entry=0x7ffae80f35b8, order_num=<optimized out>, order=<optimized out>, ignore=<optimized out>) at /mariadb/10.2o/sql/sql_table.cc:9627 #5 0x000056414582ea72 in Sql_cmd_alter_table::execute (this=<optimized out>, thd=0x7ffae4008f48) at /mariadb/10.2o/sql/sql_alter.cc:333 #6 0x000056414573f575 in mysql_execute_command (thd=<optimized out>, thd@entry=0x7ffae4008f48) at /mariadb/10.2o/sql/sql_parse.cc:5994 #7 0x000056414573cd27 in mysql_parse (thd=thd@entry=0x7ffae4008f48, rawbuf=0x7ffae401bbb0 "ALTER TABLE t1 ADD c INT", length=<optimized out>, parser_state=parser_state@entry=0x7ffae80f46f0, is_com_multi=false, is_next_command=false) at /mariadb/10.2o/sql/sql_parse.cc:7763 The reason for this appears to be that ha_innobase::open() is being invoked with the wrong TABLE_SHARE (corresponding to the old table definition, before the ADD COLUMN ): if (UNIV_UNLIKELY(n_cols != n_fields)) { ib::warn() << "Table " << norm_name << " contains " << n_cols << " user" " defined columns in InnoDB, but " << n_fields << " columns in MariaDB. Please check" " INFORMATION_SCHEMA.INNODB_SYS_COLUMNS and" " https://mariadb.com/kb/en/innodb-data-dictionary-troubleshooting/" " for how to resolve the issue." ;   /* Mark this table as corrupted, so the drop table or force recovery can still use it, but not others. */ ib_table->file_unreadable = true ; ib_table->corrupted = true ; dict_table_close(ib_table, FALSE, FALSE); goto no_such_table; } I suspect that this bug could occur independent of the MDEV-13564 change, in the highly unlikely case that the table definition had been evicted from the InnoDB data dictionary cache during this. I hope that monty can shed some light on this. He recently helped fix MDEV-24564 , which is a little similar.

            I put a breakpoint on mysql_rename_table, just before the open_table code.
            I noticed that, probably because of truncate, we had two ibd files
            t1.ibd
            #sql-5306_9

            I put a breakpoint on the above code and got the error:
            nnoDB: Table test/t1 contains 2 user defined columns in InnoDB, but 3 columns in MariaDB.

            Why is InnoDB still using the old version of the table with 2 fields, when it should be 3 fields after the alter_table_inplace and as ha_commit_inplace_alter_table() has already been called.

            One possible solution is that InnoDB did all the alter table changes in the old #sql- table (left from truncate).

            monty Michael Widenius added a comment - I put a breakpoint on mysql_rename_table, just before the open_table code. I noticed that, probably because of truncate, we had two ibd files t1.ibd #sql-5306_9 I put a breakpoint on the above code and got the error: nnoDB: Table test/t1 contains 2 user defined columns in InnoDB, but 3 columns in MariaDB. Why is InnoDB still using the old version of the table with 2 fields, when it should be 3 fields after the alter_table_inplace and as ha_commit_inplace_alter_table() has already been called. One possible solution is that InnoDB did all the alter table changes in the old #sql- table (left from truncate).

            Sorry, I got the reason for the mismatch the wrong way around. The problem ought to be caused by MDEV-13564, which is internally executing RENAME, CREATE, and DROP. For some reason (possibly due to the LOCK TABLE), we are getting the wrong table passed to ALTER TABLE:

            10.3 25ecf8ed4b4cbca69a9fa09c27bbd4e5c83fafe3

            (rr) p m_prebuilt->table->name
            $6 = {m_name = 0x7f729403a4a0 "test/#sql-ib21"}
            (rr) bt
            #0  ha_innobase::check_if_supported_inplace_alter (this=0x7f72940552e0, 
                altered_table=0x7f729405bf18, ha_alter_info=0x7f72ac04c290)
                at /mariadb/10.3/storage/innobase/handler/handler0alter.cc:950
            #1  0x0000562bbdfc1357 in mysql_alter_table (thd=thd@entry=0x7f7294017048, 
                new_db=new_db@entry=0x7f729401b730, 
                new_name=new_name@entry=0x7f729401baf8, 
                create_info=create_info@entry=0x7f72ac04d6b0, table_list=<optimized out>, 
                table_list@entry=0x7f7294022c98, 
                alter_info=alter_info@entry=0x7f72ac04d5f0, order_num=0, order=0x0, 
                ignore=false) at /mariadb/10.3/sql/sql_table.cc:9955
            #2  0x0000562bbe02a529 in Sql_cmd_alter_table::execute (this=<optimized out>, 
                thd=0x7f7294017048) at /mariadb/10.3/sql/sql_alter.cc:512
            #3  0x0000562bbdf0c848 in mysql_execute_command (thd=thd@entry=0x7f7294017048)
                at /mariadb/10.3/sql/sql_parse.cc:6053
            #4  0x0000562bbdf0e072 in mysql_parse (thd=thd@entry=0x7f7294017048, 
                rawbuf=<optimized out>, length=<optimized out>, 
                parser_state=parser_state@entry=0x7f72ac04e570, 
                is_com_multi=is_com_multi@entry=false, 
                is_next_command=is_next_command@entry=false)
                at /mariadb/10.3/sql/sql_parse.cc:7841
            #5  0x0000562bbdf107d5 in dispatch_command (command=command@entry=COM_QUERY, 
                thd=thd@entry=0x7f7294017048, packet=0x7f72ac04e570 "\377\377\377\377", 
                packet@entry=0x7f7294009f59 "ALTER TABLE t1 ADD c INT", 
            

            This is the old table that was supposed to be discarded by TRUNCATE TABLE t1.

            marko Marko Mäkelä added a comment - Sorry, I got the reason for the mismatch the wrong way around. The problem ought to be caused by MDEV-13564 , which is internally executing RENAME , CREATE , and DROP . For some reason (possibly due to the LOCK TABLE ), we are getting the wrong table passed to ALTER TABLE : 10.3 25ecf8ed4b4cbca69a9fa09c27bbd4e5c83fafe3 (rr) p m_prebuilt->table->name $6 = {m_name = 0x7f729403a4a0 "test/#sql-ib21"} (rr) bt #0 ha_innobase::check_if_supported_inplace_alter (this=0x7f72940552e0, altered_table=0x7f729405bf18, ha_alter_info=0x7f72ac04c290) at /mariadb/10.3/storage/innobase/handler/handler0alter.cc:950 #1 0x0000562bbdfc1357 in mysql_alter_table (thd=thd@entry=0x7f7294017048, new_db=new_db@entry=0x7f729401b730, new_name=new_name@entry=0x7f729401baf8, create_info=create_info@entry=0x7f72ac04d6b0, table_list=<optimized out>, table_list@entry=0x7f7294022c98, alter_info=alter_info@entry=0x7f72ac04d5f0, order_num=0, order=0x0, ignore=false) at /mariadb/10.3/sql/sql_table.cc:9955 #2 0x0000562bbe02a529 in Sql_cmd_alter_table::execute (this=<optimized out>, thd=0x7f7294017048) at /mariadb/10.3/sql/sql_alter.cc:512 #3 0x0000562bbdf0c848 in mysql_execute_command (thd=thd@entry=0x7f7294017048) at /mariadb/10.3/sql/sql_parse.cc:6053 #4 0x0000562bbdf0e072 in mysql_parse (thd=thd@entry=0x7f7294017048, rawbuf=<optimized out>, length=<optimized out>, parser_state=parser_state@entry=0x7f72ac04e570, is_com_multi=is_com_multi@entry=false, is_next_command=is_next_command@entry=false) at /mariadb/10.3/sql/sql_parse.cc:7841 #5 0x0000562bbdf107d5 in dispatch_command (command=command@entry=COM_QUERY, thd=thd@entry=0x7f7294017048, packet=0x7f72ac04e570 "\377\377\377\377", packet@entry=0x7f7294009f59 "ALTER TABLE t1 ADD c INT", This is the old table that was supposed to be discarded by TRUNCATE TABLE t1 .

            When doing a truncate on an Innodb under lock tables, InnoDB would rename the old table to #sql-... and recreate a new 't1' table. The table lock would still be on the #sql-table.

            When doing ALTER TABLE, Innodb would do the changes on the #sql table (which would disappear on close).
            When the SQL layer, as part of inline alter table, would close the original t1 table (#sql in InnoDB) and then reopen the t1 table, Innodb would notice that this does not match it's own (old) t1 table and generate an error.

            Fixed by adding code in truncate table that if we are under lock tables and truncating an InnoDB table, we would close and reopen and lock the table after truncate. This will remove the #sql table and ensure that lock tables is using the new empty table.

            monty Michael Widenius added a comment - When doing a truncate on an Innodb under lock tables, InnoDB would rename the old table to #sql-... and recreate a new 't1' table. The table lock would still be on the #sql-table. When doing ALTER TABLE, Innodb would do the changes on the #sql table (which would disappear on close). When the SQL layer, as part of inline alter table, would close the original t1 table (#sql in InnoDB) and then reopen the t1 table, Innodb would notice that this does not match it's own (old) t1 table and generate an error. Fixed by adding code in truncate table that if we are under lock tables and truncating an InnoDB table, we would close and reopen and lock the table after truncate. This will remove the #sql table and ensure that lock tables is using the new empty table.

            Pushed into 10.2

            monty Michael Widenius added a comment - Pushed into 10.2

            People

              monty Michael Widenius
              elenst Elena Stepanova
              Votes:
              0 Vote for this issue
              Watchers:
              3 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.