Debian Codegraph @

mysql-test/suite/rpl_nogtid/t/rpl_perfschema_applier_status_by_worker.test

raw
1 # ==== Purpose ====
2 #
3 # This test script serves as the functionality testing for the table
4 # performance_schema.replication_applier_status_by_worker. Test
5 # for ddl and dml operations is a part of the perfschema suite.
6 # The ddl/dml tests are named:
7 # 1) ddl_replication_applier_status_by_worker.test and
8 # 2) dml_replication_applier_status_by_worker.test.
9 #
10 # This test script does the following:
11
12 # - Verify that SELECT works for every field in the table.
13 # - The SELECT per field produces an output similar to the corresponding field
14 # in SHOW SLAVE STATUS(SSS), if there is one.
15 # - If there is no matching field in SSS, we resort to other method of testing
16 # those fields.
17 # - We perform all the testing on connection "slave". On master, the table
18 # returns an empty set.
19 #
20 # The follwing scenarios are tested in this test script:
21 #
22 # - Test each field on a fresh replication setup.
23 # - Introduce error in worker thread and check for the correctness of error
24 # error number, message and timestamp.
25 # - Verify that, the change in values are correctly shown by the table.
26 # - Verify that the values are preserved after STOP SLAVE.
27 # - Set up replication in gtid-mode=on and test 'Last_Seen_Transaction' field.
28 # - Verify that the value in 'Last_Seen_Transaction' field is preserved after
29 # STOP SLAVE.
30 #
31 # ==== Related Bugs and Worklogs ====
32 #
33 # WL#3656: PERFORMANCE SCHEMA table for SHOW SLAVE STATUS
34 # Bug#18358253: RPL.RPL_PERFSCHEMA_EXECUTE_STATUS_BY_WORKER HAS SPORADIC FAILURES ON PB2
35 #
36 # Bug #18360716 ERRORS IN MTS WORKER THREADS REPORTED WRONGLY FOR ANONYMOUS TRANSACTIONS
37 # - The result file was updated after fixing this bug.
38
39 --source include/not_group_replication_plugin.inc
40 source include/have_binlog_format_mixed.inc;
41 source include/master-slave.inc;
42
43 call mtr.add_suppression("Error 'Table 'test.t' doesn't exist' on query.");
44 call mtr.add_suppression("The replica coordinator and worker threads are stopped, possibly leaving data in inconsistent state.");
45 call mtr.add_suppression("Request to stop replica SQL Thread received while applying a group that has non-transactional changes;");
46
47 let $assert_text= On master, the table should return an empty set.;
48 let $assert_cond= [select count(*) from performance_schema.replication_applier_status_by_worker] = 0;
49 source include/assert.inc;
50
51 --echo
52 --echo # Setup MTS and perform testing on a fresh slave.
53 --echo
54
55 --source include/sync_slave_sql_with_master.inc
56 --connection slave
57
58 # PB2 runs the tests with slave-parallel-worker=4 in a combination.
59 # Reset it to 0 beacause the following test for empty set is valid only
60 # in non-MTS slave.
61
62 source include/stop_slave.inc;
63 SET @save_replica_parallel_workers=@@global.replica_parallel_workers;
64 SET @@global.replica_parallel_workers=0;
65 # to avoid warnings
66 set @save_replica_transaction_retries= @@global.replica_transaction_retries;
67 source include/start_slave.inc;
68
69 # Post Bug#20001173 fix in case of Single Threaded Slave(STS) mode SQL
70 # thread's staus will be reported as part of
71 # performance_schema.replication_applier_status_by_worker table.
72 let $assert_text= In non-MTS mode(SQL thread), the table should return one row as Single SQL applier thread will be part of replication_applier_status_by_worker table.;
73 let $assert_cond= [select count(*) from performance_schema.replication_applier_status_by_worker] = 1;
74 source include/assert.inc;
75
76 let $assert_text= In non-MTS mode(SQL thread), the table should return empty result set;
77 let $assert_cond= [select count(*) from performance_schema.replication_applier_status_by_coordinator] = 0;
78 source include/assert.inc;
79 source include/stop_slave.inc;
80
81 SET @@global.replica_parallel_workers=1;
82 # to avoid warnings
83 set @save_replica_transaction_retries= @@global.replica_transaction_retries;
84 source include/start_slave.inc;
85
86 let $ps_value= query_get_value(select Worker_Id from performance_schema.replication_applier_status_by_worker, Worker_Id, 1);
87 let $assert_text= Worker_Id should be 1;
88 let $assert_cond= "$ps_value"= 1;
89 source include/assert.inc;
90
91 # To verify the correctness of thread_id field, we check for the name of
92 # the thread.
93
94 let $thread_name= `select name from performance_schema.threads where thread_id= (select Thread_Id from performance_schema.replication_applier_status_by_worker)`;
95 let $assert_text= thread_name should should indicate worker thread.;
96 let $assert_cond= "$thread_name" = "thread/sql/replica_worker";
97 source include/assert.inc;
98
99 let $ps_value= query_get_value(select Service_State from performance_schema.replication_applier_status_by_worker, Service_State, 1);
100 let $assert_text= Service_State should be "ON" on a fresh slave server.;
101 let $assert_cond= "$ps_value"= "ON";
102 source include/assert.inc;
103
104 --connection master
105 CREATE TABLE t1 (a INT);
106 DROP TABLE t1;
107 --sync_slave_with_master
108
109 let $sss_value= query_get_value(SHOW SLAVE STATUS, Last_SQL_Errno, 1);
110 let $ps_value= query_get_value(select Last_Error_Number from performance_schema.replication_applier_status_by_worker, Last_Error_Number, 1);
111 let $assert_text= Value returned by SSS and PS table for Last_Error_Number should be same.;
112 let $assert_cond= "$sss_value" = "$ps_value";
113 source include/assert.inc;
114
115 let $sss_value= query_get_value(SHOW SLAVE STATUS, Last_SQL_Error, 1);
116 let $ps_value= query_get_value(select Last_Error_Message from performance_schema.replication_applier_status_by_worker, Last_Error_Message, 1);
117 let $assert_text= Value returned by SSS and PS table for Last_Error_Message should both be empty.;
118 let $assert_cond= "$sss_value" = "$ps_value";
119 source include/assert.inc;
120
121 let $sss_value= query_get_value(SHOW SLAVE STATUS, Last_SQL_Error_Timestamp, 1);
122 let $ps_value= query_get_value(select Last_Error_Timestamp from performance_schema.replication_applier_status_by_worker, Last_Error_Timestamp, 1);
123 let $assert_text= Value returned by PS table for Last_Error_Timestamp should be 0000-00-00 00:00:00.000000.;
124 let $assert_cond= "$ps_value" = "0000-00-00 00:00:00.000000";
125 source include/assert.inc;
126
127 --echo
128 --echo # Introduce an error in the worker thread and check for the correctness
129 --echo # of error number, message and timestamp fields.
130 --echo
131
132 # Cause an error in Worker thread.
133 # 1) Create a table 't' at master, replicate at slave.
134 # 2) Drop table 't' at slave only.
135 # 3) Insert a value in table 't' on master and replicate on slave.
136 # Since slave doesnt have table 't' anymore, worker thread will report an error.
137
138 --connection master
139 use test;
140 create table t(a int primary key);
141 sync_slave_with_master;
142 drop table t;
143 --connection master
144 insert into t values(1);
145 --connection slave
146 let $slave_sql_errno=1146;
147 source include/wait_for_slave_sql_error.inc;
148
149 --echo
150 --echo # Extract the error related fields from SSS and PS table and compare
151 --echo # them for correctness.
152 --echo
153
154 let $sss_value= query_get_value(SHOW SLAVE STATUS, Last_SQL_Errno, 1);
155 let $ps_value= query_get_value(select Last_Error_Number from performance_schema.replication_applier_status_by_worker, Last_Error_Number, 1);
156 let $assert_text= Value returned by SSS and PS table for Last_Error_Number should be same.;
157 let $assert_cond= "$sss_value" = "$ps_value";
158 source include/assert.inc;
159
160 --disable_query_log
161 --replace_regex /master-bin.[0-9]+/FILENAME/ /end_log_pos [0-9]+/end_log_pos POSITION/
162 select Last_Error_Message from performance_schema.replication_applier_status_by_worker;
163 --enable_query_log
164
165 # The timestamp format is slightly different in SSS and PS.
166 # SSS => YYMMDD HH:MM:SS
167 # PS => YYYY-MM-DD HH:MM:SS.ssssss
168 # To match the two, we get rid of hyphons from PS output and first two digits
169 # the year field and the microseconds (after WL7319) so that it can be matched
170 # directly.
171
172 let $sss_value= query_get_value(SHOW SLAVE STATUS, Last_SQL_Error_Timestamp, 1);
173 let $ps_value= query_get_value(select Last_Error_Timestamp from performance_schema.replication_applier_status_by_worker, Last_Error_Timestamp, 1);
174 let $ps_value_without_hyphons= `SELECT REPLACE("$ps_value", '-', '')`;
175 let $ps_value_in_sss_format= `select substring("$ps_value_without_hyphons", 3)`;
176 let $ps_value_without_microseconds= `select substring_index("$ps_value_in_sss_format", '.', 1)`;
177 let $assert_text= Value returned by SSS and PS table for Last_Error_Timestamp should be same.;
178 let $assert_cond= "$sss_value" = "$ps_value_without_microseconds";
179 source include/assert.inc;
180
181 --echo
182 --echo # Verify that the error fields are preserved after STOP SLAVE.
183 --echo
184
185 --echo
186 --echo # 1. Verify that thread_id changes to NULL and service_state to "off" on
187 --echo # STOP SLAVE.
188 --echo
189
190 let $ps_value= query_get_value(select thread_id from performance_schema.replication_applier_status_by_worker, thread_id, 1);
191 let $assert_text= After STOP SLAVE, thread_id should be NULL;
192 let $assert_cond= "$ps_value" = "NULL";
193 source include/assert.inc;
194
195 let $ps_value= query_get_value(select Service_State from performance_schema.replication_applier_status_by_coordinator, Service_State, 1);
196 let $assert_text= So, Service_State after STOP SLAVE should be "OFF".;
197 let $assert_cond= "$ps_value"= "OFF";
198 source include/assert.inc;
199
200 --echo
201 --echo # 2. Extract the worker_id and the error related fields from SSS and PS
202 --echo # table and compare them. These fields should preserve their values.
203 --echo
204
205 let $ps_value= query_get_value(select Worker_Id from performance_schema.replication_applier_status_by_worker, Worker_Id, 1);
206 let $assert_text= Worker_Id should be 1;
207 let $assert_cond= "$ps_value"= 1;
208 source include/assert.inc;
209
210 let $sss_value= query_get_value(SHOW SLAVE STATUS, Last_SQL_Errno, 1);
211 let $ps_value= query_get_value(select Last_Error_Number from performance_schema.replication_applier_status_by_worker, Last_Error_Number, 1);
212 let $assert_text= Value returned by SSS and PS table for Last_Error_Number should be same.;
213 let $assert_cond= "$sss_value" = "$ps_value";
214 source include/assert.inc;
215
216 --disable_query_log
217 --replace_regex /master-bin.[0-9]+/FILENAME/ /end_log_pos [0-9]+/end_log_pos POSITION/
218 select Last_Error_Message from performance_schema.replication_applier_status_by_worker;
219 --enable_query_log
220
221 # The timestamp format is slightly different in SSS and PS.
222 # SSS => YYMMDD HH:MM:SS
223 # PS => YYYY-MM-DD HH:MM:SS.ssssss
224 # To match the two, we get rid of hyphons from PS output and first two digits
225 # the year field so that it can be matched directly and the microseconds (after
226 # WL#7319).
227
228 let $sss_value= query_get_value(SHOW SLAVE STATUS, Last_SQL_Error_Timestamp, 1);
229 let $ps_value= query_get_value(select Last_Error_Timestamp from performance_schema.replication_applier_status_by_worker, Last_Error_Timestamp, 1);
230 let $ps_value_without_hyphons= `SELECT REPLACE("$ps_value", '-', '')`;
231 let $ps_value_in_sss_format= `select substring("$ps_value_without_hyphons", 3)`;
232 let $ps_value_without_microseconds= `select substring_index("$ps_value_in_sss_format", '.', 1)`;
233 let $assert_text= Value returned by SSS and PS table for Last_Error_Timestamp should be same.;
234 let $assert_cond= "$sss_value" = "$ps_value_without_microseconds";
235 source include/assert.inc;
236
237 stop slave;
238 reset slave;
239 --connection master
240 drop table t;
241 reset master;
242
243 --echo
244 --echo # Set up replication in gtid-mode=on and test 'Last_Seen_Transaction'
245 --echo # field.
246 --echo
247
248 --let $rpl_skip_sync= 1
249 --let $rpl_set_enforce_gtid_consistency= 1
250 --source include/rpl_set_gtid_mode.inc
251 --let $rpl_skip_sync= 0
252
253 --connection slave
254 stop slave;
255 replace_result $MASTER_MYPORT MASTER_MYPORT;
256 replace_column 2 ####;
257 eval CHANGE REPLICATION SOURCE to SOURCE_HOST='127.0.0.1',SOURCE_PORT=$MASTER_MYPORT, SOURCE_USER='root';
258 SET @@global.replica_parallel_workers=1;
259 # to avoid warnings
260 set @save_replica_transaction_retries= @@global.replica_transaction_retries;
261 start slave;
262
263 --connection master
264 create database d1;
265 create table d1.t1(a int);
266 insert into d1.t1 values(1);
267 let $MASTER_UUID= query_get_value(`SHOW VARIABLES LIKE '%server_uuid%'`, Value, 1);
268
269 sync_slave_with_master;
270
271 --echo #
272 --echo # BUG#20513547 REPLICATION_APPLIER_STATUS_BY_WORKER SHOWS
273 --echo # WRONG WORKER_ID WHEN SLAVE STOPS
274 --echo #
275 --source include/stop_slave.inc
276 SET @@GLOBAL.replica_parallel_workers = 3;
277 --source include/start_slave.inc
278 SELECT worker_id from performance_schema.replication_applier_status_by_worker;
279 --source include/stop_slave.inc
280 SELECT worker_id from performance_schema.replication_applier_status_by_worker;
281
282 --echo
283 --echo # Cleanup.
284 --echo
285
286 --connection master
287 DROP DATABASE d1;
288
289 --connection slave
290 --disable_warnings
291 set @@global.replica_parallel_workers= @save_replica_parallel_workers;
292 --enable_warnings
293 set @@global.replica_transaction_retries= @save_replica_transaction_retries;
294 source include/start_slave.inc;
295
296 --let $rpl_gtid_mode= OFF
297 --let $rpl_set_enforce_gtid_consistency= 0
298 --source include/rpl_set_gtid_mode.inc
299
300 source include/rpl_end.inc;