[MDEV-20102] When the ctas from a big table is interrupted ,then you can't drop or recreate the table! - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Critical
Resolution: Fixed
Affects Version/s: 10.1.35, 10.2.17, 10.3.8, 10.4.0
Fix Version/s: 10.2.26, 10.1.41, 10.3.17, 10.4.7
Component/s: Data Definition - Create Table, Storage Engine - InnoDB, Storage Engine - XtraDB
Labels:
- innodb
- regression
Environment:
rhel7.6

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

MDEV-16515 InnoDB: Failing assertion: ++retries < 10000 in file dict0dict.cc line 2737

Closed

Activity

Ascending order - Click to sort in descending order

Marko Mäkelä added a comment - 2019-07-22 07:39

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 Mäkelä added a comment - 2019-07-22 07:39 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 Mäkelä added a comment - 2019-07-22 08:16

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 Mäkelä added a comment - 2019-07-22 08:16 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.

MariaDB Server

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

Details

Description

Attachments

Issue Links

Activity

People

Dates

Git Integration