Create 'main' test directory and move 't' and 'r' there

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;