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
#############################################################
8
--source include/have_innodb.inc
10
# 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
16
--echo *** Preparing data ***
18
CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT, b VARCHAR(100), c INT NOT NULL, PRIMARY KEY(a)) ENGINE=MyISAM;
19
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
--echo *** Testing schema A->B->C->D->A ***
30
# insert data via all hosts
32
INSERT INTO t1(b,c) VALUES('A',1);
33
--sync_slave_with_master master_b
34
INSERT INTO t1(b,c) VALUES('B',1);
35
--sync_slave_with_master master_c
36
INSERT INTO t1(b,c) VALUES('C',1);
37
--sync_slave_with_master master_d
38
INSERT INTO t1(b,c) VALUES('D',1);
40
--source include/circular_rpl_for_4_hosts_sync.inc
43
SELECT 'Master A',a,b FROM t1 WHERE c = 1 ORDER BY a,b;
45
SELECT 'Master B',a,b FROM t1 WHERE c = 1 ORDER BY a,b;
47
SELECT 'Master C',a,b FROM t1 WHERE c = 1 ORDER BY a,b;
49
SELECT 'Master D',a,b FROM t1 WHERE c = 1 ORDER BY a,b;
52
--echo *** Testing schema A->B->D->A if C has failure ***
54
--echo * Do failure for C and then make new connection B->D *
55
# Do not replicate next event from C
57
connect(slave,127.0.0.1,root,,test,$SLAVE_MYPORT2);
59
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
60
source include/start_slave.inc;
63
INSERT INTO t1 VALUES(6,'C',2);
66
INSERT INTO t1(b,c) VALUES('B',2);
67
# Wait while C will stop.
69
source include/wait_for_slave_sql_to_stop.inc;
71
INSERT INTO t1(b,c) VALUES('A',2);
73
INSERT INTO t1(b,c) VALUES('D',2);
76
# Sync all servers except C
78
let $wait_condition= SELECT COUNT(*)=3 FROM t1 WHERE a > 4;
79
--source include/wait_condition.inc
82
--echo * Data on servers (C failed) *
83
# Masters C,D shouldn't have correct data
85
SELECT 'Master A',a,b FROM t1 WHERE c = 2 ORDER BY a,b;
87
SELECT 'Master B',a,b FROM t1 WHERE c = 2 ORDER BY a,b;
89
SELECT 'Master C',a,b FROM t1 WHERE c = 2 ORDER BY a,b;
91
SELECT 'Master D',a,b FROM t1 WHERE c = 2 ORDER BY a,b;
94
--echo * Reconfigure replication to schema A->B->D->A *
98
--let $pos_c= query_get_value(SHOW SLAVE STATUS, Exec_Master_Log_Pos, 1)
99
--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);
105
source include/start_slave.inc;
107
--connection master_b
108
--sync_slave_with_master master_d
109
--sync_slave_with_master master_a
112
--echo * Check data inserted before failure *
113
--connection master_a
114
SELECT 'Master A',a,b FROM t1 WHERE c = 2 ORDER BY a,b;
115
--connection master_b
116
SELECT 'Master B',a,b FROM t1 WHERE c = 2 ORDER BY a,b;
117
--connection master_c
118
SELECT 'Master C',a,b FROM t1 WHERE c = 2 ORDER BY a,b;
119
--connection master_d
120
SELECT 'Master D',a,b FROM t1 WHERE c = 2 ORDER BY a,b;
123
--echo * Check data inserted after failure *
124
--connection master_a
125
INSERT INTO t1(b,c) VALUES('A',3);
126
--connection master_b
127
INSERT INTO t1(b,c) VALUES('B',3);
128
--connection master_d
129
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
138
SELECT 'Master A',a,b FROM t1 WHERE c = 3 ORDER BY a,b;
139
--connection master_b
140
SELECT 'Master B',a,b FROM t1 WHERE c = 3 ORDER BY a,b;
141
--connection master_c
142
SELECT 'Master C',a,b FROM t1 WHERE c = 3 ORDER BY a,b;
143
--connection master_d
144
SELECT 'Master D',a,b FROM t1 WHERE c = 3 ORDER BY a,b;
145
--connection master_a
148
--echo *** Testing restoring scheme A->B->C->D->A after failure ***
150
# Master D will ignore a next event from C so that event will not be
151
# distributed to other servers
152
--echo * Remove wrong event from C and restore B->C->D *
153
--connection master_d
154
source include/stop_slave.inc;
155
--connection master_c
156
DELETE FROM t1 WHERE a = 6;
158
--connection master_b
159
--sync_slave_with_master master_c
161
--let $file_d= query_get_value(SHOW MASTER STATUS, File, 1)
162
--let $pos_d= query_get_value(SHOW MASTER STATUS, Position, 1)
163
--connection master_d
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
173
--echo * Check data inserted before restoring schema A->B->C->D->A *
174
--connection master_a
175
SELECT 'Master A',a,b FROM t1 WHERE c IN (2,3) ORDER BY a,b;
176
--sync_slave_with_master master_b
177
SELECT 'Master B',a,b FROM t1 WHERE c IN (2,3) ORDER BY a,b;
178
--sync_slave_with_master master_c
179
SELECT 'Master C',a,b FROM t1 WHERE c IN (2,3) ORDER BY a,b;
180
--sync_slave_with_master master_d
181
SELECT 'Master D',a,b FROM t1 WHERE c IN (2,3) ORDER BY a,b;
182
--sync_slave_with_master master_a
185
--echo * Check data inserted after restoring schema A->B->C->D->A *
186
--connection master_a
187
INSERT INTO t1(b,c) VALUES('A',4);
188
--connection master_b
189
INSERT INTO t1(b,c) VALUES('B',4);
190
--connection master_c
191
INSERT INTO t1(b,c) VALUES('C',4);
192
--connection master_d
193
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
199
SELECT 'Master A',a,b FROM t1 WHERE c = 4 ORDER BY a,b;
200
--connection master_b
201
SELECT 'Master B',a,b FROM t1 WHERE c = 4 ORDER BY a,b;
202
--connection master_c
203
SELECT 'Master C',a,b FROM t1 WHERE c = 4 ORDER BY a,b;
204
--connection master_d
205
SELECT 'Master D',a,b FROM t1 WHERE c = 4 ORDER BY a,b;
206
--connection master_a
209
--echo * Transactions with commits *
210
# Testing mixing of transactions and regular inserts
211
--connection master_a
213
--connection master_c
216
--connection master_a
219
--connection master_a
220
INSERT INTO t2(b,c) VALUES('A',1);
221
--connection master_b
222
INSERT INTO t2(b,c) VALUES('B',1);
223
--connection master_c
224
INSERT INTO t2(b,c) VALUES('C',1);
225
--connection master_d
226
INSERT INTO t2(b,c) VALUES('D',1);
229
--connection master_a
231
--connection master_c
233
--connection master_a
236
--source include/circular_rpl_for_4_hosts_sync.inc
238
--connection master_a
239
SELECT 'Master A',b,COUNT(*) FROM t2 WHERE c = 1 GROUP BY b ORDER BY b;
240
--connection master_b
241
SELECT 'Master B',b,COUNT(*) FROM t2 WHERE c = 1 GROUP BY b ORDER BY b;
242
--connection master_c
243
SELECT 'Master C',b,COUNT(*) FROM t2 WHERE c = 1 GROUP BY b ORDER BY b;
244
--connection master_d
245
SELECT 'Master D',b,COUNT(*) FROM t2 WHERE c = 1 GROUP BY b ORDER BY b;
246
--connection master_a
249
--echo * Transactions with rollbacks *
250
# Testing mixing of transactions with rollback and regular inserts
251
--connection master_a
253
--connection master_c
256
--connection master_a
259
--connection master_a
260
INSERT INTO t2(b,c) VALUES('A',2);
261
--connection master_b
262
INSERT INTO t2(b,c) VALUES('B',2);
263
--connection master_c
264
INSERT INTO t2(b,c) VALUES('C',2);
265
--connection master_d
266
INSERT INTO t2(b,c) VALUES('D',2);
269
--connection master_a
271
--connection master_c
273
--connection master_a
276
--source include/circular_rpl_for_4_hosts_sync.inc
278
--connection master_a
279
SELECT 'Master A',b,COUNT(*) FROM t2 WHERE c = 2 GROUP BY b ORDER BY b;
280
--connection master_b
281
SELECT 'Master B',b,COUNT(*) FROM t2 WHERE c = 2 GROUP BY b ORDER BY b;
282
--connection master_c
283
SELECT 'Master C',b,COUNT(*) FROM t2 WHERE c = 2 GROUP BY b ORDER BY b;
284
--connection master_d
285
SELECT 'Master D',b,COUNT(*) FROM t2 WHERE c = 2 GROUP BY b ORDER BY b;
286
--connection master_a
291
--echo *** Clean up ***
292
--connection master_a
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