1
-- source include/have_innodb.inc
2
-- source include/have_binlog_format_mixed_or_statement.inc
3
-- source include/not_embedded.inc
6
### bug#22725 : incorrect killed error in binlogged query
9
connect (con1, localhost, root,,);
10
connect (con2, localhost, root,,);
12
create table t1 (a int auto_increment, b int, PRIMARY KEY (a)) ENGINE=InnoDB;
13
create table t2 (a int auto_increment, b int, PRIMARY KEY (a)) ENGINE=MyISAM;
14
create table t3 (a int auto_increment, b int, PRIMARY KEY (a)) ENGINE=InnoDB;
17
# effective test for bug#22725
21
select get_lock("a", 20);
24
let $ID= `select connection_id()`;
26
send insert into t2 values (null, null), (null, get_lock("a", 10));
31
disable_abort_on_error;
38
--error 0,ER_QUERY_INTERRUPTED
40
let $rows= `select count(*) from t2 /* must be 2 or 0 */`;
42
--exec $MYSQL_BINLOG --force-if-open --start-position=135 $MYSQLTEST_VARDIR/log/master-bin.000001 > $MYSQLTEST_VARDIR/tmp/kill_query_calling_sp.binlog
43
--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
45
(@a:=load_file("$MYSQLTEST_VARDIR/tmp/kill_query_calling_sp.binlog"))
47
--replace_result $MYSQL_TEST_DIR MYSQL_TEST_DIR
48
let $error_code= `select @a like "%#%error_code=0%" /* must return 1 or 0*/`;
49
let $insert_binlogged= `select @a like "%insert into%" /* must return 1 or 0 */`;
50
eval set @result= $rows- $error_code - $insert_binlogged;
52
enable_abort_on_error;
56
select @result /* must be zero either way */;
59
--remove_file $MYSQLTEST_VARDIR/tmp/kill_query_calling_sp.binlog
61
select RELEASE_LOCK("a");
64
# bug#27571 asynchronous setting mysql_`query`::error and Query_log_e::error_code
67
# checking that killing inside of select loops is safe as before
68
# killing after the loop can be only simulated - another test
72
insert into t1 values (1,1),(2,2);
78
begin; update t1 set b=11 where a=2;
81
let $ID= `select connection_id()`;
83
send update t1 set b=b+10;
86
--replace_result $ID ID
90
# Bug #32148 killi query may be ineffective
91
# forced to comment out the test's outcome
92
# and mask out ineffective ER_QUERY_INTERRUPTED
93
# todo1: revert back upon fixing bug#32148
94
# todo2: the tests need refining in that
95
# killing should wait till the victim requested
96
# its lock (wait_condition available in 5.1 tests)
99
--error 0,ER_QUERY_INTERRUPTED
102
select * from t1 order by a /* must be the same as before (1,1),(2,2) */;
106
# commented out as Bug #31807 multi-update,delete killing does not report with ER_QUERY_INTERRUPTED
110
# begin; update t1 set b=b+10;
113
# send update t1 as t_1,t1 as t_2 set t_1.b=11 where t_2.a=2;
116
# --replace_result $ID ID
117
# eval kill query $ID;
120
# disable_abort_on_error;
123
# --error HY000,ER_QUERY_INTERRUPTED
125
# select * from t1 /* must be the same as before (1,1),(2,2) */;
127
# enable_abort_on_error;
132
begin; delete from t1 where a=2;
135
let $ID= `select connection_id()`;
137
send delete from t1 where a=2;
140
--replace_result $ID ID
145
--error 0,ER_QUERY_INTERRUPTED
149
select * from t1 order by a /* must be the same as before (1,1),(2,2) */;
154
# the same as for multi-update
157
# begin; delete from t1 where a=2;
160
# send delete t1 from t1 where t1.a=2;
163
# --replace_result $ID ID
164
# eval kill query $ID;
168
# --error 0,ER_QUERY_INTERRUPTED
170
# select * from t1 /* must be the same as before (1,1),(2,2) */;
176
drop table if exists t4;
178
create table t4 (a int, b int) engine=innodb;
179
insert into t4 values (3, 3);
180
begin; insert into t1 values (3, 3);
183
let $ID= `select connection_id()`;
185
send insert into t1 select * from t4 for update;
188
--replace_result $ID ID
193
--error 0,ER_QUERY_INTERRUPTED
197
select * from t1 /* must be the same as before (1,1),(2,2) */;
199
drop table t4; # cleanup for the sub-case
202
## non-ta table case: killing must be recorded in binlog
204
create table t4 (a int, b int) ENGINE=MyISAM /* for killing update and delete */;
207
create function bug27563(n int)
212
select get_lock("a", 20) into @a;
225
insert into t4 values (1,1), (1,1);
228
select get_lock("a", 20);
231
let $ID= `select connection_id()`;
233
send update t4 set b=b + bug27563(b);
236
let $wait_condition= select count(*) = 1 FROM INFORMATION_SCHEMA.PROCESSLIST where state='User lock';
237
source include/wait_condition.inc;
238
select count(*) FROM INFORMATION_SCHEMA.PROCESSLIST where state='User lock';
240
--replace_result $ID ID
244
--error ER_QUERY_INTERRUPTED
246
select * from t4 order by b /* must be (1,1), (1,2) */;
247
select @b /* must be 1 at the end of a stmt calling bug27563() */;
248
--echo must have the update query event more to FD
249
source include/show_binlog_events.inc;
251
# a proof the query is binlogged with an error
253
--exec $MYSQL_BINLOG --force-if-open --start-position=106 $MYSQLTEST_VARDIR/log/master-bin.000001 > $MYSQLTEST_VARDIR/tmp/binlog_killed_bug27571.binlog
254
--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
256
(@a:=load_file("$MYSQLTEST_VARDIR/tmp/binlog_killed_bug27571.binlog"))
258
--replace_result $MYSQL_TEST_DIR MYSQL_TEST_DIR
259
let $error_code= `select @a like "%#%error_code=0%" /* must return 0*/`;
260
eval select $error_code /* must return 0 to mean the killed query is in */;
262
# cleanup for the sub-case
264
select RELEASE_LOCK("a");
265
--remove_file $MYSQLTEST_VARDIR/tmp/binlog_killed_bug27571.binlog
272
insert into t4 values (1,1), (2,2);
275
select get_lock("a", 20);
278
let $ID= `select connection_id()`;
280
send delete from t4 where b=bug27563(1) or b=bug27563(2);
283
let $wait_condition= select count(*) = 1 FROM INFORMATION_SCHEMA.PROCESSLIST where state='User lock';
284
source include/wait_condition.inc;
285
select count(*) FROM INFORMATION_SCHEMA.PROCESSLIST where state='User lock';
286
--replace_result $ID ID
290
--error ER_QUERY_INTERRUPTED
292
select count(*) from t4 /* must be 1 */;
293
select @b /* must be 1 at the end of a stmt calling bug27563() */;
294
--echo must have the delete query event more to FD
295
source include/show_binlog_events.inc;
297
# a proof the query is binlogged with an error
299
--exec $MYSQL_BINLOG --force-if-open --start-position=106 $MYSQLTEST_VARDIR/log/master-bin.000001 > $MYSQLTEST_VARDIR/tmp/binlog_killed_bug27571.binlog
300
--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
302
(@a:=load_file("$MYSQLTEST_VARDIR/tmp/binlog_killed_bug27571.binlog"))
304
--replace_result $MYSQL_TEST_DIR MYSQL_TEST_DIR
305
let $error_code= `select @a like "%#%error_code=0%" /* must return 0*/`;
306
eval select $error_code /* must return 0 to mean the killed query is in */;
308
# cleanup for the sub-case
310
select RELEASE_LOCK("a");
311
--remove_file $MYSQLTEST_VARDIR/tmp/binlog_killed_bug27571.binlog
316
# load data - see simulation tests
322
drop function bug27563;
331
--echo end of the tests
added
removed
mysql-test/suite/binlog/t/binlog_killed.test
