1 -- source include/have_innodb.inc
2 -- source include/have_binlog_format_statement.inc
4 # You cannot use `KILL' with the Embedded MySQL Server library,
5 # because the embedded server merely runs inside the threads of the host
6 # application. -- the docs
8 -- source include/not_embedded.inc
11 ### bug#22725 : incorrect killed error in binlogged query
14 connect (con1, localhost, root,,);
15 connect (con2, localhost, root,,);
17 create table t1 (a int auto_increment, b int, PRIMARY KEY (a)) ENGINE=InnoDB;
18 create table t2 (a int auto_increment, b int, PRIMARY KEY (a)) ENGINE=MyISAM;
19 create table t3 (a int auto_increment, b int, PRIMARY KEY (a)) ENGINE=InnoDB;
22 # effective test for bug#22725
26 select get_lock("a", 20);
29 let $ID= `select connection_id()`;
32 # reset master does not reset binlogging on the embedded server.
33 # the test is not run on the embedded for reason of using KILL query.
34 # `reset master' problem is to be addressed by bug#15580 fixes.
37 send insert into t2 values (null, null), (null, get_lock("a", 10));
42 disable_abort_on_error;
49 --error 0,ER_QUERY_INTERRUPTED
51 let $rows= `select count(*) from t2 /* must be 2 or 0 */`;
53 let $MYSQLD_DATADIR= `select @@datadir`;
54 --exec $MYSQL_BINLOG --force-if-open --start-position=134 $MYSQLD_DATADIR/master-bin.000001 > $MYSQLTEST_VARDIR/tmp/kill_query_calling_sp.binlog
55 --replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
57 (@a:=load_file("$MYSQLTEST_VARDIR/tmp/kill_query_calling_sp.binlog"))
59 --replace_result $MYSQL_TEST_DIR MYSQL_TEST_DIR
60 let $error_code= `select @a like "%#%error_code=0%" /* must return 1 or 0*/`;
61 let $insert_binlogged= `select @a like "%insert into%" /* must return 1 or 0 */`;
62 eval set @result= $rows- $error_code - $insert_binlogged;
64 enable_abort_on_error;
68 select @result /* must be zero either way */;
71 --remove_file $MYSQLTEST_VARDIR/tmp/kill_query_calling_sp.binlog
73 select RELEASE_LOCK("a");
76 # bug#27571 asynchronous setting mysql_`query`::error and Query_log_e::error_code
79 # checking that killing inside of select loops is safe as before
80 # killing after the loop can be only simulated - another test
84 insert into t1 values (1,1),(2,2);
90 begin; update t1 set b=11 where a=2;
93 let $ID= `select connection_id()`;
95 send update t1 set b=b+10;
98 --replace_result $ID ID
102 # Bug #32148 killi query may be ineffective
103 # forced to comment out the test's outcome
104 # and mask out ineffective ER_QUERY_INTERRUPTED
105 # todo1: revert back upon fixing bug#32148
106 # todo2: the tests need refining in that
107 # killing should wait till the victim requested
108 # its lock (wait_condition available in 5.1 tests)
111 --error 0,ER_QUERY_INTERRUPTED
114 select * from t1 order by a /* must be the same as before (1,1),(2,2) */;
118 # commented out as Bug #31807 multi-update,delete killing does not report with ER_QUERY_INTERRUPTED
122 # begin; update t1 set b=b+10;
125 # send update t1 as t_1,t1 as t_2 set t_1.b=11 where t_2.a=2;
128 # --replace_result $ID ID
129 # eval kill query $ID;
132 # disable_abort_on_error;
135 # --error HY000,ER_QUERY_INTERRUPTED
137 # select * from t1 /* must be the same as before (1,1),(2,2) */;
139 # enable_abort_on_error;
144 begin; delete from t1 where a=2;
147 let $ID= `select connection_id()`;
149 send delete from t1 where a=2;
152 --replace_result $ID ID
157 --error 0,ER_QUERY_INTERRUPTED
161 select * from t1 order by a /* must be the same as before (1,1),(2,2) */;
166 # the same as for multi-update
169 # begin; delete from t1 where a=2;
172 # send delete t1 from t1 where t1.a=2;
175 # --replace_result $ID ID
176 # eval kill query $ID;
180 # --error 0,ER_QUERY_INTERRUPTED
182 # select * from t1 /* must be the same as before (1,1),(2,2) */;
188 drop table if exists t4;
190 create table t4 (a int, b int) engine=innodb;
191 insert into t4 values (3, 3);
192 begin; insert into t1 values (3, 3);
195 let $ID= `select connection_id()`;
197 send insert into t1 select * from t4 for update;
200 --replace_result $ID ID
205 --error 0,ER_QUERY_INTERRUPTED
209 select * from t1 /* must be the same as before (1,1),(2,2) */;
211 drop table t4; # cleanup for the sub-case
214 ## non-ta table case: killing must be recorded in binlog
216 create table t4 (a int, b int) ENGINE=MyISAM /* for killing update and delete */;
219 create function bug27563(n int)
224 select get_lock("a", 20) into @a;
237 insert into t4 values (1,1), (1,1);
240 select get_lock("a", 20);
243 let $ID= `select connection_id()`;
245 send update t4 set b=b + bug27563(b);
248 let $wait_condition= select count(*) = 1 FROM INFORMATION_SCHEMA.PROCESSLIST where state='User lock';
249 source include/wait_condition.inc;
250 select count(*) FROM INFORMATION_SCHEMA.PROCESSLIST where state='User lock';
252 --replace_result $ID ID
256 --error ER_QUERY_INTERRUPTED
258 select * from t4 order by b /* must be (1,1), (1,2) */;
259 select @b /* must be 1 at the end of a stmt calling bug27563() */;
260 --echo must have the update query event more to FD
261 source include/show_binlog_events.inc;
263 # a proof the query is binlogged with an error
265 --exec $MYSQL_BINLOG --force-if-open --start-position=106 $MYSQLD_DATADIR/master-bin.000001 > $MYSQLTEST_VARDIR/tmp/binlog_killed_bug27571.binlog
266 --replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
268 (@a:=load_file("$MYSQLTEST_VARDIR/tmp/binlog_killed_bug27571.binlog"))
270 --replace_result $MYSQL_TEST_DIR MYSQL_TEST_DIR
271 let $error_code= `select @a like "%#%error_code=0%" /* must return 0*/`;
272 eval select $error_code /* must return 0 to mean the killed query is in */;
274 # cleanup for the sub-case
276 select RELEASE_LOCK("a");
277 --remove_file $MYSQLTEST_VARDIR/tmp/binlog_killed_bug27571.binlog
284 insert into t4 values (1,1), (2,2);
287 select get_lock("a", 20);
290 let $ID= `select connection_id()`;
292 send delete from t4 where b=bug27563(1) or b=bug27563(2);
295 let $wait_condition= select count(*) = 1 FROM INFORMATION_SCHEMA.PROCESSLIST where state='User lock';
296 source include/wait_condition.inc;
297 select count(*) FROM INFORMATION_SCHEMA.PROCESSLIST where state='User lock';
298 --replace_result $ID ID
302 --error ER_QUERY_INTERRUPTED
304 select count(*) from t4 /* must be 1 */;
305 select @b /* must be 1 at the end of a stmt calling bug27563() */;
306 --echo must have the delete query event more to FD
307 source include/show_binlog_events.inc;
309 # a proof the query is binlogged with an error
311 --exec $MYSQL_BINLOG --force-if-open --start-position=106 $MYSQLD_DATADIR/master-bin.000001 > $MYSQLTEST_VARDIR/tmp/binlog_killed_bug27571.binlog
312 --replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
314 (@a:=load_file("$MYSQLTEST_VARDIR/tmp/binlog_killed_bug27571.binlog"))
316 --replace_result $MYSQL_TEST_DIR MYSQL_TEST_DIR
317 let $error_code= `select @a like "%#%error_code=0%" /* must return 0*/`;
318 eval select $error_code /* must return 0 to mean the killed query is in */;
320 # cleanup for the sub-case
322 select RELEASE_LOCK("a");
323 --remove_file $MYSQLTEST_VARDIR/tmp/binlog_killed_bug27571.binlog
328 # load data - see simulation tests
334 drop function bug27563;
343 --echo end of the tests