1source include/have_innodb.inc; 2source include/have_binlog_format_statement.inc; 3source include/master-slave.inc; 4 5connection slave; 6call mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT"); 7 8connection master; 9call mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT"); 10 11SET @@session.binlog_direct_non_transactional_updates= FALSE; 12 13disable_warnings; 14DROP DATABASE IF EXISTS db1; 15DROP DATABASE IF EXISTS db2; 16enable_warnings; 17 18CREATE DATABASE db1; 19CREATE DATABASE db2; 20 21use db1; 22 23CREATE TABLE db1.t1 (a INT) ENGINE=InnoDB; 24CREATE TABLE db1.t2 (s CHAR(255)) ENGINE=MyISAM; 25 26sync_slave_with_master; 27source include/stop_slave.inc; 28connection master; 29 30DELIMITER //; 31CREATE PROCEDURE db1.p1 () 32BEGIN 33 INSERT INTO t1 VALUES (1); 34 INSERT INTO t1 VALUES (2); 35 INSERT INTO t1 VALUES (3); 36 INSERT INTO t1 VALUES (4); 37 INSERT INTO t1 VALUES (5); 38END// 39 40CREATE PROCEDURE db1.p2 () 41BEGIN 42 INSERT INTO t1 VALUES (6); 43 INSERT INTO t1 VALUES (7); 44 INSERT INTO t1 VALUES (8); 45 INSERT INTO t1 VALUES (9); 46 INSERT INTO t1 VALUES (10); 47 INSERT INTO t2 VALUES ('executed db1.p2()'); 48END// 49DELIMITER ;// 50 51INSERT INTO db1.t2 VALUES ('before call db1.p1()'); 52 53# Note: the master_log_pos is set to be the position of the BEGIN + 1, 54# so before fix of BUG#43263 if the BEGIN is ignored, then all the 55# INSERTS in p1 will be replicated in AUTOCOMMIT=1 mode and the slave 56# SQL thread will stop right before the first INSERT. After fix of 57# BUG#43263, BEGIN will not be ignored by the replication db rules, 58# and then the whole transaction will be executed before slave SQL 59# stop. 60let $master_pos= query_get_value(SHOW MASTER STATUS, Position, 1); 61let $master_pos= `SELECT $master_pos + 1`; 62 63use test; 64BEGIN; 65CALL db1.p1(); 66COMMIT; 67 68# The position where the following START SLAVE UNTIL will stop at 69let $master_end_trans_pos= query_get_value(SHOW MASTER STATUS, Position, 1); 70 71INSERT INTO db1.t2 VALUES ('after call db1.p1()'); 72SELECT * FROM db1.t1; 73SELECT * FROM db1.t2; 74 75connection slave; 76 77replace_result $master_pos MASTER_POS; 78eval start slave until master_log_file='master-bin.000001', master_log_pos=$master_pos; 79source include/wait_for_slave_sql_to_stop.inc; 80let $slave_sql_stop_pos= query_get_value(SHOW SLAVE STATUS, Exec_Master_Log_Pos, 1); 81let $result= query_get_value(SELECT $slave_sql_stop_pos - $master_end_trans_pos as result, result, 1); 82 83--echo # 84--echo # If we got non-zero here, then we're suffering BUG#43263 85--echo # 86eval SELECT $result as 'Must be 0'; 87SELECT * from db1.t1; 88SELECT * from db1.t2; 89 90connection master; 91 92INSERT INTO db1.t2 VALUES ('before call db1.p2()'); 93 94# See comments above. 95let $master_pos= query_get_value(SHOW MASTER STATUS, Position, 1); 96let $master_pos= `SELECT $master_pos + 1`; 97 98BEGIN; 99CALL db1.p2(); 100disable_warnings; 101ROLLBACK; 102enable_warnings; 103let $master_end_trans_pos= query_get_value(SHOW MASTER STATUS, Position, 1); 104 105INSERT INTO db1.t2 VALUES ('after call db1.p2()'); 106SELECT * FROM db1.t1; 107SELECT * FROM db1.t2; 108 109connection slave; 110 111replace_result $master_pos MASTER_POS; 112eval start slave until master_log_file='master-bin.000001', master_log_pos=$master_pos; 113source include/wait_for_slave_sql_to_stop.inc; 114 115let $slave_sql_stop_pos= query_get_value(SHOW SLAVE STATUS, Exec_Master_Log_Pos, 1); 116let $result= query_get_value(SELECT $slave_sql_stop_pos - $master_end_trans_pos as result, result, 1); 117 118--echo # 119--echo # If we got non-zero here, then we're suffering BUG#43263 120--echo # 121eval SELECT $result as 'Must be 0'; 122SELECT * from db1.t1; 123SELECT * from db1.t2; 124 125START SLAVE; 126source include/wait_for_slave_sql_to_start.inc; 127 128--echo # 129--echo # SAVEPOINT and ROLLBACK TO have the same problem in BUG#43263 130--echo # This was reported by BUG#50407 131connection master; 132let $binlog_start=query_get_value(SHOW MASTER STATUS, Position, 1); 133 134BEGIN; 135INSERT INTO db1.t1 VALUES(20); 136 137--echo # 138--echo # Verify whether this statement is binlogged correctly 139/*comment*/ SAVEPOINT has_comment; 140USE db1; 141INSERT INTO db1.t1 VALUES(30); 142INSERT INTO db1.t2 VALUES("in savepoint has_comment"); 143USE db2; 144SavePoint mixed_cases; 145USE db1; 146INSERT INTO db1.t2 VALUES("in savepoint mixed_cases"); 147INSERT INTO db1.t1 VALUES(40); 148USE db2; 149ROLLBACK TO mixed_cases; 150ROLLBACK TO has_comment; 151USE db1; 152INSERT INTO db1.t2 VALUES("after rollback to"); 153INSERT INTO db1.t1 VALUES(50); 154USE db2; 155COMMIT; 156source include/show_binlog_events.inc; 157sync_slave_with_master; 158 159--echo # 160--echo # Verify INSERT statements in savepoints are executed, for MyISAM table 161--echo # is not effected by ROLLBACK TO 162SELECT * FROM db1.t2 WHERE s LIKE '% savepoint %'; 163--echo # 164--echo # Verify INSERT statements on the Innodb table are rolled back; 165SELECT * FROM db1.t1 WHERE a IN (30, 40); 166 167--echo # 168--echo # Clean up 169--echo # 170connection master; 171DROP DATABASE db1; 172DROP DATABASE db2; 173--source include/rpl_end.inc 174