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