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