1include/master-slave.inc 2[connection master] 3connection master; 4call mtr.add_suppression("Timeout waiting for reply of binlog"); 5call mtr.add_suppression("Read semi-sync reply"); 6call mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT."); 7call mtr.add_suppression("mysqld: Got an error reading communication packets"); 8connection slave; 9call mtr.add_suppression("Master server does not support semi-sync"); 10call mtr.add_suppression("Semi-sync slave .* reply"); 11call 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"); 12connection master; 13# 14# Uninstall semi-sync plugins on master and slave 15# 16connection slave; 17include/stop_slave.inc 18reset slave; 19set global rpl_semi_sync_master_enabled= 0; 20set global rpl_semi_sync_slave_enabled= 0; 21connection master; 22reset master; 23set global rpl_semi_sync_master_enabled= 0; 24set global rpl_semi_sync_slave_enabled= 0; 25# 26# Main test of semi-sync replication start here 27# 28connection master; 29set global rpl_semi_sync_master_timeout= 60000; 30[ default state of semi-sync on master should be OFF ] 31show variables like 'rpl_semi_sync_master_enabled'; 32Variable_name Value 33rpl_semi_sync_master_enabled OFF 34[ enable semi-sync on master ] 35set global rpl_semi_sync_master_enabled = 1; 36show variables like 'rpl_semi_sync_master_enabled'; 37Variable_name Value 38rpl_semi_sync_master_enabled ON 39[ status of semi-sync on master should be ON even without any semi-sync slaves ] 40show status like 'Rpl_semi_sync_master_clients'; 41Variable_name Value 42Rpl_semi_sync_master_clients 0 43show status like 'Rpl_semi_sync_master_status'; 44Variable_name Value 45Rpl_semi_sync_master_status ON 46show status like 'Rpl_semi_sync_master_yes_tx'; 47Variable_name Value 48Rpl_semi_sync_master_yes_tx 0 49# 50# BUG#45672 Semisync repl: ActiveTranx:insert_tranx_node: transaction node allocation failed 51# BUG#45673 Semisynch reports correct operation even if no slave is connected 52# 53[ status of semi-sync on master should be OFF ] 54show status like 'Rpl_semi_sync_master_clients'; 55Variable_name Value 56Rpl_semi_sync_master_clients 0 57show status like 'Rpl_semi_sync_master_status'; 58Variable_name Value 59Rpl_semi_sync_master_status OFF 60show status like 'Rpl_semi_sync_master_yes_tx'; 61Variable_name Value 62Rpl_semi_sync_master_yes_tx 0 63reset master; 64connection slave; 65[ default state of semi-sync on slave should be OFF ] 66show variables like 'rpl_semi_sync_slave_enabled'; 67Variable_name Value 68rpl_semi_sync_slave_enabled OFF 69[ enable semi-sync on slave ] 70set global rpl_semi_sync_slave_enabled = 1; 71show variables like 'rpl_semi_sync_slave_enabled'; 72Variable_name Value 73rpl_semi_sync_slave_enabled ON 74include/start_slave.inc 75connection master; 76[ initial master state after the semi-sync slave connected ] 77show status like 'Rpl_semi_sync_master_clients'; 78Variable_name Value 79Rpl_semi_sync_master_clients 1 80show status like 'Rpl_semi_sync_master_status'; 81Variable_name Value 82Rpl_semi_sync_master_status ON 83show status like 'Rpl_semi_sync_master_no_tx'; 84Variable_name Value 85Rpl_semi_sync_master_no_tx 0 86show status like 'Rpl_semi_sync_master_yes_tx'; 87Variable_name Value 88Rpl_semi_sync_master_yes_tx 0 89create table t1(a int) engine = ENGINE_TYPE; 90[ master state after CREATE TABLE statement ] 91show status like 'Rpl_semi_sync_master_status'; 92Variable_name Value 93Rpl_semi_sync_master_status ON 94show status like 'Rpl_semi_sync_master_no_tx'; 95Variable_name Value 96Rpl_semi_sync_master_no_tx 0 97show status like 'Rpl_semi_sync_master_yes_tx'; 98Variable_name Value 99Rpl_semi_sync_master_yes_tx 1 100select CONNECTIONS_NORMAL_SLAVE - CONNECTIONS_NORMAL_SLAVE as 'Should be 0'; 101Should be 0 1020 103[ insert records to table ] 104insert t1 values (10); 105insert t1 values (9); 106insert t1 values (8); 107insert t1 values (7); 108insert t1 values (6); 109insert t1 values (5); 110insert t1 values (4); 111insert t1 values (3); 112insert t1 values (2); 113insert t1 values (1); 114[ master status after inserts ] 115show status like 'Rpl_semi_sync_master_status'; 116Variable_name Value 117Rpl_semi_sync_master_status ON 118show status like 'Rpl_semi_sync_master_no_tx'; 119Variable_name Value 120Rpl_semi_sync_master_no_tx 0 121show status like 'Rpl_semi_sync_master_yes_tx'; 122Variable_name Value 123Rpl_semi_sync_master_yes_tx 11 124connection slave; 125[ slave status after replicated inserts ] 126show status like 'Rpl_semi_sync_slave_status'; 127Variable_name Value 128Rpl_semi_sync_slave_status ON 129select count(distinct a) from t1; 130count(distinct a) 13110 132select min(a) from t1; 133min(a) 1341 135select max(a) from t1; 136max(a) 13710 138 139# BUG#50157 140# semi-sync replication crashes when replicating a transaction which 141# include 'CREATE TEMPORARY TABLE `MyISAM_t` SELECT * FROM `Innodb_t` ; 142connection master; 143SET SESSION AUTOCOMMIT= 0; 144CREATE TABLE t2(c1 INT) ENGINE=innodb; 145connection slave; 146connection master; 147BEGIN; 148 149# Even though it is in a transaction, this statement is binlogged into binlog 150# file immediately. 151CREATE TEMPORARY TABLE t3 SELECT c1 FROM t2 where 1=1; 152 153# These statements will not be binlogged until the transaction is committed 154INSERT INTO t2 VALUES(11); 155INSERT INTO t2 VALUES(22); 156COMMIT; 157DROP TABLE t2, t3; 158SET SESSION AUTOCOMMIT= 1; 159connection slave; 160# 161# Test semi-sync master will switch OFF after one transaction 162# timeout waiting for slave reply. 163# 164connection slave; 165include/stop_slave.inc 166connection master; 167include/kill_binlog_dump_threads.inc 168set global rpl_semi_sync_master_timeout= 5000; 169[ master status should be ON ] 170show status like 'Rpl_semi_sync_master_no_tx'; 171Variable_name Value 172Rpl_semi_sync_master_no_tx 0 173show status like 'Rpl_semi_sync_master_yes_tx'; 174Variable_name Value 175Rpl_semi_sync_master_yes_tx 14 176[ semi-sync replication of these transactions will fail ] 177insert into t1 values (500); 178[ master status should be OFF ] 179show status like 'Rpl_semi_sync_master_status'; 180Variable_name Value 181Rpl_semi_sync_master_status OFF 182show status like 'Rpl_semi_sync_master_no_tx'; 183Variable_name Value 184Rpl_semi_sync_master_no_tx 1 185show status like 'Rpl_semi_sync_master_yes_tx'; 186Variable_name Value 187Rpl_semi_sync_master_yes_tx 14 188delete from t1 where a=10; 189delete from t1 where a=9; 190delete from t1 where a=8; 191delete from t1 where a=7; 192delete from t1 where a=6; 193delete from t1 where a=5; 194delete from t1 where a=4; 195delete from t1 where a=3; 196delete from t1 where a=2; 197delete from t1 where a=1; 198insert into t1 values (100); 199[ master status should be OFF ] 200show status like 'Rpl_semi_sync_master_status'; 201Variable_name Value 202Rpl_semi_sync_master_status OFF 203show status like 'Rpl_semi_sync_master_no_tx'; 204Variable_name Value 205Rpl_semi_sync_master_no_tx 12 206show status like 'Rpl_semi_sync_master_yes_tx'; 207Variable_name Value 208Rpl_semi_sync_master_yes_tx 14 209# 210# Test semi-sync status on master will be ON again when slave catches up 211# 212connection slave; 213[ slave status should be OFF ] 214show status like 'Rpl_semi_sync_slave_status'; 215Variable_name Value 216Rpl_semi_sync_slave_status OFF 217include/start_slave.inc 218[ slave status should be ON ] 219show status like 'Rpl_semi_sync_slave_status'; 220Variable_name Value 221Rpl_semi_sync_slave_status ON 222select count(distinct a) from t1; 223count(distinct a) 2242 225select min(a) from t1; 226min(a) 227100 228select max(a) from t1; 229max(a) 230500 231connection master; 232[ master status should be ON again after slave catches up ] 233show status like 'Rpl_semi_sync_master_no_tx'; 234Variable_name Value 235Rpl_semi_sync_master_no_tx 12 236show status like 'Rpl_semi_sync_master_yes_tx'; 237Variable_name Value 238Rpl_semi_sync_master_yes_tx 14 239show status like 'Rpl_semi_sync_master_clients'; 240Variable_name Value 241Rpl_semi_sync_master_clients 1 242# 243# Test disable/enable master semi-sync on the fly. 244# 245drop table t1; 246connection slave; 247include/stop_slave.inc 248# 249# Flush status 250# 251connection master; 252[ Semi-sync master status variables before FLUSH STATUS ] 253SHOW STATUS LIKE 'Rpl_semi_sync_master_no_tx'; 254Variable_name Value 255Rpl_semi_sync_master_no_tx 12 256SHOW STATUS LIKE 'Rpl_semi_sync_master_yes_tx'; 257Variable_name Value 258Rpl_semi_sync_master_yes_tx 15 259FLUSH NO_WRITE_TO_BINLOG STATUS; 260[ Semi-sync master status variables after FLUSH STATUS ] 261SHOW STATUS LIKE 'Rpl_semi_sync_master_no_tx'; 262Variable_name Value 263Rpl_semi_sync_master_no_tx 0 264SHOW STATUS LIKE 'Rpl_semi_sync_master_yes_tx'; 265Variable_name Value 266Rpl_semi_sync_master_yes_tx 0 267connection master; 268show master logs; 269Log_name master-bin.000001 270File_size # 271show variables like 'rpl_semi_sync_master_enabled'; 272Variable_name Value 273rpl_semi_sync_master_enabled ON 274[ disable semi-sync on the fly ] 275set global rpl_semi_sync_master_enabled=0; 276show variables like 'rpl_semi_sync_master_enabled'; 277Variable_name Value 278rpl_semi_sync_master_enabled OFF 279show status like 'Rpl_semi_sync_master_status'; 280Variable_name Value 281Rpl_semi_sync_master_status OFF 282[ enable semi-sync on the fly ] 283set global rpl_semi_sync_master_enabled=1; 284show variables like 'rpl_semi_sync_master_enabled'; 285Variable_name Value 286rpl_semi_sync_master_enabled ON 287show status like 'Rpl_semi_sync_master_status'; 288Variable_name Value 289Rpl_semi_sync_master_status ON 290# 291# Test RESET MASTER/SLAVE 292# 293connection slave; 294include/start_slave.inc 295connection master; 296create table t1 (a int) engine = ENGINE_TYPE; 297drop table t1; 298connection slave; 299[ test reset master ] 300connection master; 301reset master; 302show status like 'Rpl_semi_sync_master_status'; 303Variable_name Value 304Rpl_semi_sync_master_status ON 305show status like 'Rpl_semi_sync_master_no_tx'; 306Variable_name Value 307Rpl_semi_sync_master_no_tx 0 308show status like 'Rpl_semi_sync_master_yes_tx'; 309Variable_name Value 310Rpl_semi_sync_master_yes_tx 0 311connection slave; 312include/stop_slave.inc 313reset slave; 314include/kill_binlog_dump_threads.inc 315connection slave; 316include/start_slave.inc 317connection master; 318create table t1 (a int) engine = ENGINE_TYPE; 319insert into t1 values (1); 320insert into t1 values (2), (3); 321connection slave; 322select * from t1; 323a 3241 3252 3263 327connection master; 328[ master semi-sync status should be ON ] 329show status like 'Rpl_semi_sync_master_status'; 330Variable_name Value 331Rpl_semi_sync_master_status ON 332show status like 'Rpl_semi_sync_master_no_tx'; 333Variable_name Value 334Rpl_semi_sync_master_no_tx 0 335show status like 'Rpl_semi_sync_master_yes_tx'; 336Variable_name Value 337Rpl_semi_sync_master_yes_tx 3 338# 339# Start semi-sync replication without SUPER privilege 340# 341connection slave; 342include/stop_slave.inc 343reset slave; 344connection master; 345reset master; 346include/kill_binlog_dump_threads.inc 347set sql_log_bin=0; 348grant replication slave on *.* to rpl@127.0.0.1 identified by 'rpl_password'; 349flush privileges; 350set sql_log_bin=1; 351connection slave; 352grant replication slave on *.* to rpl@127.0.0.1 identified by 'rpl_password'; 353flush privileges; 354change master to master_user='rpl',master_password='rpl_password'; 355include/start_slave.inc 356show status like 'Rpl_semi_sync_slave_status'; 357Variable_name Value 358Rpl_semi_sync_slave_status ON 359connection master; 360[ master semi-sync should be ON ] 361show status like 'Rpl_semi_sync_master_clients'; 362Variable_name Value 363Rpl_semi_sync_master_clients 1 364show status like 'Rpl_semi_sync_master_status'; 365Variable_name Value 366Rpl_semi_sync_master_status ON 367show status like 'Rpl_semi_sync_master_no_tx'; 368Variable_name Value 369Rpl_semi_sync_master_no_tx 0 370show status like 'Rpl_semi_sync_master_yes_tx'; 371Variable_name Value 372Rpl_semi_sync_master_yes_tx 0 373insert into t1 values (4); 374insert into t1 values (5); 375[ master semi-sync should be ON ] 376show status like 'Rpl_semi_sync_master_clients'; 377Variable_name Value 378Rpl_semi_sync_master_clients 1 379show status like 'Rpl_semi_sync_master_status'; 380Variable_name Value 381Rpl_semi_sync_master_status ON 382show status like 'Rpl_semi_sync_master_no_tx'; 383Variable_name Value 384Rpl_semi_sync_master_no_tx 0 385show status like 'Rpl_semi_sync_master_yes_tx'; 386Variable_name Value 387Rpl_semi_sync_master_yes_tx 2 388# 389# Test semi-sync slave connect to non-semi-sync master 390# 391connection slave; 392include/stop_slave.inc 393SHOW STATUS LIKE 'Rpl_semi_sync_slave_status'; 394Variable_name Value 395Rpl_semi_sync_slave_status OFF 396connection master; 397include/kill_binlog_dump_threads.inc 398[ Semi-sync status on master should be ON ] 399show status like 'Rpl_semi_sync_master_status'; 400Variable_name Value 401Rpl_semi_sync_master_status ON 402set global rpl_semi_sync_master_enabled= 0; 403connection slave; 404SHOW VARIABLES LIKE 'rpl_semi_sync_slave_enabled'; 405Variable_name Value 406rpl_semi_sync_slave_enabled ON 407include/start_slave.inc 408connection master; 409insert into t1 values (8); 410[ master semi-sync clients should be 1, status should be OFF ] 411show status like 'Rpl_semi_sync_master_clients'; 412Variable_name Value 413Rpl_semi_sync_master_clients 1 414show status like 'Rpl_semi_sync_master_status'; 415Variable_name Value 416Rpl_semi_sync_master_status OFF 417connection slave; 418show status like 'Rpl_semi_sync_slave_status'; 419Variable_name Value 420Rpl_semi_sync_slave_status ON 421connection slave; 422include/stop_slave.inc 423connection master; 424set global rpl_semi_sync_master_enabled= 0; 425connection slave; 426SHOW VARIABLES LIKE 'rpl_semi_sync_slave_enabled'; 427Variable_name Value 428rpl_semi_sync_slave_enabled ON 429include/start_slave.inc 430connection master; 431insert into t1 values (10); 432connection slave; 433# 434# Test non-semi-sync slave connect to semi-sync master 435# 436connection master; 437set global rpl_semi_sync_master_timeout= 5000; 438set global rpl_semi_sync_master_enabled= 1; 439connection slave; 440include/stop_slave.inc 441SHOW STATUS LIKE 'Rpl_semi_sync_slave_status'; 442Variable_name Value 443Rpl_semi_sync_slave_status OFF 444[ uninstall semi-sync slave plugin ] 445set global rpl_semi_sync_slave_enabled= 0; 446[ reinstall semi-sync slave plugin and disable semi-sync ] 447SHOW VARIABLES LIKE 'rpl_semi_sync_slave_enabled'; 448Variable_name Value 449rpl_semi_sync_slave_enabled OFF 450SHOW STATUS LIKE 'Rpl_semi_sync_slave_status'; 451Variable_name Value 452Rpl_semi_sync_slave_status OFF 453include/start_slave.inc 454SHOW STATUS LIKE 'Rpl_semi_sync_slave_status'; 455Variable_name Value 456Rpl_semi_sync_slave_status OFF 457# 458# Clean up 459# 460connection slave; 461include/stop_slave.inc 462set global rpl_semi_sync_slave_enabled= 0; 463connection master; 464set global rpl_semi_sync_master_enabled= 0; 465connection slave; 466change master to master_user='root',master_password=''; 467include/start_slave.inc 468connection master; 469drop table t1; 470connection slave; 471connection master; 472drop user rpl@127.0.0.1; 473flush privileges; 474set global rpl_semi_sync_master_timeout= default; 475include/rpl_end.inc 476