1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
|
call mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT");
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;
select get_lock("a", 20);
get_lock("a", 20)
1
reset master;
insert into t2 values (null, null), (null, get_lock("a", 10));
kill query ID;
select
(@a:=load_file("MYSQLTEST_VARDIR/tmp/kill_query_calling_sp.binlog"))
is not null;
set @result= 2 - 1 - 1;
select @result /* must be zero either way */;
@result
0
select RELEASE_LOCK("a");
RELEASE_LOCK("a")
1
delete from t1;
delete from t2;
insert into t1 values (1,1),(2,2);
begin;
update t1 set b=11 where a=2;
begin;
update t1 set b=b+10;
kill query ID;
rollback;
rollback;
select * from t1 order by a /* must be the same as before (1,1),(2,2) */;
a b
1 1
2 2
begin;
delete from t1 where a=2;
begin;
delete from t1 where a=2;
kill query ID;
rollback;
rollback;
select * from t1 order by a /* must be the same as before (1,1),(2,2) */;
a b
1 1
2 2
drop table if exists t4;
create table t4 (a int, b int) engine=innodb;
insert into t4 values (3, 3);
begin;
insert into t1 values (3, 3);
begin;
insert into t1 select * from t4 for update;
kill query ID;
rollback;
rollback;
select * from t1 /* must be the same as before (1,1),(2,2) */;
a b
1 1
2 2
drop table t4;
create table t4 (a int, b int) ENGINE=MyISAM /* for killing update and delete */;
create function bug27563(n int)
RETURNS int(11)
NOT DETERMINISTIC
begin
if @b > 0 then
select get_lock("a", 20) into @a;
else
set @b= 1;
end if;
return n;
end|
delete from t4;
insert into t4 values (1,1), (1,1);
reset master;
select get_lock("a", 20);
get_lock("a", 20)
1
set @b= 0;
update t4 set b=b + bug27563(b);
select count(*) FROM INFORMATION_SCHEMA.PROCESSLIST where state='User lock';
count(*)
1
kill query ID;
ERROR 70100: Query execution was interrupted
select * from t4 order by b /* must be (1,1), (1,2) */;
a b
1 1
1 2
select @b /* must be 1 at the end of a stmt calling bug27563() */;
@b
1
must have the update query event on the 4th line
include/show_binlog_events.inc
Log_name Pos Event_type Server_id End_log_pos Info
master-bin.000001 # Gtid # # BEGIN GTID #-#-#
master-bin.000001 # User var # # @`b`=0
master-bin.000001 # Query # # use `test`; update t4 set b=b + bug27563(b)
master-bin.000001 # Query # # COMMIT
*** a proof the query is binlogged with an error ***
select
(@a:=load_file("MYSQLTEST_VARDIR/tmp/binlog_killed_bug27571.binlog"))
is not null;
(@a:=load_file("MYSQLTEST_VARDIR/tmp/binlog_killed_bug27571.binlog"))
is not null
1
select 0 /* must return 0 to mean the killed update is in */;
0
0
select RELEASE_LOCK("a");
RELEASE_LOCK("a")
1
delete from t4;
insert into t4 values (1,1), (2,2);
reset master;
select get_lock("a", 20);
get_lock("a", 20)
1
set @b= 0;
delete from t4 where b=bug27563(1) or b=bug27563(2);
select count(*) FROM INFORMATION_SCHEMA.PROCESSLIST where state='User lock';
count(*)
1
kill query ID;
ERROR 70100: Query execution was interrupted
select count(*) from t4 /* must be 1 */;
count(*)
1
select @b /* must be 1 at the end of a stmt calling bug27563() */;
@b
1
must have the delete query event on the 4th line
include/show_binlog_events.inc
Log_name Pos Event_type Server_id End_log_pos Info
master-bin.000001 # Gtid # # BEGIN GTID #-#-#
master-bin.000001 # User var # # @`b`=0
master-bin.000001 # Query # # use `test`; delete from t4 where b=bug27563(1) or b=bug27563(2)
master-bin.000001 # Query # # COMMIT
select
(@a:=load_file("MYSQLTEST_VARDIR/tmp/binlog_killed_bug27571.binlog"))
is not null;
(@a:=load_file("MYSQLTEST_VARDIR/tmp/binlog_killed_bug27571.binlog"))
is not null
1
select 0 /* must return 0 to mean the killed delete is in */;
0
0
select RELEASE_LOCK("a");
RELEASE_LOCK("a")
1
drop table t4;
drop function bug27563;
drop table t1,t2,t3;
end of the tests
|
