set global rpl_semi_sync_master_wait_point=AFTER_SYNC; include/master-slave.inc [connection master] connection master; call mtr.add_suppression("Timeout waiting for reply of binlog"); call mtr.add_suppression("Read semi-sync reply"); call mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT."); call mtr.add_suppression("mysqld: Got an error reading communication packets"); connection slave; set sql_log_bin=0; call mtr.add_suppression("Semi-sync slave .* reply"); call mtr.add_suppression("Slave SQL.*Request to stop slave SQL Thread received while applying a group that has non-transactional changes; waiting for completion of the group"); set sql_log_bin=1; connection master; # # Uninstall semi-sync plugins on master and slave # connection slave; include/stop_slave.inc include/reset_slave.inc set global rpl_semi_sync_master_enabled= 0; set global rpl_semi_sync_slave_enabled= 0; connection master; reset master; set global rpl_semi_sync_master_enabled= 0; set global rpl_semi_sync_slave_enabled= 0; # # Main test of semi-sync replication start here # connection master; set global rpl_semi_sync_master_timeout= 2000; [ default state of semi-sync on master should be OFF ] show variables like 'rpl_semi_sync_master_enabled'; Variable_name Value rpl_semi_sync_master_enabled OFF [ enable semi-sync on master ] set global rpl_semi_sync_master_enabled = 1; show variables like 'rpl_semi_sync_master_enabled'; Variable_name Value rpl_semi_sync_master_enabled ON [ status of semi-sync on master should be ON even without any semi-sync slaves ] show status like 'Rpl_semi_sync_master_clients'; Variable_name Value Rpl_semi_sync_master_clients 0 show status like 'Rpl_semi_sync_master_status'; Variable_name Value Rpl_semi_sync_master_status ON show status like 'Rpl_semi_sync_master_yes_tx'; Variable_name Value Rpl_semi_sync_master_yes_tx 0 # # BUG#45672 Semisync repl: ActiveTranx:insert_tranx_node: transaction node allocation failed # BUG#45673 Semisynch reports correct operation even if no slave is connected # [ status of semi-sync on master should be OFF ] show status like 'Rpl_semi_sync_master_clients'; Variable_name Value Rpl_semi_sync_master_clients 0 show status like 'Rpl_semi_sync_master_status'; Variable_name Value Rpl_semi_sync_master_status OFF show status like 'Rpl_semi_sync_master_yes_tx'; Variable_name Value Rpl_semi_sync_master_yes_tx 0 reset master; connection slave; [ default state of semi-sync on slave should be OFF ] show variables like 'rpl_semi_sync_slave_enabled'; Variable_name Value rpl_semi_sync_slave_enabled OFF [ enable semi-sync on slave ] set global rpl_semi_sync_slave_enabled = 1; show variables like 'rpl_semi_sync_slave_enabled'; Variable_name Value rpl_semi_sync_slave_enabled ON include/start_slave.inc connection master; [ initial master state after the semi-sync slave connected ] show status like 'Rpl_semi_sync_master_clients'; Variable_name Value Rpl_semi_sync_master_clients 1 show status like 'Rpl_semi_sync_master_status'; Variable_name Value Rpl_semi_sync_master_status ON show status like 'Rpl_semi_sync_master_no_tx'; Variable_name Value Rpl_semi_sync_master_no_tx 0 show status like 'Rpl_semi_sync_master_yes_tx'; Variable_name Value Rpl_semi_sync_master_yes_tx 0 create table t1(a int) engine = ENGINE_TYPE; [ master state after CREATE TABLE statement ] show status like 'Rpl_semi_sync_master_status'; Variable_name Value Rpl_semi_sync_master_status ON show status like 'Rpl_semi_sync_master_no_tx'; Variable_name Value Rpl_semi_sync_master_no_tx 0 show status like 'Rpl_semi_sync_master_yes_tx'; Variable_name Value Rpl_semi_sync_master_yes_tx 1 select CONNECTIONS_NORMAL_SLAVE - CONNECTIONS_NORMAL_SLAVE as 'Should be 0'; Should be 0 0 [ insert records to table ] insert t1 values (10); insert t1 values (9); insert t1 values (8); insert t1 values (7); insert t1 values (6); insert t1 values (5); insert t1 values (4); insert t1 values (3); insert t1 values (2); insert t1 values (1); [ master status after inserts ] show status like 'Rpl_semi_sync_master_status'; Variable_name Value Rpl_semi_sync_master_status ON show status like 'Rpl_semi_sync_master_no_tx'; Variable_name Value Rpl_semi_sync_master_no_tx 0 show status like 'Rpl_semi_sync_master_yes_tx'; Variable_name Value Rpl_semi_sync_master_yes_tx 11 connection slave; [ slave status after replicated inserts ] show status like 'Rpl_semi_sync_slave_status'; Variable_name Value Rpl_semi_sync_slave_status ON select count(distinct a) from t1; count(distinct a) 10 select min(a) from t1; min(a) 1 select max(a) from t1; max(a) 10 # BUG#50157 # semi-sync replication crashes when replicating a transaction which # include 'CREATE TEMPORARY TABLE `MyISAM_t` SELECT * FROM `Innodb_t` ; connection master; SET SESSION AUTOCOMMIT= 0; CREATE TABLE t2(c1 INT) ENGINE=innodb; connection slave; connection master; BEGIN; # Even though it is in a transaction, this statement is binlogged into binlog # file immediately. CREATE TEMPORARY TABLE t3 SELECT c1 FROM t2 where 1=1; # These statements will not be binlogged until the transaction is committed INSERT INTO t2 VALUES(11); INSERT INTO t2 VALUES(22); COMMIT; DROP TABLE t2, t3; SET SESSION AUTOCOMMIT= 1; connection slave; # # Test semi-sync master will switch OFF after one transaction # timeout waiting for slave reply. # connection master; show status like "Rpl_semi_sync_master_status"; Variable_name Value Rpl_semi_sync_master_status ON connection slave; include/stop_slave.inc connection master; include/kill_binlog_dump_threads.inc set global rpl_semi_sync_master_timeout= 2000; [ master status should be ON ] show status like 'Rpl_semi_sync_master_no_tx'; Variable_name Value Rpl_semi_sync_master_no_tx 0 show status like 'Rpl_semi_sync_master_yes_tx'; Variable_name Value Rpl_semi_sync_master_yes_tx 16 [ semi-sync replication of these transactions will fail ] insert into t1 values (500); [ master status should be OFF ] show status like 'Rpl_semi_sync_master_status'; Variable_name Value Rpl_semi_sync_master_status OFF show status like 'Rpl_semi_sync_master_no_tx'; Variable_name Value Rpl_semi_sync_master_no_tx 1 show status like 'Rpl_semi_sync_master_yes_tx'; Variable_name Value Rpl_semi_sync_master_yes_tx 16 delete from t1 where a=10; delete from t1 where a=9; delete from t1 where a=8; delete from t1 where a=7; delete from t1 where a=6; delete from t1 where a=5; delete from t1 where a=4; delete from t1 where a=3; delete from t1 where a=2; delete from t1 where a=1; insert into t1 values (100); [ master status should be OFF ] show status like 'Rpl_semi_sync_master_status'; Variable_name Value Rpl_semi_sync_master_status OFF show status like 'Rpl_semi_sync_master_no_tx'; Variable_name Value Rpl_semi_sync_master_no_tx 12 show status like 'Rpl_semi_sync_master_yes_tx'; Variable_name Value Rpl_semi_sync_master_yes_tx 16 # # Test semi-sync status on master will be ON again when slave catches up # connection slave; [ slave status should be OFF ] show status like 'Rpl_semi_sync_slave_status'; Variable_name Value Rpl_semi_sync_slave_status OFF include/start_slave.inc [ slave status should be ON ] show status like 'Rpl_semi_sync_slave_status'; Variable_name Value Rpl_semi_sync_slave_status ON select count(distinct a) from t1; count(distinct a) 2 select min(a) from t1; min(a) 100 select max(a) from t1; max(a) 500 connection master; [ master status should be ON again after slave catches up ] show status like 'Rpl_semi_sync_master_no_tx'; Variable_name Value Rpl_semi_sync_master_no_tx 12 show status like 'Rpl_semi_sync_master_yes_tx'; Variable_name Value Rpl_semi_sync_master_yes_tx 16 show status like 'Rpl_semi_sync_master_clients'; Variable_name Value Rpl_semi_sync_master_clients 1 # # Test disable/enable master semi-sync on the fly. # drop table t1; connection slave; include/stop_slave.inc # # Flush status # connection master; [ Semi-sync master status variables before FLUSH STATUS ] SHOW STATUS LIKE 'Rpl_semi_sync_master_no_tx'; Variable_name Value Rpl_semi_sync_master_no_tx 12 SHOW STATUS LIKE 'Rpl_semi_sync_master_yes_tx'; Variable_name Value Rpl_semi_sync_master_yes_tx 17 FLUSH NO_WRITE_TO_BINLOG STATUS; [ Semi-sync master status variables after FLUSH STATUS ] SHOW STATUS LIKE 'Rpl_semi_sync_master_no_tx'; Variable_name Value Rpl_semi_sync_master_no_tx 0 SHOW STATUS LIKE 'Rpl_semi_sync_master_yes_tx'; Variable_name Value Rpl_semi_sync_master_yes_tx 0 connection master; show master logs; Log_name master-bin.000001 File_size # show variables like 'rpl_semi_sync_master_enabled'; Variable_name Value rpl_semi_sync_master_enabled ON [ disable semi-sync on the fly ] set global rpl_semi_sync_master_enabled=0; show variables like 'rpl_semi_sync_master_enabled'; Variable_name Value rpl_semi_sync_master_enabled OFF show status like 'Rpl_semi_sync_master_status'; Variable_name Value Rpl_semi_sync_master_status OFF [ enable semi-sync on the fly ] set global rpl_semi_sync_master_enabled=1; show variables like 'rpl_semi_sync_master_enabled'; Variable_name Value rpl_semi_sync_master_enabled ON show status like 'Rpl_semi_sync_master_status'; Variable_name Value Rpl_semi_sync_master_status ON # # Test RESET MASTER/SLAVE # connection slave; include/start_slave.inc connection master; create table t1 (a int) engine = ENGINE_TYPE; drop table t1; connection slave; [ test reset master ] connection master; reset master; show status like 'Rpl_semi_sync_master_status'; Variable_name Value Rpl_semi_sync_master_status ON show status like 'Rpl_semi_sync_master_no_tx'; Variable_name Value Rpl_semi_sync_master_no_tx 0 show status like 'Rpl_semi_sync_master_yes_tx'; Variable_name Value Rpl_semi_sync_master_yes_tx 0 connection slave; include/stop_slave.inc include/reset_slave.inc include/kill_binlog_dump_threads.inc connection slave; include/start_slave.inc connection master; connection slave; connection master; create table t1 (a int) engine = ENGINE_TYPE; insert into t1 values (1); insert into t1 values (2), (3); connection slave; select * from t1; a 1 2 3 connection master; [ master semi-sync status should be ON ] show status like 'Rpl_semi_sync_master_status'; Variable_name Value Rpl_semi_sync_master_status ON show status like 'Rpl_semi_sync_master_no_tx'; Variable_name Value Rpl_semi_sync_master_no_tx 0 show status like 'Rpl_semi_sync_master_yes_tx'; Variable_name Value Rpl_semi_sync_master_yes_tx 3 # # Start semi-sync replication without SUPER privilege # connection slave; include/stop_slave.inc include/reset_slave.inc connection master; reset master; include/kill_binlog_dump_threads.inc set sql_log_bin=0; grant replication slave on *.* to rpl@127.0.0.1 identified by 'rpl_password'; flush privileges; set sql_log_bin=1; connection slave; grant replication slave on *.* to rpl@127.0.0.1 identified by 'rpl_password'; flush privileges; change master to master_user='rpl',master_password='rpl_password'; include/start_slave.inc show status like 'Rpl_semi_sync_slave_status'; Variable_name Value Rpl_semi_sync_slave_status ON connection master; connection slave; connection master; [ master semi-sync should be ON ] show status like 'Rpl_semi_sync_master_clients'; Variable_name Value Rpl_semi_sync_master_clients 1 show status like 'Rpl_semi_sync_master_status'; Variable_name Value Rpl_semi_sync_master_status ON show status like 'Rpl_semi_sync_master_no_tx'; Variable_name Value Rpl_semi_sync_master_no_tx 0 show status like 'Rpl_semi_sync_master_yes_tx'; Variable_name Value Rpl_semi_sync_master_yes_tx 0 insert into t1 values (4); insert into t1 values (5); [ master semi-sync should be ON ] show status like 'Rpl_semi_sync_master_clients'; Variable_name Value Rpl_semi_sync_master_clients 1 show status like 'Rpl_semi_sync_master_status'; Variable_name Value Rpl_semi_sync_master_status ON show status like 'Rpl_semi_sync_master_no_tx'; Variable_name Value Rpl_semi_sync_master_no_tx 0 show status like 'Rpl_semi_sync_master_yes_tx'; Variable_name Value Rpl_semi_sync_master_yes_tx 2 # # Test semi-sync slave connect to non-semi-sync master # connection slave; include/stop_slave.inc SHOW STATUS LIKE 'Rpl_semi_sync_slave_status'; Variable_name Value Rpl_semi_sync_slave_status OFF connection master; include/kill_binlog_dump_threads.inc [ Semi-sync status on master should be ON ] show status like 'Rpl_semi_sync_master_status'; Variable_name Value Rpl_semi_sync_master_status ON set global rpl_semi_sync_master_enabled= 0; connection slave; SHOW VARIABLES LIKE 'rpl_semi_sync_slave_enabled'; Variable_name Value rpl_semi_sync_slave_enabled ON include/start_slave.inc connection master; insert into t1 values (8); [ master semi-sync clients should be 1, status should be OFF ] show status like 'Rpl_semi_sync_master_clients'; Variable_name Value Rpl_semi_sync_master_clients 1 show status like 'Rpl_semi_sync_master_status'; Variable_name Value Rpl_semi_sync_master_status OFF connection slave; show status like 'Rpl_semi_sync_slave_status'; Variable_name Value Rpl_semi_sync_slave_status ON connection slave; include/stop_slave.inc connection master; set global rpl_semi_sync_master_enabled= 0; connection slave; SHOW VARIABLES LIKE 'rpl_semi_sync_slave_enabled'; Variable_name Value rpl_semi_sync_slave_enabled ON include/start_slave.inc connection master; insert into t1 values (10); connection slave; # # Test non-semi-sync slave connect to semi-sync master # connection master; set global rpl_semi_sync_master_timeout= 5000; set global rpl_semi_sync_master_enabled= 1; connection slave; include/stop_slave.inc SHOW STATUS LIKE 'Rpl_semi_sync_slave_status'; Variable_name Value Rpl_semi_sync_slave_status OFF [ uninstall semi-sync slave plugin ] set global rpl_semi_sync_slave_enabled= 0; [ reinstall semi-sync slave plugin and disable semi-sync ] SHOW VARIABLES LIKE 'rpl_semi_sync_slave_enabled'; Variable_name Value rpl_semi_sync_slave_enabled OFF SHOW STATUS LIKE 'Rpl_semi_sync_slave_status'; Variable_name Value Rpl_semi_sync_slave_status OFF include/start_slave.inc SHOW STATUS LIKE 'Rpl_semi_sync_slave_status'; Variable_name Value Rpl_semi_sync_slave_status OFF # # Clean up # connection slave; include/stop_slave.inc set global rpl_semi_sync_slave_enabled= 0; connection master; set global rpl_semi_sync_master_enabled= 0; connection slave; change master to master_user='root',master_password=''; include/start_slave.inc connection master; drop table t1; connection slave; connection master; drop user rpl@127.0.0.1; flush privileges; set global rpl_semi_sync_master_timeout= default; include/rpl_end.inc set global rpl_semi_sync_master_wait_point=default;