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

When the ctas from a big table is interrupted ,then you can't drop or recreate the table!

Details

    Description

      MariaDB [lfdb]> create table t5 as select * from t1;
      ^CCtrl-C – query killed. Continuing normally.
      ERROR 1317 (70100): Query execution was interrupted
      MariaDB [lfdb]> create table t5 as select * from t1;
      ERROR 1813 (HY000): Tablespace for table '`lfdb`.`t5`' exists. Please DISCARD the tablespace before IMPORT

      try to delete the tablespace
      [root@19c lfdb]# rm t5.ibd
      rm: remove regular file ‘t5.ibd’? y
      [root@19c lfdb]# ls
      db.opt t1.frm t1.ibd t2.frm t2.ibd t3.frm t3.ibd t4.frm t4.ibd

      try to recreate the table
      MariaDB [lfdb]> truncate table t5;
      ERROR 1146 (42S02): Table 'lfdb.t5' doesn't exist
      MariaDB [lfdb]> create table t5 as select * from t1;
      ERROR 1050 (42S01): Table '`lfdb`.`t5`' already exists
      MariaDB [lfdb]> drop table t5;
      ERROR 1051 (42S02): Unknown table 'lfdb.t5'

      why? and how do i fix this?
      Thanks.

      Attachments

        Issue Links

          is caused by

          Bug - A problem which impairs or prevents the functions of the product. MDEV-16515 InnoDB: Failing assertion: ++retries < 10000 in file dict0dict.cc line 2737

          • Major - Major loss of function.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            marko Marko Mäkelä added a comment -

            I can repeat with the following test, on both mariadb-10.3.14 and the latest 10.3 development version (ef44ec4afaa):

            --source include/have_innodb.inc
            		 
            --source include/have_sequence.inc
            		 
            --source include/count_sessions.inc
            		 
             
            		 
            let $ID= `SELECT @id := CONNECTION_ID()`;
            		 
             
            		 
            connect (con1, localhost, root,,);
            		 
            let $ignore= `SELECT @id := $ID`;
            		 
             
            		 
            connection default;
            		 
            send CREATE TABLE t1 ENGINE=InnoDB SELECT * FROM seq_1_to_100000000;
            		 
             
            		 
            connection con1;
            		 
            let $wait_condition=
            		 
              select count(*) = 1 from information_schema.processlist
            		 
              where state = 'Sending data'
            		 
              and info = 'CREATE TABLE t1 ENGINE=InnoDB SELECT * FROM seq_1_to_100000000';
            		 
            --source include/wait_condition.inc
            		 
            KILL QUERY @id;
            		 
            disconnect con1;
            		 
             
            		 
            connection default;
            		 
            --error ER_QUERY_INTERRUPTED
            		 
            reap;
            		 
             
            		 
            CREATE TABLE t1 (a SERIAL) ENGINE=InnoDB;
            		 
            DROP TABLE t1;
            		 
            --source include/wait_until_count_sessions.inc

            The CREATE TABLE…SELECT is internally implemented as two transactions, performing CREATE TABLE and INSERT…SELECT. If the operation is interrupted at the CREATE TABLE phase, then it will be rolled back properly. If it is interrupted during the INSERT…SELECT, then apparently the .frm file will be deleted, but the table will not be dropped inside InnoDB. We appear to be missing error handling (DROP TABLE) if the operation was interrupted. Such handling does exists for some errors.

            In MariaDB 5.5, there is no SEQUENCE engine, so I tried a different test:

            --source include/have_innodb.inc
            		 
            --source include/count_sessions.inc
            		 
             
            		 
            let $ID= `SELECT @id := CONNECTION_ID()`;
            		 
             
            		 
            CREATE TEMPORARY TABLE t (a INT);
            		 
            INSERT INTO t VALUES(1),(2),(3);
            		 
             
            		 
            connect (con1, localhost, root,,);
            		 
            let $ignore= `SELECT @id := $ID`;
            		 
             
            		 
            connection default;
            		 
            send CREATE TABLE t1 ENGINE=InnoDB SELECT a,SLEEP(1) FROM t;
            		 
             
            		 
            connection con1;
            		 
            let $wait_condition=
            		 
              select count(*) = 1 from information_schema.processlist
            		 
              where state != 'Creating table'
            		 
              and info = 'CREATE TABLE t1 ENGINE=InnoDB SELECT a,SLEEP(1) FROM t';
            		 
            --source include/wait_condition.inc
            		 
            KILL QUERY @id;
            		 
            disconnect con1;
            		 
             
            		 
            connection default;
            		 
            --error ER_QUERY_INTERRUPTED
            		 
            reap;
            		 
             
            		 
            CREATE TABLE t1 (a SERIAL) ENGINE=InnoDB;
            		 
            DROP TABLE t1;
            		 
            --source include/wait_until_count_sessions.inc

            It looks like when the execution is interrupted while executing the SLEEP function, the table will be dropped correctly (in both 10.3 and 5.5, which I tested). Here is the call stack for dropping the table in 5.5:

            5.5 f90040fd9ccb99cb4541d181a7052c37dc38decb

            		 
            #0  ha_innobase::delete_table (this=0x7fffc4015b18, 
                name=0x7ffff43abac0 "./test/t1")
            		 
                at /mariadb/5.5/storage/xtradb/handler/ha_innodb.cc:8746
            		 
            #1  0x0000555555af12de in handler::ha_delete_table (this=0x7fffc4015b18, 
                name=0x7ffff43abac0 "./test/t1") at /mariadb/5.5/sql/handler.cc:3724
            		 
            #2  0x0000555555aee3eb in ha_delete_table (thd=0x555557c07090, 
                table_type=0x555556fe7e30, path=0x7ffff43abac0 "./test/t1", 
                db=0x7fffc40077f8 "test", alias=0x7fffc40071e8 "t1", 
                generate_warning=false) at /mariadb/5.5/sql/handler.cc:2156
            		 
            #3  0x00005555559d0c67 in quick_rm_table (base=0x555556fe7e30, 
                db=0x7fffc40077f8 "test", table_name=0x7fffc40071e8 "t1", flags=0)
            		 
                at /mariadb/5.5/sql/sql_table.cc:2491
            		 
            #4  0x00005555558d9fb2 in drop_open_table (thd=0x555557c07090, 
                table=0x7fffc4017ad0, db_name=0x7fffc40077f8 "test", 
                table_name=0x7fffc40071e8 "t1") at /mariadb/5.5/sql/sql_base.cc:2410
            		 
            #5  0x0000555555924a14 in select_create::abort_result_set (
            		 
                this=0x7fffc40082d8) at /mariadb/5.5/sql/sql_insert.cc:4318
            		 
            #6  0x0000555555966ea2 in handle_select (thd=0x555557c07090, 
                lex=0x555557c0a308, result=0x7fffc40082d8, setup_tables_done_option=0)
            		 
                at /mariadb/5.5/sql/sql_select.cc:330
            		 
            #7  0x0000555555939a3e in mysql_execute_command (thd=0x555557c07090)
            		 
                at /mariadb/5.5/sql/sql_parse.cc:2610
            		 
            #8  0x00005555559428bd in mysql_parse (thd=0x555557c07090, 
                rawbuf=0x7fffc4007108 "CREATE TABLE t1 ENGINE=InnoDB SELECT a,SLEEP(1) FROM t", length=54, parser_state=0x7ffff43ac650)
            		 
                at /mariadb/5.5/sql/sql_parse.cc:5929

            Workaround: To drop the table from the InnoDB data dictionary afterwards, you could copy the .frm file of a similar InnoDB table to the ‘phantom’ name, and then issue DROP TABLE.

            marko Marko Mäkelä added a comment - I can repeat with the following test, on both mariadb-10.3.14 and the latest 10.3 development version (ef44ec4afaa): --source include/have_innodb.inc --source include/have_sequence.inc --source include/count_sessions.inc   let $ID= ` SELECT @id := CONNECTION_ID()`;   connect (con1, localhost, root,,); let $ ignore = ` SELECT @id := $ID`;   connection default ; send CREATE TABLE t1 ENGINE=InnoDB SELECT * FROM seq_1_to_100000000;   connection con1; let $wait_condition= select count (*) = 1 from information_schema.processlist where state = 'Sending data' and info = 'CREATE TABLE t1 ENGINE=InnoDB SELECT * FROM seq_1_to_100000000' ; --source include/wait_condition.inc KILL QUERY @id; disconnect con1;   connection default ; --error ER_QUERY_INTERRUPTED reap;   CREATE TABLE t1 (a SERIAL) ENGINE=InnoDB; DROP TABLE t1; --source include/wait_until_count_sessions.inc The CREATE TABLE…SELECT is internally implemented as two transactions, performing CREATE TABLE and INSERT…SELECT . If the operation is interrupted at the CREATE TABLE phase, then it will be rolled back properly. If it is interrupted during the INSERT…SELECT , then apparently the .frm file will be deleted, but the table will not be dropped inside InnoDB. We appear to be missing error handling ( DROP TABLE ) if the operation was interrupted. Such handling does exists for some errors. In MariaDB 5.5, there is no SEQUENCE engine, so I tried a different test: --source include/have_innodb.inc --source include/count_sessions.inc   let $ID= ` SELECT @id := CONNECTION_ID()`;   CREATE TEMPORARY TABLE t (a INT ); INSERT INTO t VALUES (1),(2),(3);   connect (con1, localhost, root,,); let $ ignore = ` SELECT @id := $ID`;   connection default ; send CREATE TABLE t1 ENGINE=InnoDB SELECT a,SLEEP(1) FROM t;   connection con1; let $wait_condition= select count (*) = 1 from information_schema.processlist where state != 'Creating table' and info = 'CREATE TABLE t1 ENGINE=InnoDB SELECT a,SLEEP(1) FROM t' ; --source include/wait_condition.inc KILL QUERY @id; disconnect con1;   connection default ; --error ER_QUERY_INTERRUPTED reap;   CREATE TABLE t1 (a SERIAL) ENGINE=InnoDB; DROP TABLE t1; --source include/wait_until_count_sessions.inc It looks like when the execution is interrupted while executing the SLEEP function, the table will be dropped correctly (in both 10.3 and 5.5, which I tested). Here is the call stack for dropping the table in 5.5: 5.5 f90040fd9ccb99cb4541d181a7052c37dc38decb #0 ha_innobase::delete_table (this=0x7fffc4015b18, name=0x7ffff43abac0 "./test/t1") at /mariadb/5.5/storage/xtradb/handler/ha_innodb.cc:8746 #1 0x0000555555af12de in handler::ha_delete_table (this=0x7fffc4015b18, name=0x7ffff43abac0 "./test/t1") at /mariadb/5.5/sql/handler.cc:3724 #2 0x0000555555aee3eb in ha_delete_table (thd=0x555557c07090, table_type=0x555556fe7e30, path=0x7ffff43abac0 "./test/t1", db=0x7fffc40077f8 "test", alias=0x7fffc40071e8 "t1", generate_warning=false) at /mariadb/5.5/sql/handler.cc:2156 #3 0x00005555559d0c67 in quick_rm_table (base=0x555556fe7e30, db=0x7fffc40077f8 "test", table_name=0x7fffc40071e8 "t1", flags=0) at /mariadb/5.5/sql/sql_table.cc:2491 #4 0x00005555558d9fb2 in drop_open_table (thd=0x555557c07090, table=0x7fffc4017ad0, db_name=0x7fffc40077f8 "test", table_name=0x7fffc40071e8 "t1") at /mariadb/5.5/sql/sql_base.cc:2410 #5 0x0000555555924a14 in select_create::abort_result_set ( this=0x7fffc40082d8) at /mariadb/5.5/sql/sql_insert.cc:4318 #6 0x0000555555966ea2 in handle_select (thd=0x555557c07090, lex=0x555557c0a308, result=0x7fffc40082d8, setup_tables_done_option=0) at /mariadb/5.5/sql/sql_select.cc:330 #7 0x0000555555939a3e in mysql_execute_command (thd=0x555557c07090) at /mariadb/5.5/sql/sql_parse.cc:2610 #8 0x00005555559428bd in mysql_parse (thd=0x555557c07090, rawbuf=0x7fffc4007108 "CREATE TABLE t1 ENGINE=InnoDB SELECT a,SLEEP(1) FROM t", length=54, parser_state=0x7ffff43ac650) at /mariadb/5.5/sql/sql_parse.cc:5929 Workaround: To drop the table from the InnoDB data dictionary afterwards, you could copy the .frm file of a similar InnoDB table to the ‘phantom’ name, and then issue DROP TABLE .
            marko Marko Mäkelä added a comment -

            This regression was introduced by MDEV-16515. With that revision in 10.1, the server even crashes:

            10.1 c4eb4bcef648eb2ebdc6edc06905f39f95ef7f6b

            		 
            mysqltest: At line 24: query 'reap' failed with wrong errno 2013: 'Lost connection to MySQL server during query', instead of 1317...
            		 
            …
            		 
            #2  0x00005579181062aa in handle_fatal_signal (sig=11) at /mariadb/10.1/sql/signal_handler.cc:296
            		 
            #3  <signal handler called>
            		 
            #4  0x000055791810eae8 in handler::print_error (this=0x7fa66800ab40, error=188, errflag=0) at /mariadb/10.1/sql/handler.cc:3416
            		 
            #5  0x000055791810bb97 in ha_delete_table (thd=0x55791a996fa8, table_type=0x55791a371928, path=0x7fa6b0288690 "./test/t1", db=0x7fa668006898 "test", alias=0x7fa668006270 "t1", generate_warning=false) at /mariadb/10.1/sql/handler.cc:2405
            		 
            #6  0x0000557917f846c2 in quick_rm_table (thd=0x55791a996fa8, base=0x55791a371928, db=0x7fa668006898 "test", table_name=0x7fa668006270 "t1", flags=0, table_path=0x0) at /mariadb/10.1/sql/sql_table.cc:2740
            		 
            #7  0x0000557917e643e2 in drop_open_table (thd=0x55791a996fa8, table=0x7fa668169568, db_name=0x7fa668006898 "test", table_name=0x7fa668006270 "t1") at /mariadb/10.1/sql/sql_base.cc:1928
            		 
            #8  0x0000557917eb4e90 in select_create::abort_result_set (this=0x7fa668008090) at /mariadb/10.1/sql/sql_insert.cc:4537
            		 
            #9  0x0000557917f07871 in handle_select (thd=0x55791a996fa8, lex=0x55791a99aa18, result=0x7fa668008090, setup_tables_done_option=0) at /mariadb/10.1/sql/sql_select.cc:394
            		 
            #10 0x0000557917ece959 in mysql_execute_command (thd=0x55791a996fa8) at /mariadb/10.1/sql/sql_parse.cc:3454
            		 
            #11 0x0000557917edaff2 in mysql_parse (thd=0x55791a996fa8, rawbuf=0x7fa668006180 "CREATE TABLE t1 ENGINE=InnoDB SELECT * FROM seq_1_to_100000000", length=62, parser_state=0x7fa6b0289590) at /mariadb/10.1/sql/sql_parse.cc:7449

            Strangely, with the latest 10.1 (4aa97ba5944f4afe751525e68b77100b1aa51524), the test does pass. With mariadb-10.1.35, the test fails just like described.

            The idea of MDEV-16515 is to allow DROP TABLE to be interrupted, in case it was stuck due to some concurrent activity. We already made some cases of internal DROP TABLE immune to kill in MDEV-18237, MDEV-16647, MDEV-17470. We must include CREATE…SELECT in the list of such internal DROP TABLE. What happens here is that the user interrupt (or KILL QUERY in my test) effectively disables the error handling, which would drop the incompletely created table.

            marko Marko Mäkelä added a comment - This regression was introduced by MDEV-16515 . With that revision in 10.1, the server even crashes: 10.1 c4eb4bcef648eb2ebdc6edc06905f39f95ef7f6b mysqltest: At line 24: query 'reap' failed with wrong errno 2013: 'Lost connection to MySQL server during query', instead of 1317... … #2 0x00005579181062aa in handle_fatal_signal (sig=11) at /mariadb/10.1/sql/signal_handler.cc:296 #3 <signal handler called> #4 0x000055791810eae8 in handler::print_error (this=0x7fa66800ab40, error=188, errflag=0) at /mariadb/10.1/sql/handler.cc:3416 #5 0x000055791810bb97 in ha_delete_table (thd=0x55791a996fa8, table_type=0x55791a371928, path=0x7fa6b0288690 "./test/t1", db=0x7fa668006898 "test", alias=0x7fa668006270 "t1", generate_warning=false) at /mariadb/10.1/sql/handler.cc:2405 #6 0x0000557917f846c2 in quick_rm_table (thd=0x55791a996fa8, base=0x55791a371928, db=0x7fa668006898 "test", table_name=0x7fa668006270 "t1", flags=0, table_path=0x0) at /mariadb/10.1/sql/sql_table.cc:2740 #7 0x0000557917e643e2 in drop_open_table (thd=0x55791a996fa8, table=0x7fa668169568, db_name=0x7fa668006898 "test", table_name=0x7fa668006270 "t1") at /mariadb/10.1/sql/sql_base.cc:1928 #8 0x0000557917eb4e90 in select_create::abort_result_set (this=0x7fa668008090) at /mariadb/10.1/sql/sql_insert.cc:4537 #9 0x0000557917f07871 in handle_select (thd=0x55791a996fa8, lex=0x55791a99aa18, result=0x7fa668008090, setup_tables_done_option=0) at /mariadb/10.1/sql/sql_select.cc:394 #10 0x0000557917ece959 in mysql_execute_command (thd=0x55791a996fa8) at /mariadb/10.1/sql/sql_parse.cc:3454 #11 0x0000557917edaff2 in mysql_parse (thd=0x55791a996fa8, rawbuf=0x7fa668006180 "CREATE TABLE t1 ENGINE=InnoDB SELECT * FROM seq_1_to_100000000", length=62, parser_state=0x7fa6b0289590) at /mariadb/10.1/sql/sql_parse.cc:7449 Strangely, with the latest 10.1 (4aa97ba5944f4afe751525e68b77100b1aa51524), the test does pass. With mariadb-10.1.35 , the test fails just like described. The idea of MDEV-16515 is to allow DROP TABLE to be interrupted, in case it was stuck due to some concurrent activity. We already made some cases of internal DROP TABLE immune to kill in MDEV-18237 , MDEV-16647 , MDEV-17470 . We must include CREATE…SELECT in the list of such internal DROP TABLE . What happens here is that the user interrupt (or KILL QUERY in my test) effectively disables the error handling, which would drop the incompletely created table.

            People

              marko Marko Mäkelä
              oilz oilz
              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.