1include/master-slave.inc 2[connection master] 3connection slave; 4call mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT"); 5connection master; 6call mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT"); 7SET @@session.binlog_direct_non_transactional_updates= FALSE; 8DROP DATABASE IF EXISTS db1; 9DROP DATABASE IF EXISTS db2; 10CREATE DATABASE db1; 11CREATE DATABASE db2; 12use db1; 13CREATE TABLE db1.t1 (a INT) ENGINE=InnoDB; 14CREATE TABLE db1.t2 (s CHAR(255)) ENGINE=MyISAM; 15connection slave; 16include/stop_slave.inc 17connection master; 18CREATE PROCEDURE db1.p1 () 19BEGIN 20INSERT INTO t1 VALUES (1); 21INSERT INTO t1 VALUES (2); 22INSERT INTO t1 VALUES (3); 23INSERT INTO t1 VALUES (4); 24INSERT INTO t1 VALUES (5); 25END// 26CREATE PROCEDURE db1.p2 () 27BEGIN 28INSERT INTO t1 VALUES (6); 29INSERT INTO t1 VALUES (7); 30INSERT INTO t1 VALUES (8); 31INSERT INTO t1 VALUES (9); 32INSERT INTO t1 VALUES (10); 33INSERT INTO t2 VALUES ('executed db1.p2()'); 34END// 35INSERT INTO db1.t2 VALUES ('before call db1.p1()'); 36use test; 37BEGIN; 38CALL db1.p1(); 39COMMIT; 40INSERT INTO db1.t2 VALUES ('after call db1.p1()'); 41SELECT * FROM db1.t1; 42a 431 442 453 464 475 48SELECT * FROM db1.t2; 49s 50before call db1.p1() 51after call db1.p1() 52connection slave; 53start slave until master_log_file='master-bin.000001', master_log_pos=MASTER_POS; 54include/wait_for_slave_sql_to_stop.inc 55# 56# If we got non-zero here, then we're suffering BUG#43263 57# 58SELECT 0 as 'Must be 0'; 59Must be 0 600 61SELECT * from db1.t1; 62a 631 642 653 664 675 68SELECT * from db1.t2; 69s 70before call db1.p1() 71connection master; 72INSERT INTO db1.t2 VALUES ('before call db1.p2()'); 73BEGIN; 74CALL db1.p2(); 75Warnings: 76Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it accesses a non-transactional table after accessing a transactional table within the same transaction 77ROLLBACK; 78INSERT INTO db1.t2 VALUES ('after call db1.p2()'); 79SELECT * FROM db1.t1; 80a 811 822 833 844 855 86SELECT * FROM db1.t2; 87s 88before call db1.p1() 89after call db1.p1() 90before call db1.p2() 91executed db1.p2() 92after call db1.p2() 93connection slave; 94start slave until master_log_file='master-bin.000001', master_log_pos=MASTER_POS; 95include/wait_for_slave_sql_to_stop.inc 96# 97# If we got non-zero here, then we're suffering BUG#43263 98# 99SELECT 0 as 'Must be 0'; 100Must be 0 1010 102SELECT * from db1.t1; 103a 1041 1052 1063 1074 1085 109SELECT * from db1.t2; 110s 111before call db1.p1() 112executed db1.p2() 113START SLAVE; 114include/wait_for_slave_sql_to_start.inc 115# 116# SAVEPOINT and ROLLBACK TO have the same problem in BUG#43263 117# This was reported by BUG#50407 118connection master; 119BEGIN; 120INSERT INTO db1.t1 VALUES(20); 121# 122# Verify whether this statement is binlogged correctly 123/*comment*/ SAVEPOINT has_comment; 124USE db1; 125INSERT INTO db1.t1 VALUES(30); 126INSERT INTO db1.t2 VALUES("in savepoint has_comment"); 127Warnings: 128Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it accesses a non-transactional table after accessing a transactional table within the same transaction 129USE db2; 130SavePoint mixed_cases; 131USE db1; 132INSERT INTO db1.t2 VALUES("in savepoint mixed_cases"); 133Warnings: 134Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it accesses a non-transactional table after accessing a transactional table within the same transaction 135INSERT INTO db1.t1 VALUES(40); 136USE db2; 137ROLLBACK TO mixed_cases; 138Warnings: 139Warning 1196 Some non-transactional changed tables couldn't be rolled back 140ROLLBACK TO has_comment; 141Warnings: 142Warning 1196 Some non-transactional changed tables couldn't be rolled back 143USE db1; 144INSERT INTO db1.t2 VALUES("after rollback to"); 145Warnings: 146Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it accesses a non-transactional table after accessing a transactional table within the same transaction 147INSERT INTO db1.t1 VALUES(50); 148USE db2; 149COMMIT; 150include/show_binlog_events.inc 151Log_name Pos Event_type Server_id End_log_pos Info 152master-bin.000001 # Gtid # # BEGIN GTID #-#-# 153master-bin.000001 # Query # # use `test`; INSERT INTO db1.t1 VALUES(20) 154master-bin.000001 # Query # # SAVEPOINT `has_comment` 155master-bin.000001 # Query # # use `db1`; INSERT INTO db1.t1 VALUES(30) 156master-bin.000001 # Query # # use `db1`; INSERT INTO db1.t2 VALUES("in savepoint has_comment") 157master-bin.000001 # Query # # SAVEPOINT `mixed_cases` 158master-bin.000001 # Query # # use `db1`; INSERT INTO db1.t2 VALUES("in savepoint mixed_cases") 159master-bin.000001 # Query # # use `db1`; INSERT INTO db1.t1 VALUES(40) 160master-bin.000001 # Query # # ROLLBACK TO `mixed_cases` 161master-bin.000001 # Query # # ROLLBACK TO `has_comment` 162master-bin.000001 # Query # # use `db1`; INSERT INTO db1.t2 VALUES("after rollback to") 163master-bin.000001 # Query # # use `db1`; INSERT INTO db1.t1 VALUES(50) 164master-bin.000001 # Xid # # COMMIT /* XID */ 165connection slave; 166# 167# Verify INSERT statements in savepoints are executed, for MyISAM table 168# is not effected by ROLLBACK TO 169SELECT * FROM db1.t2 WHERE s LIKE '% savepoint %'; 170s 171in savepoint has_comment 172in savepoint mixed_cases 173# 174# Verify INSERT statements on the Innodb table are rolled back; 175SELECT * FROM db1.t1 WHERE a IN (30, 40); 176a 177# 178# Clean up 179# 180connection master; 181DROP DATABASE db1; 182DROP DATABASE db2; 183include/rpl_end.inc 184