Debian Codegraph @

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

raw
1 #
2 # WL#1697: Multisource replication
3 #
4 # This is the basic test required in for multisource replication
5 # The aim of this file is to test the basic usecases of msr.
6 # 0. Create two masters and a slave and setup a multisource replication
7 # between them.
8 # 1. create a different databases on each master and test if they are replicated
9 # to the slave.
10 # 2. create a different table on each master and test if they are replicated to
11 # the to the slave.
12 # 3. Create a table with the same name on both masters and update non conflicting
13 # data on that table. Test if the replication is done properly.
14 # 4. Check if updates happen on different master such that the resulting
15 # data on slave is conflicting, check that one of the channels the slave
16 # SQL thread is stopped.
17 #
18 #
19 # Note: Out of convention, server 2 is always made a slave for multisource testing.
20 #
21
22 #Skip on group replication runs
23 --source include/not_group_replication_plugin.inc
24 # Test requires master-info-repository=TABLE, relay-log-info-repository=TABLE
25 --source include/have_slave_repository_type_table.inc
26
27 --echo #
28 --echo # set up masters server_1 and server_3 with server_2 being a slave.
29 --echo #.
30 --let $rpl_topology= 1->2,3->2
31 --let $rpl_multi_source= 1
32 --source include/rpl_init.inc
33
34 --echo #
35 --echo # Test case 1: 1.a) create a database and table db1.t1 on server_1
36 --echo # and insert values in the table.
37 --let $rpl_connection_name= server_1
38 --source include/rpl_connection.inc
39 CREATE DATABASE db1;
40 CREATE TABLE db1.t1 ( a int);
41 --let $i= 10
42 while($i)
43 {
44 eval INSERT INTO db1.t1 VALUES ($i);
45 --dec $i
46 }
47 SHOW DATABASES;
48
49 --echo #
50 --echo # 1.b create database and table db3.t1 on server_3
51 --echo # and insert values in db3.t1
52 --let $rpl_connection_name= server_3
53 --source include/rpl_connection.inc
54 CREATE DATABASE db3;
55 CREATE TABLE db3.t1 (a int);
56 --let $i= 10
57 while($i)
58 {
59 eval INSERT INTO db3.t1 VALUES (3*$i);
60 --dec $i
61 }
62 SHOW DATABASES;
63
64 --echo #
65 --echo # 1.c sync the slave (server_2) with both masters (server_1 and server_3)
66 --echo #
67 #sync the slave with server_1
68 --let $rpl_connection_name= server_1
69 --source include/rpl_connection.inc
70
71 --let $rpl_channel_name= 'channel_1'
72 --let $sync_slave_connection= server_2
73 --source include/sync_slave_sql_with_master.inc
74
75 # sync the slave with server_3
76 --let $rpl_connection_name= server_3
77 --source include/rpl_connection.inc
78
79 --let $rpl_channel_name= 'channel_3'
80 --let $sync_slave_connection=server_2
81 --source include/sync_slave_sql_with_master.inc
82
83 --echo #
84 --echo # 1.d Check that slave has replicated from both masters.
85 --echo # Slave should have databases db1 and db3 and tables.
86 SHOW DATABASES;
87 --let $assert_text= Slave should have 10 rows in db1.t1
88 --let $assert_cond= `SELECT COUNT(*) = 10 `rows` FROM db1.t1`
89 --source include/assert.inc
90 --let $assert_text= Slave should have 10 rows in db3.t1
91 --let $assert_cond= `SELECT COUNT(*) = 10 `rows` FROM db3.t1`
92 --source include/assert.inc
93
94 --echo #
95 --echo # Stop slave (on server_2) for test case 2.
96 --echo #
97 --source include/rpl_stop_slaves.inc
98
99 --echo #
100 --echo # Test case 2: 2.a) Test that different tables in the same database
101 --echo # are replicated to the slave
102
103 #create dbcommon.t11 on server_1
104 --let $rpl_connection_name= server_1
105 --source include/rpl_connection.inc
106
107 CREATE DATABASE dbcommon;
108
109 ### Make sure that this is the first create database to reach
110 ### the slave.
111 --let $rpl_channel_name= 'channel_1'
112
113 --let $rpl_connection_name= server_2
114 --source include/rpl_connection.inc
115
116 --source include/start_slave.inc
117
118 --let $rpl_connection_name= server_1
119 --source include/rpl_connection.inc
120
121 --let $rpl_channel_name= 'channel_1'
122 --let $sync_slave_connection=server_2
123 --source include/sync_slave_sql_with_master.inc
124
125 --source include/stop_slave.inc
126
127 --let $rpl_channel_name=
128
129 #sync the slave with server_1
130 --let $rpl_connection_name= server_1
131 --source include/rpl_connection.inc
132
133 CREATE TABLE dbcommon.t11(a int);
134
135 --let $i= 10
136 while($i)
137 {
138 eval INSERT INTO dbcommon.t11 values (11*$i);
139 --dec $i
140 }
141
142 #create dbcommon.t11 on server_2
143 # IF NOT EXISTS added to avoid data conflicts
144 #
145
146 --let $rpl_connection_name= server_3
147 --source include/rpl_connection.inc
148
149 CREATE DATABASE IF NOT EXISTS dbcommon;
150 CREATE TABLE dbcommon.t33(a int);
151 --let $i= 10
152 while ($i)
153 {
154 eval INSERT INTO dbcommon.t33 values(33*$i);
155 --dec $i
156 }
157 --echo #
158 --echo # 2.b Start replication from the masters
159 --echo #
160 --let $rpl_connection_name= server_2
161 --source include/rpl_connection.inc
162
163 --disable_warnings
164 --source include/rpl_start_slaves.inc
165 --enable_warnings
166
167 --echo #
168 --echo # 2.c sync the slave with both masters
169 --echo #
170 #sync the slave with server_1
171 --let $rpl_connection_name= server_1
172 --source include/rpl_connection.inc
173
174 --let $rpl_channel_name= 'channel_1'
175 --let $sync_slave_connection=server_2
176 --source include/sync_slave_sql_with_master.inc
177
178 # sync the slave with server_3
179 --let $rpl_connection_name= server_3
180 --source include/rpl_connection.inc
181
182 --let $rpl_channel_name= 'channel_3'
183 --let $sync_slave_connection= server_2
184 --source include/sync_slave_sql_with_master.inc
185
186 --echo #
187 --echo # 2.d Now check the data
188 --echo #
189 SHOW DATABASES;
190 USE dbcommon;
191 SHOW TABLES;
192 --let $assert_text= Slave should have 10 rows in dbcommon.t11
193 --let $assert_cond= `SELECT COUNT(*) = 10 `rows` FROM dbcommon.t11`
194 --source include/assert.inc
195 --let $assert_text= Slave should have 10 rows in dbcommon.t33
196 --let $assert_cond= `SELECT COUNT(*) = 10 `rows` FROM dbcommon.t33`
197 --source include/assert.inc
198
199 --echo #
200 --echo # Test case 3: 3.a) Update nonconflicting data on the same table
201 --echo # and see if they are replicated to the slave.
202
203 --let $rpl_connection_name= server_1
204 --source include/rpl_connection.inc
205 CREATE TABLE dbcommon.t101(a int, PRIMARY KEY (a));
206
207 ### Make sure that this is the first create table to reach
208 ### the slave.
209
210 #sync the slave with server_1
211 --let $rpl_connection_name= server_1
212 --source include/rpl_connection.inc
213
214 --let $rpl_channel_name= 'channel_1'
215 --let $sync_slave_connection=server_2
216 --source include/sync_slave_sql_with_master.inc
217 --let $rpl_channel_name=
218
219 --echo #
220 --echo # Stop slave (on server_2) for test case 3.
221 --echo #
222 --source include/rpl_stop_slaves.inc
223
224 #sync the slave with server_1
225 --let $rpl_connection_name= server_1
226 --source include/rpl_connection.inc
227
228 --let $i= 10
229 while($i)
230 {
231 eval INSERT INTO dbcommon.t101 values (101*$i);
232 --dec $i
233 }
234
235 --let $rpl_connection_name= server_3
236 --source include/rpl_connection.inc
237 CREATE TABLE IF NOT EXISTS dbcommon.t101(a int, PRIMARY KEY (a));
238 --let $i= 10
239 while ($i)
240 {
241 eval INSERT INTO dbcommon.t101 values(33*$i);
242 --dec $i
243 }
244
245 --echo #
246 --echo # 3.b Start replication from the masters
247 --echo #
248
249 --let $rpl_connection_name= server_2
250 --source include/rpl_connection.inc
251
252 --disable_warnings
253 --source include/rpl_start_slaves.inc
254 --enable_warnings
255
256 --echo #
257 --echo # 3.c sync slave server with all the masters.
258 --echo #
259 --let $rpl_connection_name= server_1
260 --source include/rpl_connection.inc
261
262 --let $rpl_channel_name= 'channel_1'
263 --let $sync_slave_connection= server_2
264 --source include/sync_slave_sql_with_master.inc
265
266 # sync the slave with server_3
267 --let $rpl_connection_name= server_3
268 --source include/rpl_connection.inc
269
270 --let $rpl_channel_name= 'channel_3'
271 --let $sync_slave_connection= server_2
272 --source include/sync_slave_sql_with_master.inc
273
274 --echo #
275 --echo # check that data has been replicated to the slave.
276 --echo #
277 USE dbcommon;
278 SHOW TABLES;
279 --let $assert_text= Slave should have 20 rows in dbcommon.t101
280 --let $assert_cond= `SELECT COUNT(*) = 20 `rows` FROM dbcommon.t101`
281 --source include/assert.inc
282
283
284 --echo #
285 --echo # Test case 4: Update tables on both masters with conflicting data
286 --echo # In this case, slave shall stop on a SQL thread.
287
288 --echo # 4.a) Do an update on server_1 and sync with the slave.
289 --let $rpl_connection_name= server_1
290 --source include/rpl_connection.inc
291 INSERT INTO dbcommon.t101 values(1729);
292
293 --let $rpl_channel_name= 'channel_1'
294 --let $sync_slave_connection= server_2
295 --source include/sync_slave_sql_with_master.inc
296
297 --echo # 4.b) Do a conflicting update on server_3 and check for conflicting error
298 --let $rpl_connection_name= server_3
299 --source include/rpl_connection.inc
300 INSERT INTO dbcommon.t101 values (1729);
301
302 # connection to be shifted to slave
303 --let $rpl_connection_name= server_2
304 --source include/rpl_connection.inc
305
306 #suppress warnings and errors due to conflicts in MTS and non MTS mode
307 call mtr.add_suppression("Replica SQL.*:.* 'Duplicate entry '1729' for key 't101.PRIMARY'' on query.*");
308 call mtr.add_suppression("Replica SQL.*: Could not execute Write_rows event on table dbcommon.t101; Duplicate entry '1729' for key 't101.PRIMARY'*");
309 call mtr.add_suppression("The replica coordinator and worker threads are stopped, possibly leaving data in inconsistent state");
310 call mtr.add_suppression("Replica SQL.*:.* Duplicate entry '1729' for key 't101.PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY.*");
311
312 # sync the slave with server_3
313 --let $rpl_channel_name= 'channel_3'
314 --let $slave_sql_errno= 1062
315 --source include/wait_for_slave_sql_error.inc
316
317 #check if the error is present in pfs table
318 let $sss_value= query_get_value(SHOW SLAVE STATUS FOR CHANNEL "channel_3", Last_SQL_Errno, 1);
319 # Post Bug#20001173 fix, in STS mode check performance_schema.replication_applier_status_by_worker
320 # table for applier status
321 let $worker_count= `select @@global.replica_parallel_workers`;
322 if ($worker_count == 0)
323 {
324 let $ps_value= query_get_value(select Last_Error_Number from performance_schema.replication_applier_status_by_worker where channel_name = "channel_3", Last_Error_Number, 1);
325 }
326 # In MTS mode check performance_schema.replication_applier_status_by_coordinator
327 # table for applier status
328 if ($worker_count != 0)
329 {
330 let $ps_value= query_get_value(select Last_Error_Number from performance_schema.replication_applier_status_by_coordinator where channel_name = "channel_3", Last_Error_Number, 1);
331 }
332
333 let $assert_text= Value returned by SSS and PS table for Last_Error_Number should be same.;
334 let $assert_cond= "$sss_value" = "$ps_value";
335 --source include/assert.inc
336
337 # since conflict has occurred remove the entry from the slave
338 # and start slave again
339 DELETE FROM dbcommon.t101 where a=1729;
340
341 --disable_warnings
342 --let $rpl_channel_name= 'channel_3'
343 --source include/start_slave.inc
344 --enable_warnings
345
346 #
347 # Cleanup; This involves deleting/dropping and manual syncing
348 #
349
350 # sync the channels before anything else, so that previous
351 # events from different channels do not have the chance of
352 # being overtaken by the next statements and thus cause
353 # random failures
354
355 --echo # channel 1
356 --let $rpl_connection_name= server_1
357 --source include/rpl_connection.inc
358
359 --let $rpl_channel_name= 'channel_1'
360 --let $sync_slave_connection= server_2
361 --source include/sync_slave_sql_with_master.inc
362
363 --echo # channel 3
364 --let $rpl_connection_name= server_3
365 --source include/rpl_connection.inc
366
367 --let $rpl_channel_name= 'channel_3'
368 --let $sync_slave_connection= server_2
369 --source include/sync_slave_sql_with_master.inc
370
371 # now do the clean up
372
373 --echo connection_server_1
374 --connection server_1
375 DROP DATABASE db1;
376 DROP DATABASE dbcommon;
377 --let $rpl_channel_name= 'channel_1'
378 --let $sync_slave_connection= server_2
379 --source include/sync_slave_sql_with_master.inc
380
381 --echo connection_server_3
382 --connection server_3
383 DROP DATABASE db3;
384 DROP DATABASE IF EXISTS dbcommon;
385 --let $rpl_channel_name= 'channel_3'
386 --let $sync_slave_connection= server_2
387 --source include/sync_slave_sql_with_master.inc
388
389 --let $rpl_skip_sync= 1
390 --source include/rpl_end.inc