Debian Codegraph @

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

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