| 1 |
*** Set up circular replication on four servers *** |
| 2 |
include/rpl_init.inc [topology=1->2->3->4->1] |
| 3 |
Warnings: |
| 4 |
Note #### Sending passwords in plain text without SSL/TLS is extremely insecure. |
| 5 |
Note #### Storing MySQL user name or password information in the connection metadata repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START REPLICA; see the 'START REPLICA Syntax' in the MySQL Manual for more information. |
| 6 |
Warnings: |
| 7 |
Note #### Sending passwords in plain text without SSL/TLS is extremely insecure. |
| 8 |
Note #### Storing MySQL user name or password information in the connection metadata repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START REPLICA; see the 'START REPLICA Syntax' in the MySQL Manual for more information. |
| 9 |
Warnings: |
| 10 |
Note #### Sending passwords in plain text without SSL/TLS is extremely insecure. |
| 11 |
Note #### Storing MySQL user name or password information in the connection metadata repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START REPLICA; see the 'START REPLICA Syntax' in the MySQL Manual for more information. |
| 12 |
Warnings: |
| 13 |
Note #### Sending passwords in plain text without SSL/TLS is extremely insecure. |
| 14 |
Note #### Storing MySQL user name or password information in the connection metadata repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START REPLICA; see the 'START REPLICA Syntax' in the MySQL Manual for more information. |
| 15 |
|
| 16 |
[connection server_4] |
| 17 |
SET auto_increment_increment= 4; |
| 18 |
SET auto_increment_offset= 4; |
| 19 |
[connection server_3] |
| 20 |
SET auto_increment_increment= 4; |
| 21 |
SET auto_increment_offset= 3; |
| 22 |
[connection server_2] |
| 23 |
SET auto_increment_increment= 4; |
| 24 |
SET auto_increment_offset= 2; |
| 25 |
[connection server_1] |
| 26 |
SET auto_increment_increment= 4; |
| 27 |
SET auto_increment_offset= 1; |
| 28 |
*** Preparing data *** |
| 29 |
CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT, b VARCHAR(100), c INT NOT NULL, PRIMARY KEY(a)) ENGINE=MyISAM; |
| 30 |
include/rpl_sync.inc |
| 31 |
call mtr.add_suppression("Replica SQL.*Request to stop replica SQL Thread received while applying a group that has non-transactional changes; waiting for completion of the group"); |
| 32 |
call mtr.add_suppression("The replica coordinator and worker threads are stopped, possibly leaving data in inconsistent state"); |
| 33 |
call mtr.add_suppression("Replica SQL: Coordinator thread of multi-threaded replica is exiting seeing a failed Worker to apply an event"); |
| 34 |
call mtr.add_suppression("Replica worker thread has failed to apply an event"); |
| 35 |
call mtr.add_suppression("Replica SQL.*Duplicate entry .6. for key .t1.PRIMARY.* Error_code: MY-001062"); |
| 36 |
|
| 37 |
*** Testing schema A->B->C->D->A *** |
| 38 |
|
| 39 |
INSERT INTO t1(b,c) VALUES('A',1); |
| 40 |
INSERT INTO t1(b,c) VALUES('B',1); |
| 41 |
INSERT INTO t1(b,c) VALUES('C',1); |
| 42 |
INSERT INTO t1(b,c) VALUES('D',1); |
| 43 |
include/rpl_sync.inc |
| 44 |
SELECT 'Master A',a,b FROM t1 WHERE c = 1 ORDER BY a,b; |
| 45 |
Master A a b |
| 46 |
Master A 1 A |
| 47 |
Master A 2 B |
| 48 |
Master A 3 C |
| 49 |
Master A 4 D |
| 50 |
SELECT 'Master B',a,b FROM t1 WHERE c = 1 ORDER BY a,b; |
| 51 |
Master B a b |
| 52 |
Master B 1 A |
| 53 |
Master B 2 B |
| 54 |
Master B 3 C |
| 55 |
Master B 4 D |
| 56 |
SELECT 'Master C',a,b FROM t1 WHERE c = 1 ORDER BY a,b; |
| 57 |
Master C a b |
| 58 |
Master C 1 A |
| 59 |
Master C 2 B |
| 60 |
Master C 3 C |
| 61 |
Master C 4 D |
| 62 |
SELECT 'Master D',a,b FROM t1 WHERE c = 1 ORDER BY a,b; |
| 63 |
Master D a b |
| 64 |
Master D 1 A |
| 65 |
Master D 2 B |
| 66 |
Master D 3 C |
| 67 |
Master D 4 D |
| 68 |
|
| 69 |
*** Testing schema A->B->D->A if C has failure *** |
| 70 |
|
| 71 |
* Do failure for C and then make new connection B->D * |
| 72 |
STOP SLAVE; |
| 73 |
Warnings: |
| 74 |
Warning 1287 'STOP SLAVE' is deprecated and will be removed in a future release. Please use STOP REPLICA instead |
| 75 |
SET GLOBAL SQL_REPLICA_SKIP_COUNTER = 1; |
| 76 |
include/start_slave.inc |
| 77 |
INSERT INTO t1 VALUES(6,'C',2); |
| 78 |
lock table t1 write /* must block B_2^6 coming */; |
| 79 |
INSERT INTO t1(b,c) VALUES('B',2); |
| 80 |
unlock tables; |
| 81 |
include/wait_for_slave_sql_error.inc [errno=1062] |
| 82 |
INSERT INTO t1(b,c) VALUES('A',2); |
| 83 |
INSERT INTO t1(b,c) VALUES('D',2); |
| 84 |
|
| 85 |
* Data on servers (C failed) * |
| 86 |
SELECT 'Master A',a,b FROM t1 WHERE c = 2 ORDER BY a,b; |
| 87 |
Master A a b |
| 88 |
Master A 5 A |
| 89 |
Master A 8 D |
| 90 |
SELECT 'Master B',a,b FROM t1 WHERE c = 2 ORDER BY a,b; |
| 91 |
Master B a b |
| 92 |
Master B 5 A |
| 93 |
Master B 6 B |
| 94 |
Master B 8 D |
| 95 |
SELECT 'Master C',a,b FROM t1 WHERE c = 2 ORDER BY a,b; |
| 96 |
Master C a b |
| 97 |
Master C 6 C |
| 98 |
SELECT 'Master D',a,b FROM t1 WHERE c = 2 ORDER BY a,b; |
| 99 |
Master D a b |
| 100 |
Master D 8 D |
| 101 |
|
| 102 |
* Reconfigure replication to schema A->B->D->A * |
| 103 |
include/stop_slave_io.inc |
| 104 |
include/stop_slave.inc |
| 105 |
include/rpl_change_topology.inc [new topology=1->2->4->1,2->3] |
| 106 |
Warnings: |
| 107 |
Note #### Sending passwords in plain text without SSL/TLS is extremely insecure. |
| 108 |
Note #### Storing MySQL user name or password information in the connection metadata repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START REPLICA; see the 'START REPLICA Syntax' in the MySQL Manual for more information. |
| 109 |
include/start_slave.inc |
| 110 |
|
| 111 |
* Check data inserted before failure * |
| 112 |
SELECT 'Master A',a,b FROM t1 WHERE c = 2 ORDER BY a,b; |
| 113 |
Master A a b |
| 114 |
Master A 5 A |
| 115 |
Master A 6 B |
| 116 |
Master A 8 D |
| 117 |
SELECT 'Master B',a,b FROM t1 WHERE c = 2 ORDER BY a,b; |
| 118 |
Master B a b |
| 119 |
Master B 5 A |
| 120 |
Master B 6 B |
| 121 |
Master B 8 D |
| 122 |
SELECT 'Master C',a,b FROM t1 WHERE c = 2 ORDER BY a,b; |
| 123 |
Master C a b |
| 124 |
Master C 6 C |
| 125 |
SELECT 'Master D',a,b FROM t1 WHERE c = 2 ORDER BY a,b; |
| 126 |
Master D a b |
| 127 |
Master D 5 A |
| 128 |
Master D 6 B |
| 129 |
Master D 8 D |
| 130 |
|
| 131 |
* Check data inserted after failure * |
| 132 |
INSERT INTO t1(b,c) VALUES('A',3); |
| 133 |
INSERT INTO t1(b,c) VALUES('B',3); |
| 134 |
INSERT INTO t1(b,c) VALUES('D',3); |
| 135 |
include/rpl_sync.inc |
| 136 |
SELECT 'Master A',a,b FROM t1 WHERE c = 3 ORDER BY a,b; |
| 137 |
Master A a b |
| 138 |
Master A 9 A |
| 139 |
Master A 10 B |
| 140 |
Master A 12 D |
| 141 |
SELECT 'Master B',a,b FROM t1 WHERE c = 3 ORDER BY a,b; |
| 142 |
Master B a b |
| 143 |
Master B 9 A |
| 144 |
Master B 10 B |
| 145 |
Master B 12 D |
| 146 |
SELECT 'Master C',a,b FROM t1 WHERE c = 3 ORDER BY a,b; |
| 147 |
Master C a b |
| 148 |
SELECT 'Master D',a,b FROM t1 WHERE c = 3 ORDER BY a,b; |
| 149 |
Master D a b |
| 150 |
Master D 9 A |
| 151 |
Master D 10 B |
| 152 |
Master D 12 D |
| 153 |
|
| 154 |
*** Testing restoring scheme A->B->C->D->A after failure *** |
| 155 |
|
| 156 |
* Remove wrong event from C and restore B->C->D * |
| 157 |
include/stop_slave.inc |
| 158 |
DELETE FROM t1 WHERE a = 6; |
| 159 |
include/start_slave.inc |
| 160 |
RESET MASTER; |
| 161 |
RESET SLAVE; |
| 162 |
Warnings: |
| 163 |
Warning 1287 'RESET SLAVE' is deprecated and will be removed in a future release. Please use RESET REPLICA instead |
| 164 |
include/rpl_change_topology.inc [new topology=1->2->3->4->1] |
| 165 |
Warnings: |
| 166 |
Note #### Sending passwords in plain text without SSL/TLS is extremely insecure. |
| 167 |
Note #### Storing MySQL user name or password information in the connection metadata repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START REPLICA; see the 'START REPLICA Syntax' in the MySQL Manual for more information. |
| 168 |
include/start_slave.inc |
| 169 |
include/rpl_sync.inc |
| 170 |
|
| 171 |
* Check data inserted before restoring schema A->B->C->D->A * |
| 172 |
SELECT 'Master A',a,b FROM t1 WHERE c IN (2,3) ORDER BY a,b; |
| 173 |
Master A a b |
| 174 |
Master A 5 A |
| 175 |
Master A 6 B |
| 176 |
Master A 8 D |
| 177 |
Master A 9 A |
| 178 |
Master A 10 B |
| 179 |
Master A 12 D |
| 180 |
SELECT 'Master B',a,b FROM t1 WHERE c IN (2,3) ORDER BY a,b; |
| 181 |
Master B a b |
| 182 |
Master B 5 A |
| 183 |
Master B 6 B |
| 184 |
Master B 8 D |
| 185 |
Master B 9 A |
| 186 |
Master B 10 B |
| 187 |
Master B 12 D |
| 188 |
SELECT 'Master C',a,b FROM t1 WHERE c IN (2,3) ORDER BY a,b; |
| 189 |
Master C a b |
| 190 |
Master C 5 A |
| 191 |
Master C 6 B |
| 192 |
Master C 8 D |
| 193 |
Master C 9 A |
| 194 |
Master C 10 B |
| 195 |
Master C 12 D |
| 196 |
SELECT 'Master D',a,b FROM t1 WHERE c IN (2,3) ORDER BY a,b; |
| 197 |
Master D a b |
| 198 |
Master D 5 A |
| 199 |
Master D 6 B |
| 200 |
Master D 8 D |
| 201 |
Master D 9 A |
| 202 |
Master D 10 B |
| 203 |
Master D 12 D |
| 204 |
|
| 205 |
* Check data inserted after restoring schema A->B->C->D->A * |
| 206 |
INSERT INTO t1(b,c) VALUES('A',4); |
| 207 |
INSERT INTO t1(b,c) VALUES('B',4); |
| 208 |
INSERT INTO t1(b,c) VALUES('C',4); |
| 209 |
INSERT INTO t1(b,c) VALUES('D',4); |
| 210 |
include/rpl_sync.inc |
| 211 |
SELECT 'Master A',a,b FROM t1 WHERE c = 4 ORDER BY a,b; |
| 212 |
Master A a b |
| 213 |
Master A 13 A |
| 214 |
Master A 14 B |
| 215 |
Master A 15 C |
| 216 |
Master A 16 D |
| 217 |
SELECT 'Master B',a,b FROM t1 WHERE c = 4 ORDER BY a,b; |
| 218 |
Master B a b |
| 219 |
Master B 13 A |
| 220 |
Master B 14 B |
| 221 |
Master B 15 C |
| 222 |
Master B 16 D |
| 223 |
SELECT 'Master C',a,b FROM t1 WHERE c = 4 ORDER BY a,b; |
| 224 |
Master C a b |
| 225 |
Master C 13 A |
| 226 |
Master C 14 B |
| 227 |
Master C 15 C |
| 228 |
Master C 16 D |
| 229 |
SELECT 'Master D',a,b FROM t1 WHERE c = 4 ORDER BY a,b; |
| 230 |
Master D a b |
| 231 |
Master D 13 A |
| 232 |
Master D 14 B |
| 233 |
Master D 15 C |
| 234 |
Master D 16 D |
| 235 |
|
| 236 |
*** Clean up *** |
| 237 |
DROP TABLE t1; |
| 238 |
include/rpl_end.inc |