path: root/mysql-test/t/kill.test

# This test doesn't work with the embedded version as this code
# assumes that one query is running while we are doing queries on
# a second connection.
# This would work if mysqltest run would be threaded and handle each
# connection in a separate thread.
#
-- source include/not_embedded.inc

# Disable concurrent inserts to avoid test failures when reading the
# connection id which was inserted into a table by another thread.
set @old_concurrent_insert= @@global.concurrent_insert;
set @@global.concurrent_insert= 0;

connect (con1, localhost, root,,);
connect (con2, localhost, root,,);

#remember id of con1
connection con1;
--disable_warnings
drop table if exists t1, t2, t3;
--enable_warnings

--disable_reconnect
create table t1 (kill_id int);
insert into t1 values(connection_id());

#kill con1
connection con2;
select ((@id := kill_id) - kill_id) from t1; 
kill @id;

connection con1;
--sleep 2

--disable_query_log
--disable_result_log
# One of the following statements should fail
--error 0,2006,2013
select 1;
--error 0,2006,2013
select 1;
--enable_query_log
--enable_result_log

--enable_reconnect
# this should work, and we should have a new connection_id()
select ((@id := kill_id) - kill_id) from t1;
select @id != connection_id();

#make sure the server is still alive
connection con2;
select 4;
drop table t1;
connection default;

--error ER_NOT_SUPPORTED_YET
kill (select count(*) from mysql.user);

#
# BUG#14851: killing long running subquery processed via a temporary table.
#
create table t1 (id int primary key);
create table t2 (id int unsigned not null);

connect (conn1, localhost, root,,);
connection conn1;

-- disable_result_log
-- disable_query_log
begin;
let $1 = 4096;
while ($1)
{
  eval insert into t1 values ($1);
  dec $1;
}
commit;
-- enable_query_log
-- enable_result_log

insert into t2 select id from t1;

create table t3 (kill_id int);
insert into t3 values(connection_id());

connect (conn2, localhost, root,,);
connection conn2;

connection conn1;
-- disable_result_log
# 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 a.id, b.id, c.id, d.id having a.id between 10 and 20);
-- enable_result_log

connection conn2;
select ((@id := kill_id) - kill_id) from t3;
-- sleep 1
kill @id;

connection conn1;
-- error 1317,2013
reap;

connection default;

drop table t1, t2, t3;

# End of 4.1 tests

#
# test of blocking of sending ERROR after OK or EOF
#
connection con1;
select get_lock("a", 10);
connection con2;
let $ID= `select connection_id()`;
send select get_lock("a", 10);
real_sleep 2;
connection con1;
disable_query_log;
eval kill query $ID;
enable_query_log;
connection con2;
reap;
select 1;
connection con1;
select RELEASE_LOCK("a");

#
# 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 @a= get_lock("lock27563", 10);
  return 1;
end|
delimiter ;|
# Test stored functions
# Test INSERT
connection con1;
select get_lock("lock27563",10);
connection con2;
let $ID= `select connection_id()`;
send insert into t1 values (bug27563());
real_sleep 2;
connection con1;
disable_query_log;
eval kill query $ID;
enable_query_log;
connection con2;
--error 1317
reap;
select @a;
connection con1;
select * from t1;

# Test UPDATE
insert into t1 values(0);
connection con2;
send update t1 set f1= bug27563();
real_sleep 2;
connection con1;
disable_query_log;
eval kill query $ID;
enable_query_log;
connection con2;
--error 1317
reap;
select @a;
connection con1;
select * from t1;

# Test DELETE
insert into t1 values(1);
connection con2;
send delete from t1 where bug27563() is null;
real_sleep 2;
connection con1;
disable_query_log;
eval kill query $ID;
enable_query_log;
connection con2;
--error 1317
reap;
select @a;
connection con1;
select * from t1;

# Test SELECT
connection con2;
send select * from t1 where f1= bug27563();
real_sleep 2;
connection con1;
disable_query_log;
eval kill query $ID;
enable_query_log;
connection con2;
--error 1317
reap;
select @a;

# Test PROCEDURE
connection con2;
delimiter |;
create procedure proc27563()
begin
  declare continue handler for sqlstate '70100' set @a:= 'killed';
  declare continue handler for sqlexception set @a:= 'exception';
  select get_lock("lock27563",10);
  select "shouldn't be selected";
end|
delimiter ;|
send call proc27563();
real_sleep 2;
connection con1;
disable_query_log;
eval kill query $ID;
enable_query_log;
connection con2;
--error 1317
reap;
select @a;

# Test TRIGGERS
connection con2;
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';
  set @a:= get_lock("lock27563",10);
  insert into t2 values(1);
end|
delimiter ;|
send insert into t1 values(2),(3);
real_sleep 2;
connection con1;
disable_query_log;
eval kill query $ID;
enable_query_log;
connection con2;
--error 1317
reap;
select @a;
connection con1;
select * from t1;
select * from t2;

# Cleanup
select release_lock("lock27563");
drop table t1, t2;
drop function bug27563;
drop procedure proc27563;

