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