######################################################## # Author: Jeb # Date: Friday the 13th, 2007 # Purpose: To ensure Bug#12691 & 23171 have been patched # REQUIREMENT: If a transaction cause triggers # to be executed, any skip of part of # the transaction will skip the whole # transaction including all trigger changes. ######################################################### ############################## # Test #1 from Bug 12691 ############################## connection master; --disable_warnings DROP TABLE IF EXISTS test.t1; --enable_warnings --echo --echo **** Create Table for Test 1 **** --echo eval CREATE TABLE t1 ( a INT ) ENGINE=$engine_type; commit; sync_slave_with_master; --echo --echo **** Drop Table on slave for Test 1 **** --echo connection slave; DROP TABLE t1; --echo --echo **** Insert data on master for Test 1 **** --echo connection master; BEGIN; INSERT INTO t1 VALUES (1); INSERT INTO t1 VALUES (2); INSERT INTO t1 VALUES (3); COMMIT; sleep 3; --echo --echo **** Connect to slave and show status Test 1 **** --echo connection slave; --replace_result $MASTER_MYPORT MASTER_MYPORT --replace_column 1 # 23 # 33 # --vertical_results SHOW SLAVE STATUS; --echo --echo **** Stop slave and set skip for Test 1 **** --echo STOP SLAVE; SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; --echo --echo **** Start slave and show status for Test 1 **** --echo START SLAVE; sleep 3; --replace_result $MASTER_MYPORT MASTER_MYPORT --replace_column 1 # 23 # 33 # SHOW SLAVE STATUS; --echo --echo **** Stop slave and set skip for Test 1 **** --echo STOP SLAVE; SET GLOBAL SQL_SLAVE_SKIP_COUNTER=2; --echo --echo **** Start slave and show status for Test 1 **** --echo START SLAVE; sleep 3; --replace_result $MASTER_MYPORT MASTER_MYPORT --replace_column 1 # 23 # 33 # SHOW SLAVE STATUS; --echo --echo **** Stop slave and set skip for Test 1 **** --echo STOP SLAVE; SET GLOBAL SQL_SLAVE_SKIP_COUNTER=3; --echo --echo **** Start slave and show status for Test 1 **** --echo START SLAVE; sleep 3; --replace_result $MASTER_MYPORT MASTER_MYPORT --replace_column 1 # 23 # 33 # SHOW SLAVE STATUS; #cleanup --echo --echo **** cleanup for Test 1 **** --echo --disable_warnings connection master; drop table if exists t1; commit; connection slave; drop table if exists t1; commit; --enable_warnings ######################################### # Test #2 Skip w/ Triggers BUG #23171 ######################################### connection master; RESET MASTER; connection slave; STOP SLAVE; RESET SLAVE; START SLAVE; connection master; --echo --echo **** Create Tables for Test 2 **** --echo eval CREATE TABLE t1 ( a INT ) ENGINE=$engine_type; eval CREATE TABLE t2 ( a INT AUTO_INCREMENT, KEY (a) ) ENGINE=$engine_type; commit; --echo --echo **** Create trigger for Test 2 **** --echo delimiter |; create trigger t1_ai after insert on t1 for each row begin insert into t2 values (NULL); end| delimiter ;| --echo --echo **** Drop Table t1 on slave for Test 2 **** --echo sync_slave_with_master; connection slave; DROP TABLE t1; --echo --echo **** Insert data on master for Test 2 **** --echo connection master; BEGIN; INSERT INTO t1 VALUES (1); INSERT INTO t1 VALUES (2); COMMIT; SELECT * FROM t1 ORDER BY a; SELECT * FROM t2 ORDER BY a; --echo --echo **** Connect to slave and show status Test 2 **** --echo sleep 3; connection slave; --replace_result $MASTER_MYPORT MASTER_MYPORT --replace_column 1 # 23 # 33 # --vertical_results SHOW SLAVE STATUS; --echo --echo **** Stop slave and set skip for Test 2 **** --echo STOP SLAVE; SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; --echo --echo **** Start slave and show status for Test 2 **** --echo START SLAVE; sleep 3; --replace_result $MASTER_MYPORT MASTER_MYPORT --replace_column 1 # 23 # 33 # SHOW SLAVE STATUS; SELECT * FROM t2 ORDER BY a; --echo --echo **** Stop slave and set skip for Test 2 **** --echo STOP SLAVE; SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; --echo --echo **** Start slave and show status for Test 2 **** --echo START SLAVE; sleep 3; --replace_result $MASTER_MYPORT MASTER_MYPORT --replace_column 1 # 23 # 33 # SHOW SLAVE STATUS; SELECT * FROM t2 ORDER BY a; --echo --echo **** Stop slave and set skip for Test 2 **** --echo STOP SLAVE; SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; --echo --echo **** Start slave and show status for Test 2 **** --echo START SLAVE; sleep 3; --replace_result $MASTER_MYPORT MASTER_MYPORT --replace_column 1 # 23 # 33 # SHOW SLAVE STATUS; SELECT * FROM t2 ORDER BY a; #cleanup --echo --echo **** cleanup for Test 2 **** --echo --disable_warnings connection master; drop table if exists t1; drop table if exists t2; commit; connection slave; drop table if exists t1; drop table if exists t2; commit; --enable_warnings ######################################### # Test #3 Incorrect value in column End_log_pos BUG #22540 ######################################### connection master; RESET MASTER; connection slave; STOP SLAVE; RESET SLAVE; START SLAVE; connection master; reset master; eval create table t1(n int) engine=$engine_type; insert into t1 values (1); drop table t1; SHOW BINLOG EVENTS FROM 0; sync_slave_with_master; #End 5.1 test