binlog_killed.test 7.79 KB
Newer Older
1
-- source include/have_innodb.inc
2
-- source include/have_binlog_format_mixed_or_statement.inc
3
-- source include/not_embedded.inc
4 5 6 7 8 9 10 11

###
### bug#22725 : incorrect killed error in binlogged query
###

connect (con1, localhost, root,,);
connect (con2, localhost, root,,);

12 13 14 15 16 17 18 19 20 21 22 23 24 25
create table t1 (a int auto_increment, b int, PRIMARY KEY (a)) ENGINE=InnoDB;
create table t2 (a int auto_increment, b int, PRIMARY KEY (a)) ENGINE=MyISAM;
create table t3 (a int auto_increment, b int, PRIMARY KEY (a)) ENGINE=InnoDB;

#
# effective test for bug#22725
#

connection con1;
select get_lock("a", 20);

connection con2;
let $ID= `select connection_id()`;
reset master;
26
send insert into t2 values (null, null), (null, get_lock("a", 10));
27 28 29


connection con1;
30 31 32 33 34

disable_abort_on_error;
disable_query_log;
disable_result_log;

35 36 37
eval kill query $ID;

connection con2;
38
--error 0,ER_QUERY_INTERRUPTED
39
reap;
40
let $rows= `select count(*) from t2  /* must be 2 or 0 */`;
41

42
--exec $MYSQL_BINLOG --start-position=134 $MYSQLTEST_VARDIR/log/master-bin.000001 > $MYSQLTEST_VARDIR/tmp/kill_query_calling_sp.binlog
43 44 45 46 47
--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
eval select
(@a:=load_file("$MYSQLTEST_VARDIR/tmp/kill_query_calling_sp.binlog"))
is not null;
--replace_result $MYSQL_TEST_DIR MYSQL_TEST_DIR
48 49
let $error_code= `select @a like "%#%error_code=0%" /* must return 1 or 0*/`;
let $insert_binlogged= `select @a like "%insert into%" /* must return 1 or 0 */`;
50
eval set @result= $rows- $error_code - $insert_binlogged;
51 52 53 54 55 56 57

enable_abort_on_error;
enable_query_log;
enable_result_log;

select @result /* must be zero either way */;

58

59
--remove_file $MYSQLTEST_VARDIR/tmp/kill_query_calling_sp.binlog
aelkin/elkin@koti.dsl.inet.fi's avatar
aelkin/elkin@koti.dsl.inet.fi committed
60 61
connection con1;
select RELEASE_LOCK("a");
62

63 64 65
#
# bug#27571 asynchronous setting mysql_`query`::error and Query_log_e::error_code
#
66

67 68
# checking that killing inside of select loops is safe as before
# killing after the loop can be only simulated - another test
69

70 71 72
delete from t1;
delete from t2;
insert into t1 values (1,1),(2,2);
73

74 75 76
#
# simple update
#
77
connection con1;
78
begin; update t1 set b=11 where a=2;
79 80

connection con2;
81
let $ID= `select connection_id()`;
aelkin/elkin@koti.dsl.inet.fi's avatar
aelkin/elkin@koti.dsl.inet.fi committed
82
begin;
83
send update t1 set b=b+10;
84 85

connection con1;
86
--replace_result $ID ID
87
eval kill query $ID;
88 89
rollback;

aelkin/elkin@koti.dsl.inet.fi's avatar
aelkin/elkin@koti.dsl.inet.fi committed
90 91 92 93 94 95 96 97
# Bug #32148  	killi query may be ineffective 
# forced to comment out the test's outcome 
# and mask out ineffective ER_QUERY_INTERRUPTED
# todo1: revert back upon fixing bug#32148
# todo2: the tests need refining in that
#        killing should wait till the victim requested
#        its lock (wait_condition available in 5.1 tests)

98
connection con2;
99
--error 0,ER_QUERY_INTERRUPTED
100
reap;
aelkin/elkin@koti.dsl.inet.fi's avatar
aelkin/elkin@koti.dsl.inet.fi committed
101
rollback;
102
select * from t1  order by a /* must be the same as before (1,1),(2,2) */;
103 104 105 106 107 108 109 110

