diff options
Diffstat (limited to 'mysql-test/main/kill.test')
| -rw-r--r-- | mysql-test/main/kill.test | 644 |
1 files changed, 644 insertions, 0 deletions
diff --git a/mysql-test/main/kill.test b/mysql-test/main/kill.test new file mode 100644 index 00000000000..b6000ffced1 --- /dev/null +++ b/mysql-test/main/kill.test @@ -0,0 +1,644 @@ +# +# Test KILL and KILL QUERY statements. +# +# Killing a connection in an embedded server does not work like in a normal +# server, if it is waiting for a new statement. In an embedded server, the +# connection does not read() from a socket, but returns control to the +# application. 'mysqltest' does not handle the kill request. +# + +-- source include/not_embedded.inc +-- source include/have_debug_sync.inc +-- source include/not_threadpool.inc +set local sql_mode=""; +set global sql_mode=""; + +--disable_warnings +SET DEBUG_SYNC = 'RESET'; +DROP TABLE IF EXISTS t1, t2, t3; +DROP FUNCTION IF EXISTS MY_KILL; +--enable_warnings + +delimiter |; +# Helper function used to repeatedly kill a session. +CREATE FUNCTION MY_KILL(tid INT) RETURNS INT +BEGIN + DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN END; + KILL tid; + RETURN (SELECT COUNT(*) = 0 FROM INFORMATION_SCHEMA.PROCESSLIST WHERE ID = tid); +END| +delimiter ;| + +connect (con1, localhost, root,,); +connect (con2, localhost, root,,); + +# Save id of con1 +connection con1; +--disable_reconnect +let $ID= `SELECT @id := CONNECTION_ID()`; +connection con2; +let $ignore= `SELECT @id := $ID`; +connection con1; +# Signal when this connection is terminating. +SET DEBUG_SYNC= 'thread_end SIGNAL con1_end'; +# See if we can kill read(). +# Run into read() immediately after hitting 'before_do_command_net_read'. +SET DEBUG_SYNC= 'before_do_command_net_read SIGNAL con1_read'; + +# Kill con1 +connection con2; +SET DEBUG_SYNC='now WAIT_FOR con1_read'; +# At this point we have no way to figure out, when con1 is blocked in +# reading from the socket. Sending KILL to early would not terminate +# con1. So we repeat KILL until con1 terminates. +let $wait_condition= SELECT MY_KILL(@id); +--source include/wait_condition.inc +# If KILL missed the read(), sync point wait will time out. +SET DEBUG_SYNC= 'now WAIT_FOR con1_end'; +SET DEBUG_SYNC = 'RESET'; + +connection con1; +--error 1053,2006,2013 +SELECT 1; + +--enable_reconnect +# this should work, and we should have a new connection_id() +SELECT 1; +let $ignore= `SELECT @id := $ID`; +SELECT @id != CONNECTION_ID(); + +#make sure the server is still alive +connection con2; +SELECT 4; +connection default; + +--error ER_SUBQUERIES_NOT_SUPPORTED +KILL (SELECT COUNT(*) FROM mysql.user); + +connection con1; +let $ID= `SELECT @id := CONNECTION_ID()`; +connection con2; +let $ignore= `SELECT @id := $ID`; +connection con1; +disable_reconnect; +# Signal when this connection is terminating. +SET DEBUG_SYNC= 'thread_end SIGNAL con1_end'; +# See if we can kill the sync point itself. +# Wait in 'before_do_command_net_read' until killed. +# It doesn't wait for a signal 'kill' but for to be killed. +# The signal name doesn't matter here. +SET DEBUG_SYNC= 'before_do_command_net_read SIGNAL con1_read WAIT_FOR kill'; +connection con2; +SET DEBUG_SYNC= 'now WAIT_FOR con1_read'; +# Repeat KILL until con1 terminates. +let $wait_condition= SELECT MY_KILL(@id); +--source include/wait_condition.inc +SET DEBUG_SYNC= 'now WAIT_FOR con1_end'; +SET DEBUG_SYNC = 'RESET'; + +connection con1; +--error 1053,2006,2013 +SELECT 1; +enable_reconnect; +SELECT 1; +let $ignore= `SELECT @id := $ID`; +SELECT @id != CONNECTION_ID(); +connection con2; +SELECT 4; +connection default; + +# +# BUG#14851: killing long running subquery processed via a temporary table. +# + +CREATE TABLE t1 (id INT PRIMARY KEY AUTO_INCREMENT); +CREATE TABLE t2 (id INT UNSIGNED NOT NULL); + +INSERT INTO t1 VALUES +(0),(0),(0),(0),(0),(0),(0),(0), (0),(0),(0),(0),(0),(0),(0),(0), +(0),(0),(0),(0),(0),(0),(0),(0), (0),(0),(0),(0),(0),(0),(0),(0), +(0),(0),(0),(0),(0),(0),(0),(0), (0),(0),(0),(0),(0),(0),(0),(0), +(0),(0),(0),(0),(0),(0),(0),(0), (0),(0),(0),(0),(0),(0),(0),(0); +INSERT t1 SELECT 0 FROM t1 AS a1, t1 AS a2 LIMIT 4032; + +INSERT INTO t2 SELECT id FROM t1; + +connection con1; +let $ID= `SELECT @id := CONNECTION_ID()`; +connection con2; +let $ignore= `SELECT @id := $ID`; + +connection con1; +SET DEBUG_SYNC= 'thread_end SIGNAL con1_end'; +SET DEBUG_SYNC= 'before_acos_function SIGNAL in_sync'; +# This is a very long running query. If this test start failing, +# it may be necessary to change to an even longer query. +send SELECT id FROM t1 WHERE id IN + (SELECT DISTINCT a.id FROM t2 a, t2 b, t2 c, t2 d + GROUP BY ACOS(1/a.id), b.id, c.id, d.id + HAVING a.id BETWEEN 10 AND 20); + +connection con2; +SET DEBUG_SYNC= 'now WAIT_FOR in_sync'; +KILL @id; +SET DEBUG_SYNC= 'now WAIT_FOR con1_end'; + +connection con1; +--error 1317,1053,2006,2013 +reap; +SELECT 1; + +connection default; +SET DEBUG_SYNC = 'RESET'; +DROP TABLE t1, t2; + +# +# Test of blocking of sending ERROR after OK or EOF +# +connection con1; +let $ID= `SELECT @id := CONNECTION_ID()`; +connection con2; +let $ignore= `SELECT @id := $ID`; +connection con1; +SET DEBUG_SYNC= 'before_acos_function SIGNAL in_sync WAIT_FOR kill'; +send SELECT ACOS(0); +connection con2; +SET DEBUG_SYNC= 'now WAIT_FOR in_sync'; +KILL QUERY @id; +connection con1; +reap; +SELECT 1; +SELECT @id = CONNECTION_ID(); +connection default; +SET DEBUG_SYNC = 'RESET'; + +# +# Bug#27563: Stored functions and triggers wasn't throwing an error when killed. +# +CREATE TABLE t1 (f1 INT); +delimiter |; +CREATE FUNCTION bug27563() RETURNS INT(11) +DETERMINISTIC +BEGIN + DECLARE CONTINUE HANDLER FOR SQLSTATE '70100' SET @a:= 'killed'; + DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET @a:= 'exception'; + SET DEBUG_SYNC= 'now SIGNAL in_sync WAIT_FOR kill'; + RETURN 1; +END| +delimiter ;| +# Test stored functions +# Test INSERT +connection con1; +let $ID= `SELECT @id := CONNECTION_ID()`; +connection con2; +let $ignore= `SELECT @id := $ID`; +connection con1; +send INSERT INTO t1 VALUES (bug27563()); +connection con2; +SET DEBUG_SYNC= 'now WAIT_FOR in_sync'; +KILL QUERY @id; +connection con1; +--error 1317 +reap; +SELECT * FROM t1; +connection default; +SET DEBUG_SYNC = 'RESET'; + +# Test UPDATE +INSERT INTO t1 VALUES(0); +connection con1; +send UPDATE t1 SET f1= bug27563(); +connection con2; +SET DEBUG_SYNC= 'now WAIT_FOR in_sync'; +KILL QUERY @id; +connection con1; +--error 1317 +reap; +SELECT * FROM t1; +connection default; +SET DEBUG_SYNC = 'RESET'; + +# Test DELETE +INSERT INTO t1 VALUES(1); +connection con1; +send DELETE FROM t1 WHERE bug27563() IS NULL; +connection con2; +SET DEBUG_SYNC= 'now WAIT_FOR in_sync'; +KILL QUERY @id; +connection con1; +--error 1317 +reap; +SELECT * FROM t1; +connection default; +SET DEBUG_SYNC = 'RESET'; + +# Test SELECT +connection con1; +send SELECT * FROM t1 WHERE f1= bug27563(); +connection con2; +SET DEBUG_SYNC= 'now WAIT_FOR in_sync'; +KILL QUERY @id; +connection con1; +--error 1317 +reap; +SELECT * FROM t1; +connection default; +SET DEBUG_SYNC = 'RESET'; +DROP FUNCTION bug27563; + +# Test TRIGGERS +CREATE TABLE t2 (f2 INT); +delimiter |; +CREATE TRIGGER trg27563 BEFORE INSERT ON t1 FOR EACH ROW +BEGIN + DECLARE CONTINUE HANDLER FOR SQLSTATE '70100' SET @a:= 'killed'; + DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET @a:= 'exception'; + INSERT INTO t2 VALUES(0); + SET DEBUG_SYNC= 'now SIGNAL in_sync WAIT_FOR kill'; + INSERT INTO t2 VALUES(1); +END| +delimiter ;| +connection con1; +send INSERT INTO t1 VALUES(2),(3); +connection con2; +SET DEBUG_SYNC= 'now WAIT_FOR in_sync'; +KILL QUERY @id; +connection con1; +--error 1317 +reap; +SELECT * FROM t1; +SELECT * FROM t2; +connection default; +SET DEBUG_SYNC = 'RESET'; +DROP TABLE t1, t2; + +# +# Bug#28598: mysqld crash when killing a long-running explain query. +# +connection con1; +let $ID= `SELECT @id := CONNECTION_ID()`; +connection con2; +let $ignore= `SELECT @id := $ID`; +connection con1; +--disable_query_log +let $tab_count= 40; + +--disable_query_log +begin; +let $i= $tab_count; +while ($i) +{ + eval CREATE TABLE t$i (a$i INT, KEY(a$i)); + eval INSERT INTO t$i VALUES (1),(2),(3),(4),(5),(6),(7); + dec $i ; +} + +commit; +--enable_query_log + +SET SESSION optimizer_search_depth=0; + +let $i=$tab_count; +while ($i) +{ + let $a= a$i; + let $t= t$i; + dec $i; + if ($i) + { + let $comma=,; + let $from=$comma$t$from; + let $where=a$i=$a $and $where; + } + if (!$i) + { + let $from=FROM $t$from; + let $where=WHERE $where; + } + let $and=AND; +} + +--enable_query_log +SET DEBUG_SYNC= 'before_join_optimize SIGNAL in_sync'; +eval PREPARE stmt FROM 'EXPLAIN SELECT * $from $where'; +send EXECUTE stmt; + +connection con2; +SET DEBUG_SYNC= 'now WAIT_FOR in_sync'; +KILL QUERY @id; +connection con1; +--error 1317 +reap; +--disable_query_log +let $i= $tab_count; +while ($i) +{ + eval DROP TABLE t$i; + dec $i ; +} +--enable_query_log +connection default; +SET DEBUG_SYNC = 'RESET'; + +--echo # +--echo # Bug#19723: kill of active connection yields different error code +--echo # depending on platform. +--echo # + +--connection con1 +let $ID= `SELECT @id := CONNECTION_ID()`; +SET DEBUG_SYNC= 'thread_end SIGNAL con1_end'; +--disable_reconnect +--error ER_CONNECTION_KILLED +KILL @id; + +connection con2; +SET DEBUG_SYNC= 'now WAIT_FOR con1_end'; +connection con1; +--echo # ER_SERVER_SHUTDOWN, CR_SERVER_GONE_ERROR, CR_SERVER_LOST, +--echo # depending on the timing of close of the connection socket +--error 1053,2006,2013 +SELECT 1; +--enable_reconnect +SELECT 1; +let $ignore= `SELECT @id := $ID`; +SELECT @id != CONNECTION_ID(); +connection default; +SET DEBUG_SYNC = 'RESET'; + +--echo # +--echo # Additional test for WL#3726 "DDL locking for all metadata objects" +--echo # Check that DDL and DML statements waiting for metadata locks can +--echo # be killed. Note that we don't cover all situations here since it +--echo # can be tricky to write test case for some of them (e.g. REPAIR or +--echo # ALTER and other statements under LOCK TABLES). +--echo # +--disable_warnings +drop tables if exists t1, t2, t3; +--enable_warnings + +create table t1 (i int primary key); +connect (blocker, localhost, root, , ); +connect (dml, localhost, root, , ); +connect (ddl, localhost, root, , ); + +--echo # Test for RENAME TABLE +connection blocker; +lock table t1 read; +connection ddl; +let $ID= `select connection_id()`; +--send rename table t1 to t2 +connection default; +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Waiting for table metadata lock" and + info = "rename table t1 to t2"; +--source include/wait_condition.inc +--replace_result $ID ID +eval kill query $ID; +connection ddl; +--error ER_QUERY_INTERRUPTED +--reap + +--echo # Test for DROP TABLE +--send drop table t1 +connection default; +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Waiting for table metadata lock" and + info = "drop table t1"; +--source include/wait_condition.inc +--replace_result $ID ID +eval kill query $ID; +connection ddl; +--error ER_QUERY_INTERRUPTED +--reap + +--echo # Test for CREATE TRIGGER +--send create trigger t1_bi before insert on t1 for each row set @a:=1 +connection default; +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Waiting for table metadata lock" and + info = "create trigger t1_bi before insert on t1 for each row set @a:=1"; +--source include/wait_condition.inc +--replace_result $ID ID +eval kill query $ID; +connection ddl; +--error ER_QUERY_INTERRUPTED +--reap + +--echo # +--echo # Tests for various kinds of ALTER TABLE +--echo # +--echo # Full-blown ALTER which should copy table +--send alter table t1 add column j int +connection default; +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Waiting for table metadata lock" and + info = "alter table t1 add column j int"; +--source include/wait_condition.inc +--replace_result $ID ID +eval kill query $ID; +connection ddl; +--error ER_QUERY_INTERRUPTED +--reap + +--echo # Two kinds of simple ALTER +--send alter table t1 rename to t2 +connection default; +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Waiting for table metadata lock" and + info = "alter table t1 rename to t2"; +--source include/wait_condition.inc +--replace_result $ID ID +eval kill query $ID; +connection ddl; +--error ER_QUERY_INTERRUPTED +--reap +--send alter table t1 disable keys +connection default; +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Waiting for table metadata lock" and + info = "alter table t1 disable keys"; +--source include/wait_condition.inc +--replace_result $ID ID +eval kill query $ID; +connection ddl; +--error ER_QUERY_INTERRUPTED +--reap +--echo # Fast ALTER +--send alter table t1 alter column i set default 100 +connection default; +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Waiting for table metadata lock" and + info = "alter table t1 alter column i set default 100"; +--source include/wait_condition.inc +--replace_result $ID ID +eval kill query $ID; +connection ddl; +--error ER_QUERY_INTERRUPTED +--reap +--echo # Special case which is triggered only for MERGE tables. +connection blocker; +unlock tables; +create table t2 (i int primary key) engine=merge union=(t1); +lock tables t2 read; +connection ddl; +--send alter table t2 alter column i set default 100 +connection default; +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Waiting for table metadata lock" and + info = "alter table t2 alter column i set default 100"; +--source include/wait_condition.inc +--replace_result $ID ID +eval kill query $ID; +connection ddl; +--error ER_QUERY_INTERRUPTED +--reap + +--echo # Test for DML waiting for meta-data lock +connection blocker; +unlock tables; +lock tables t1 read; +connection ddl; +# Let us add pending exclusive metadata lock on t2 +--send truncate table t1 +connection dml; +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Waiting for table metadata lock" and + info = "truncate table t1"; +--source include/wait_condition.inc +let $ID2= `select connection_id()`; +--send insert into t1 values (1) +connection default; +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Waiting for table metadata lock" and + info = "insert into t1 values (1)"; +--source include/wait_condition.inc +--replace_result $ID2 ID2 +eval kill query $ID2; +connection dml; +--error ER_QUERY_INTERRUPTED +--reap +connection blocker; +unlock tables; +connection ddl; +--reap + +--echo # Test for DML waiting for tables to be flushed +connection blocker; +lock tables t1 read; +connection ddl; +--echo # Let us mark locked table t1 as old +--send flush tables +connection dml; +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Waiting for table flush" and + info = "flush tables"; +--source include/wait_condition.inc +--send select * from t1 +connection default; +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Waiting for table flush" and + info = "select * from t1"; +--source include/wait_condition.inc +--replace_result $ID2 ID2 +eval kill query $ID2; +connection dml; +--error ER_QUERY_INTERRUPTED +--reap +connection blocker; +unlock tables; +connection ddl; +--reap + +--echo # Cleanup. +connection default; +drop table t1; +drop table t2; + +########################################################################### + +--echo # +--echo # Test kill USER +--echo # +grant ALL on test.* to test@localhost; +grant ALL on test.* to test2@localhost; +connect (con3, localhost, test,,); +connect (con4, localhost, test2,,); +connection default; +--enable_info +kill hard query user test2@nohost; +kill soft query user test@localhost; +kill hard query user test@localhost; +kill soft connection user test2; +kill hard connection user test@localhost; +--disable_info +revoke all privileges on test.* from test@localhost; +revoke all privileges on test.* from test2@localhost; +drop user test@localhost; +drop user test2@localhost; + +connection con3; +--error 2013,2006 +select 1; +connection con4; +--error 2013,2006 +select 1; +connection default; + +--echo # +--echo # MDEV-4911 - add KILL query id, and add query id information to +--echo # processlist +--echo # +send SELECT SLEEP(1000); +connection con1; +let $wait_condition= SELECT @id:=QUERY_ID FROM INFORMATION_SCHEMA.PROCESSLIST WHERE INFO='SELECT SLEEP(1000)'; +source include/wait_condition.inc; +KILL QUERY ID @id; +connection default; +reap; + +--error ER_NO_SUCH_QUERY +KILL QUERY ID 0; + +--echo # +--echo # MDEV-5096 - Wrong error message on attempt to kill somebody else's +--echo # query ID +--echo # +CREATE USER u1@localhost; +send SELECT SLEEP(1000); + +connection con1; +let $wait_condition= SELECT @id:=QUERY_ID FROM INFORMATION_SCHEMA.PROCESSLIST WHERE INFO='SELECT SLEEP(1000)'; +source include/wait_condition.inc; +let $id= `SELECT @id`; + +connect(con5, localhost, u1,,); +--replace_result $id ID +--error ER_KILL_QUERY_DENIED_ERROR +eval KILL QUERY ID $id; + +connection con1; +KILL QUERY ID @id; + +connection default; +reap; +disconnect con5; +DROP USER u1@localhost; + + +SET DEBUG_SYNC = 'RESET'; +DROP FUNCTION MY_KILL; + +set global sql_mode=default; |