#
# Bug#28598: mysqld crash when killing a long-running explain query.
#
--disable_query_log
connection con1;
let $ID= `select connection_id()`;
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
eval PREPARE stmt FROM 'EXPLAIN SELECT * $from $where';
send EXECUTE stmt;
--disable_query_log

connection con2;
real_sleep 2;
eval kill query $ID;
let $i= $tab_count;
while ($i)
{
  eval DROP TABLE t$i;
  dec $i ;
}
--enable_query_log

###########################################################################

--echo #
--echo # Bug#19723: kill of active connection yields different error code
--echo # depending on platform.
--echo #

--echo
--echo # Connection: con2.
--connection con2

KILL CONNECTION_ID();

--echo # CR_SERVER_LOST, CR_SERVER_GONE_ERROR, depending on the timing 
--echo # of close of the connection socket
--error 2013, 2006 
SELECT 1;
--connection default

--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
--echo # Switching to connection 'blocker'
connection blocker;
lock table t1 read;
--echo # Switching to connection 'ddl'
connection ddl;
let $ID= `select connection_id()`;
--send rename table t1 to t2
--echo # Switching to connection 'default'
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;
--echo # Switching to connection 'ddl'
connection ddl;
--error ER_QUERY_INTERRUPTED
--reap

--echo # Test for DROP TABLE
--send drop table t1
--echo # Switching to connection 'default'
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;
--echo # Switching to connection 'ddl'
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
--echo # Switching to connection 'default'
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;
--echo # Switching to connection 'ddl'
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
--echo # Switching to connection 'default'
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;
--echo # Switching to connection 'ddl'
connection ddl;
--error ER_QUERY_INTERRUPTED
--reap

--echo # Two kinds of simple ALTER
--send alter table t1 rename to t2
--echo # Switching to connection 'default'
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;
--echo # Switching to connection 'ddl'
connection ddl;
--error ER_QUERY_INTERRUPTED
--reap
--send alter table t1 disable keys
--echo # Switching to connection 'default'
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;
--echo # Switching to connection 'ddl'
connection ddl;
--error ER_QUERY_INTERRUPTED
--reap
--echo # Fast ALTER
--send alter table t1 alter column i set default 100
--echo # Switching to connection 'default'
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;
--echo # Switching to connection 'ddl'
connection ddl;
--error ER_QUERY_INTERRUPTED
--reap
--echo # Special case which is triggered only for MERGE tables.
--echo # Switching to connection 'blocker'
connection blocker;
unlock tables;
create table t2 (i int primary key) engine=merge union=(t1);
lock tables t2 read;
--echo # Switching to connection 'ddl'
connection ddl;
--send alter table t2 alter column i set default 100
--echo # Switching to connection 'default'
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;
--echo # Switching to connection 'ddl'
connection ddl;
--error ER_QUERY_INTERRUPTED
--reap

--echo # Test for DML waiting for meta-data lock
--echo # Switching to connection 'blocker'
connection blocker;
unlock tables;
drop table t2;
create table t2 (k int);
lock tables t1 read;
--echo # Switching to connection 'ddl'
connection ddl;
# Let us add pending exclusive metadata lock on t2
--send rename tables t1 to t3, t2 to t1
--echo # Switching to connection 'dml'
connection dml;
let $wait_condition=
  select count(*) = 1 from information_schema.processlist
  where state = "Waiting for table metadata lock" and
        info = "rename tables t1 to t3, t2 to t1";
--source include/wait_condition.inc
let $ID2= `select connection_id()`;
--send insert into t2 values (1)
--echo # Switching to connection 'default'
connection default;
let $wait_condition=
  select count(*) = 1 from information_schema.processlist
  where state = "Waiting for table metadata lock" and
        info = "insert into t2 values (1)";
--source include/wait_condition.inc
--replace_result $ID2 ID2
eval kill query $ID2;
--echo # Switching to connection 'dml'
connection dml;
--error ER_QUERY_INTERRUPTED
--reap
--echo # Switching to connection 'blocker'
connection blocker;
unlock tables;
--echo # Switching to connection 'ddl'
connection ddl;
--reap

--echo # Test for DML waiting for tables to be flushed
--echo # Switching to connection 'blocker'
connection blocker;
lock tables t1 read;
--echo # Switching to connection 'ddl'
connection ddl;
--echo # Let us mark locked table t1 as old
--send flush tables
--echo # Switching to connection 'dml'
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
--echo # Switching to connection 'default'
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;
--echo # Switching to connection 'dml'
connection dml;
--error ER_QUERY_INTERRUPTED
--reap
--echo # Switching to connection 'blocker'
connection blocker;
unlock tables;
--echo # Switching to connection 'ddl'
connection ddl;
--reap

--echo # Cleanup.
--echo # Switching to connection 'default'
connection default;
drop table t3;
drop table t1;

###########################################################################

# Restore global concurrent_insert value. Keep in the end of the test file.
set @@global.concurrent_insert= @old_concurrent_insert;