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