set local sql_mode=""; set global sql_mode=""; SET DEBUG_SYNC = 'RESET'; DROP TABLE IF EXISTS t1, t2, t3; DROP FUNCTION IF EXISTS MY_KILL; 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| connect con1, localhost, root,,; connect con2, localhost, root,,; connection con1; connection con2; connection con1; SET DEBUG_SYNC= 'thread_end SIGNAL con1_end'; SET DEBUG_SYNC= 'before_do_command_net_read SIGNAL con1_read'; connection con2; SET DEBUG_SYNC='now WAIT_FOR con1_read'; SET DEBUG_SYNC= 'now WAIT_FOR con1_end'; SET DEBUG_SYNC = 'RESET'; connection con1; SELECT 1; Got one of the listed errors SELECT 1; 1 1 SELECT @id != CONNECTION_ID(); @id != CONNECTION_ID() 1 connection con2; SELECT 4; 4 4 connection default; KILL (SELECT COUNT(*) FROM mysql.user); ERROR 42000: KILL does not support subqueries or stored functions connection con1; connection con2; connection con1; SET DEBUG_SYNC= 'thread_end SIGNAL con1_end'; SET DEBUG_SYNC= 'before_do_command_net_read SIGNAL con1_read WAIT_FOR kill'; connection con2; SET DEBUG_SYNC= 'now WAIT_FOR con1_read'; SET DEBUG_SYNC= 'now WAIT_FOR con1_end'; SET DEBUG_SYNC = 'RESET'; connection con1; SELECT 1; Got one of the listed errors SELECT 1; 1 1 SELECT @id != CONNECTION_ID(); @id != CONNECTION_ID() 1 connection con2; SELECT 4; 4 4 connection default; 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; connection con2; connection con1; SET DEBUG_SYNC= 'thread_end SIGNAL con1_end'; SET DEBUG_SYNC= 'before_acos_function SIGNAL in_sync'; 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; Got one of the listed errors SELECT 1; 1 1 connection default; SET DEBUG_SYNC = 'RESET'; DROP TABLE t1, t2; connection con1; connection con2; connection con1; SET DEBUG_SYNC= 'before_acos_function SIGNAL in_sync WAIT_FOR kill'; SELECT ACOS(0); connection con2; SET DEBUG_SYNC= 'now WAIT_FOR in_sync'; KILL QUERY @id; connection con1; ACOS(0) 1.5707963267948966 SELECT 1; 1 1 SELECT @id = CONNECTION_ID(); @id = CONNECTION_ID() 1 connection default; SET DEBUG_SYNC = 'RESET'; CREATE TABLE t1 (f1 INT); 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| connection con1; connection con2; connection con1; INSERT INTO t1 VALUES (bug27563()); connection con2; SET DEBUG_SYNC= 'now WAIT_FOR in_sync'; KILL QUERY @id; connection con1; ERROR 70100: Query execution was interrupted SELECT * FROM t1; f1 connection default; SET DEBUG_SYNC = 'RESET'; INSERT INTO t1 VALUES(0); connection con1; UPDATE t1 SET f1= bug27563(); connection con2; SET DEBUG_SYNC= 'now WAIT_FOR in_sync'; KILL QUERY @id; connection con1; ERROR 70100: Query execution was interrupted SELECT * FROM t1; f1 0 connection default; SET DEBUG_SYNC = 'RESET'; INSERT INTO t1 VALUES(1); connection con1; DELETE FROM t1 WHERE bug27563() IS NULL; connection con2; SET DEBUG_SYNC= 'now WAIT_FOR in_sync'; KILL QUERY @id; connection con1; ERROR 70100: Query execution was interrupted SELECT * FROM t1; f1 0 1 connection default; SET DEBUG_SYNC = 'RESET'; connection con1; SELECT * FROM t1 WHERE f1= bug27563(); connection con2; SET DEBUG_SYNC= 'now WAIT_FOR in_sync'; KILL QUERY @id; connection con1; ERROR 70100: Query execution was interrupted SELECT * FROM t1; f1 0 1 connection default; SET DEBUG_SYNC = 'RESET'; DROP FUNCTION bug27563; CREATE TABLE t2 (f2 INT); 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| connection con1; INSERT INTO t1 VALUES(2),(3); connection con2; SET DEBUG_SYNC= 'now WAIT_FOR in_sync'; KILL QUERY @id; connection con1; ERROR 70100: Query execution was interrupted SELECT * FROM t1; f1 0 1 SELECT * FROM t2; f2 0 connection default; SET DEBUG_SYNC = 'RESET'; DROP TABLE t1, t2; connection con1; connection con2; connection con1; SET SESSION optimizer_search_depth=0; SET DEBUG_SYNC= 'before_join_optimize SIGNAL in_sync'; PREPARE stmt FROM 'EXPLAIN SELECT * FROM t1,t2,t3,t4,t5,t6,t7,t8,t9,t10,t11,t12,t13,t14,t15,t16,t17,t18,t19,t20,t21,t22,t23,t24,t25,t26,t27,t28,t29,t30,t31,t32,t33,t34,t35,t36,t37,t38,t39,t40 WHERE a1=a2 AND a2=a3 AND a3=a4 AND a4=a5 AND a5=a6 AND a6=a7 AND a7=a8 AND a8=a9 AND a9=a10 AND a10=a11 AND a11=a12 AND a12=a13 AND a13=a14 AND a14=a15 AND a15=a16 AND a16=a17 AND a17=a18 AND a18=a19 AND a19=a20 AND a20=a21 AND a21=a22 AND a22=a23 AND a23=a24 AND a24=a25 AND a25=a26 AND a26=a27 AND a27=a28 AND a28=a29 AND a29=a30 AND a30=a31 AND a31=a32 AND a32=a33 AND a33=a34 AND a34=a35 AND a35=a36 AND a36=a37 AND a37=a38 AND a38=a39 AND a39=a40 '; EXECUTE stmt; connection con2; SET DEBUG_SYNC= 'now WAIT_FOR in_sync'; KILL QUERY @id; connection con1; ERROR 70100: Query execution was interrupted connection default; SET DEBUG_SYNC = 'RESET'; # # Bug#19723: kill of active connection yields different error code # depending on platform. # connection con1; SET DEBUG_SYNC= 'thread_end SIGNAL con1_end'; KILL @id; ERROR 70100: Connection was killed connection con2; SET DEBUG_SYNC= 'now WAIT_FOR con1_end'; connection con1; # ER_SERVER_SHUTDOWN, CR_SERVER_GONE_ERROR, CR_SERVER_LOST, # depending on the timing of close of the connection socket SELECT 1; Got one of the listed errors SELECT 1; 1 1 SELECT @id != CONNECTION_ID(); @id != CONNECTION_ID() 1 connection default; SET DEBUG_SYNC = 'RESET'; # # Additional test for WL#3726 "DDL locking for all metadata objects" # Check that DDL and DML statements waiting for metadata locks can # be killed. Note that we don't cover all situations here since it # can be tricky to write test case for some of them (e.g. REPAIR or # ALTER and other statements under LOCK TABLES). # drop tables if exists t1, t2, t3; create table t1 (i int primary key); connect blocker, localhost, root, , ; connect dml, localhost, root, , ; connect ddl, localhost, root, , ; # Test for RENAME TABLE connection blocker; lock table t1 read; connection ddl; rename table t1 to t2; connection default; kill query ID; connection ddl; ERROR 70100: Query execution was interrupted # Test for DROP TABLE drop table t1; connection default; kill query ID; connection ddl; ERROR 70100: Query execution was interrupted # Test for CREATE TRIGGER create trigger t1_bi before insert on t1 for each row set @a:=1; connection default; kill query ID; connection ddl; ERROR 70100: Query execution was interrupted # # Tests for various kinds of ALTER TABLE # # Full-blown ALTER which should copy table alter table t1 add column j int; connection default; kill query ID; connection ddl; ERROR 70100: Query execution was interrupted # Two kinds of simple ALTER alter table t1 rename to t2; connection default; kill query ID; connection ddl; ERROR 70100: Query execution was interrupted alter table t1 disable keys; connection default; kill query ID; connection ddl; ERROR 70100: Query execution was interrupted # Fast ALTER alter table t1 alter column i set default 100; connection default; kill query ID; connection ddl; ERROR 70100: Query execution was interrupted # 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; alter table t2 alter column i set default 100; connection default; kill query ID; connection ddl; ERROR 70100: Query execution was interrupted # Test for DML waiting for meta-data lock connection blocker; unlock tables; lock tables t1 read; connection ddl; truncate table t1; connection dml; insert into t1 values (1); connection default; kill query ID2; connection dml; ERROR 70100: Query execution was interrupted connection blocker; unlock tables; connection ddl; # Test for DML waiting for tables to be flushed connection blocker; lock tables t1 read; connection ddl; # Let us mark locked table t1 as old flush tables t1; connection dml; select * from t1; connection default; kill query ID2; connection dml; ERROR 70100: Query execution was interrupted connection blocker; unlock tables; connection ddl; # Cleanup. connection default; drop table t1; drop table t2; # # Test kill USER # grant ALL on test.* to test@localhost; grant ALL on test.* to test2@localhost; connect con3, localhost, test,,; connect con4, localhost, test2,,; connection default; kill hard query user test2@nohost; affected rows: 0 kill soft query user test@localhost; affected rows: 1 kill hard query user test@localhost; affected rows: 1 kill soft connection user test2; affected rows: 1 kill hard connection user test@localhost; affected rows: 1 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; select 1; Got one of the listed errors connection con4; select 1; Got one of the listed errors connection default; # # MDEV-4911 - add KILL query id, and add query id information to # processlist # SELECT SLEEP(1000); connection con1; KILL QUERY ID @id; connection default; ERROR 70100: Query execution was interrupted KILL QUERY ID 0; ERROR HY000: Unknown query id: 0 # # MDEV-5096 - Wrong error message on attempt to kill somebody else's # query ID # CREATE USER u1@localhost; SELECT SLEEP(1000); connection con1; connect con5, localhost, u1,,; KILL QUERY ID ID; ERROR HY000: You are not owner of query ID connection con1; KILL QUERY ID @id; connection default; ERROR 70100: Query execution was interrupted disconnect con5; DROP USER u1@localhost; SET DEBUG_SYNC = 'RESET'; DROP FUNCTION MY_KILL; set global sql_mode=default; # # MDEV-17998 # Deadlock and eventual Assertion `!table->pos_in_locked_tables' failed # in tc_release_table on KILL_TIMEOUT # SET max_statement_time= 2; CREATE TABLE t1 (a INT); CREATE VIEW v1 AS SELECT * FROM t1; CREATE TABLE t2 (b INT, c INT); LOCK TABLES v1 READ, t2 WRITE, t1 WRITE; ALTER TABLE t1 CHANGE f1 f2 DOUBLE; Got one of the listed errors ALTER TABLE t2 DROP c; UNLOCK TABLES; DROP VIEW v1; DROP TABLE t1, t2; # # KILL QUERY ID USER # kill query id user 'foo'; ERROR 42S22: Unknown column 'user' in 'field list'