1*** Set up circular replication on four servers *** 2include/rpl_init.inc [topology=1->2->3->4->1] 3 4connection server_4; 5SET auto_increment_increment= 4; 6SET auto_increment_offset= 4; 7connection server_3; 8SET auto_increment_increment= 4; 9SET auto_increment_offset= 3; 10connection server_2; 11SET auto_increment_increment= 4; 12SET auto_increment_offset= 2; 13connection server_1; 14SET auto_increment_increment= 4; 15SET auto_increment_offset= 1; 16*** Preparing data *** 17connection server_1; 18CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT, b VARCHAR(100), c INT NOT NULL, PRIMARY KEY(a)) ENGINE=MyISAM; 19CREATE TABLE t2 (a INT NOT NULL AUTO_INCREMENT, b VARCHAR(100), c INT NOT NULL, PRIMARY KEY(a)) ENGINE=InnoDB; 20include/rpl_sync.inc 21connection server_4; 22call 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"); 23 24*** Testing schema A->B->C->D->A *** 25 26connection server_1; 27INSERT INTO t1(b,c) VALUES('A',1); 28connection server_2; 29INSERT INTO t1(b,c) VALUES('B',1); 30connection server_3; 31INSERT INTO t1(b,c) VALUES('C',1); 32connection server_4; 33INSERT INTO t1(b,c) VALUES('D',1); 34include/rpl_sync.inc 35connection server_1; 36SELECT 'Master A',a,b FROM t1 WHERE c = 1 ORDER BY a,b; 37Master A a b 38Master A 1 A 39Master A 2 B 40Master A 3 C 41Master A 4 D 42connection server_2; 43SELECT 'Master B',a,b FROM t1 WHERE c = 1 ORDER BY a,b; 44Master B a b 45Master B 1 A 46Master B 2 B 47Master B 3 C 48Master B 4 D 49connection server_3; 50SELECT 'Master C',a,b FROM t1 WHERE c = 1 ORDER BY a,b; 51Master C a b 52Master C 1 A 53Master C 2 B 54Master C 3 C 55Master C 4 D 56connection server_4; 57SELECT 'Master D',a,b FROM t1 WHERE c = 1 ORDER BY a,b; 58Master D a b 59Master D 1 A 60Master D 2 B 61Master D 3 C 62Master D 4 D 63 64*** Testing schema A->B->D->A if C has failure *** 65 66* Do failure for C and then make new connection B->D * 67connection server_4; 68STOP SLAVE; 69SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; 70include/start_slave.inc 71connection server_3; 72INSERT INTO t1 VALUES(6,'C',2); 73connection server_4; 74connection server_2; 75INSERT INTO t1(b,c) VALUES('B',2); 76connection server_3; 77call mtr.add_suppression("Slave SQL.*Duplicate entry .6. for key .PRIMARY.* error.* 1062"); 78include/wait_for_slave_sql_error.inc [errno=1062] 79connection server_1; 80INSERT INTO t1(b,c) VALUES('A',2); 81connection server_4; 82INSERT INTO t1(b,c) VALUES('D',2); 83connection server_2; 84 85* Data on servers (C failed) * 86connection server_1; 87SELECT 'Master A',a,b FROM t1 WHERE c = 2 ORDER BY a,b; 88Master A a b 89Master A 5 A 90Master A 8 D 91connection server_2; 92SELECT 'Master B',a,b FROM t1 WHERE c = 2 ORDER BY a,b; 93Master B a b 94Master B 5 A 95Master B 6 B 96Master B 8 D 97connection server_3; 98SELECT 'Master C',a,b FROM t1 WHERE c = 2 ORDER BY a,b; 99Master C a b 100Master C 6 C 101connection server_4; 102SELECT 'Master D',a,b FROM t1 WHERE c = 2 ORDER BY a,b; 103Master D a b 104Master D 8 D 105 106* Reconfigure replication to schema A->B->D->A * 107connection server_3; 108include/stop_slave_io.inc 109connection server_4; 110include/stop_slave.inc 111include/rpl_change_topology.inc [new topology=1->2->4->1,2->3] 112include/start_slave.inc 113connection server_2; 114connection server_4; 115connection server_1; 116 117* Check data inserted before failure * 118connection server_1; 119SELECT 'Master A',a,b FROM t1 WHERE c = 2 ORDER BY a,b; 120Master A a b 121Master A 5 A 122Master A 6 B 123Master A 8 D 124connection server_2; 125SELECT 'Master B',a,b FROM t1 WHERE c = 2 ORDER BY a,b; 126Master B a b 127Master B 5 A 128Master B 6 B 129Master B 8 D 130connection server_3; 131SELECT 'Master C',a,b FROM t1 WHERE c = 2 ORDER BY a,b; 132Master C a b 133Master C 6 C 134connection server_4; 135SELECT 'Master D',a,b FROM t1 WHERE c = 2 ORDER BY a,b; 136Master D a b 137Master D 5 A 138Master D 6 B 139Master D 8 D 140 141* Check data inserted after failure * 142connection server_1; 143INSERT INTO t1(b,c) VALUES('A',3); 144connection server_2; 145INSERT INTO t1(b,c) VALUES('B',3); 146connection server_4; 147INSERT INTO t1(b,c) VALUES('D',3); 148connection server_1; 149include/rpl_sync.inc 150connection server_1; 151SELECT 'Master A',a,b FROM t1 WHERE c = 3 ORDER BY a,b; 152Master A a b 153Master A 9 A 154Master A 10 B 155Master A 12 D 156connection server_2; 157SELECT 'Master B',a,b FROM t1 WHERE c = 3 ORDER BY a,b; 158Master B a b 159Master B 9 A 160Master B 10 B 161Master B 12 D 162connection server_3; 163SELECT 'Master C',a,b FROM t1 WHERE c = 3 ORDER BY a,b; 164Master C a b 165connection server_4; 166SELECT 'Master D',a,b FROM t1 WHERE c = 3 ORDER BY a,b; 167Master D a b 168Master D 9 A 169Master D 10 B 170Master D 12 D 171connection server_1; 172 173*** Testing restoring scheme A->B->C->D->A after failure *** 174 175* Remove wrong event from C and restore B->C->D * 176connection server_4; 177include/stop_slave.inc 178connection server_3; 179DELETE FROM t1 WHERE a = 6; 180include/start_slave.inc 181connection server_2; 182connection server_3; 183RESET MASTER; 184connection server_4; 185RESET SLAVE; 186include/rpl_change_topology.inc [new topology=1->2->3->4->1] 187include/start_slave.inc 188connection server_3; 189connection server_4; 190include/rpl_sync.inc 191 192* Check data inserted before restoring schema A->B->C->D->A * 193connection server_1; 194SELECT 'Master A',a,b FROM t1 WHERE c IN (2,3) ORDER BY a,b; 195Master A a b 196Master A 5 A 197Master A 6 B 198Master A 8 D 199Master A 9 A 200Master A 10 B 201Master A 12 D 202connection server_2; 203SELECT 'Master B',a,b FROM t1 WHERE c IN (2,3) ORDER BY a,b; 204Master B a b 205Master B 5 A 206Master B 6 B 207Master B 8 D 208Master B 9 A 209Master B 10 B 210Master B 12 D 211connection server_3; 212SELECT 'Master C',a,b FROM t1 WHERE c IN (2,3) ORDER BY a,b; 213Master C a b 214Master C 5 A 215Master C 6 B 216Master C 8 D 217Master C 9 A 218Master C 10 B 219Master C 12 D 220connection server_4; 221SELECT 'Master D',a,b FROM t1 WHERE c IN (2,3) ORDER BY a,b; 222Master D a b 223Master D 5 A 224Master D 6 B 225Master D 8 D 226Master D 9 A 227Master D 10 B 228Master D 12 D 229connection server_1; 230 231* Check data inserted after restoring schema A->B->C->D->A * 232connection server_1; 233INSERT INTO t1(b,c) VALUES('A',4); 234connection server_2; 235INSERT INTO t1(b,c) VALUES('B',4); 236connection server_3; 237INSERT INTO t1(b,c) VALUES('C',4); 238connection server_4; 239INSERT INTO t1(b,c) VALUES('D',4); 240connection server_1; 241include/rpl_sync.inc 242connection server_1; 243SELECT 'Master A',a,b FROM t1 WHERE c = 4 ORDER BY a,b; 244Master A a b 245Master A 13 A 246Master A 14 B 247Master A 15 C 248Master A 16 D 249connection server_2; 250SELECT 'Master B',a,b FROM t1 WHERE c = 4 ORDER BY a,b; 251Master B a b 252Master B 13 A 253Master B 14 B 254Master B 15 C 255Master B 16 D 256connection server_3; 257SELECT 'Master C',a,b FROM t1 WHERE c = 4 ORDER BY a,b; 258Master C a b 259Master C 13 A 260Master C 14 B 261Master C 15 C 262Master C 16 D 263connection server_4; 264SELECT 'Master D',a,b FROM t1 WHERE c = 4 ORDER BY a,b; 265Master D a b 266Master D 13 A 267Master D 14 B 268Master D 15 C 269Master D 16 D 270connection server_1; 271 272* Transactions with commits * 273connection server_1; 274BEGIN; 275connection server_3; 276BEGIN; 277connection server_1; 278include/rpl_sync.inc 279connection server_1; 280SELECT 'Master A',b,COUNT(*) FROM t2 WHERE c = 1 GROUP BY b ORDER BY b; 281Master A b COUNT(*) 282Master A A 100 283Master A B 100 284Master A C 100 285Master A D 100 286connection server_2; 287SELECT 'Master B',b,COUNT(*) FROM t2 WHERE c = 1 GROUP BY b ORDER BY b; 288Master B b COUNT(*) 289Master B A 100 290Master B B 100 291Master B C 100 292Master B D 100 293connection server_3; 294SELECT 'Master C',b,COUNT(*) FROM t2 WHERE c = 1 GROUP BY b ORDER BY b; 295Master C b COUNT(*) 296Master C A 100 297Master C B 100 298Master C C 100 299Master C D 100 300connection server_4; 301SELECT 'Master D',b,COUNT(*) FROM t2 WHERE c = 1 GROUP BY b ORDER BY b; 302Master D b COUNT(*) 303Master D A 100 304Master D B 100 305Master D C 100 306Master D D 100 307connection server_1; 308 309* Transactions with rollbacks * 310connection server_1; 311BEGIN; 312connection server_3; 313BEGIN; 314connection server_1; 315include/rpl_sync.inc 316connection server_1; 317SELECT 'Master A',b,COUNT(*) FROM t2 WHERE c = 2 GROUP BY b ORDER BY b; 318Master A b COUNT(*) 319Master A B 100 320Master A D 100 321connection server_2; 322SELECT 'Master B',b,COUNT(*) FROM t2 WHERE c = 2 GROUP BY b ORDER BY b; 323Master B b COUNT(*) 324Master B B 100 325Master B D 100 326connection server_3; 327SELECT 'Master C',b,COUNT(*) FROM t2 WHERE c = 2 GROUP BY b ORDER BY b; 328Master C b COUNT(*) 329Master C B 100 330Master C D 100 331connection server_4; 332SELECT 'Master D',b,COUNT(*) FROM t2 WHERE c = 2 GROUP BY b ORDER BY b; 333Master D b COUNT(*) 334Master D B 100 335Master D D 100 336connection server_1; 337 338*** Clean up *** 339connection server_1; 340DROP TABLE t1,t2; 341include/rpl_end.inc 342