2
# Test KILL and KILL QUERY statements.
4
# Killing a connection in an embedded server does not work like in a normal
5
# server, if it is waiting for a new statement. In an embedded server, the
6
# connection does not read() from a socket, but returns control to the
7
# application. 'mysqltest' does not handle the kill request.
10
-- source include/not_embedded.inc
11
-- source include/have_debug_sync.inc
12
-- source include/not_threadpool.inc
15
SET DEBUG_SYNC = 'RESET';
16
DROP TABLE IF EXISTS t1, t2, t3;
17
DROP FUNCTION IF EXISTS MY_KILL;
21
# Helper function used to repeatedly kill a session.
22
CREATE FUNCTION MY_KILL(tid INT) RETURNS INT
24
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN END;
26
RETURN (SELECT COUNT(*) = 0 FROM INFORMATION_SCHEMA.PROCESSLIST WHERE ID = tid);
30
connect (con1, localhost, root,,);
31
connect (con2, localhost, root,,);
36
let $ID= `SELECT @id := CONNECTION_ID()`;
38
let $ignore= `SELECT @id := $ID`;
40
# Signal when this connection is terminating.
41
SET DEBUG_SYNC= 'thread_end SIGNAL con1_end';
42
# See if we can kill read().
43
# Run into read() immediately after hitting 'before_do_command_net_read'.
44
SET DEBUG_SYNC= 'before_do_command_net_read SIGNAL con1_read';
48
SET DEBUG_SYNC='now WAIT_FOR con1_read';
49
# At this point we have no way to figure out, when con1 is blocked in
50
# reading from the socket. Sending KILL to early would not terminate
51
# con1. So we repeat KILL until con1 terminates.
52
let $wait_condition= SELECT MY_KILL(@id);
53
--source include/wait_condition.inc
54
# If KILL missed the read(), sync point wait will time out.
55
SET DEBUG_SYNC= 'now WAIT_FOR con1_end';
56
SET DEBUG_SYNC = 'RESET';
59
--error 1053,2006,2013
63
# this should work, and we should have a new connection_id()
65
let $ignore= `SELECT @id := $ID`;
66
SELECT @id != CONNECTION_ID();
68
#make sure the server is still alive
73
--error ER_NOT_SUPPORTED_YET
74
KILL (SELECT COUNT(*) FROM mysql.user);
77
let $ID= `SELECT @id := CONNECTION_ID()`;
79
let $ignore= `SELECT @id := $ID`;
82
# Signal when this connection is terminating.
83
SET DEBUG_SYNC= 'thread_end SIGNAL con1_end';
84
# See if we can kill the sync point itself.
85
# Wait in 'before_do_command_net_read' until killed.
86
# It doesn't wait for a signal 'kill' but for to be killed.
87
# The signal name doesn't matter here.
88
SET DEBUG_SYNC= 'before_do_command_net_read SIGNAL con1_read WAIT_FOR kill';
90
SET DEBUG_SYNC= 'now WAIT_FOR con1_read';
91
# Repeat KILL until con1 terminates.
92
let $wait_condition= SELECT MY_KILL(@id);
93
--source include/wait_condition.inc
94
SET DEBUG_SYNC= 'now WAIT_FOR con1_end';
95
SET DEBUG_SYNC = 'RESET';
98
--error 1053,2006,2013
102
let $ignore= `SELECT @id := $ID`;
103
SELECT @id != CONNECTION_ID();
109
# BUG#14851: killing long running subquery processed via a temporary table.
112
CREATE TABLE t1 (id INT PRIMARY KEY AUTO_INCREMENT);
113
CREATE TABLE t2 (id INT UNSIGNED NOT NULL);
115
INSERT INTO t1 VALUES
116
(0),(0),(0),(0),(0),(0),(0),(0), (0),(0),(0),(0),(0),(0),(0),(0),
117
(0),(0),(0),(0),(0),(0),(0),(0), (0),(0),(0),(0),(0),(0),(0),(0),
118
(0),(0),(0),(0),(0),(0),(0),(0), (0),(0),(0),(0),(0),(0),(0),(0),
119
(0),(0),(0),(0),(0),(0),(0),(0), (0),(0),(0),(0),(0),(0),(0),(0);
120
INSERT t1 SELECT 0 FROM t1 AS a1, t1 AS a2 LIMIT 4032;
122
INSERT INTO t2 SELECT id FROM t1;
125
let $ID= `SELECT @id := CONNECTION_ID()`;
127
let $ignore= `SELECT @id := $ID`;
130
SET DEBUG_SYNC= 'thread_end SIGNAL con1_end';
131
SET DEBUG_SYNC= 'before_acos_function SIGNAL in_sync';
132
# This is a very long running query. If this test start failing,
133
# it may be necessary to change to an even longer query.
134
send SELECT id FROM t1 WHERE id IN
135
(SELECT DISTINCT a.id FROM t2 a, t2 b, t2 c, t2 d
136
GROUP BY ACOS(1/a.id), b.id, c.id, d.id
137
HAVING a.id BETWEEN 10 AND 20);
140
SET DEBUG_SYNC= 'now WAIT_FOR in_sync';
142
SET DEBUG_SYNC= 'now WAIT_FOR con1_end';
145
--error 1317,1053,2006,2013
150
SET DEBUG_SYNC = 'RESET';
154
# Test of blocking of sending ERROR after OK or EOF
157
let $ID= `SELECT @id := CONNECTION_ID()`;
159
let $ignore= `SELECT @id := $ID`;
161
SET DEBUG_SYNC= 'before_acos_function SIGNAL in_sync WAIT_FOR kill';
164
SET DEBUG_SYNC= 'now WAIT_FOR in_sync';
169
SELECT @id = CONNECTION_ID();
171
SET DEBUG_SYNC = 'RESET';
174
# Bug#27563: Stored functions and triggers wasn't throwing an error when killed.
176
CREATE TABLE t1 (f1 INT);
178
CREATE FUNCTION bug27563() RETURNS INT(11)
181
DECLARE CONTINUE HANDLER FOR SQLSTATE '70100' SET @a:= 'killed';
182
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET @a:= 'exception';
183
SET DEBUG_SYNC= 'now SIGNAL in_sync WAIT_FOR kill';
187
# Test stored functions
190
let $ID= `SELECT @id := CONNECTION_ID()`;
192
let $ignore= `SELECT @id := $ID`;
194
send INSERT INTO t1 VALUES (bug27563());
196
SET DEBUG_SYNC= 'now WAIT_FOR in_sync';
203
SET DEBUG_SYNC = 'RESET';
206
INSERT INTO t1 VALUES(0);
208
send UPDATE t1 SET f1= bug27563();
210
SET DEBUG_SYNC= 'now WAIT_FOR in_sync';
217
SET DEBUG_SYNC = 'RESET';
220
INSERT INTO t1 VALUES(1);
222
send DELETE FROM t1 WHERE bug27563() IS NULL;
224
SET DEBUG_SYNC= 'now WAIT_FOR in_sync';
231
SET DEBUG_SYNC = 'RESET';
235
send SELECT * FROM t1 WHERE f1= bug27563();
237
SET DEBUG_SYNC= 'now WAIT_FOR in_sync';
244
SET DEBUG_SYNC = 'RESET';
245
DROP FUNCTION bug27563;
248
CREATE TABLE t2 (f2 INT);
250
CREATE TRIGGER trg27563 BEFORE INSERT ON t1 FOR EACH ROW
252
DECLARE CONTINUE HANDLER FOR SQLSTATE '70100' SET @a:= 'killed';
253
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET @a:= 'exception';
254
INSERT INTO t2 VALUES(0);
255
SET DEBUG_SYNC= 'now SIGNAL in_sync WAIT_FOR kill';
256
INSERT INTO t2 VALUES(1);
260
send INSERT INTO t1 VALUES(2),(3);
262
SET DEBUG_SYNC= 'now WAIT_FOR in_sync';
270
SET DEBUG_SYNC = 'RESET';
274
# Bug#28598: mysqld crash when killing a long-running explain query.
277
let $ID= `SELECT @id := CONNECTION_ID()`;
279
let $ignore= `SELECT @id := $ID`;
287
eval CREATE TABLE t$i (a$i INT, KEY(a$i));
288
eval INSERT INTO t$i VALUES (1),(2),(3),(4),(5),(6),(7);
291
SET SESSION optimizer_search_depth=0;
302
let $from=$comma$t$from;
303
let $where=a$i=$a $and $where;
307
let $from=FROM $t$from;
308
let $where=WHERE $where;
314
SET DEBUG_SYNC= 'before_join_optimize SIGNAL in_sync';
315
eval PREPARE stmt FROM 'EXPLAIN SELECT * $from $where';
319
SET DEBUG_SYNC= 'now WAIT_FOR in_sync';
333
SET DEBUG_SYNC = 'RESET';
336
--echo # Bug#19723: kill of active connection yields different error code
337
--echo # depending on platform.
341
--echo # Connection: con1.
343
let $ID= `SELECT @id := CONNECTION_ID()`;
344
SET DEBUG_SYNC= 'thread_end SIGNAL con1_end';
346
--error ER_QUERY_INTERRUPTED
350
SET DEBUG_SYNC= 'now WAIT_FOR con1_end';
352
--echo # ER_SERVER_SHUTDOWN, CR_SERVER_GONE_ERROR, CR_SERVER_LOST,
353
--echo # depending on the timing of close of the connection socket
354
--error 1053,2006,2013
358
let $ignore= `SELECT @id := $ID`;
359
SELECT @id != CONNECTION_ID();
361
SET DEBUG_SYNC = 'RESET';
364
--echo # Additional test for WL#3726 "DDL locking for all metadata objects"
365
--echo # Check that DDL and DML statements waiting for metadata locks can
366
--echo # be killed. Note that we don't cover all situations here since it
367
--echo # can be tricky to write test case for some of them (e.g. REPAIR or
368
--echo # ALTER and other statements under LOCK TABLES).
371
drop tables if exists t1, t2, t3;
374
create table t1 (i int primary key);
375
connect (blocker, localhost, root, , );
376
connect (dml, localhost, root, , );
377
connect (ddl, localhost, root, , );
379
--echo # Test for RENAME TABLE
380
--echo # Switching to connection 'blocker'
383
--echo # Switching to connection 'ddl'
385
let $ID= `select connection_id()`;
386
--send rename table t1 to t2
387
--echo # Switching to connection 'default'
390
select count(*) = 1 from information_schema.processlist
391
where state = "Waiting for table metadata lock" and
392
info = "rename table t1 to t2";
393
--source include/wait_condition.inc
394
--replace_result $ID ID
396
--echo # Switching to connection 'ddl'
398
--error ER_QUERY_INTERRUPTED
401
--echo # Test for DROP TABLE
403
--echo # Switching to connection 'default'
406
select count(*) = 1 from information_schema.processlist
407
where state = "Waiting for table metadata lock" and
408
info = "drop table t1";
409
--source include/wait_condition.inc
410
--replace_result $ID ID
412
--echo # Switching to connection 'ddl'
414
--error ER_QUERY_INTERRUPTED
417
--echo # Test for CREATE TRIGGER
418
--send create trigger t1_bi before insert on t1 for each row set @a:=1
419
--echo # Switching to connection 'default'
422
select count(*) = 1 from information_schema.processlist
423
where state = "Waiting for table metadata lock" and
424
info = "create trigger t1_bi before insert on t1 for each row set @a:=1";
425
--source include/wait_condition.inc
426
--replace_result $ID ID
428
--echo # Switching to connection 'ddl'
430
--error ER_QUERY_INTERRUPTED
434
--echo # Tests for various kinds of ALTER TABLE
436
--echo # Full-blown ALTER which should copy table
437
--send alter table t1 add column j int
438
--echo # Switching to connection 'default'
441
select count(*) = 1 from information_schema.processlist
442
where state = "Waiting for table metadata lock" and
443
info = "alter table t1 add column j int";
444
--source include/wait_condition.inc
445
--replace_result $ID ID
447
--echo # Switching to connection 'ddl'
449
--error ER_QUERY_INTERRUPTED
452
--echo # Two kinds of simple ALTER
453
--send alter table t1 rename to t2
454
--echo # Switching to connection 'default'
457
select count(*) = 1 from information_schema.processlist
458
where state = "Waiting for table metadata lock" and
459
info = "alter table t1 rename to t2";
460
--source include/wait_condition.inc
461
--replace_result $ID ID
463
--echo # Switching to connection 'ddl'
465
--error ER_QUERY_INTERRUPTED
467
--send alter table t1 disable keys
468
--echo # Switching to connection 'default'
471
select count(*) = 1 from information_schema.processlist
472
where state = "Waiting for table metadata lock" and
473
info = "alter table t1 disable keys";
474
--source include/wait_condition.inc
475
--replace_result $ID ID
477
--echo # Switching to connection 'ddl'
479
--error ER_QUERY_INTERRUPTED
482
--send alter table t1 alter column i set default 100
483
--echo # Switching to connection 'default'
486
select count(*) = 1 from information_schema.processlist
487
where state = "Waiting for table metadata lock" and
488
info = "alter table t1 alter column i set default 100";
489
--source include/wait_condition.inc
490
--replace_result $ID ID
492
--echo # Switching to connection 'ddl'
494
--error ER_QUERY_INTERRUPTED
496
--echo # Special case which is triggered only for MERGE tables.
497
--echo # Switching to connection 'blocker'
500
create table t2 (i int primary key) engine=merge union=(t1);
502
--echo # Switching to connection 'ddl'
504
--send alter table t2 alter column i set default 100
505
--echo # Switching to connection 'default'
508
select count(*) = 1 from information_schema.processlist
509
where state = "Waiting for table metadata lock" and
510
info = "alter table t2 alter column i set default 100";
511
--source include/wait_condition.inc
512
--replace_result $ID ID
514
--echo # Switching to connection 'ddl'
516
--error ER_QUERY_INTERRUPTED
519
--echo # Test for DML waiting for meta-data lock
520
--echo # Switching to connection 'blocker'
524
--echo # Switching to connection 'ddl'
526
# Let us add pending exclusive metadata lock on t2
527
--send truncate table t1
528
--echo # Switching to connection 'dml'
531
select count(*) = 1 from information_schema.processlist
532
where state = "Waiting for table metadata lock" and
533
info = "truncate table t1";
534
--source include/wait_condition.inc
535
let $ID2= `select connection_id()`;
536
--send insert into t1 values (1)
537
--echo # Switching to connection 'default'
540
select count(*) = 1 from information_schema.processlist
541
where state = "Waiting for table metadata lock" and
542
info = "insert into t1 values (1)";
543
--source include/wait_condition.inc
544
--replace_result $ID2 ID2
545
eval kill query $ID2;
546
--echo # Switching to connection 'dml'
548
--error ER_QUERY_INTERRUPTED
550
--echo # Switching to connection 'blocker'
553
--echo # Switching to connection 'ddl'
557
--echo # Test for DML waiting for tables to be flushed
558
--echo # Switching to connection 'blocker'
561
--echo # Switching to connection 'ddl'
563
--echo # Let us mark locked table t1 as old
565
--echo # Switching to connection 'dml'
568
select count(*) = 1 from information_schema.processlist
569
where state = "Waiting for table flush" and
570
info = "flush tables";
571
--source include/wait_condition.inc
572
--send select * from t1
573
--echo # Switching to connection 'default'
576
select count(*) = 1 from information_schema.processlist
577
where state = "Waiting for table flush" and
578
info = "select * from t1";
579
--source include/wait_condition.inc
580
--replace_result $ID2 ID2
581
eval kill query $ID2;
582
--echo # Switching to connection 'dml'
584
--error ER_QUERY_INTERRUPTED
586
--echo # Switching to connection 'blocker'
589
--echo # Switching to connection 'ddl'
594
--echo # Switching to connection 'default'
599
###########################################################################
601
SET DEBUG_SYNC = 'RESET';
602
DROP FUNCTION MY_KILL;