Debian Codegraph @

mysql-test/suite/rpl/t/rpl_circular_for_4_hosts.test

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