#
# multi update
# commented out as Bug #31807 multi-update,delete killing does not report with ER_QUERY_INTERRUPTED
# in the way
#
# connection con1;
# begin; update t1 set b=b+10;
111

112 113
# connection con2;
# send update t1 as t_1,t1 as t_2 set t_1.b=11 where t_2.a=2;
114

115 116 117 118
# connection con1;
# --replace_result $ID ID
# eval kill query $ID;
# rollback;
119

120 121 122 123 124 125 126 127 128 129 130 131 132
# disable_abort_on_error;

# connection con2;
# --error HY000,ER_QUERY_INTERRUPTED
# reap;
# select * from t1 /* must be the same as before (1,1),(2,2) */;

# enable_abort_on_error;
#
# simple delete
#
connection con1;
begin; delete from t1 where a=2;
133 134

connection con2;
135
let $ID= `select connection_id()`;
aelkin/elkin@koti.dsl.inet.fi's avatar
aelkin/elkin@koti.dsl.inet.fi committed
136
begin;
137
send delete from t1 where a=2;
138 139

connection con1;
140
--replace_result $ID ID
141
eval kill query $ID;
142
rollback;
143 144

connection con2;
145
--error 0,ER_QUERY_INTERRUPTED
146
reap;
aelkin/elkin@koti.dsl.inet.fi's avatar
aelkin/elkin@koti.dsl.inet.fi committed
147 148
rollback;
# todo1,2 above
149
select * from t1 order by a /* must be the same as before (1,1),(2,2) */;
150

aelkin/elkin@koti.dsl.inet.fi's avatar
aelkin/elkin@koti.dsl.inet.fi committed
151

152 153 154 155 156 157
#
# multi delete
# the same as for multi-update
#
# connection con1;
# begin; delete from t1 where a=2;
158

159 160
# connection con2;
# send delete t1 from t1 where t1.a=2;
161

162 163 164 165
# connection con1;
# --replace_result $ID ID
# eval kill query $ID;
# rollback;
166

167 168 169 170 171 172 173 174 175 176 177 178 179 180
# connection con2;
# --error 0,ER_QUERY_INTERRUPTED
# reap;
# select * from t1 /* must be the same as before (1,1),(2,2) */;
#
# insert select
#
connection con1;
--disable_warnings
drop table if exists t4;
--enable_warnings
create table t4 (a int, b int) engine=innodb;
insert into t4 values (3, 3);
begin; insert into t1 values (3, 3);
181 182

connection con2;
183
let $ID= `select connection_id()`;
184 185
begin;
send insert into t1 select * from t4 for update;
186 187

connection con1;
188
--replace_result $ID ID
189
eval kill query $ID;
190
rollback;
191 192

connection con2;
193
--error 0,ER_QUERY_INTERRUPTED
194
reap;
aelkin/elkin@koti.dsl.inet.fi's avatar
aelkin/elkin@koti.dsl.inet.fi committed
195
# todo 1,2 above
196 197 198 199 200 201 202 203
rollback;
select * from t1 /* must be the same as before (1,1),(2,2) */;

drop table t4; # cleanup for the sub-case

###
## non-ta table case: killing must be recorded in binlog
###
204 205
create table t4 (a int, b int) ENGINE=MyISAM /* for killing update and delete */;

206 207 208 209 210
delimiter |;
create function bug27563(n int) 
RETURNS int(11)
DETERMINISTIC
begin
211
  if @b > 0 then
aelkin/elkin@koti.dsl.inet.fi's avatar
aelkin/elkin@koti.dsl.inet.fi committed
212
     select get_lock("a", 20)  into @a;
213 214
  else 
     set @b= 1;
215 216 217 218
  end if;
  return n;
end|
delimiter ;|
219 220

#
221 222 223
# update
#

224 225
delete from t4;
insert into t4 values (1,1), (1,1);
226
reset master;
227 228
connection con1;
select get_lock("a", 20);
229 230 231

connection con2;
let $ID= `select connection_id()`;
232 233
set @b= 0;
send update t4 set b=b + bug27563(b);
234 235

