Debian Codegraph @

mysql-test/suite/binlog/t/binlog_killed.test

raw
1 -- source include/have_myisam.inc
2 -- source include/have_binlog_format_statement.inc
3
4 call mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT");
5
6
7
8 ###
9 ### bug#22725 : incorrect killed error in binlogged query
10 ###
11
12 connect (con1, localhost, root,,);
13 connect (con2, localhost, root,,);
14
15 create table t1 (a int auto_increment, b int, PRIMARY KEY (a)) ENGINE=InnoDB;
16 create table t2 (a int auto_increment, b int, PRIMARY KEY (a)) ENGINE=MyISAM;
17 create table t3 (a int auto_increment, b int, PRIMARY KEY (a)) ENGINE=InnoDB;
18
19 #
20 # effective test for bug#22725
21 #
22
23 connection con1;
24 select get_lock("a", 20);
25
26 connection con2;
27 let $ID= `select connection_id()`;
28
29 #
30 # `reset master' problem is to be addressed by bug#15580 fixes.
31 #
32 reset master;
33 send insert into t2 values (null, null), (null, get_lock("a", 10));
34
35
36 connection con1;
37
38 --disable_abort_on_error
39 --disable_warnings
40
41 let $wait_condition=
42 select count(*) = 1 from information_schema.processlist
43 where info like "%insert into t2 values%" and state like 'User lock';
44 --source include/wait_condition_or_abort.inc
45
46 --replace_regex /[0-9]+/ID/
47 eval kill query $ID;
48
49 connection con2;
50 --error 0,ER_QUERY_INTERRUPTED
51 reap;
52 let $rows= `select count(*) from t2 /* must be 1 or 0 */`;
53 let $MYSQLD_DATADIR= `select @@datadir`;
54 --let $row_number=6
55 --let $binlog_killed_pos=query_get_value(SHOW BINLOG EVENTS, Pos, $row_number)
56 --let $binlog_killed_end_log_pos=query_get_value(SHOW BINLOG EVENTS, End_log_pos, $row_number)
57 --exec $MYSQL_BINLOG --force-if-open --start-position=$binlog_killed_pos --stop-position=$binlog_killed_end_log_pos $MYSQLD_DATADIR/binlog.000001 > $MYSQLTEST_VARDIR/tmp/kill_query_calling_sp.binlog
58 --replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
59 --disable_result_log
60 eval select
61 (@a:=load_file("$MYSQLTEST_VARDIR/tmp/kill_query_calling_sp.binlog"))
62 is not null;
63 --enable_result_log
64 --replace_result $MYSQL_TEST_DIR MYSQL_TEST_DIR
65 let $error_code= `select @a like "%#%error_code=0%" /* must return 1 or 0*/`;
66 let $insert_binlogged= `select @a like "%insert into%" /* must return 1 or 0 */`;
67 eval set @result= $rows - $error_code - $insert_binlogged;
68
69 --enable_warnings
70 --enable_abort_on_error
71
72 if (`SELECT @result != 0`)
73 {
74 --source include/show_rpl_debug_info.inc
75 -- echo error_code:$error_code, insert:$insert_binlogged, rows:$rows
76 SELECT * FROM t2;
77
78 --die DIE
79 }
80
81 select @result /* must be zero either way */;
82
83
84 --remove_file $MYSQLTEST_VARDIR/tmp/kill_query_calling_sp.binlog
85 connection con1;
86 select RELEASE_LOCK("a");
87
88 #
89 # bug#27571 asynchronous setting mysql_`query`::error and Query_log_e::error_code
90 #
91
92 # checking that killing inside of select loops is safe as before
93 # killing after the loop can be only simulated - another test
94
95 delete from t1;
96 delete from t2;
97 insert into t1 values (1,1),(2,2);
98
99 #
100 # simple update
101 #
102 connection con1;
103 begin; update t1 set b=11 where a=2;
104
105 connection con2;
106 let $ID= `select connection_id()`;
107 begin;
108 send update t1 set b=b+10;
109
110 connection con1;
111 --replace_result $ID ID
112 eval kill query $ID;
113 rollback;
114
115 # Bug #32148 killi query may be ineffective
116 # forced to comment out the test's outcome
117 # and mask out ineffective ER_QUERY_INTERRUPTED
118 # todo1: revert back upon fixing bug#32148
119 # todo2: the tests need refining in that
120 # killing should wait till the victim requested
121 # its lock (wait_condition available in 5.1 tests)
122
123 connection con2;
124 --error 0,ER_QUERY_INTERRUPTED
125 reap;
126 rollback;
127 select * from t1 order by a /* must be the same as before (1,1),(2,2) */;
128
129 #
130 # multi update
131 # commented out as Bug #31807 multi-update,delete killing does not report with ER_QUERY_INTERRUPTED
132 # in the way
133 #
134 # connection con1;
135 # begin; update t1 set b=b+10;
136
137 # connection con2;
138 # send update t1 as t_1,t1 as t_2 set t_1.b=11 where t_2.a=2;
139
140 # connection con1;
141 # --replace_result $ID ID
142 # eval kill query $ID;
143 # rollback;
144
145 # disable_abort_on_error;
146
147 # connection con2;
148 # --error HY000,ER_QUERY_INTERRUPTED
149 # reap;
150 # select * from t1 /* must be the same as before (1,1),(2,2) */;
151
152 # enable_abort_on_error;
153 #
154 # simple delete
155 #
156 connection con1;
157 begin; delete from t1 where a=2;
158
159 connection con2;
160 let $ID= `select connection_id()`;
161 begin;
162 send delete from t1 where a=2;
163
164 connection con1;
165 --replace_result $ID ID
166 eval kill query $ID;
167 rollback;
168
169 connection con2;
170 --error 0,ER_QUERY_INTERRUPTED
171 reap;
172 rollback;
173 # todo1,2 above
174 select * from t1 order by a /* must be the same as before (1,1),(2,2) */;
175
176
177 #
178 # multi delete
179 # the same as for multi-update
180 #
181 # connection con1;
182 # begin; delete from t1 where a=2;
183
184 # connection con2;
185 # send delete t1 from t1 where t1.a=2;
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;
195 # select * from t1 /* must be the same as before (1,1),(2,2) */;
196 #
197 # insert select
198 #
199 connection con1;
200 --disable_warnings
201 drop table if exists t4;
202 --enable_warnings
203 create table t4 (a int, b int) engine=innodb;
204 insert into t4 values (3, 3);
205 begin; insert into t1 values (3, 3);
206
207 connection con2;
208 let $ID= `select connection_id()`;
209 begin;
210 send insert into t1 select * from t4 for update;
211
212 connection con1;
213 --replace_result $ID ID
214 eval kill query $ID;
215 rollback;
216
217 connection con2;
218 --error 0,ER_QUERY_INTERRUPTED
219 reap;
220 # todo 1,2 above
221 rollback;
222 select * from t1 /* must be the same as before (1,1),(2,2) */;
223
224 drop table t4; # cleanup for the sub-case
225
226 ###
227 ## non-ta table case: killing must be recorded in binlog
228 ###
229 create table t4 (a int, b int) ENGINE=MyISAM /* for killing update and delete */;
230
231 delimiter |;
232 create function bug27563(n int)
233 RETURNS int(11)
234 DETERMINISTIC
235 begin
236 if @b > 0 then
237 select get_lock("a", 20) into @a;
238 else
239 set @b= 1;
240 end if;
241 return n;
242 end|
243 delimiter ;|
244
245 #
246 # update
247 #
248
249 delete from t4;
250 insert into t4 values (1,1), (1,1);
251 reset master;
252 connection con1;
253 select get_lock("a", 20);
254
255 connection con2;
256 let $ID= `select connection_id()`;
257 set @b= 0;
258 send update t4 set b=b + bug27563(b);
259
260 connection con1;
261 let $wait_condition= select count(*) = 1 FROM INFORMATION_SCHEMA.PROCESSLIST where state='User lock';
262 source include/wait_condition_or_abort.inc;
263 select count(*) FROM INFORMATION_SCHEMA.PROCESSLIST where state='User lock';
264
265 --replace_result $ID ID
266 eval kill query $ID;
267
268 connection con2;
269 --error ER_QUERY_INTERRUPTED
270 reap;
271 select * from t4 order by b /* must be (1,1), (1,2) */;
272 select @b /* must be 1 at the end of a stmt calling bug27563() */;
273 --echo must have the update query event on the 4th line
274 source include/show_binlog_events.inc;
275 --let $row_number= 6
276
277 --let $binlog_killed_pos= query_get_value(SHOW BINLOG EVENTS, Pos, $row_number)
278 --let $binlog_killed_end_log_pos= query_get_value(SHOW BINLOG EVENTS, End_log_pos, $row_number)
279
280 --echo *** a proof the query is binlogged with an error ***
281
282 --exec $MYSQL_BINLOG --force-if-open --start-position=$binlog_killed_pos --stop-position=$binlog_killed_end_log_pos $MYSQLD_DATADIR/binlog.000001 > $MYSQLTEST_VARDIR/tmp/binlog_killed_bug27571.binlog
283 --replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
284 eval select
285 (@a:=load_file("$MYSQLTEST_VARDIR/tmp/binlog_killed_bug27571.binlog"))
286 is not null AS Loaded;
287 let $error_code= `select @a like "%#%error_code=0%" /* must return 0*/`;
288 eval select $error_code /* must return 0 to mean the killed update is in */;
289
290 # cleanup for the sub-case
291 connection con1;
292 select RELEASE_LOCK("a");
293 --remove_file $MYSQLTEST_VARDIR/tmp/binlog_killed_bug27571.binlog
294
295 #
296 # delete
297 #
298
299 delete from t4;
300 insert into t4 values (1,1), (2,2);
301 reset master;
302 connection con1;
303 select get_lock("a", 20);
304
305 connection con2;
306 let $ID= `select connection_id()`;
307 set @b= 0;
308 send delete from t4 where b=bug27563(1) or b=bug27563(2);
309
310 connection con1;
311 let $wait_condition= select count(*) = 1 FROM INFORMATION_SCHEMA.PROCESSLIST where state='User lock';
312 source include/wait_condition_or_abort.inc;
313 select count(*) FROM INFORMATION_SCHEMA.PROCESSLIST where state='User lock';
314 --replace_result $ID ID
315 eval kill query $ID;
316
317 connection con2;
318 --error ER_QUERY_INTERRUPTED
319 reap;
320 select count(*) from t4 /* must be 1 */;
321 select @b /* must be 1 at the end of a stmt calling bug27563() */;
322 --echo must have the delete query event on the 4th line
323 source include/show_binlog_events.inc;
324
325 --let $row_number= 6
326
327 --let $binlog_killed_pos= query_get_value(SHOW BINLOG EVENTS, Pos, $row_number)
328 --let $binlog_killed_end_log_pos= query_get_value(SHOW BINLOG EVENTS, End_log_pos, $row_number)
329
330 # a proof the query is binlogged with an error
331
332 --exec $MYSQL_BINLOG --force-if-open --start-position=$binlog_killed_pos --stop-position=$binlog_killed_end_log_pos $MYSQLD_DATADIR/binlog.000001 > $MYSQLTEST_VARDIR/tmp/binlog_killed_bug27571.binlog
333 --replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
334 eval select
335 (@a:=load_file("$MYSQLTEST_VARDIR/tmp/binlog_killed_bug27571.binlog"))
336 is not null AS Loaded;
337 --replace_result $MYSQL_TEST_DIR MYSQL_TEST_DIR
338 let $error_code= `select @a like "%#%error_code=0%" /* must return 0*/`;
339 eval select $error_code /* must return 0 to mean the killed delete is in */;
340
341 # cleanup for the sub-case
342 connection con1;
343 select RELEASE_LOCK("a");
344 --remove_file $MYSQLTEST_VARDIR/tmp/binlog_killed_bug27571.binlog
345
346 drop table t4;
347
348 #
349 # load data - see simulation tests
350 #
351
352
353 # bug#27571 cleanup
354
355 drop function bug27563;
356
357
358 #
359 # common cleanup
360 #
361
362 connection con1;
363 disconnect con1;
364 --source include/wait_until_disconnected.inc
365 connection con2;
366 disconnect con2;
367 --source include/wait_until_disconnected.inc
368 connection default;
369
370 drop table t1,t2,t3;
371
372 --echo end of the tests