Debian Codegraph @

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

raw
1 #
2 # WL#1697: Multi source Replication
3 #
4 # This test verifies that the replication performance schema tables work fine
5 # with multi-source replication.
6 # General idea of the test:
7 # 1) test the count of the rows in all the tables are fine.
8 # 2) count decreases or increases with channels
9 # 3) Joins could be made without error
10 #
11 # Note: we do not test exclusively the correctness of each field of all
12 # channels. The tests to test the correctness of individual channel (i.e default)
13 # are present in rpl_perfschema_* tests.
14
15 #Skip on group replication runs
16 --source include/not_group_replication_plugin.inc
17 --source include/have_binlog_format_mixed.inc
18 --source include/not_have_privilege_checks_user.inc
19 # Test requires master-info-repository=TABLE, relay-log-info-repository=TABLE
20 --source include/have_slave_repository_type_table.inc
21 --echo #
22 --echo # Set up masters server_1 and server_3 with server_2 being a slave.
23 --echo #
24 --let $rpl_topology= 1->2,3->2
25 --let $rpl_multi_source= 1
26 --source include/rpl_init.inc
27
28 --echo #
29 --echo # Test case 1: Create multiple sources and make sure that
30 --echo # number of sources= number of rows in all the performance schema tables.
31 --echo # On master, where replication is not setup, it shall show 0 rows.
32 --let $rpl_connection_name= server_1
33 --source include/rpl_connection.inc
34
35 --let $num= `select count(*) from performance_schema.replication_connection_configuration`
36 --let $assert_text= Number of rows = 0 on master for repliation_connection_configuration table.
37 --let $assert_cond= $num = 0;
38 --source include/assert.inc
39
40 --let $num= `select count(*) from performance_schema.replication_connection_status`
41 --let $assert_text= Number of rows = 0 on master for repliation_connection_status table.
42 --let $assert_cond= $num= 0;
43 --source include/assert.inc
44
45 --let $num= `select count(*) from performance_schema.replication_applier_configuration`
46 --let $assert_text= Number of rows = 0 on master for repliation_applier_configuration table.
47 --let $assert_cond= $num = 0;
48 --source include/assert.inc
49
50 --let $num= `select count(*) from performance_schema.replication_applier_status`
51 --let $assert_text= Number of rows = 0 on master for repliation_applier_status table.
52 --let $assert_cond= $num = 0;
53 --source include/assert.inc
54
55 --let $num= `select count(*) from performance_schema.replication_applier_status_by_coordinator`
56 --let $assert_text= Number of rows = 0 on master for replication_applier_status_by coordinator table.
57 --let $assert_cond= $num = 0;
58 --source include/assert.inc
59
60 --let $num= `select count(*) from performance_schema.replication_applier_status_by_worker`
61 --let $assert_text= Number of rows = 0 on master for replication_applier_status_by_worker table.
62 --let $assert_cond= "$num" = 0;
63 --source include/assert.inc
64
65 --echo #
66 --echo # Test case 2: On the slave, number of rows should be zero for a non existent channel
67 --echo #
68 --let $rpl_connection_name= server_2
69 --source include/rpl_connection.inc
70
71 --let $num= `select count(*) from performance_schema.replication_connection_configuration where channel_name="non_existent"`
72 --let $assert_text= Number of rows= 0 on slave for a non existent channel for repliation_connection_configuration table.
73 --let $assert_cond= $num = 0;
74 --source include/assert.inc
75
76 --let $num= `select count(*) from performance_schema.replication_connection_status where channel_name="non_existent"`
77 --let $assert_text= Number of rows= 0 on slave for a non existent channel for repliation_connection_status table.
78 --let $assert_cond= $num = 0;
79 --source include/assert.inc
80
81 --let $num= `select count(*) from performance_schema.replication_applier_configuration where channel_name="non_existent"`
82 --let $assert_text= Number of rows= 0 on slave for a non existent channel for repliation_applier_configuration table.
83 --let $assert_cond= $num= 0;
84 --source include/assert.inc
85
86 --let $num= `select count(*) from performance_schema.replication_applier_status where channel_name="non_existent"`
87 --let $assert_text= Number of rows= 0 on slave for a non existent channel for repliation_applier_status table.
88 --let $assert_cond= $num = 0;
89 --source include/assert.inc
90
91 --let $num= `select count(*) from performance_schema.replication_applier_status_by_coordinator where channel_name="non_existent"`
92 --let $assert_text= Number of rows= 0 on slave for a non existent channel for repliation_applier_status_by_coordinaotor table.
93 --let $assert_cond= $num= 0;
94 --source include/assert.inc
95
96 --let $num= `select count(*) from performance_schema.replication_applier_status_by_worker where channel_name="non_existent"`
97 --let $assert_text= Number of rows= 0 on slave for a non existent channel for replication_applier_status_by_worker table.
98 --let $assert_cond= "$num"= 0;
99 --source include/assert.inc
100
101 --echo #
102 --echo # Test case 3: Rows on multi sourced slave, shall be 2. In workers table it should be
103 --echo # 2*@@global.replica_parallel_workers
104 USE performance_schema;
105
106 --let $assert_text= there shall be 2 rows for replication_connection_configuration
107 --let $assert_cond= [select count(*) from performance_schema.replication_connection_configuration] = 2;
108 --source include/assert.inc
109
110 --let $assert_text= there shall be 2 rows for replication_connection_status
111 --let $assert_cond= [select count(*) from performance_schema.replication_connection_status] = 2;
112 --source include/assert.inc
113
114 --let $assert_text= there shall be 2 rows for replication_applier_configuration
115 --let $assert_cond= [select count(*) from performance_schema.replication_applier_configuration] = 2 ;
116 --source include/assert.inc
117
118 --let $assert_text= there shall be 2 rows for replication_applier_status
119 --let $assert_cond= [select count(*) from performance_schema.replication_applier_status] = 2;
120 --source include/assert.inc
121
122 # Post Bug#20001173 fix in case of Single Threaded Slave(STS) mode SQL
123 # thread's status will be reported as part of
124 # performance_schema.replication_applier_status_by_worker table.
125 --let $w_rows= `select @@global.replica_parallel_workers`
126 if ($w_rows != 0)
127 {
128 --let $assert_text= there shall be 2 rows for replication_connection_configuration
129 --let $assert_cond= [select count(*) from performance_schema.replication_applier_status_by_coordinator] = 2;
130 --source include/assert.inc
131 }
132
133 if ($w_rows == 0)
134 {
135 --let $assert_text= there shall be 2 rows for replication_connection_configuration
136 --let $assert_cond= [select count(*) from performance_schema.replication_applier_status_by_worker] = 2;
137 --source include/assert.inc
138 }
139
140 # Post Bug#20001173 fix in case of Single Threaded Slave(STS) mode SQL
141 # thread's status will be reported as part of
142 # performance_schema.replication_applier_status_by_worker table.
143 # SQL thread will have the corresponding worker_id as 0.
144 --let $pfs_w_rows= `select count(*) from performance_schema.replication_applier_status_by_worker where worker_id != 0`
145 --let $w_rows= `select 2*@@global.replica_parallel_workers`
146 --let $assert_text= there shall be 2*global.replica_parallel_workers rows for 2 configured channels.
147 --let $assert_cond= "$pfs_w_rows" = "$w_rows"
148 --source include/assert.inc
149
150
151 --echo #
152 --echo # Test case 4: Verify that selects work on named channels
153 --echo # Do this verification by comparing with some known data
154 --echo #
155 #check that port value returned by channel_3 is same as SERVER_MYPORT_3
156 --let $channel_3_port= `select port from performance_schema.replication_connection_configuration where channel_name="channel_3"`
157 --let $assert_text= pfs query shall return the correct query value of the channel_3 port.
158 --let $assert_cond= "$channel_3_port"= "$SERVER_MYPORT_3"
159 --source include/assert.inc
160
161 --let $service_state= `select service_state from performance_schema.replication_connection_status where channel_name="channel_3"`
162 --let $assert_text= service state of receiver thread shall be ON.
163 --let $assert_cond= "ON" = "$service_state"
164 --source include/assert.inc
165
166 let $sss_value=query_get_value(SHOW SLAVE STATUS FOR CHANNEL 'channel_3', SQL_Delay, 1);
167 let $ps_value= query_get_value(select Desired_Delay from performance_schema.replication_applier_configuration where channel_name = "channel_3", Desired_Delay, 1);
168 let $assert_text= Value returned by SSS and PS table for Desired_Delay should be same.;
169 let $assert_cond= "$sss_value" = "$ps_value";
170 --source include/assert.inc
171
172 --let $service_state= `select service_state from performance_schema.replication_applier_status where channel_name="channel_3"`
173 --let $assert_text= service state shall be ON
174 --let $assert_cond= "ON" = "$service_state"
175 --source include/assert.inc
176
177 # Post Bug#20001173 fix in case of Single Threaded Slave(STS) mode SQL
178 # thread's status will be reported as part of
179 # performance_schema.replication_applier_status_by_worker table.
180 --let $service_state= `select service_state from performance_schema.replication_applier_status_by_worker where channel_name="channel_3"`
181 --let $assert_text= service state of coordinator shall be ON
182 --let $assert_cond= "ON" = "$service_state"
183 --source include/assert.inc
184
185 if ( `select @@GLOBAL.replica_parallel_workers > 0`)
186 {
187 --let $service_state= `select service_state from performance_schema.replication_applier_status_by_worker where channel_name="channel_3" and worker_id= 1`
188 --let $assert_text= service state of worker thread shall be ON
189 --let $assert_cond= "ON" = "$service_state"
190 --let $include_silent= 1
191 --source include/assert.inc
192 --let $include_silent= 0
193 }
194
195
196 --echo #
197 --echo # Stop the slave on channel_1 and verify that rows are still instact
198 --echo #
199 --let $rpl_channel_name= 'channel_1'
200 --source include/stop_slave.inc
201
202 --echo #
203 --echo # Test case 5: Verify that selects work on named channels even
204 --echo # after a channel is stopped. test that service state is OFF
205 --echo #
206
207 --let $channel_1_port= `select port from performance_schema.replication_connection_configuration where channel_name="channel_1"`
208 --let $assert_text= After stop slave for channel, replication_connection_configuration is intact
209 --let $assert_cond= "$channel_1_port"= "$SERVER_MYPORT_1"
210 --source include/assert.inc
211
212 --let $service_state= `select service_state from performance_schema.replication_connection_status where channel_name="channel_1"`
213 --let $assert_text= After stop slave for channel, service state is OFF for connection_status table.
214 --let $assert_cond= "OFF" = "$service_state"
215 --source include/assert.inc
216
217 --let $service_state= `select service_state from performance_schema.replication_applier_status where channel_name="channel_1"`
218 --let $assert_text= After stop slave for channel, service state is OFF for applier_status table.
219 --let $assert_cond= "OFF" = "$service_state"
220 --source include/assert.inc
221
222 # Post Bug#20001173 fix in case of Single Threaded Slave(STS) mode SQL
223 # thread's status will be reported as part of
224 # performance_schema.replication_applier_status_by_worker table.
225 --let $service_state= `select service_state from performance_schema.replication_applier_status_by_worker where channel_name="channel_1"`
226 --let $assert_text= After stop slave for channel, service state is OFF by replication_applier_status_by_worker table.
227 --let $assert_cond= "OFF" = "$service_state"
228 --source include/assert.inc
229
230 if ( `select @@GLOBAL.replica_parallel_workers > 0`)
231 {
232 --let $service_state= `select service_state from performance_schema.replication_applier_status_by_worker where channel_name="channel_1" and worker_id= 1`
233 --let $assert_text= service state of worker thread shall be OFF
234 --let $assert_cond= "OFF" = "$service_state"
235 --let $include_silent= 1
236 --source include/assert.inc
237 --let $include_silent= 0
238 }
239
240
241 --echo #
242 --echo # Test case 6): Do a sanity check that joins can be performed as replication tables
243 --echo # contain multiple rows now.
244 # Post Bug#20001173 fix in case of Single Threaded Slave(STS) mode SQL
245 # thread's status will be reported as part of
246 # performance_schema.replication_applier_status_by_worker table.
247 --let $w_rows= `select @@global.replica_parallel_workers`
248 if ($w_rows != 0)
249 {
250 --disable_query_log
251 --replace_column 3 # 4 # 6 # 7 # 13 #
252 --query_vertical(select connection_status.CHANNEL_NAME, GROUP_NAME, SOURCE_UUID, connection_status.THREAD_ID, connection_status.SERVICE_STATE, COUNT_RECEIVED_HEARTBEATS, LAST_HEARTBEAT_TIMESTAMP, RECEIVED_TRANSACTION_SET, connection_status.LAST_ERROR_NUMBER, connection_status.LAST_ERROR_MESSAGE, connection_status.LAST_ERROR_TIMESTAMP, coordinator_status.CHANNEL_NAME, coordinator_status.THREAD_ID, coordinator_status.SERVICE_STATE, coordinator_status.LAST_ERROR_NUMBER, coordinator_status.LAST_ERROR_MESSAGE, coordinator_status.LAST_ERROR_TIMESTAMP from replication_connection_status connection_status, replication_applier_status_by_coordinator coordinator_status where connection_status.channel_name= coordinator_status.channel_name)
253 --enable_query_log
254 }
255
256 if ($w_rows == 0)
257 {
258 --disable_query_log
259 --replace_column 3 # 4 # 6 # 7 # 13 #
260 --query_vertical(select connection_status.CHANNEL_NAME, GROUP_NAME, SOURCE_UUID, connection_status.THREAD_ID, connection_status.SERVICE_STATE, COUNT_RECEIVED_HEARTBEATS, LAST_HEARTBEAT_TIMESTAMP, RECEIVED_TRANSACTION_SET, connection_status.LAST_ERROR_NUMBER, connection_status.LAST_ERROR_MESSAGE, connection_status.LAST_ERROR_TIMESTAMP, worker_status.CHANNEL_NAME, worker_status.THREAD_ID, worker_status.SERVICE_STATE, worker_status.LAST_ERROR_NUMBER, worker_status.LAST_ERROR_MESSAGE, worker_status.LAST_ERROR_TIMESTAMP from replication_connection_status connection_status, replication_applier_status_by_worker worker_status where connection_status.channel_name= worker_status.channel_name and worker_status.worker_id=0)
261 --enable_query_log
262 }
263
264 --echo #
265 --echo # Test case 7: Delete channel_3 and count the number of rows in each table.
266 --echo #
267 --let $rpl_channel_name= 'channel_3'
268 --source include/stop_slave.inc
269 RESET SLAVE ALL FOR CHANNEL 'channel_3';
270
271 --let $assert_text= there shall be 1 in replication connection_configuration table after a channel is deleted.
272 --let $assert_cond= [select count(*) from performance_schema.replication_connection_configuration] = 1;
273 --source include/assert.inc
274
275 --let $assert_text= there shall be 1 in replication connection_status table after a channel is deleted.
276 --let $assert_cond= [select count(*) from performance_schema.replication_connection_status] = 1;
277 --source include/assert.inc
278
279 --let $assert_text= there shall be 1 in replication applier_config table after a channel is deleted.
280 --let $assert_cond= [select count(*) from performance_schema.replication_applier_configuration] = 1 ;
281 --source include/assert.inc
282
283 --let $assert_text= there shall be 1 in replication applier_status table after a channel is deleted.
284 --let $assert_cond= [select count(*) from performance_schema.replication_applier_status] = 1;
285 --source include/assert.inc
286
287 # Post Bug#20001173 fix in case of Single Threaded Slave(STS) mode SQL
288 # thread's status will be reported as part of
289 # performance_schema.replication_applier_status_by_worker table.
290 # SQL thread will have the corresponding worker_id as 0.
291 --let $w_rows= `select @@global.replica_parallel_workers`
292
293 if ($w_rows == 0)
294 {
295 --let $assert_text= there shall be 1 applier after a channel is deleted.
296 --let $assert_cond= [select count(*) from performance_schema.replication_applier_status_by_worker where worker_id=0] = 1;
297 --source include/assert.inc
298 --let $pfs_w_rows= `select count(*) from performance_schema.replication_applier_status_by_worker where worker_id !=0`
299 --let $w_rows= `select @@global.replica_parallel_workers`
300 --let $assert_text= there shall be global.replica_parallel_workers rows after a channel is deleted.
301 --let $assert_cond= "$pfs_w_rows" = "$w_rows"
302 --source include/assert.inc
303 }
304 if ($w_rows != 0)
305 {
306 --let $assert_text= there shall be 1 applier after a channel is deleted.
307 --let $assert_cond= [select count(*) from performance_schema.replication_applier_status_by_coordinator] = 1;
308 --source include/assert.inc
309 --let $pfs_w_rows= `select count(*) from performance_schema.replication_applier_status_by_worker`
310 --let $w_rows= `select @@global.replica_parallel_workers`
311 --let $assert_text= there shall be global.replica_parallel_workers rows after a channel is deleted.
312 --let $assert_cond= "$pfs_w_rows" = "$w_rows"
313 --source include/assert.inc
314 }
315
316
317
318 --echo #
319 --echo # Test case 8: Do a complete delete and see if the number of rows are zero
320 --echo #
321 RESET SLAVE ALL;
322
323
324 --let $assert_text= there shall be 0 rows after all channels are deleted.
325 --let $assert_cond= [select count(*) from performance_schema.replication_connection_configuration] = 0;
326 --source include/assert.inc
327
328 --let $assert_text= there shall be 0 rows after all channels are deleted.
329 --let $assert_cond= [select count(*) from performance_schema.replication_connection_status] = 0;
330 --source include/assert.inc
331
332 --let $assert_text= there shall be 0 rows after all channels are deleted.
333 --let $assert_cond= [select count(*) from performance_schema.replication_applier_configuration] = 0 ;
334 --source include/assert.inc
335
336 --let $assert_text= there shall be 0 rows after all channels are deleted.
337 --let $assert_cond= [select count(*) from performance_schema.replication_applier_status] = 0;
338 --source include/assert.inc
339
340 --let $assert_text= there shall be 0 rows after all channels are deleted.
341 --let $assert_cond= [select count(*) from performance_schema.replication_applier_status_by_coordinator] = 0;
342 --source include/assert.inc
343
344 --let $pfs_w_rows= `select count(*) from performance_schema.replication_applier_status_by_worker`
345 --let $w_rows= `select @@global.replica_parallel_workers`
346 --let $assert_text= there shall be global.replica_parallel_workers rows after a channel is deleted.
347
348 --let $assert_text= there shall be 0 rows after all channels are deleted.
349 --let $assert_cond= "$pfs_w_rows" = "0"
350 --source include/assert.inc
351
352 --let $rpl_skip_sync= 1
353 --let $rpl_skip_delete_channels= 1
354 --let $rpl_only_running_threads= 1
355 --let $rpl_group_replication= 1
356 --source include/rpl_end.inc