Debian Codegraph @

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