Debian Codegraph @

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

raw
1 -- source include/have_innodb.inc
2 -- source include/have_binlog_format_statement.inc
3 -- source include/binlog_start_pos.inc
4
5 call mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT");
6
7 # You cannot use `KILL' with the Embedded MySQL Server library,
8 # because the embedded server merely runs inside the threads of the host
9 # application. -- the docs
10
11 -- source include/not_embedded.inc
12
13 #
14 # Avoid printing binlog checkpoints
15 #
16
17 --let $skip_checkpoint_events=1
18
19
20 ###
21 ### bug#22725 : incorrect killed error in binlogged query
22 ###
23
24 connect (con1, localhost, root,,);
25 connect (con2, localhost, root,,);
26
27 create table t1 (a int auto_increment, b int, PRIMARY KEY (a)) ENGINE=InnoDB;
28 create table t2 (a int auto_increment, b int, PRIMARY KEY (a)) ENGINE=MyISAM;
29 create table t3 (a int auto_increment, b int, PRIMARY KEY (a)) ENGINE=InnoDB;
30
31 #
32 # effective test for bug#22725
33 #
34
35 connection con1;
36 select get_lock("a", 20);
37
38 connection con2;
39 let $ID= `select connection_id()`;
40
41 #
42 # reset master does not reset binlogging on the embedded server.
43 # the test is not run on the embedded for reason of using KILL query.
44 # `reset master' problem is to be addressed by bug#15580 fixes.
45 #
46 reset master;
47 send insert into t2 values (null, null), (null, get_lock("a", 10));
48
49
50 connection con1;
51
52 --disable_abort_on_error
53 --disable_warnings
54
55 let $wait_condition=
56 select count(*) = 1 from information_schema.processlist
57 where info like "%insert into t2 values%" and state like 'User lock';
58 --source include/wait_condition.inc
59
60 --replace_regex /[0-9]+/ID/
61 eval kill query $ID;
62
63 connection con2;
64 --error 0,ER_QUERY_INTERRUPTED
65 reap;
66 let $rows= `select count(*) from t2 /* must be 2 or 0 */`;
67
68 let $MYSQLD_DATADIR= `select @@datadir`;
69 --let $binlog_killed_pos=query_get_value(SHOW BINLOG EVENTS, Pos, 6)
70 --let $binlog_killed_end_log_pos=query_get_value(SHOW BINLOG EVENTS, Pos, 7)
71 --exec $MYSQL_BINLOG --force-if-open --start-position=$binlog_killed_pos --stop-position=$binlog_killed_end_log_pos $MYSQLD_DATADIR/master-bin.000001 > $MYSQLTEST_VARDIR/tmp/kill_query_calling_sp.binlog
72 --replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
73 --disable_result_log
74 eval select
75 (@a:=load_file("$MYSQLTEST_VARDIR/tmp/kill_query_calling_sp.binlog"))
76 is not null;
77 --enable_result_log
78 --replace_result $MYSQL_TEST_DIR MYSQL_TEST_DIR
79 let $error_code= `select @a like "%#%error_code=0%" /* must return 1 or 0*/`;
80 let $insert_binlogged= `select @a like "%insert into%" /* must return 1 or 0 */`;
81 eval set @result= $rows - $error_code - $insert_binlogged;
82
83 --enable_warnings
84 --enable_abort_on_error
85
86 select @result /* must be zero either way */;
87
88
89 --remove_file $MYSQLTEST_VARDIR/tmp/kill_query_calling_sp.binlog
90 connection con1;
91 select RELEASE_LOCK("a");
92
93 #
94 # bug#27571 asynchronous setting mysql_`query`::error and Query_log_e::error_code
95 #
96
97 # checking that killing inside of select loops is safe as before
98 # killing after the loop can be only simulated - another test
99
100 delete from t1;
101 delete from t2;
102 insert into t1 values (1,1),(2,2);
103
104 #
105 # simple update
106 #
107 connection con1;
108 begin; update t1 set b=11 where a=2;
109
110 connection con2;
111 let $ID= `select connection_id()`;
112 begin;
113 send update t1 set b=b+10;
114
115 connection con1;
116 --replace_result $ID ID
117 eval kill query $ID;
118 rollback;
119
120 # Bug #32148 killi query may be ineffective
121 # forced to comment out the test's outcome
122 # and mask out ineffective ER_QUERY_INTERRUPTED
123 # todo1: revert back upon fixing bug#32148
124 # todo2: the tests need refining in that
125 # killing should wait till the victim requested
126 # its lock (wait_condition available in 5.1 tests)
127
128 connection con2;
129 --error 0,ER_QUERY_INTERRUPTED
130 reap;
131 rollback;
132 select * from t1 order by a /* must be the same as before (1,1),(2,2) */;
133
134 #
135 # multi update
136 # commented out as Bug #31807 multi-update,delete killing does not report with ER_QUERY_INTERRUPTED
137 # in the way
138 #
139 # connection con1;
140 # begin; update t1 set b=b+10;
141
142 # connection con2;
143 # send update t1 as t_1,t1 as t_2 set t_1.b=11 where t_2.a=2;
144
145 # connection con1;
146 # --replace_result $ID ID
147 # eval kill query $ID;
148 # rollback;
149
150 # disable_abort_on_error;
151
152 # connection con2;
153 # --error HY000,ER_QUERY_INTERRUPTED
154 # reap;
155 # select * from t1 /* must be the same as before (1,1),(2,2) */;
156
157 # enable_abort_on_error;
158 #
159 # simple delete
160 #
161 connection con1;
162 begin; delete from t1 where a=2;
163
164 connection con2;
165 let $ID= `select connection_id()`;
166 begin;
167 send delete from t1 where a=2;
168
169 connection con1;
170 --replace_result $ID ID
171 eval kill query $ID;
172 rollback;
173
174 connection con2;
175 --error 0,ER_QUERY_INTERRUPTED
176 reap;
177 rollback;
178 # todo1,2 above
179 select * from t1 order by a /* must be the same as before (1,1),(2,2) */;
180
181
182 #
183 # multi delete
184 # the same as for multi-update
185 #
186 # connection con1;
187 # begin; delete from t1 where a=2;
188
189 # connection con2;
190 # send delete t1 from t1 where t1.a=2;
191
192 # connection con1;
193 # --replace_result $ID ID
194 # eval kill query $ID;
195 # rollback;
196
197 # connection con2;
198 # --error 0,ER_QUERY_INTERRUPTED
199 # reap;
200 # select * from t1 /* must be the same as before (1,1),(2,2) */;
201 #
202 # insert select
203 #
204 connection con1;
205 --disable_warnings
206 drop table if exists t4;
207 --enable_warnings
208 create table t4 (a int, b int) engine=innodb;
209 insert into t4 values (3, 3);
210 begin; insert into t1 values (3, 3);
211
212 connection con2;
213 let $ID= `select connection_id()`;
214 begin;
215 send insert into t1 select * from t4 for update;
216
217 connection con1;
218 --replace_result $ID ID
219 eval kill query $ID;
220 rollback;
221
222 connection con2;
223 --error 0,ER_QUERY_INTERRUPTED,ER_LOCK_WAIT_TIMEOUT
224 reap;
225 # todo 1,2 above
226 rollback;
227 select * from t1 /* must be the same as before (1,1),(2,2) */;
228
229 drop table t4; # cleanup for the sub-case
230
231 ###
232 ## non-ta table case: killing must be recorded in binlog
233 ###
234 create table t4 (a int, b int) ENGINE=MyISAM /* for killing update and delete */;
235
236 delimiter |;
237 create function bug27563(n int)
238 RETURNS int(11)
239 NOT DETERMINISTIC
240 begin
241 if @b > 0 then
242 select get_lock("a", 20) into @a;
243 else
244 set @b= 1;
245 end if;
246 return n;
247 end|
248 delimiter ;|
249
250 #
251 # update
252 #
253
254 delete from t4;
255 insert into t4 values (1,1), (1,1);
256 reset master;
257 connection con1;
258 select get_lock("a", 20);
259
260 connection con2;
261 let $ID= `select connection_id()`;
262 set @b= 0;
263 send update t4 set b=b + bug27563(b);
264
265 connection con1;
266 let $wait_condition= select count(*) = 1 FROM INFORMATION_SCHEMA.PROCESSLIST where state='User lock';
267 source include/wait_condition.inc;
268 select count(*) FROM INFORMATION_SCHEMA.PROCESSLIST where state='User lock';
269
270 --replace_result $ID ID
271 eval kill query $ID;
272
273 connection con2;
274 --error ER_QUERY_INTERRUPTED
275 reap;
276 select * from t4 order by b /* must be (1,1), (1,2) */;
277 select @b /* must be 1 at the end of a stmt calling bug27563() */;
278 --echo must have the update query event on the 4th line
279 source include/show_binlog_events.inc;
280 --let $binlog_killed_pos= query_get_value(SHOW BINLOG EVENTS, Pos, 5)
281 --let $binlog_killed_end_log_pos= query_get_value(SHOW BINLOG EVENTS, End_log_pos, 5)
282
283 --echo *** a proof the query is binlogged with an error ***
284
285 --exec $MYSQL_BINLOG --force-if-open --start-position=$binlog_killed_pos --stop-position=$binlog_killed_end_log_pos $MYSQLD_DATADIR/master-bin.000001 > $MYSQLTEST_VARDIR/tmp/binlog_killed_bug27571.binlog
286 --replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
287 eval select
288 (@a:=load_file("$MYSQLTEST_VARDIR/tmp/binlog_killed_bug27571.binlog"))
289 is not null;
290 --replace_result $MYSQL_TEST_DIR MYSQL_TEST_DIR
291 let $error_code= `select @a like "%#%error_code=0%" /* must return 0*/`;
292 eval select $error_code /* must return 0 to mean the killed update is in */;
293
294 # cleanup for the sub-case
295 connection con1;
296 select RELEASE_LOCK("a");
297 --remove_file $MYSQLTEST_VARDIR/tmp/binlog_killed_bug27571.binlog
298
299 #
300 # delete
301 #
302
303 delete from t4;
304 insert into t4 values (1,1), (2,2);
305 reset master;
306 connection con1;
307 select get_lock("a", 20);
308
309 connection con2;
310 let $ID= `select connection_id()`;
311 set @b= 0;
312 send delete from t4 where b=bug27563(1) or b=bug27563(2);
313
314 connection con1;
315 let $wait_condition= select count(*) = 1 FROM INFORMATION_SCHEMA.PROCESSLIST where state='User lock';
316 source include/wait_condition.inc;
317 select count(*) FROM INFORMATION_SCHEMA.PROCESSLIST where state='User lock';
318 --replace_result $ID ID
319 eval kill query $ID;
320
321 connection con2;
322 --error ER_QUERY_INTERRUPTED
323 reap;
324 select count(*) from t4 /* must be 1 */;
325 select @b /* must be 1 at the end of a stmt calling bug27563() */;
326 --echo must have the delete query event on the 4th line
327 source include/show_binlog_events.inc;
328 --let $binlog_killed_pos= query_get_value(SHOW BINLOG EVENTS, Pos, 5)
329 --let $binlog_killed_end_log_pos= query_get_value(SHOW BINLOG EVENTS, End_log_pos, 5)
330
331 # a proof the query is binlogged with an error
332
333 --exec $MYSQL_BINLOG --force-if-open --start-position=$binlog_killed_pos --stop-position=$binlog_killed_end_log_pos $MYSQLD_DATADIR/master-bin.000001 > $MYSQLTEST_VARDIR/tmp/binlog_killed_bug27571.binlog
334 --replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
335 eval select
336 (@a:=load_file("$MYSQLTEST_VARDIR/tmp/binlog_killed_bug27571.binlog"))
337 is not null;
338 --replace_result $MYSQL_TEST_DIR MYSQL_TEST_DIR
339 let $error_code= `select @a like "%#%error_code=0%" /* must return 0*/`;
340 eval select $error_code /* must return 0 to mean the killed delete is in */;
341
342 # cleanup for the sub-case
343 connection con1;
344 select RELEASE_LOCK("a");
345 --remove_file $MYSQLTEST_VARDIR/tmp/binlog_killed_bug27571.binlog
346
347 drop table t4;
348
349 #
350 # load data - see simulation tests
351 #
352
353
354 # bug#27571 cleanup
355
356 drop function bug27563;
357
358
359 # Prove that killing connection in the middle
360 # of mixed engine transactions affect binlogging
361 # as specified.
362
363 # keep binlogging for this piece of test in a new file
364 FLUSH LOGS;
365
366 # Connection con3 as transaction generator thoughout the test
367 connect (con3, localhost, root,,);
368
369 connection con3;
370 let $ID= `select connection_id()`;
371
372 --let $threads_connected=`select variable_value from information_schema.global_status where variable_name="threads_connected"`
373 --let wait_condition=select variable_value < $threads_connected from information_schema.global_status where variable_name="threads_connected"
374
375 --echo MI: MyISAM, INNODB
376 BEGIN;
377 INSERT INTO t2 VALUES (NULL, 1);
378 INSERT INTO t1 VALUES (NULL, 2);
379
380 #Connection con1 as killer throughout the test
381 connection con1;
382 --replace_result $ID ID
383 --eval KILL $ID
384 let $binlog_file= query_get_value(SHOW MASTER STATUS, File, 1);
385 --source include/wait_condition.inc
386 --let $binlog_start= 4
387 --source include/show_binlog_events.inc
388 --let $binlog_killed_pos=query_get_value(SHOW MASTER STATUS, Position, 1)
389
390 disconnect con3;
391 connect (con3, localhost, root,,);
392 connection con3;
393 let $ID= `select connection_id()`;
394
395 --echo IM: INNODB, MyISAM
396 BEGIN;
397 INSERT INTO t1 VALUES (NULL, 3);
398 INSERT INTO t2 VALUES (NULL, 4);
399
400 connection con1;
401 --replace_result $ID ID
402 --eval KILL $ID
403 --source include/wait_condition.inc
404 --let $binlog_start= $binlog_killed_pos
405 --source include/show_binlog_events.inc
406 --let $binlog_killed_pos=query_get_value(SHOW MASTER STATUS, Position, 1)
407
408 disconnect con3;
409 connect (con3, localhost, root,,);
410 connection con3;
411 let $ID= `select connection_id()`;
412
413 --echo IMI: INNODB, MyISAM, INNODB
414 BEGIN;
415 INSERT INTO t1 VALUES (NULL, 5);
416 INSERT INTO t2 VALUES (NULL, 6);
417 INSERT INTO t1 VALUES (NULL, 7);
418
419 connection con1;
420 --replace_result $ID ID
421 --eval KILL $ID
422 --source include/wait_condition.inc
423 --let $binlog_start= $binlog_killed_pos
424 --source include/show_binlog_events.inc
425 --let $binlog_killed_pos=query_get_value(SHOW MASTER STATUS, Position, 1)
426
427 disconnect con3;
428 connect (con3, localhost, root,,);
429 connection con3;
430 let $ID= `select connection_id()`;
431
432 --echo MI2: MyISAM, INNODB, MyISAM, INNODB
433 BEGIN;
434 INSERT INTO t2 VALUES (NULL, 8);
435 INSERT INTO t1 VALUES (NULL, 9);
436 INSERT INTO t2 VALUES (NULL, 10);
437 INSERT INTO t1 VALUES (NULL, 11);
438
439 connection con1;
440 --replace_result $ID ID
441 --eval KILL $ID
442 --source include/wait_condition.inc
443 --let $binlog_start= $binlog_killed_pos
444 --source include/show_binlog_events.inc
445
446
447 #
448 # common cleanup
449 #
450
451 connection default;
452 disconnect con1;
453 disconnect con2;
454
455 drop table t1,t2,t3;
456
457 --echo end of the tests