Debian Codegraph @

mysql-test/suite/binlog/include/mix_innodb_myisam_binlog.test

raw
1 # Check that binlog is ok when a transaction mixes updates to InnoDB and
2 # MyISAM.
3 # It would be nice to make this a replication test, but in 4.0 the
4 # slave is always with --skip-innodb in the testsuite. I (Guilhem) however
5 # did some tests manually on a slave; tables are replicated fine and
6 # Exec_Master_Log_Pos advances as expected.
7
8 -- source include/have_log_bin.inc
9 -- source include/have_innodb.inc
10
11 call mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT");
12
13 --disable_warnings
14 drop table if exists t1, t2;
15 --enable_warnings
16
17 connect (con1,localhost,root,,);
18 connect (con2,localhost,root,,);
19
20 connection con1;
21 create table t1 (a int) engine=innodb;
22 create table t2 (a int) engine=myisam;
23
24 reset master;
25
26 begin;
27 insert into t1 values(1);
28 insert into t2 select * from t1;
29 commit;
30
31 source include/show_binlog_events.inc;
32
33 delete from t1;
34 delete from t2;
35 reset master;
36
37 begin;
38 insert into t1 values(2);
39 insert into t2 select * from t1;
40 # should say some changes to non-transact1onal tables couldn't be rolled back
41 rollback;
42
43 source include/show_binlog_events.inc;
44
45 delete from t1;
46 delete from t2;
47 reset master;
48
49 begin;
50 insert into t1 values(3);
51 savepoint my_savepoint;
52 insert into t1 values(4);
53 insert into t2 select * from t1;
54 rollback to savepoint my_savepoint;
55 commit;
56
57 source include/show_binlog_events.inc;
58
59 delete from t1;
60 delete from t2;
61 reset master;
62
63 begin;
64 insert into t1 values(5);
65 savepoint my_savepoint;
66 insert into t1 values(6);
67 insert into t2 select * from t1;
68 rollback to savepoint my_savepoint;
69 insert into t1 values(7);
70 commit;
71 select a from t1 order by a; # check that savepoints work :)
72
73 source include/show_binlog_events.inc;
74
75 # and when ROLLBACK is not explicit?
76 delete from t1;
77 delete from t2;
78 reset master;
79
80 select get_lock("a",10);
81 begin;
82 insert into t1 values(8);
83 insert into t2 select * from t1;
84 disconnect con1;
85
86 connection con2;
87 # We want to SHOW BINLOG EVENTS, to know what was logged. But there is no
88 # guarantee that logging of the terminated con1 has been done yet (it may not
89 # even be started, so con1 may have not even attempted to lock the binlog yet;
90 # so SHOW BINLOG EVENTS may come before con1 does the loggin. To be sure that
91 # logging has been done, we use a user lock.
92 select get_lock("a",10);
93 source include/show_binlog_events.inc;
94
95 # and when not in a transact1on?
96 delete from t1;
97 delete from t2;
98 reset master;
99
100 insert into t1 values(9);
101 insert into t2 select * from t1;
102
103 source include/show_binlog_events.inc;
104
105 # Check that when the query updat1ng the MyISAM table is the first in the
106 # transaction, we log it immediately.
107 delete from t1;
108 delete from t2;
109 reset master;
110
111 insert into t1 values(10); # first make t1 non-empty
112 begin;
113 insert into t2 select * from t1;
114 source include/show_binlog_events.inc;
115 insert into t1 values(11);
116 commit;
117
118 source include/show_binlog_events.inc;
119
120 # Check that things work like before this BEGIN/ROLLBACK code was added,
121 # when t2 is INNODB
122
123 alter table t2 engine=INNODB;
124
125 delete from t1;
126 delete from t2;
127 reset master;
128
129 begin;
130 insert into t1 values(12);
131 insert into t2 select * from t1;
132 commit;
133
134 source include/show_binlog_events.inc;
135
136 delete from t1;
137 delete from t2;
138 reset master;
139
140 begin;
141 insert into t1 values(13);
142 insert into t2 select * from t1;
143 rollback;
144
145 source include/show_binlog_events.inc;
146
147 delete from t1;
148 delete from t2;
149 reset master;
150
151 begin;
152 insert into t1 values(14);
153 savepoint my_savepoint;
154 insert into t1 values(15);
155 insert into t2 select * from t1;
156 rollback to savepoint my_savepoint;
157 commit;
158
159 source include/show_binlog_events.inc;
160
161 delete from t1;
162 delete from t2;
163 reset master;
164
165 begin;
166 insert into t1 values(16);
167 savepoint my_savepoint;
168 insert into t1 values(17);
169 insert into t2 select * from t1;
170 rollback to savepoint my_savepoint;
171 insert into t1 values(18);
172 commit;
173 select a from t1 order by a; # check that savepoints work :)
174
175 source include/show_binlog_events.inc;
176
177 # Test for BUG#5714, where a MyISAM update in the transaction used to
178 # release row-level locks in InnoDB
179
180 connect (con3,localhost,root,,);
181
182 connection con3;
183 delete from t1;
184 delete from t2;
185 --disable_warnings
186 alter table t2 engine=MyISAM;
187 --enable_warnings
188 insert into t1 values (1);
189 begin;
190 select * from t1 for update;
191
192 connection con2;
193 select (@before:=unix_timestamp())*0; # always give repeatable output
194 begin;
195 send select * from t1 for update;
196
197 connection con3;
198 insert into t2 values (20);
199
200 connection con2;
201 --error 1205
202 reap;
203 select (@after:=unix_timestamp())*0; # always give repeatable output
204 # verify that innodb_lock_wait_timeout was exceeded. When there was
205 # the bug, the reap would return immediately after the insert into t2.
206 select (@after-@before) >= 2;
207
208 connection con3;
209 commit;
210
211 connection con2;
212 drop table t1,t2;
213 commit;
214
215 # test for BUG#7947 - DO RELEASE_LOCK() not written to binlog on rollback in
216 # the middle of a transaction
217
218 connection con2;
219 begin;
220 create temporary table ti (a int) engine=innodb;
221 rollback;
222 insert into ti values(1);
223 set autocommit=0;
224 create temporary table t1 (a int) engine=myisam;
225 commit;
226 insert t1 values (1);
227 rollback;
228 create table t0 (n int);
229 insert t0 select * from t1;
230 set autocommit=1;
231 insert into t0 select GET_LOCK("lock1",0);
232 set autocommit=0;
233 create table t2 (n int) engine=innodb;
234 insert into t2 values (3);
235 disconnect con2;
236 connection con3;
237 select get_lock("lock1",60);
238 source include/show_binlog_events.inc;
239 do release_lock("lock1");
240 drop table t0,t2;
241
242 # End of 4.1 tests
243
244 #
245 # Test behaviour of CREATE ... SELECT when mixing MyISAM and InnoDB tables
246 #
247
248 set autocommit=0;
249 CREATE TABLE t1 (a int, b int) engine=myisam;
250 reset master;
251 INSERT INTO t1 values (1,1),(1,2);
252 --error ER_DUP_ENTRY
253 CREATE TABLE t2 (primary key (a)) engine=innodb select * from t1;
254 # This should give warning
255 DROP TABLE if exists t2;
256 INSERT INTO t1 values (3,3);
257 --error ER_DUP_ENTRY
258 CREATE TEMPORARY TABLE t2 (primary key (a)) engine=innodb select * from t1;
259 ROLLBACK;
260 # This should give warning
261 DROP TABLE IF EXISTS t2;
262
263 CREATE TABLE t2 (a int, b int, primary key (a)) engine=innodb;
264 INSERT INTO t1 VALUES (4,4);
265 CREATE TABLE IF NOT EXISTS t2 (primary key (a)) engine=innodb select * from t1;
266 SELECT * from t2;
267 TRUNCATE table t2;
268 INSERT INTO t1 VALUES (5,5);
269 --error ER_DUP_ENTRY
270 INSERT INTO t2 select * from t1;
271 SELECT * FROM t2;
272 DROP TABLE t2;
273
274 INSERT INTO t1 values (6,6);
275 CREATE TEMPORARY TABLE t2 (a int, b int, primary key (a)) engine=innodb ;
276 INSERT INTO t1 values (7,7);
277 ROLLBACK;
278 INSERT INTO t1 values (8,8);
279 CREATE TEMPORARY TABLE IF NOT EXISTS t2 (primary key (a)) engine=innodb select * from t1;
280 COMMIT;
281 INSERT INTO t1 values (9,9);
282 CREATE TEMPORARY TABLE IF NOT EXISTS t2 (primary key (a)) engine=innodb select * from t1;
283 ROLLBACK;
284 SELECT * from t2;
285 TRUNCATE table t2;
286 INSERT INTO t1 values (10,10);
287 --error ER_DUP_ENTRY
288 INSERT INTO t2 select * from t1;
289 SELECT * from t1;
290 INSERT INTO t2 values (100,100);
291 CREATE TEMPORARY TABLE IF NOT EXISTS t2 (primary key (a)) engine=innodb select * from t1;
292 COMMIT;
293 INSERT INTO t2 values (101,101);
294 CREATE TEMPORARY TABLE IF NOT EXISTS t2 (primary key (a)) engine=innodb select * from t1;
295 ROLLBACK;
296 SELECT * from t2;
297 DROP TABLE t1,t2;
298 source include/show_binlog_events.inc;
299
300 # Test for BUG#16559 (ROLLBACK should always have a zero error code in
301 # binlog). Has to be here and not earlier, as the SELECTs influence
302 # XIDs differently between normal and ps-protocol (and SHOW BINLOG
303 # EVENTS above read XIDs).
304
305 connect (con4,localhost,root,,);
306 connection con3;
307 reset master;
308 create table t1 (a int) engine=innodb;
309 create table t2 (a int) engine=myisam;
310 select get_lock("a",10);
311 begin;
312 insert into t1 values(8);
313 insert into t2 select * from t1;
314
315 disconnect con3;
316
317 connection con4;
318 select get_lock("a",10); # wait for rollback to finish
319 if (`select @@binlog_format = 'STATEMENT' || @@binlog_format = 'MIXED'`)
320 {
321 --let $binlog_rollback= query_get_value(SHOW BINLOG EVENTS, Pos, 11)
322 --let $binlog_query= query_get_value(SHOW BINLOG EVENTS, Info, 11)
323 if ($binlog_query != ROLLBACK) {
324 --echo Wrong query from SHOW BINLOG EVENTS. Expected ROLLBACK, got '$binlog_query'
325 --source include/show_rpl_debug_info.inc
326 --die Wrong value for slave parameter
327 }
328 }
329 flush logs;
330
331 let $MYSQLD_DATADIR= `select @@datadir`;
332 # we check that the error code of the "ROLLBACK" event is 0 and not
333 # ER_SERVER_SHUTDOWN (i.e. disconnection just rolls back transaction
334 # and does not make slave to stop)
335
336 -- source include/binlog_start_pos.inc
337
338 if (`select @@binlog_format = 'ROW'`)
339 {
340 --echo There is nothing to roll back; transactional changes are removed from the trans cache.
341 }
342
343 if (`select @@binlog_format = 'STATEMENT' || @@binlog_format = 'MIXED'`)
344 {
345 --exec $MYSQL_BINLOG --start-position=$binlog_rollback $MYSQLD_DATADIR/master-bin.000001 > $MYSQLTEST_VARDIR/tmp/mix_innodb_myisam_binlog.output
346
347 --replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
348 eval select
349 (@a:=load_file("$MYSQLTEST_VARDIR/tmp/mix_innodb_myisam_binlog.output"))
350 is not null;
351 --replace_result $MYSQL_TEST_DIR MYSQL_TEST_DIR
352 eval select
353 @a like "%#%error_code=0%ROLLBACK\\n/*!*/;%ROLLBACK /* added by mysqlbinlog */;%" OR
354 @a like "%#%error_code=0%ROLLBACK\\r\\n/*!*/;%ROLLBACK /* added by mysqlbinlog */;%",
355 @a not like "%#%error_code=%error_code=%";
356 }
357 drop table t1, t2;
358
359 #
360 # Bug #27417 thd->no_trans_update.stmt lost value inside of SF-exec-stack
361 # bug #28960 non-trans temp table changes with insert .. select
362 # not binlogged after rollback
363 #
364 # testing appearence of insert into temp_table in binlog.
365 # There are two branches of execution that require different setup.
366
367 ## send_eof() branch
368
369 # prepare
370
371 create temporary table tt (a int unique);
372 create table ti (a int) engine=innodb;
373 reset master;
374
375 # action
376
377 begin;
378 insert into ti values (1);
379 insert into ti values (2) ;
380 # This is SAFE because --binlog-direct-non-transactional-updates=FALSE
381 insert into tt select * from ti;
382 rollback;
383
384 # check
385
386 select count(*) from tt /* 2 */;
387 source include/show_binlog_events.inc;
388 select count(*) from ti /* zero */;
389 insert into ti select * from tt;
390 select * from ti /* that is what slave would miss - a bug */;
391
392
393 ## send_error() branch
394 delete from ti;
395 delete from tt where a=1;
396 reset master;
397
398 # action
399
400 begin;
401 insert into ti values (1);
402 insert into ti values (2) /* to make the dup error in the following */;
403 --error ER_DUP_ENTRY
404 insert into tt select * from ti /* one affected and error */;
405 rollback;
406
407 # check
408
409 source include/show_binlog_events.inc;
410 select count(*) from ti /* zero */;
411 insert into ti select * from tt;
412 select * from tt /* that is what otherwise slave missed - the bug */;
413
414 drop table ti, tt;
415
416
417 #
418 # Bug #27417 thd->no_trans_update.stmt lost value inside of SF-exec-stack
419 #
420 # Testing asserts: if there is a side effect of modifying non-transactional
421 # table thd->no_trans_update.stmt must be TRUE;
422 # the assert is active with debug build
423 #
424
425 --disable_warnings
426 drop function if exists bug27417;
427 drop table if exists t1,t2;
428 --enable_warnings
429 # side effect table
430 CREATE TABLE t1 (a int NOT NULL auto_increment primary key) ENGINE=MyISAM;
431 # target tables
432 CREATE TABLE t2 (a int NOT NULL auto_increment, PRIMARY KEY (a));
433
434 delimiter |;
435 create function bug27417(n int)
436 RETURNS int(11)
437 begin
438 insert into t1 values (null);
439 return n;
440 end|
441 delimiter ;|
442
443 reset master;
444
445 # execute
446
447 insert into t2 values (bug27417(1));
448 insert into t2 select bug27417(2);
449 reset master;
450
451 --error ER_DUP_ENTRY
452 insert into t2 values (bug27417(2));
453 source include/show_binlog_events.inc; /* only (!) with fixes for #23333 will show there is the query */;
454 select count(*) from t1 /* must be 3 */;
455
456 reset master;
457 select count(*) from t2;
458 delete from t2 where a=bug27417(3);
459 select count(*) from t2 /* nothing got deleted */;
460 source include/show_binlog_events.inc; /* the query must be in regardless of #23333 */;
461 select count(*) from t1 /* must be 5 */;
462
463 --enable_info
464 delete t2 from t2 where t2.a=bug27417(100) /* must not affect t2 */;
465 --disable_info
466 select count(*) from t1 /* must be 7 */;
467
468 # function bug27417 remains for the following testing of bug#23333
469 drop table t1,t2;
470
471 #
472 # Bug#23333 using the patch (and the test) for bug#27471
473 #
474 # throughout the bug tests
475 # t1 - non-trans side effects gatherer;
476 # t2 - transactional table;
477 #
478
479 CREATE TABLE t1 (a int NOT NULL auto_increment primary key) ENGINE=MyISAM;
480 CREATE TABLE t2 (a int, PRIMARY KEY (a)) ENGINE=InnoDB;
481 CREATE TABLE t3 (a int, PRIMARY KEY (a), b int unique) ENGINE=MyISAM;
482 CREATE TABLE t4 (a int, PRIMARY KEY (a), b int unique) ENGINE=Innodb;
483 CREATE TABLE t5 (a int, PRIMARY KEY (a)) ENGINE=InnoDB;
484
485
486 #
487 # INSERT
488 #
489
490 # prepare
491
492 insert into t2 values (1);
493 reset master;
494
495 # execute
496
497 --error ER_DUP_ENTRY
498 insert into t2 values (bug27417(1));
499
500 # check
501
502 source include/show_binlog_events.inc; /* the output must denote there is the query */;
503 select count(*) from t1 /* must be 1 */;
504
505 #
506 # INSERT SELECT
507 #
508
509 # prepare
510 delete from t1;
511 delete from t2;
512 insert into t2 values (2);
513 reset master;
514
515 # execute
516
517 --error ER_DUP_ENTRY
518 insert into t2 select bug27417(1) union select bug27417(2);
519
520 # check
521
522 source include/show_binlog_events.inc; /* the output must denote there is the query */;
523 select count(*) from t1 /* must be 2 */;
524
525 #
526 # UPDATE inc multi-update
527 #
528
529 # prepare
530 delete from t1;
531 insert into t3 values (1,1),(2,3),(3,4);
532 reset master;
533
534 # execute
535 --error ER_DUP_ENTRY
536 update t3 set b=b+bug27417(1);
537
538 # check
539 source include/show_binlog_events.inc; /* the output must denote there is the query */;
540 select count(*) from t1 /* must be 2 */;
541
542 ## multi_update::send_eof() branch
543
544 # prepare
545 delete from t3;
546 delete from t4;
547 insert into t3 values (1,1);
548 insert into t4 values (1,1),(2,2);
549
550 reset master;
551
552 # execute
553 --error ER_DUP_ENTRY
554 UPDATE t4,t3 SET t4.a=t3.a + bug27417(1) /* top level non-ta table */;
555
556 # check
557 source include/show_binlog_events.inc; /* the output must denote there is the query */;
558 select count(*) from t1 /* must be 4 */;
559
560 ## send_error() branch of multi_update
561
562 # prepare
563 delete from t1;
564 delete from t3;
565 delete from t4;
566 insert into t3 values (1,1),(2,2);
567 insert into t4 values (1,1),(2,2);
568
569 reset master;
570
571 # execute
572 --error ER_DUP_ENTRY
573 UPDATE t3,t4 SET t3.a=t4.a + bug27417(1);
574
575 # check
576 select count(*) from t1 /* must be 1 */;
577
578 # cleanup
579 drop table t4;
580
581
582 #
583 # DELETE incl multi-delete
584 #
585
586 # prepare
587 delete from t1;
588 delete from t2;
589 delete from t3;
590 insert into t2 values (1);
591 insert into t3 values (1,1);
592 create trigger trg_del before delete on t2 for each row
593 insert into t3 values (bug27417(1), 2);
594 reset master;
595
596 # execute
597 --error ER_DUP_ENTRY
598 delete from t2;
599 # check
600 source include/show_binlog_events.inc; /* the output must denote there is the query */;
601 select count(*) from t1 /* must be 1 */;
602
603 # cleanup
604 drop trigger trg_del;
605
606 # prepare
607 delete from t1;
608 delete from t2;
609 delete from t5;
610 create trigger trg_del_t2 after delete on t2 for each row
611 insert into t1 values (1);
612 insert into t2 values (2),(3);
613 insert into t5 values (1),(2);
614 reset master;
615
616 # execute
617 --error ER_DUP_ENTRY
618 delete t2.* from t2,t5 where t2.a=t5.a + 1;
619
620 # check
621 source include/show_binlog_events.inc; /* the output must denote there is the query */;
622 select count(*) from t1 /* must be 1 */;
623
624
625 #
626 # LOAD DATA
627 #
628
629 # prepare
630 delete from t1;
631 create table t4 (a int default 0, b int primary key) engine=innodb;
632 insert into t4 values (0, 17);
633 reset master;
634
635 # execute
636 --error ER_DUP_ENTRY
637 load data infile '../../std_data/rpl_loaddata.dat' into table t4 (a, @b) set b= @b + bug27417(2);
638 # check
639 select * from t4;
640 select count(*) from t1 /* must be 2 */;
641 source include/show_binlog_events.inc; /* the output must denote there is the query */;
642
643 #
644 # bug#23333 cleanup
645 #
646
647
648 drop trigger trg_del_t2;
649 drop table t1,t2,t3,t4,t5;
650 drop function bug27417;
651
652
653 --echo end of tests
654