Debian Codegraph @

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

raw
1 call mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT");
2 drop table if exists t1, t2;
3 connect con1,localhost,root,,;
4 connect con2,localhost,root,,;
5 connection con1;
6 create table t1 (a int) engine=innodb;
7 create table t2 (a int) engine=myisam;
8 reset master;
9 begin;
10 insert into t1 values(1);
11 insert into t2 select * from t1;
12 commit;
13 include/show_binlog_events.inc
14 Log_name Pos Event_type Server_id End_log_pos Info
15 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
16 master-bin.000001 # Annotate_rows # # insert into t2 select * from t1
17 master-bin.000001 # Table_map # # table_id: # (test.t2)
18 master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F
19 master-bin.000001 # Query # # COMMIT
20 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
21 master-bin.000001 # Annotate_rows # # insert into t1 values(1)
22 master-bin.000001 # Table_map # # table_id: # (test.t1)
23 master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F
24 master-bin.000001 # Xid # # COMMIT /* XID */
25 delete from t1;
26 delete from t2;
27 reset master;
28 begin;
29 insert into t1 values(2);
30 insert into t2 select * from t1;
31 rollback;
32 Warnings:
33 Warning 1196 Some non-transactional changed tables couldn't be rolled back
34 include/show_binlog_events.inc
35 Log_name Pos Event_type Server_id End_log_pos Info
36 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
37 master-bin.000001 # Annotate_rows # # insert into t2 select * from t1
38 master-bin.000001 # Table_map # # table_id: # (test.t2)
39 master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F
40 master-bin.000001 # Query # # COMMIT
41 delete from t1;
42 delete from t2;
43 reset master;
44 begin;
45 insert into t1 values(3);
46 savepoint my_savepoint;
47 insert into t1 values(4);
48 insert into t2 select * from t1;
49 rollback to savepoint my_savepoint;
50 Warnings:
51 Warning 1196 Some non-transactional changed tables couldn't be rolled back
52 commit;
53 include/show_binlog_events.inc
54 Log_name Pos Event_type Server_id End_log_pos Info
55 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
56 master-bin.000001 # Annotate_rows # # insert into t2 select * from t1
57 master-bin.000001 # Table_map # # table_id: # (test.t2)
58 master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F
59 master-bin.000001 # Query # # COMMIT
60 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
61 master-bin.000001 # Annotate_rows # # insert into t1 values(3)
62 master-bin.000001 # Table_map # # table_id: # (test.t1)
63 master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F
64 master-bin.000001 # Query # # SAVEPOINT `my_savepoint`
65 master-bin.000001 # Annotate_rows # # insert into t1 values(4)
66 master-bin.000001 # Table_map # # table_id: # (test.t1)
67 master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F
68 master-bin.000001 # Query # # ROLLBACK TO `my_savepoint`
69 master-bin.000001 # Xid # # COMMIT /* XID */
70 delete from t1;
71 delete from t2;
72 reset master;
73 begin;
74 insert into t1 values(5);
75 savepoint my_savepoint;
76 insert into t1 values(6);
77 insert into t2 select * from t1;
78 rollback to savepoint my_savepoint;
79 Warnings:
80 Warning 1196 Some non-transactional changed tables couldn't be rolled back
81 insert into t1 values(7);
82 commit;
83 select a from t1 order by a;
84 a
85 5
86 7
87 include/show_binlog_events.inc
88 Log_name Pos Event_type Server_id End_log_pos Info
89 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
90 master-bin.000001 # Annotate_rows # # insert into t2 select * from t1
91 master-bin.000001 # Table_map # # table_id: # (test.t2)
92 master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F
93 master-bin.000001 # Query # # COMMIT
94 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
95 master-bin.000001 # Annotate_rows # # insert into t1 values(5)
96 master-bin.000001 # Table_map # # table_id: # (test.t1)
97 master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F
98 master-bin.000001 # Query # # SAVEPOINT `my_savepoint`
99 master-bin.000001 # Annotate_rows # # insert into t1 values(6)
100 master-bin.000001 # Table_map # # table_id: # (test.t1)
101 master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F
102 master-bin.000001 # Query # # ROLLBACK TO `my_savepoint`
103 master-bin.000001 # Annotate_rows # # insert into t1 values(7)
104 master-bin.000001 # Table_map # # table_id: # (test.t1)
105 master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F
106 master-bin.000001 # Xid # # COMMIT /* XID */
107 delete from t1;
108 delete from t2;
109 reset master;
110 select get_lock("a",10);
111 get_lock("a",10)
112 1
113 begin;
114 insert into t1 values(8);
115 insert into t2 select * from t1;
116 disconnect con1;
117 connection con2;
118 select get_lock("a",10);
119 get_lock("a",10)
120 1
121 include/show_binlog_events.inc
122 Log_name Pos Event_type Server_id End_log_pos Info
123 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
124 master-bin.000001 # Annotate_rows # # insert into t2 select * from t1
125 master-bin.000001 # Table_map # # table_id: # (test.t2)
126 master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F
127 master-bin.000001 # Query # # COMMIT
128 delete from t1;
129 delete from t2;
130 reset master;
131 insert into t1 values(9);
132 insert into t2 select * from t1;
133 include/show_binlog_events.inc
134 Log_name Pos Event_type Server_id End_log_pos Info
135 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
136 master-bin.000001 # Annotate_rows # # insert into t1 values(9)
137 master-bin.000001 # Table_map # # table_id: # (test.t1)
138 master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F
139 master-bin.000001 # Xid # # COMMIT /* XID */
140 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
141 master-bin.000001 # Annotate_rows # # insert into t2 select * from t1
142 master-bin.000001 # Table_map # # table_id: # (test.t2)
143 master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F
144 master-bin.000001 # Query # # COMMIT
145 delete from t1;
146 delete from t2;
147 reset master;
148 insert into t1 values(10);
149 begin;
150 insert into t2 select * from t1;
151 include/show_binlog_events.inc
152 Log_name Pos Event_type Server_id End_log_pos Info
153 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
154 master-bin.000001 # Annotate_rows # # insert into t1 values(10)
155 master-bin.000001 # Table_map # # table_id: # (test.t1)
156 master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F
157 master-bin.000001 # Xid # # COMMIT /* XID */
158 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
159 master-bin.000001 # Annotate_rows # # insert into t2 select * from t1
160 master-bin.000001 # Table_map # # table_id: # (test.t2)
161 master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F
162 master-bin.000001 # Query # # COMMIT
163 insert into t1 values(11);
164 commit;
165 include/show_binlog_events.inc
166 Log_name Pos Event_type Server_id End_log_pos Info
167 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
168 master-bin.000001 # Annotate_rows # # insert into t1 values(10)
169 master-bin.000001 # Table_map # # table_id: # (test.t1)
170 master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F
171 master-bin.000001 # Xid # # COMMIT /* XID */
172 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
173 master-bin.000001 # Annotate_rows # # insert into t2 select * from t1
174 master-bin.000001 # Table_map # # table_id: # (test.t2)
175 master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F
176 master-bin.000001 # Query # # COMMIT
177 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
178 master-bin.000001 # Annotate_rows # # insert into t1 values(11)
179 master-bin.000001 # Table_map # # table_id: # (test.t1)
180 master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F
181 master-bin.000001 # Xid # # COMMIT /* XID */
182 alter table t2 engine=INNODB;
183 delete from t1;
184 delete from t2;
185 reset master;
186 begin;
187 insert into t1 values(12);
188 insert into t2 select * from t1;
189 commit;
190 include/show_binlog_events.inc
191 Log_name Pos Event_type Server_id End_log_pos Info
192 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
193 master-bin.000001 # Annotate_rows # # insert into t1 values(12)
194 master-bin.000001 # Table_map # # table_id: # (test.t1)
195 master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F
196 master-bin.000001 # Annotate_rows # # insert into t2 select * from t1
197 master-bin.000001 # Table_map # # table_id: # (test.t2)
198 master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F
199 master-bin.000001 # Xid # # COMMIT /* XID */
200 delete from t1;
201 delete from t2;
202 reset master;
203 begin;
204 insert into t1 values(13);
205 insert into t2 select * from t1;
206 rollback;
207 include/show_binlog_events.inc
208 delete from t1;
209 delete from t2;
210 reset master;
211 begin;
212 insert into t1 values(14);
213 savepoint my_savepoint;
214 insert into t1 values(15);
215 insert into t2 select * from t1;
216 rollback to savepoint my_savepoint;
217 commit;
218 include/show_binlog_events.inc
219 Log_name Pos Event_type Server_id End_log_pos Info
220 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
221 master-bin.000001 # Annotate_rows # # insert into t1 values(14)
222 master-bin.000001 # Table_map # # table_id: # (test.t1)
223 master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F
224 master-bin.000001 # Query # # SAVEPOINT `my_savepoint`
225 master-bin.000001 # Xid # # COMMIT /* XID */
226 delete from t1;
227 delete from t2;
228 reset master;
229 begin;
230 insert into t1 values(16);
231 savepoint my_savepoint;
232 insert into t1 values(17);
233 insert into t2 select * from t1;
234 rollback to savepoint my_savepoint;
235 insert into t1 values(18);
236 commit;
237 select a from t1 order by a;
238 a
239 16
240 18
241 include/show_binlog_events.inc
242 Log_name Pos Event_type Server_id End_log_pos Info
243 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
244 master-bin.000001 # Annotate_rows # # insert into t1 values(16)
245 master-bin.000001 # Table_map # # table_id: # (test.t1)
246 master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F
247 master-bin.000001 # Query # # SAVEPOINT `my_savepoint`
248 master-bin.000001 # Annotate_rows # # insert into t1 values(18)
249 master-bin.000001 # Table_map # # table_id: # (test.t1)
250 master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F
251 master-bin.000001 # Xid # # COMMIT /* XID */
252 connect con3,localhost,root,,;
253 connection con3;
254 delete from t1;
255 delete from t2;
256 alter table t2 engine=MyISAM;
257 insert into t1 values (1);
258 begin;
259 select * from t1 for update;
260 a
261 1
262 connection con2;
263 select (@before:=unix_timestamp())*0;
264 (@before:=unix_timestamp())*0
265 0
266 begin;
267 select * from t1 for update;
268 connection con3;
269 insert into t2 values (20);
270 connection con2;
271 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
272 select (@after:=unix_timestamp())*0;
273 (@after:=unix_timestamp())*0
274 0
275 select (@after-@before) >= 2;
276 (@after-@before) >= 2
277 1
278 connection con3;
279 commit;
280 connection con2;
281 drop table t1,t2;
282 commit;
283 connection con2;
284 begin;
285 create temporary table ti (a int) engine=innodb;
286 rollback;
287 insert into ti values(1);
288 set autocommit=0;
289 create temporary table t1 (a int) engine=myisam;
290 commit;
291 insert t1 values (1);
292 rollback;
293 Warnings:
294 Warning 1196 Some non-transactional changed tables couldn't be rolled back
295 create table t0 (n int);
296 insert t0 select * from t1;
297 set autocommit=1;
298 insert into t0 select GET_LOCK("lock1",0);
299 set autocommit=0;
300 create table t2 (n int) engine=innodb;
301 insert into t2 values (3);
302 disconnect con2;
303 connection con3;
304 select get_lock("lock1",60);
305 get_lock("lock1",60)
306 1
307 include/show_binlog_events.inc
308 Log_name Pos Event_type Server_id End_log_pos Info
309 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
310 master-bin.000001 # Annotate_rows # # insert into t1 values(16)
311 master-bin.000001 # Table_map # # table_id: # (test.t1)
312 master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F
313 master-bin.000001 # Query # # SAVEPOINT `my_savepoint`
314 master-bin.000001 # Annotate_rows # # insert into t1 values(18)
315 master-bin.000001 # Table_map # # table_id: # (test.t1)
316 master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F
317 master-bin.000001 # Xid # # COMMIT /* XID */
318 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
319 master-bin.000001 # Annotate_rows # # delete from t1
320 master-bin.000001 # Table_map # # table_id: # (test.t1)
321 master-bin.000001 # Delete_rows_v1 # # table_id: # flags: STMT_END_F
322 master-bin.000001 # Xid # # COMMIT /* XID */
323 master-bin.000001 # Gtid # # GTID #-#-#
324 master-bin.000001 # Query # # use `test`; alter table t2 engine=MyISAM
325 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
326 master-bin.000001 # Annotate_rows # # insert into t1 values (1)
327 master-bin.000001 # Table_map # # table_id: # (test.t1)
328 master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F
329 master-bin.000001 # Xid # # COMMIT /* XID */
330 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
331 master-bin.000001 # Annotate_rows # # insert into t2 values (20)
332 master-bin.000001 # Table_map # # table_id: # (test.t2)
333 master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F
334 master-bin.000001 # Query # # COMMIT
335 master-bin.000001 # Gtid # # GTID #-#-#
336 master-bin.000001 # Query # # use `test`; DROP TABLE `t1`,`t2` /* generated by server */
337 master-bin.000001 # Gtid # # GTID #-#-#
338 master-bin.000001 # Query # # use `test`; create table t0 (n int)
339 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
340 master-bin.000001 # Annotate_rows # # insert t0 select * from t1
341 master-bin.000001 # Table_map # # table_id: # (test.t0)
342 master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F
343 master-bin.000001 # Query # # COMMIT
344 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
345 master-bin.000001 # Annotate_rows # # insert into t0 select GET_LOCK("lock1",0)
346 master-bin.000001 # Table_map # # table_id: # (test.t0)
347 master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F
348 master-bin.000001 # Query # # COMMIT
349 master-bin.000001 # Gtid # # GTID #-#-#
350 master-bin.000001 # Query # # use `test`; create table t2 (n int) engine=innodb
351 do release_lock("lock1");
352 drop table t0,t2;
353 set autocommit=0;
354 CREATE TABLE t1 (a int, b int) engine=myisam;
355 reset master;
356 INSERT INTO t1 values (1,1),(1,2);
357 CREATE TABLE t2 (primary key (a)) engine=innodb select * from t1;
358 ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
359 DROP TABLE if exists t2;
360 Warnings:
361 Note 1051 Unknown table 'test.t2'
362 INSERT INTO t1 values (3,3);
363 CREATE TEMPORARY TABLE t2 (primary key (a)) engine=innodb select * from t1;
364 ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
365 ROLLBACK;
366 Warnings:
367 Warning 1196 Some non-transactional changed tables couldn't be rolled back
368 DROP TABLE IF EXISTS t2;
369 Warnings:
370 Note 1051 Unknown table 'test.t2'
371 CREATE TABLE t2 (a int, b int, primary key (a)) engine=innodb;
372 INSERT INTO t1 VALUES (4,4);
373 CREATE TABLE IF NOT EXISTS t2 (primary key (a)) engine=innodb select * from t1;
374 Warnings:
375 Note 1050 Table 't2' already exists
376 SELECT * from t2;
377 a b
378 TRUNCATE table t2;
379 INSERT INTO t1 VALUES (5,5);
380 INSERT INTO t2 select * from t1;
381 ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
382 SELECT * FROM t2;
383 a b
384 DROP TABLE t2;
385 INSERT INTO t1 values (6,6);
386 CREATE TEMPORARY TABLE t2 (a int, b int, primary key (a)) engine=innodb ;
387 INSERT INTO t1 values (7,7);
388 ROLLBACK;
389 Warnings:
390 Warning 1196 Some non-transactional changed tables couldn't be rolled back
391 INSERT INTO t1 values (8,8);
392 CREATE TEMPORARY TABLE IF NOT EXISTS t2 (primary key (a)) engine=innodb select * from t1;
393 Warnings:
394 Note 1050 Table 't2' already exists
395 COMMIT;
396 INSERT INTO t1 values (9,9);
397 CREATE TEMPORARY TABLE IF NOT EXISTS t2 (primary key (a)) engine=innodb select * from t1;
398 Warnings:
399 Note 1050 Table 't2' already exists
400 ROLLBACK;
401 Warnings:
402 Warning 1196 Some non-transactional changed tables couldn't be rolled back
403 SELECT * from t2;
404 a b
405 TRUNCATE table t2;
406 INSERT INTO t1 values (10,10);
407 INSERT INTO t2 select * from t1;
408 ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
409 SELECT * from t1;
410 a b
411 1 1
412 1 2
413 3 3
414 4 4
415 5 5
416 6 6
417 7 7
418 8 8
419 9 9
420 10 10
421 INSERT INTO t2 values (100,100);
422 CREATE TEMPORARY TABLE IF NOT EXISTS t2 (primary key (a)) engine=innodb select * from t1;
423 Warnings:
424 Note 1050 Table 't2' already exists
425 COMMIT;
426 INSERT INTO t2 values (101,101);
427 CREATE TEMPORARY TABLE IF NOT EXISTS t2 (primary key (a)) engine=innodb select * from t1;
428 Warnings:
429 Note 1050 Table 't2' already exists
430 ROLLBACK;
431 SELECT * from t2;
432 a b
433 100 100
434 DROP TABLE t1,t2;
435 include/show_binlog_events.inc
436 Log_name Pos Event_type Server_id End_log_pos Info
437 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
438 master-bin.000001 # Annotate_rows # # INSERT INTO t1 values (1,1),(1,2)
439 master-bin.000001 # Table_map # # table_id: # (test.t1)
440 master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F
441 master-bin.000001 # Query # # COMMIT
442 master-bin.000001 # Gtid # # GTID #-#-#
443 master-bin.000001 # Query # # use `test`; DROP TABLE IF EXISTS `t2` /* generated by server */
444 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
445 master-bin.000001 # Annotate_rows # # INSERT INTO t1 values (3,3)
446 master-bin.000001 # Table_map # # table_id: # (test.t1)
447 master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F
448 master-bin.000001 # Query # # COMMIT
449 master-bin.000001 # Gtid # # GTID #-#-#
450 master-bin.000001 # Query # # use `test`; DROP TABLE IF EXISTS `t2` /* generated by server */
451 master-bin.000001 # Gtid # # GTID #-#-#
452 master-bin.000001 # Query # # use `test`; CREATE TABLE t2 (a int, b int, primary key (a)) engine=innodb
453 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
454 master-bin.000001 # Annotate_rows # # INSERT INTO t1 VALUES (4,4)
455 master-bin.000001 # Table_map # # table_id: # (test.t1)
456 master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F
457 master-bin.000001 # Query # # COMMIT
458 master-bin.000001 # Gtid # # GTID #-#-#
459 master-bin.000001 # Query # # use `test`; TRUNCATE table t2
460 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
461 master-bin.000001 # Annotate_rows # # INSERT INTO t1 VALUES (5,5)
462 master-bin.000001 # Table_map # # table_id: # (test.t1)
463 master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F
464 master-bin.000001 # Query # # COMMIT
465 master-bin.000001 # Gtid # # GTID #-#-#
466 master-bin.000001 # Query # # use `test`; DROP TABLE `t2` /* generated by server */
467 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
468 master-bin.000001 # Annotate_rows # # INSERT INTO t1 values (6,6)
469 master-bin.000001 # Table_map # # table_id: # (test.t1)
470 master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F
471 master-bin.000001 # Query # # COMMIT
472 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
473 master-bin.000001 # Annotate_rows # # INSERT INTO t1 values (7,7)
474 master-bin.000001 # Table_map # # table_id: # (test.t1)
475 master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F
476 master-bin.000001 # Query # # COMMIT
477 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
478 master-bin.000001 # Annotate_rows # # INSERT INTO t1 values (8,8)
479 master-bin.000001 # Table_map # # table_id: # (test.t1)
480 master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F
481 master-bin.000001 # Query # # COMMIT
482 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
483 master-bin.000001 # Annotate_rows # # INSERT INTO t1 values (9,9)
484 master-bin.000001 # Table_map # # table_id: # (test.t1)
485 master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F
486 master-bin.000001 # Query # # COMMIT
487 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
488 master-bin.000001 # Annotate_rows # # INSERT INTO t1 values (10,10)
489 master-bin.000001 # Table_map # # table_id: # (test.t1)
490 master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F
491 master-bin.000001 # Query # # COMMIT
492 master-bin.000001 # Gtid # # GTID #-#-#
493 master-bin.000001 # Query # # use `test`; DROP TABLE `t1` /* generated by server */
494 connect con4,localhost,root,,;
495 connection con3;
496 reset master;
497 create table t1 (a int) engine=innodb;
498 create table t2 (a int) engine=myisam;
499 select get_lock("a",10);
500 get_lock("a",10)
501 1
502 begin;
503 insert into t1 values(8);
504 insert into t2 select * from t1;
505 disconnect con3;
506 connection con4;
507 select get_lock("a",10);
508 get_lock("a",10)
509 1
510 flush logs;
511 There is nothing to roll back; transactional changes are removed from the trans cache.
512 drop table t1, t2;
513 create temporary table tt (a int unique);
514 create table ti (a int) engine=innodb;
515 reset master;
516 begin;
517 insert into ti values (1);
518 insert into ti values (2) ;
519 insert into tt select * from ti;
520 rollback;
521 Warnings:
522 Warning 1196 Some non-transactional changed tables couldn't be rolled back
523 select count(*) from tt /* 2 */;
524 count(*)
525 2
526 include/show_binlog_events.inc
527 select count(*) from ti /* zero */;
528 count(*)
529 0
530 insert into ti select * from tt;
531 select * from ti /* that is what slave would miss - a bug */;
532 a
533 1
534 2
535 delete from ti;
536 delete from tt where a=1;
537 reset master;
538 begin;
539 insert into ti values (1);
540 insert into ti values (2) /* to make the dup error in the following */;
541 insert into tt select * from ti /* one affected and error */;
542 ERROR 23000: Duplicate entry '2' for key 'a'
543 rollback;
544 Warnings:
545 Warning 1196 Some non-transactional changed tables couldn't be rolled back
546 include/show_binlog_events.inc
547 select count(*) from ti /* zero */;
548 count(*)
549 0
550 insert into ti select * from tt;
551 select * from tt /* that is what otherwise slave missed - the bug */;
552 a
553 1
554 2
555 drop table ti, tt;
556 drop function if exists bug27417;
557 drop table if exists t1,t2;
558 CREATE TABLE t1 (a int NOT NULL auto_increment primary key) ENGINE=MyISAM;
559 CREATE TABLE t2 (a int NOT NULL auto_increment, PRIMARY KEY (a));
560 create function bug27417(n int)
561 RETURNS int(11)
562 begin
563 insert into t1 values (null);
564 return n;
565 end|
566 reset master;
567 insert into t2 values (bug27417(1));
568 insert into t2 select bug27417(2);
569 reset master;
570 insert into t2 values (bug27417(2));
571 ERROR 23000: Duplicate entry '2' for key 'PRIMARY'
572 include/show_binlog_events.inc
573 Log_name Pos Event_type Server_id End_log_pos Info
574 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
575 master-bin.000001 # Annotate_rows # # insert into t1 values (null)
576 master-bin.000001 # Table_map # # table_id: # (test.t2)
577 master-bin.000001 # Table_map # # table_id: # (test.t1)
578 master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F
579 master-bin.000001 # Query # # COMMIT
580 /* only (!) with fixes for #23333 will show there is the query */;
581 select count(*) from t1 /* must be 3 */;
582 count(*)
583 3
584 reset master;
585 select count(*) from t2;
586 count(*)
587 2
588 delete from t2 where a=bug27417(3);
589 select count(*) from t2 /* nothing got deleted */;
590 count(*)
591 2
592 include/show_binlog_events.inc
593 Log_name Pos Event_type Server_id End_log_pos Info
594 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
595 master-bin.000001 # Annotate_rows # # insert into t1 values (null)
596 master-bin.000001 # Table_map # # table_id: # (test.t2)
597 master-bin.000001 # Table_map # # table_id: # (test.t1)
598 master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F
599 master-bin.000001 # Query # # COMMIT
600 /* the query must be in regardless of #23333 */;
601 select count(*) from t1 /* must be 5 */;
602 count(*)
603 5
604 delete t2 from t2 where t2.a=bug27417(100) /* must not affect t2 */;
605 affected rows: 0
606 select count(*) from t1 /* must be 7 */;
607 count(*)
608 7
609 drop table t1,t2;
610 CREATE TABLE t1 (a int NOT NULL auto_increment primary key) ENGINE=MyISAM;
611 CREATE TABLE t2 (a int, PRIMARY KEY (a)) ENGINE=InnoDB;
612 CREATE TABLE t3 (a int, PRIMARY KEY (a), b int unique) ENGINE=MyISAM;
613 CREATE TABLE t4 (a int, PRIMARY KEY (a), b int unique) ENGINE=Innodb;
614 CREATE TABLE t5 (a int, PRIMARY KEY (a)) ENGINE=InnoDB;
615 insert into t2 values (1);
616 reset master;
617 insert into t2 values (bug27417(1));
618 ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
619 include/show_binlog_events.inc
620 Log_name Pos Event_type Server_id End_log_pos Info
621 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
622 master-bin.000001 # Annotate_rows # # insert into t1 values (null)
623 master-bin.000001 # Table_map # # table_id: # (test.t1)
624 master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F
625 master-bin.000001 # Query # # COMMIT
626 /* the output must denote there is the query */;
627 select count(*) from t1 /* must be 1 */;
628 count(*)
629 1
630 delete from t1;
631 delete from t2;
632 insert into t2 values (2);
633 reset master;
634 insert into t2 select bug27417(1) union select bug27417(2);
635 ERROR 23000: Duplicate entry '2' for key 'PRIMARY'
636 include/show_binlog_events.inc
637 Log_name Pos Event_type Server_id End_log_pos Info
638 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
639 master-bin.000001 # Annotate_rows # # insert into t1 values (null)
640 master-bin.000001 # Table_map # # table_id: # (test.t1)
641 master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F
642 master-bin.000001 # Query # # COMMIT
643 /* the output must denote there is the query */;
644 select count(*) from t1 /* must be 2 */;
645 count(*)
646 2
647 delete from t1;
648 insert into t3 values (1,1),(2,3),(3,4);
649 reset master;
650 update t3 set b=b+bug27417(1);
651 ERROR 23000: Duplicate entry '4' for key 'b'
652 include/show_binlog_events.inc
653 Log_name Pos Event_type Server_id End_log_pos Info
654 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
655 master-bin.000001 # Annotate_rows # # insert into t1 values (null)
656 master-bin.000001 # Table_map # # table_id: # (test.t3)
657 master-bin.000001 # Table_map # # table_id: # (test.t1)
658 master-bin.000001 # Write_rows_v1 # # table_id: #
659 master-bin.000001 # Update_rows_v1 # # table_id: #
660 master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F
661 master-bin.000001 # Query # # COMMIT
662 /* the output must denote there is the query */;
663 select count(*) from t1 /* must be 2 */;
664 count(*)
665 2
666 delete from t3;
667 delete from t4;
668 insert into t3 values (1,1);
669 insert into t4 values (1,1),(2,2);
670 reset master;
671 UPDATE t4,t3 SET t4.a=t3.a + bug27417(1) /* top level non-ta table */;
672 ERROR 23000: Duplicate entry '2' for key 'PRIMARY'
673 include/show_binlog_events.inc
674 Log_name Pos Event_type Server_id End_log_pos Info
675 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
676 master-bin.000001 # Annotate_rows # # insert into t1 values (null)
677 master-bin.000001 # Table_map # # table_id: # (test.t1)
678 master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F
679 master-bin.000001 # Query # # COMMIT
680 /* the output must denote there is the query */;
681 select count(*) from t1 /* must be 4 */;
682 count(*)
683 4
684 delete from t1;
685 delete from t3;
686 delete from t4;
687 insert into t3 values (1,1),(2,2);
688 insert into t4 values (1,1),(2,2);
689 reset master;
690 UPDATE t3,t4 SET t3.a=t4.a + bug27417(1);
691 ERROR 23000: Duplicate entry '2' for key 'PRIMARY'
692 select count(*) from t1 /* must be 1 */;
693 count(*)
694 2
695 drop table t4;
696 delete from t1;
697 delete from t2;
698 delete from t3;
699 insert into t2 values (1);
700 insert into t3 values (1,1);
701 create trigger trg_del before delete on t2 for each row
702 insert into t3 values (bug27417(1), 2);
703 reset master;
704 delete from t2;
705 ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
706 include/show_binlog_events.inc
707 Log_name Pos Event_type Server_id End_log_pos Info
708 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
709 master-bin.000001 # Annotate_rows # # insert into t1 values (null)
710 master-bin.000001 # Table_map # # table_id: # (test.t3)
711 master-bin.000001 # Table_map # # table_id: # (test.t1)
712 master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F
713 master-bin.000001 # Query # # COMMIT
714 /* the output must denote there is the query */;
715 select count(*) from t1 /* must be 1 */;
716 count(*)
717 1
718 drop trigger trg_del;
719 delete from t1;
720 delete from t2;
721 delete from t5;
722 create trigger trg_del_t2 after delete on t2 for each row
723 insert into t1 values (1);
724 insert into t2 values (2),(3);
725 insert into t5 values (1),(2);
726 reset master;
727 delete t2.* from t2,t5 where t2.a=t5.a + 1;
728 ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
729 include/show_binlog_events.inc
730 Log_name Pos Event_type Server_id End_log_pos Info
731 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
732 master-bin.000001 # Annotate_rows # # delete t2.* from t2,t5 where t2.a=t5.a + 1
733 master-bin.000001 # Table_map # # table_id: # (test.t1)
734 master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F
735 master-bin.000001 # Query # # COMMIT
736 /* the output must denote there is the query */;
737 select count(*) from t1 /* must be 1 */;
738 count(*)
739 1
740 delete from t1;
741 create table t4 (a int default 0, b int primary key) engine=innodb;
742 insert into t4 values (0, 17);
743 reset master;
744 load data infile '../../std_data/rpl_loaddata.dat' into table t4 (a, @b) set b= @b + bug27417(2);
745 ERROR 23000: Duplicate entry '17' for key 'PRIMARY'
746 select * from t4;
747 a b
748 0 17
749 select count(*) from t1 /* must be 2 */;
750 count(*)
751 2
752 include/show_binlog_events.inc
753 Log_name Pos Event_type Server_id End_log_pos Info
754 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
755 master-bin.000001 # Annotate_rows # # insert into t1 values (null)
756 master-bin.000001 # Table_map # # table_id: # (test.t1)
757 master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F
758 master-bin.000001 # Query # # COMMIT
759 /* the output must denote there is the query */;
760 drop trigger trg_del_t2;
761 drop table t1,t2,t3,t4,t5;
762 drop function bug27417;
763 end of tests
764 create temporary table tt (a int unique);
765 create table ti (a int) engine=innodb;
766 reset master;
767 begin;
768 insert into ti values (1);
769 insert into ti values (2) ;
770 insert into tt select * from ti;
771 rollback;
772 Warnings:
773 Warning 1196 Some non-transactional changed tables couldn't be rolled back
774 select count(*) from tt /* 2 */;
775 count(*)
776 2
777 include/show_binlog_events.inc
778 select count(*) from ti /* zero */;
779 count(*)
780 0
781 insert into ti select * from tt;
782 select * from ti /* that is what slave would miss - bug#28960 */;
783 a
784 1
785 2
786 delete from ti;
787 delete from tt where a=1;
788 reset master;
789 begin;
790 insert into ti values (1);
791 insert into ti values (2) /* to make the dup error in the following */;
792 insert into tt select * from ti /* one affected and error */;
793 ERROR 23000: Duplicate entry '2' for key 'a'
794 rollback;
795 Warnings:
796 Warning 1196 Some non-transactional changed tables couldn't be rolled back
797 include/show_binlog_events.inc
798 select count(*) from ti /* zero */;
799 count(*)
800 0
801 insert into ti select * from tt;
802 select * from tt /* that is what otherwise slave missed - the bug */;
803 a
804 1
805 2
806 drop table ti;
807 drop function if exists bug27417;
808 drop table if exists t1,t2;
809 CREATE TABLE t1 (a int NOT NULL auto_increment primary key) ENGINE=MyISAM;
810 CREATE TABLE t2 (a int NOT NULL auto_increment, PRIMARY KEY (a));
811 create function bug27417(n int)
812 RETURNS int(11)
813 begin
814 insert into t1 values (null);
815 return n;
816 end|
817 reset master;
818 insert into t2 values (bug27417(1));
819 insert into t2 select bug27417(2);
820 reset master;
821 insert into t2 values (bug27417(2));
822 ERROR 23000: Duplicate entry '2' for key 'PRIMARY'
823 include/show_binlog_events.inc
824 Log_name Pos Event_type Server_id End_log_pos Info
825 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
826 master-bin.000001 # Annotate_rows # # insert into t1 values (null)
827 master-bin.000001 # Table_map # # table_id: # (test.t2)
828 master-bin.000001 # Table_map # # table_id: # (test.t1)
829 master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F
830 master-bin.000001 # Query # # COMMIT
831 select count(*) from t1 /* must be 3 */;
832 count(*)
833 3
834 reset master;
835 select count(*) from t2;
836 count(*)
837 2
838 delete from t2 where a=bug27417(3);
839 select count(*) from t2 /* nothing got deleted */;
840 count(*)
841 2
842 include/show_binlog_events.inc
843 Log_name Pos Event_type Server_id End_log_pos Info
844 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
845 master-bin.000001 # Annotate_rows # # insert into t1 values (null)
846 master-bin.000001 # Table_map # # table_id: # (test.t2)
847 master-bin.000001 # Table_map # # table_id: # (test.t1)
848 master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F
849 master-bin.000001 # Query # # COMMIT
850 select count(*) from t1 /* must be 5 */;
851 count(*)
852 5
853 delete t2 from t2 where t2.a=bug27417(100) /* must not affect t2 */;
854 affected rows: 0
855 select count(*) from t1 /* must be 7 */;
856 count(*)
857 7
858 drop table t1,t2;
859 CREATE TABLE t1 (a int NOT NULL auto_increment primary key) ENGINE=MyISAM;
860 CREATE TABLE t2 (a int, PRIMARY KEY (a)) ENGINE=InnoDB;
861 CREATE TABLE t3 (a int, PRIMARY KEY (a), b int unique) ENGINE=MyISAM;
862 CREATE TABLE t4 (a int, PRIMARY KEY (a), b int unique) ENGINE=Innodb;
863 CREATE TABLE t5 (a int, PRIMARY KEY (a)) ENGINE=InnoDB;
864 insert into t2 values (1);
865 reset master;
866 insert into t2 values (bug27417(1));
867 ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
868 include/show_binlog_events.inc
869 Log_name Pos Event_type Server_id End_log_pos Info
870 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
871 master-bin.000001 # Annotate_rows # # insert into t1 values (null)
872 master-bin.000001 # Table_map # # table_id: # (test.t1)
873 master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F
874 master-bin.000001 # Query # # COMMIT
875 select count(*) from t1 /* must be 1 */;
876 count(*)
877 1
878 delete from t1;
879 delete from t2;
880 insert into t2 values (2);
881 reset master;
882 insert into t2 select bug27417(1) union select bug27417(2);
883 ERROR 23000: Duplicate entry '2' for key 'PRIMARY'
884 include/show_binlog_events.inc
885 Log_name Pos Event_type Server_id End_log_pos Info
886 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
887 master-bin.000001 # Annotate_rows # # insert into t1 values (null)
888 master-bin.000001 # Table_map # # table_id: # (test.t1)
889 master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F
890 master-bin.000001 # Query # # COMMIT
891 select count(*) from t1 /* must be 2 */;
892 count(*)
893 2
894 delete from t1;
895 insert into t3 values (1,1),(2,3),(3,4);
896 reset master;
897 update t3 set b=b+bug27417(1);
898 ERROR 23000: Duplicate entry '4' for key 'b'
899 include/show_binlog_events.inc
900 Log_name Pos Event_type Server_id End_log_pos Info
901 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
902 master-bin.000001 # Annotate_rows # # insert into t1 values (null)
903 master-bin.000001 # Table_map # # table_id: # (test.t3)
904 master-bin.000001 # Table_map # # table_id: # (test.t1)
905 master-bin.000001 # Write_rows_v1 # # table_id: #
906 master-bin.000001 # Update_rows_v1 # # table_id: #
907 master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F
908 master-bin.000001 # Query # # COMMIT
909 select count(*) from t1 /* must be 2 */;
910 count(*)
911 2
912 delete from t3;
913 delete from t4;
914 insert into t3 values (1,1);
915 insert into t4 values (1,1),(2,2);
916 reset master;
917 UPDATE t4,t3 SET t4.a=t3.a + bug27417(1) /* top level non-ta table */;
918 ERROR 23000: Duplicate entry '2' for key 'PRIMARY'
919 include/show_binlog_events.inc
920 Log_name Pos Event_type Server_id End_log_pos Info
921 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
922 master-bin.000001 # Annotate_rows # # insert into t1 values (null)
923 master-bin.000001 # Table_map # # table_id: # (test.t1)
924 master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F
925 master-bin.000001 # Query # # COMMIT
926 select count(*) from t1 /* must be 4 */;
927 count(*)
928 4
929 delete from t1;
930 delete from t3;
931 delete from t4;
932 insert into t3 values (1,1),(2,2);
933 insert into t4 values (1,1),(2,2);
934 reset master;
935 UPDATE t3,t4 SET t3.a = t4.a + bug27417(1) where t3.a = 1;
936 ERROR 23000: Duplicate entry '2' for key 'PRIMARY'
937 select count(*) from t1 /* must be 1 */;
938 count(*)
939 1
940 drop table t4;
941 delete from t1;
942 delete from t2;
943 delete from t3;
944 insert into t2 values (1);
945 insert into t3 values (1,1);
946 create trigger trg_del before delete on t2 for each row
947 insert into t3 values (bug27417(1), 2);
948 reset master;
949 delete from t2;
950 ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
951 include/show_binlog_events.inc
952 Log_name Pos Event_type Server_id End_log_pos Info
953 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
954 master-bin.000001 # Annotate_rows # # insert into t1 values (null)
955 master-bin.000001 # Table_map # # table_id: # (test.t3)
956 master-bin.000001 # Table_map # # table_id: # (test.t1)
957 master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F
958 master-bin.000001 # Query # # COMMIT
959 select count(*) from t1 /* must be 1 */;
960 count(*)
961 1
962 drop trigger trg_del;
963 delete from t1;
964 delete from t2;
965 delete from t5;
966 create trigger trg_del_t2 after delete on t2 for each row
967 insert into t1 values (1);
968 insert into t2 values (2),(3);
969 insert into t5 values (1),(2);
970 reset master;
971 delete t2.* from t2,t5 where t2.a=t5.a + 1;
972 ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
973 include/show_binlog_events.inc
974 Log_name Pos Event_type Server_id End_log_pos Info
975 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
976 master-bin.000001 # Annotate_rows # # delete t2.* from t2,t5 where t2.a=t5.a + 1
977 master-bin.000001 # Table_map # # table_id: # (test.t1)
978 master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F
979 master-bin.000001 # Query # # COMMIT
980 select count(*) from t1 /* must be 1 */;
981 count(*)
982 1
983 delete from t1;
984 create table t4 (a int default 0, b int primary key) engine=innodb;
985 insert into t4 values (0, 17);
986 reset master;
987 load data infile '../../std_data/rpl_loaddata.dat' into table t4 (a, @b) set b= @b + bug27417(2);
988 ERROR 23000: Duplicate entry '17' for key 'PRIMARY'
989 select * from t4;
990 a b
991 0 17
992 select count(*) from t1 /* must be 2 */;
993 count(*)
994 2
995 include/show_binlog_events.inc
996 Log_name Pos Event_type Server_id End_log_pos Info
997 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
998 master-bin.000001 # Annotate_rows # # insert into t1 values (null)
999 master-bin.000001 # Table_map # # table_id: # (test.t1)
1000 master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F
1001 master-bin.000001 # Query # # COMMIT
1002 drop trigger trg_del_t2;
1003 drop table t1,t2,t3,t4,t5;
1004 drop function bug27417;