|
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.
|
|
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.
|