1# ==== Purpose ==== 2# 3# Setup: circular replication on four hosts, i.e., topology 4# server_1 -> server_2 -> server_3 -> server_4 -> server_1 5# 6# Tested properties: 7# - Correctly configured autoinc works. 8# - Manual failover works. 9# 10# ==== Related bugs and worklogs ==== 11# 12# WL#3754 13# BUG#49978 14 15--source include/have_innodb.inc 16 17# Use wait_for_slave_to_(start|stop) for current connections 18let $keep_connection= 1; 19 20# Set up circular ring and new names for servers 21--echo *** Set up circular replication on four servers *** 22--let $rpl_topology= 1->2->3->4->1 23--source include/rpl_init.inc 24--echo 25 26#set auto inc variables at each server 27--let $_rpl_server= $rpl_server_count 28while ($_rpl_server) 29{ 30 connection server_$_rpl_server; 31 eval SET auto_increment_increment= $rpl_server_count; 32 eval SET auto_increment_offset= $_rpl_server; 33 34 --dec $_rpl_server 35} 36 37# Preparing data. 38--echo *** Preparing data *** 39--connection server_1 40CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT, b VARCHAR(100), c INT NOT NULL, PRIMARY KEY(a)) ENGINE=MyISAM; 41CREATE TABLE t2 (a INT NOT NULL AUTO_INCREMENT, b VARCHAR(100), c INT NOT NULL, PRIMARY KEY(a)) ENGINE=InnoDB; 42--source include/rpl_sync.inc 43--connection server_4 44call 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"); 45--echo 46 47# 48# Testing 49# 50 51--echo *** Testing schema A->B->C->D->A *** 52--echo 53# insert data via all hosts 54--connection server_1 55INSERT INTO t1(b,c) VALUES('A',1); 56--sync_slave_with_master server_2 57INSERT INTO t1(b,c) VALUES('B',1); 58--sync_slave_with_master server_3 59INSERT INTO t1(b,c) VALUES('C',1); 60--sync_slave_with_master server_4 61INSERT INTO t1(b,c) VALUES('D',1); 62 63--source include/rpl_sync.inc 64 65--connection server_1 66SELECT 'Master A',a,b FROM t1 WHERE c = 1 ORDER BY a,b; 67--connection server_2 68SELECT 'Master B',a,b FROM t1 WHERE c = 1 ORDER BY a,b; 69--connection server_3 70SELECT 'Master C',a,b FROM t1 WHERE c = 1 ORDER BY a,b; 71--connection server_4 72SELECT 'Master D',a,b FROM t1 WHERE c = 1 ORDER BY a,b; 73--echo 74 75--echo *** Testing schema A->B->D->A if C has failure *** 76--echo 77--echo * Do failure for C and then make new connection B->D * 78 79# Note: server_N has auto_increment_offset=N. Below, we insert value 6 80# in the autoinc column on server_3 (and prevent it from replicating 81# further using SQL_SLAVE_SKIP_COUNTER on server_4). Due to the 82# auto_increment_offset setting, the autoinc value 6 is normally 83# generated on server_2. When we later insert a row on server_2, we 84# thus cause a duplicate key error on server_3. 85 86# Do not replicate next event from C 87--connection server_4 88STOP SLAVE; 89SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; 90source include/start_slave.inc; 91--connection server_3 92INSERT INTO t1 VALUES(6,'C',2); 93--sync_slave_with_master server_4 94 95--connection server_2 96INSERT INTO t1(b,c) VALUES('B',2); 97# Wait while C will stop. 98--connection server_3 99# 1062 = ER_DUP_ENTRY 100call mtr.add_suppression("Slave SQL.*Duplicate entry .6. for key .PRIMARY.* error.* 1062"); 101--let $slave_sql_errno= 1062 102--source include/wait_for_slave_sql_error.inc 103--connection server_1 104INSERT INTO t1(b,c) VALUES('A',2); 105--connection server_4 106INSERT INTO t1(b,c) VALUES('D',2); 107 108 109# Sync all servers except C 110--connection server_2 111let $wait_condition= SELECT COUNT(*)=3 FROM t1 WHERE a > 4; 112--let $server_connection= server_1 113--source include/wait_condition.inc 114 115--echo 116--echo * Data on servers (C failed) * 117# Masters C,D shouldn't have correct data 118--connection server_1 119SELECT 'Master A',a,b FROM t1 WHERE c = 2 ORDER BY a,b; 120--connection server_2 121SELECT 'Master B',a,b FROM t1 WHERE c = 2 ORDER BY a,b; 122--connection server_3 123SELECT 'Master C',a,b FROM t1 WHERE c = 2 ORDER BY a,b; 124--connection server_4 125SELECT 'Master D',a,b FROM t1 WHERE c = 2 ORDER BY a,b; 126--echo 127 128--echo * Reconfigure replication to schema A->B->D->A * 129# Exclude Master C 130--connection server_3 131--source include/stop_slave_io.inc 132--let $pos_c= query_get_value(SHOW SLAVE STATUS, Exec_Master_Log_Pos, 1) 133--let $file_c= query_get_value(SHOW SLAVE STATUS, Master_Log_File, 1) 134 135--connection server_4 136--source include/stop_slave.inc 137 138--let $rpl_topology= 1->2->4->1,2->3 139--let $rpl_master_log_file= 4:$file_c 140--let $rpl_master_log_pos= 4:$pos_c 141--source include/rpl_change_topology.inc 142 143#--replace_result $SERVER_MYPORT_2 SERVER_MYPORT_2 $file_c LOG_FILE $pos_c LOG_POS 144#--eval CHANGE MASTER TO master_host='127.0.0.1',master_port=$SERVER_MYPORT_2,master_user='root',master_log_file='$file_c',master_log_pos=$pos_c 145source include/start_slave.inc; 146--connection server_2 147--sync_slave_with_master server_4 148--sync_slave_with_master server_1 149--echo 150 151--echo * Check data inserted before failure * 152--connection server_1 153SELECT 'Master A',a,b FROM t1 WHERE c = 2 ORDER BY a,b; 154--connection server_2 155SELECT 'Master B',a,b FROM t1 WHERE c = 2 ORDER BY a,b; 156--connection server_3 157SELECT 'Master C',a,b FROM t1 WHERE c = 2 ORDER BY a,b; 158--connection server_4 159SELECT 'Master D',a,b FROM t1 WHERE c = 2 ORDER BY a,b; 160--echo 161 162--echo * Check data inserted after failure * 163--connection server_1 164INSERT INTO t1(b,c) VALUES('A',3); 165--connection server_2 166INSERT INTO t1(b,c) VALUES('B',3); 167--connection server_4 168INSERT INTO t1(b,c) VALUES('D',3); 169connection server_1; 170 171--let $rpl_only_running_threads= 1 172--source include/rpl_sync.inc 173 174--connection server_1 175SELECT 'Master A',a,b FROM t1 WHERE c = 3 ORDER BY a,b; 176--connection server_2 177SELECT 'Master B',a,b FROM t1 WHERE c = 3 ORDER BY a,b; 178--connection server_3 179SELECT 'Master C',a,b FROM t1 WHERE c = 3 ORDER BY a,b; 180--connection server_4 181SELECT 'Master D',a,b FROM t1 WHERE c = 3 ORDER BY a,b; 182--connection server_1 183--echo 184 185--echo *** Testing restoring scheme A->B->C->D->A after failure *** 186--echo 187# Master D will ignore a next event from C so that event will not be 188# distributed to other servers 189--echo * Remove wrong event from C and restore B->C->D * 190--connection server_4 191source include/stop_slave.inc; 192--connection server_3 193DELETE FROM t1 WHERE a = 6; 194--source include/start_slave.inc 195--connection server_2 196--sync_slave_with_master server_3 197RESET MASTER; 198--let $file_d= query_get_value(SHOW MASTER STATUS, File, 1) 199--let $pos_d= query_get_value(SHOW MASTER STATUS, Position, 1) 200--connection server_4 201RESET SLAVE; 202--let $rpl_topology= 1->2->3->4->1 203--let $rpl_master_log_file= 4:$file_d 204--let $rpl_master_log_pos= 4:$pos_d 205--source include/rpl_change_topology.inc 206#--replace_result $SERVER_MYPORT_3 SERVER_MYPORT_3 $file_d LOG_FILE $pos_d LOG_POS 207#--eval CHANGE MASTER TO master_host='127.0.0.1',master_port=$SERVER_MYPORT_3,master_user='root',master_log_file='$file_d',master_log_pos=$pos_d 208--source include/start_slave.inc 209--connection server_3 210--sync_slave_with_master server_4 211--source include/rpl_sync.inc 212 213--echo 214--echo * Check data inserted before restoring schema A->B->C->D->A * 215--connection server_1 216SELECT 'Master A',a,b FROM t1 WHERE c IN (2,3) ORDER BY a,b; 217--sync_slave_with_master server_2 218SELECT 'Master B',a,b FROM t1 WHERE c IN (2,3) ORDER BY a,b; 219--sync_slave_with_master server_3 220SELECT 'Master C',a,b FROM t1 WHERE c IN (2,3) ORDER BY a,b; 221--sync_slave_with_master server_4 222SELECT 'Master D',a,b FROM t1 WHERE c IN (2,3) ORDER BY a,b; 223--sync_slave_with_master server_1 224--echo 225 226--echo * Check data inserted after restoring schema A->B->C->D->A * 227--connection server_1 228INSERT INTO t1(b,c) VALUES('A',4); 229--connection server_2 230INSERT INTO t1(b,c) VALUES('B',4); 231--connection server_3 232INSERT INTO t1(b,c) VALUES('C',4); 233--connection server_4 234INSERT INTO t1(b,c) VALUES('D',4); 235--connection server_1 236 237--source include/rpl_sync.inc 238 239--connection server_1 240SELECT 'Master A',a,b FROM t1 WHERE c = 4 ORDER BY a,b; 241--connection server_2 242SELECT 'Master B',a,b FROM t1 WHERE c = 4 ORDER BY a,b; 243--connection server_3 244SELECT 'Master C',a,b FROM t1 WHERE c = 4 ORDER BY a,b; 245--connection server_4 246SELECT 'Master D',a,b FROM t1 WHERE c = 4 ORDER BY a,b; 247--connection server_1 248--echo 249 250--echo * Transactions with commits * 251# Testing mixing of transactions and regular inserts 252--connection server_1 253BEGIN; 254--connection server_3 255BEGIN; 256let $counter= 100; 257--connection server_1 258--disable_query_log 259while ($counter) { 260 --connection server_1 261 INSERT INTO t2(b,c) VALUES('A',1); 262 --connection server_2 263 INSERT INTO t2(b,c) VALUES('B',1); 264 --connection server_3 265 INSERT INTO t2(b,c) VALUES('C',1); 266 --connection server_4 267 INSERT INTO t2(b,c) VALUES('D',1); 268 dec $counter; 269} 270--connection server_1 271COMMIT; 272--connection server_3 273COMMIT; 274--connection server_1 275--enable_query_log 276 277--source include/rpl_sync.inc 278 279--connection server_1 280SELECT 'Master A',b,COUNT(*) FROM t2 WHERE c = 1 GROUP BY b ORDER BY b; 281--connection server_2 282SELECT 'Master B',b,COUNT(*) FROM t2 WHERE c = 1 GROUP BY b ORDER BY b; 283--connection server_3 284SELECT 'Master C',b,COUNT(*) FROM t2 WHERE c = 1 GROUP BY b ORDER BY b; 285--connection server_4 286SELECT 'Master D',b,COUNT(*) FROM t2 WHERE c = 1 GROUP BY b ORDER BY b; 287--connection server_1 288--echo 289 290--echo * Transactions with rollbacks * 291# Testing mixing of transactions with rollback and regular inserts 292--connection server_1 293BEGIN; 294--connection server_3 295BEGIN; 296let $counter= 100; 297--connection server_1 298--disable_query_log 299while ($counter) { 300 --connection server_1 301 INSERT INTO t2(b,c) VALUES('A',2); 302 --connection server_2 303 INSERT INTO t2(b,c) VALUES('B',2); 304 --connection server_3 305 INSERT INTO t2(b,c) VALUES('C',2); 306 --connection server_4 307 INSERT INTO t2(b,c) VALUES('D',2); 308 dec $counter; 309} 310--connection server_1 311ROLLBACK; 312--connection server_3 313ROLLBACK; 314--connection server_1 315--enable_query_log 316 317--source include/rpl_sync.inc 318 319--connection server_1 320SELECT 'Master A',b,COUNT(*) FROM t2 WHERE c = 2 GROUP BY b ORDER BY b; 321--connection server_2 322SELECT 'Master B',b,COUNT(*) FROM t2 WHERE c = 2 GROUP BY b ORDER BY b; 323--connection server_3 324SELECT 'Master C',b,COUNT(*) FROM t2 WHERE c = 2 GROUP BY b ORDER BY b; 325--connection server_4 326SELECT 'Master D',b,COUNT(*) FROM t2 WHERE c = 2 GROUP BY b ORDER BY b; 327--connection server_1 328 329--echo 330 331# Clean up 332--echo *** Clean up *** 333--connection server_1 334DROP TABLE t1,t2; 335 336--source include/rpl_end.inc 337