Debian Codegraph @

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