Debian Codegraph @

mysql-test/suite/binlog/r/binlog_killed.result

raw
1 call mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT");
2 connect con1, localhost, root,,;
3 connect con2, localhost, root,,;
4 create table t1 (a int auto_increment, b int, PRIMARY KEY (a)) ENGINE=InnoDB;
5 create table t2 (a int auto_increment, b int, PRIMARY KEY (a)) ENGINE=MyISAM;
6 create table t3 (a int auto_increment, b int, PRIMARY KEY (a)) ENGINE=InnoDB;
7 connection con1;
8 select get_lock("a", 20);
9 get_lock("a", 20)
10 1
11 connection con2;
12 reset master;
13 insert into t2 values (null, null), (null, get_lock("a", 10));
14 connection con1;
15 kill query ID;
16 connection con2;
17 select
18 (@a:=load_file("MYSQLTEST_VARDIR/tmp/kill_query_calling_sp.binlog"))
19 is not null;
20 set @result= 2 - 1 - 1;
21 select @result /* must be zero either way */;
22 @result
23 0
24 connection con1;
25 select RELEASE_LOCK("a");
26 RELEASE_LOCK("a")
27 1
28 delete from t1;
29 delete from t2;
30 insert into t1 values (1,1),(2,2);
31 connection con1;
32 begin;
33 update t1 set b=11 where a=2;
34 connection con2;
35 begin;
36 update t1 set b=b+10;
37 connection con1;
38 kill query ID;
39 rollback;
40 connection con2;
41 rollback;
42 select * from t1 order by a /* must be the same as before (1,1),(2,2) */;
43 a b
44 1 1
45 2 2
46 connection con1;
47 begin;
48 delete from t1 where a=2;
49 connection con2;
50 begin;
51 delete from t1 where a=2;
52 connection con1;
53 kill query ID;
54 rollback;
55 connection con2;
56 rollback;
57 select * from t1 order by a /* must be the same as before (1,1),(2,2) */;
58 a b
59 1 1
60 2 2
61 connection con1;
62 drop table if exists t4;
63 create table t4 (a int, b int) engine=innodb;
64 insert into t4 values (3, 3);
65 begin;
66 insert into t1 values (3, 3);
67 connection con2;
68 begin;
69 insert into t1 select * from t4 for update;
70 connection con1;
71 kill query ID;
72 rollback;
73 connection con2;
74 rollback;
75 select * from t1 /* must be the same as before (1,1),(2,2) */;
76 a b
77 1 1
78 2 2
79 drop table t4;
80 create table t4 (a int, b int) ENGINE=MyISAM /* for killing update and delete */;
81 create function bug27563(n int)
82 RETURNS int(11)
83 NOT DETERMINISTIC
84 begin
85 if @b > 0 then
86 select get_lock("a", 20) into @a;
87 else
88 set @b= 1;
89 end if;
90 return n;
91 end|
92 delete from t4;
93 insert into t4 values (1,1), (1,1);
94 reset master;
95 connection con1;
96 select get_lock("a", 20);
97 get_lock("a", 20)
98 1
99 connection con2;
100 set @b= 0;
101 update t4 set b=b + bug27563(b);
102 connection con1;
103 select count(*) FROM INFORMATION_SCHEMA.PROCESSLIST where state='User lock';
104 count(*)
105 1
106 kill query ID;
107 connection con2;
108 ERROR 70100: Query execution was interrupted
109 select * from t4 order by b /* must be (1,1), (1,2) */;
110 a b
111 1 1
112 1 2
113 select @b /* must be 1 at the end of a stmt calling bug27563() */;
114 @b
115 1
116 must have the update query event on the 4th line
117 include/show_binlog_events.inc
118 Log_name Pos Event_type Server_id End_log_pos Info
119 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
120 master-bin.000001 # User var # # @`b`=0
121 master-bin.000001 # Query # # use `test`; update t4 set b=b + bug27563(b)
122 master-bin.000001 # Query # # COMMIT
123 *** a proof the query is binlogged with an error ***
124 select
125 (@a:=load_file("MYSQLTEST_VARDIR/tmp/binlog_killed_bug27571.binlog"))
126 is not null;
127 (@a:=load_file("MYSQLTEST_VARDIR/tmp/binlog_killed_bug27571.binlog"))
128 is not null
129 1
130 select 0 /* must return 0 to mean the killed update is in */;
131 0
132 0
133 connection con1;
134 select RELEASE_LOCK("a");
135 RELEASE_LOCK("a")
136 1
137 delete from t4;
138 insert into t4 values (1,1), (2,2);
139 reset master;
140 connection con1;
141 select get_lock("a", 20);
142 get_lock("a", 20)
143 1
144 connection con2;
145 set @b= 0;
146 delete from t4 where b=bug27563(1) or b=bug27563(2);
147 connection con1;
148 select count(*) FROM INFORMATION_SCHEMA.PROCESSLIST where state='User lock';
149 count(*)
150 1
151 kill query ID;
152 connection con2;
153 ERROR 70100: Query execution was interrupted
154 select count(*) from t4 /* must be 1 */;
155 count(*)
156 1
157 select @b /* must be 1 at the end of a stmt calling bug27563() */;
158 @b
159 1
160 must have the delete query event on the 4th line
161 include/show_binlog_events.inc
162 Log_name Pos Event_type Server_id End_log_pos Info
163 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
164 master-bin.000001 # User var # # @`b`=0
165 master-bin.000001 # Query # # use `test`; delete from t4 where b=bug27563(1) or b=bug27563(2)
166 master-bin.000001 # Query # # COMMIT
167 select
168 (@a:=load_file("MYSQLTEST_VARDIR/tmp/binlog_killed_bug27571.binlog"))
169 is not null;
170 (@a:=load_file("MYSQLTEST_VARDIR/tmp/binlog_killed_bug27571.binlog"))
171 is not null
172 1
173 select 0 /* must return 0 to mean the killed delete is in */;
174 0
175 0
176 connection con1;
177 select RELEASE_LOCK("a");
178 RELEASE_LOCK("a")
179 1
180 drop table t4;
181 drop function bug27563;
182 FLUSH LOGS;
183 connect con3, localhost, root,,;
184 connection con3;
185 MI: MyISAM, INNODB
186 BEGIN;
187 INSERT INTO t2 VALUES (NULL, 1);
188 INSERT INTO t1 VALUES (NULL, 2);
189 connection con1;
190 KILL ID;
191 include/show_binlog_events.inc
192 Log_name Pos Event_type Server_id End_log_pos Info
193 master-bin.000002 # Format_desc # # SERVER_VERSION, BINLOG_VERSION
194 master-bin.000002 # Gtid_list # # [#-#-#]
195 master-bin.000002 # Gtid # # BEGIN GTID #-#-#
196 master-bin.000002 # Intvar # # INSERT_ID=3
197 master-bin.000002 # Query # # use `test`; INSERT INTO t2 VALUES (NULL, 1)
198 master-bin.000002 # Query # # COMMIT
199 master-bin.000002 # Gtid # # BEGIN GTID #-#-#
200 master-bin.000002 # Intvar # # INSERT_ID=4
201 master-bin.000002 # Query # # use `test`; INSERT INTO t1 VALUES (NULL, 2)
202 master-bin.000002 # Query # # ROLLBACK
203 disconnect con3;
204 connect con3, localhost, root,,;
205 connection con3;
206 IM: INNODB, MyISAM
207 BEGIN;
208 INSERT INTO t1 VALUES (NULL, 3);
209 INSERT INTO t2 VALUES (NULL, 4);
210 connection con1;
211 KILL ID;
212 include/show_binlog_events.inc
213 Log_name Pos Event_type Server_id End_log_pos Info
214 master-bin.000002 # Gtid # # BEGIN GTID #-#-#
215 master-bin.000002 # Intvar # # INSERT_ID=4
216 master-bin.000002 # Query # # use `test`; INSERT INTO t2 VALUES (NULL, 4)
217 master-bin.000002 # Query # # COMMIT
218 master-bin.000002 # Gtid # # BEGIN GTID #-#-#
219 master-bin.000002 # Intvar # # INSERT_ID=5
220 master-bin.000002 # Query # # use `test`; INSERT INTO t1 VALUES (NULL, 3)
221 master-bin.000002 # Query # # ROLLBACK
222 disconnect con3;
223 connect con3, localhost, root,,;
224 connection con3;
225 IMI: INNODB, MyISAM, INNODB
226 BEGIN;
227 INSERT INTO t1 VALUES (NULL, 5);
228 INSERT INTO t2 VALUES (NULL, 6);
229 INSERT INTO t1 VALUES (NULL, 7);
230 connection con1;
231 KILL ID;
232 include/show_binlog_events.inc
233 Log_name Pos Event_type Server_id End_log_pos Info
234 master-bin.000002 # Gtid # # BEGIN GTID #-#-#
235 master-bin.000002 # Intvar # # INSERT_ID=5
236 master-bin.000002 # Query # # use `test`; INSERT INTO t2 VALUES (NULL, 6)
237 master-bin.000002 # Query # # COMMIT
238 master-bin.000002 # Gtid # # BEGIN GTID #-#-#
239 master-bin.000002 # Intvar # # INSERT_ID=6
240 master-bin.000002 # Query # # use `test`; INSERT INTO t1 VALUES (NULL, 5)
241 master-bin.000002 # Intvar # # INSERT_ID=7
242 master-bin.000002 # Query # # use `test`; INSERT INTO t1 VALUES (NULL, 7)
243 master-bin.000002 # Query # # ROLLBACK
244 disconnect con3;
245 connect con3, localhost, root,,;
246 connection con3;
247 MI2: MyISAM, INNODB, MyISAM, INNODB
248 BEGIN;
249 INSERT INTO t2 VALUES (NULL, 8);
250 INSERT INTO t1 VALUES (NULL, 9);
251 INSERT INTO t2 VALUES (NULL, 10);
252 INSERT INTO t1 VALUES (NULL, 11);
253 connection con1;
254 KILL ID;
255 include/show_binlog_events.inc
256 Log_name Pos Event_type Server_id End_log_pos Info
257 master-bin.000002 # Gtid # # BEGIN GTID #-#-#
258 master-bin.000002 # Intvar # # INSERT_ID=6
259 master-bin.000002 # Query # # use `test`; INSERT INTO t2 VALUES (NULL, 8)
260 master-bin.000002 # Query # # COMMIT
261 master-bin.000002 # Gtid # # BEGIN GTID #-#-#
262 master-bin.000002 # Intvar # # INSERT_ID=7
263 master-bin.000002 # Query # # use `test`; INSERT INTO t2 VALUES (NULL, 10)
264 master-bin.000002 # Query # # COMMIT
265 master-bin.000002 # Gtid # # BEGIN GTID #-#-#
266 master-bin.000002 # Intvar # # INSERT_ID=8
267 master-bin.000002 # Query # # use `test`; INSERT INTO t1 VALUES (NULL, 9)
268 master-bin.000002 # Intvar # # INSERT_ID=9
269 master-bin.000002 # Query # # use `test`; INSERT INTO t1 VALUES (NULL, 11)
270 master-bin.000002 # Query # # ROLLBACK
271 connection default;
272 disconnect con1;
273 disconnect con2;
274 drop table t1,t2,t3;
275 end of the tests