connection con1;
aelkin/elkin@koti.dsl.inet.fi's avatar
aelkin/elkin@koti.dsl.inet.fi committed
236 237 238 239
let $wait_condition= select count(*) = 1 FROM INFORMATION_SCHEMA.PROCESSLIST where state='User lock';
source include/wait_condition.inc;
select count(*) FROM INFORMATION_SCHEMA.PROCESSLIST where state='User lock';

240
--replace_result $ID ID
241 242 243
eval kill query $ID;

connection con2;
aelkin/elkin@koti.dsl.inet.fi's avatar
aelkin/elkin@koti.dsl.inet.fi committed
244
--error ER_QUERY_INTERRUPTED
245
reap;
246 247 248
select * from t4 order by b /* must be (1,1), (1,2) */;
select @b  /* must be 1 at the end of a stmt calling bug27563() */;
--echo must have the update query event more to FD
249
source include/show_binlog_events.inc;
250 251 252

# a proof the query is binlogged with an error

253
--exec $MYSQL_BINLOG --start-position=106 $MYSQLTEST_VARDIR/log/master-bin.000001 > $MYSQLTEST_VARDIR/tmp/binlog_killed_bug27571.binlog
254 255 256 257 258 259 260 261 262
--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
eval select
(@a:=load_file("$MYSQLTEST_VARDIR/tmp/binlog_killed_bug27571.binlog"))
is not null;
--replace_result $MYSQL_TEST_DIR MYSQL_TEST_DIR
let $error_code= `select @a like "%#%error_code=0%" /* must return 0*/`;
eval select $error_code /* must return 0 to mean the killed query is in */;

# cleanup for the sub-case
263
connection con1;
264 265 266 267 268 269 270
select RELEASE_LOCK("a");
--remove_file $MYSQLTEST_VARDIR/tmp/binlog_killed_bug27571.binlog

#
# delete
#

271 272
delete from t4;
insert into t4 values (1,1), (2,2);
273
reset master;
274 275
connection con1;
select get_lock("a", 20);
276 277 278

connection con2;
let $ID= `select connection_id()`;
279 280
set @b= 0;
send delete from t4 where b=bug27563(1) or b=bug27563(2);
281 282

connection con1;
aelkin/elkin@koti.dsl.inet.fi's avatar
aelkin/elkin@koti.dsl.inet.fi committed
283 284 285
let $wait_condition= select count(*) = 1 FROM INFORMATION_SCHEMA.PROCESSLIST where state='User lock';
source include/wait_condition.inc;
select count(*) FROM INFORMATION_SCHEMA.PROCESSLIST where state='User lock';
286
--replace_result $ID ID
287 288 289
eval kill query $ID;

connection con2;
aelkin/elkin@koti.dsl.inet.fi's avatar
aelkin/elkin@koti.dsl.inet.fi committed
290
--error ER_QUERY_INTERRUPTED
291
reap;
292 293 294
select count(*) from t4 /* must be 1 */;
select @b  /* must be 1 at the end of a stmt calling bug27563() */;
--echo must have the delete query event more to FD
295
source include/show_binlog_events.inc;
296

297 298
# a proof the query is binlogged with an error

299
--exec $MYSQL_BINLOG --start-position=106 $MYSQLTEST_VARDIR/log/master-bin.000001 > $MYSQLTEST_VARDIR/tmp/binlog_killed_bug27571.binlog
300 301 302 303 304 305 306 307 308 309 310 311 312
--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
eval select
(@a:=load_file("$MYSQLTEST_VARDIR/tmp/binlog_killed_bug27571.binlog"))
is not null;
--replace_result $MYSQL_TEST_DIR MYSQL_TEST_DIR
let $error_code= `select @a like "%#%error_code=0%" /* must return 0*/`;
eval select $error_code /* must return 0 to mean the killed query is in */;

# cleanup for the sub-case
connection con1;
select RELEASE_LOCK("a");
--remove_file $MYSQLTEST_VARDIR/tmp/binlog_killed_bug27571.binlog

313 314
drop table t4;

315 316 317 318 319 320
#
# load data - see simulation tests
#


# bug#27571 cleanup
321 322

drop function bug27563;
323

324 325 326 327

#
# common cleanup 
#
328 329

drop table t1,t2,t3;
330

331
--echo end of the tests