Debian Codegraph @

mysql-test/suite/rpl/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 # MDEV-20220: Merge 5.7 P_S replication table 'replication_applier_status_by_worker
34 #
35 --source include/have_perfschema.inc
36 --source include/have_binlog_format_mixed.inc
37 --source include/have_innodb.inc
38 --source include/master-slave.inc
39
40
41 let $assert_text= On master, the table should return an empty set.;
42 let $assert_cond= count(*) = 0 from performance_schema.replication_applier_status_by_worker;
43 source include/assert.inc;
44
45 --echo
46 --echo # Setup MTS and perform testing on a fresh slave.
47 --echo
48 --connection slave
49 call mtr.add_suppression("Error 'Table 'test.t' doesn't exist' on query.");
50 source include/stop_slave.inc;
51 set @save_slave_parallel_workers= @@global.slave_parallel_workers;
52 # to avoid warnings
53 set @save_slave_transaction_retries= @@global.slave_transaction_retries;
54 RESET SLAVE ALL;
55 evalp CHANGE MASTER 'slave1' TO MASTER_USER='root',MASTER_PORT=$MASTER_MYPORT, MASTER_HOST='127.0.0.1', MASTER_USE_GTID=slave_pos, MASTER_SSL_VERIFY_SERVER_CERT=0;
56 SET default_master_connection='slave1';
57 SET @@global.slave_parallel_workers=1;
58 ALTER TABLE mysql.gtid_slave_pos ENGINE=InnoDB;
59 START SLAVE 'slave1';
60 --source include/wait_for_slave_to_start.inc
61
62 let $ps_value= query_get_value(select channel_name from performance_schema.replication_applier_status_by_worker, channel_name, 1);
63 let $assert_text= Channel_name will be empty for a worker when it has not processed any transaction;
64 let $assert_cond= "$ps_value"= "";
65 source include/assert.inc;
66
67 # To verify the correctness of thread_id field, we check for the name of
68 # the thread.
69 let $thread_name= `select name from performance_schema.threads where thread_id= (select Thread_Id from performance_schema.replication_applier_status_by_worker)`;
70 let $assert_text= thread_name should should indicate worker thread.;
71 let $assert_cond= "$thread_name" = "thread/sql/rpl_parallel";
72 source include/assert.inc;
73
74 let $ps_value= query_get_value(select Service_State from performance_schema.replication_applier_status_by_worker, Service_State, 1);
75 let $assert_text= Service_State should be "ON" on a fresh slave server.;
76 let $assert_cond= "$ps_value"= "ON";
77 source include/assert.inc;
78
79 let $ps_value= query_get_value(select Last_Seen_Transaction from performance_schema.replication_applier_status_by_worker, Last_Seen_Transaction, 1);
80 let $assert_text= Last_Seen_Transaction should show "" if no transaction applierd;
81 let $assert_cond= "$ps_value" = "";
82 source include/assert.inc;
83
84 --connection master
85 CREATE TABLE t1 (a INT) ENGINE=InnoDB;
86 --source include/save_master_gtid.inc
87
88 connection slave;
89 --source include/sync_with_master_gtid.inc
90
91 let $ps_value= query_get_value(select channel_name from performance_schema.replication_applier_status_by_worker, channel_name, 1);
92 let $assert_text= Channel_name must be slave1;
93 let $assert_cond= "$ps_value"= "slave1";
94 source include/assert.inc;
95
96 let $ps_value= query_get_value(select Last_Seen_Transaction from performance_schema.replication_applier_status_by_worker, Last_Seen_Transaction, 1);
97 let $sss_value= query_get_value(SHOW SLAVE STATUS, Gtid_IO_Pos, 1);
98 let $assert_text= Last_Seen_Transaction should show $sss_value;
99 let $assert_cond= "$ps_value" = "$sss_value";
100 source include/assert.inc;
101
102 let $sss_value= query_get_value(SHOW SLAVE STATUS, Last_SQL_Errno, 1);
103 let $ps_value= query_get_value(select Last_Error_Number from performance_schema.replication_applier_status_by_worker, Last_Error_Number, 1);
104 let $assert_text= Value returned by SSS and PS table for Last_Error_Number should be same.;
105 let $assert_cond= "$sss_value" = "$ps_value";
106 source include/assert.inc;
107
108 let $sss_value= query_get_value(SHOW SLAVE STATUS, Last_SQL_Error, 1);
109 let $ps_value= query_get_value(select Last_Error_Message from performance_schema.replication_applier_status_by_worker, Last_Error_Message, 1);
110 let $assert_text= Value returned by SSS and PS table for Last_Error_Message should both be empty.;
111 let $assert_cond= "$sss_value" = "$ps_value";
112 source include/assert.inc;
113
114 let $ps_value= query_get_value(select Last_Error_Timestamp from performance_schema.replication_applier_status_by_worker, Last_Error_Timestamp, 1);
115 let $assert_text= Value returned by PS table for Last_Error_Timestamp should be 0000-00-00 00:00:00.;
116 let $assert_cond= "$ps_value" = "0000-00-00 00:00:00";
117 source include/assert.inc;
118
119 --connection master
120 sleep 1;
121 --connection slave
122 let $ps_value= query_get_value(select worker_idle_time from performance_schema.replication_applier_status_by_worker, worker_idle_time, 1);
123 let $assert_text= Value returned by PS table for worker_idle_time should be >= 1;
124 let $assert_cond= "$ps_value" >= "1";
125 source include/assert.inc;
126
127 --connection master
128 DROP TABLE t1;
129 --save_master_pos
130
131 --connection slave
132 --sync_with_master 0,'slave1'
133
134 STOP SLAVE 'slave1';
135 --source include/wait_for_slave_to_stop.inc
136 RESET SLAVE ALL;
137 SET default_master_connection='';
138 evalp CHANGE MASTER TO MASTER_USER='root', MASTER_HOST='127.0.0.1',MASTER_PORT=$MASTER_MYPORT;
139 --source include/start_slave.inc
140
141 --echo
142 --echo # Introduce an error in the worker thread and check for the correctness
143 --echo # of error number, message and timestamp fields.
144 --echo
145
146 # Cause an error in Worker thread.
147 # 1) Create a table 't' at master, replicate at slave.
148 # 2) Drop table 't' at slave only.
149 # 3) Insert a value in table 't' on master and replicate on slave.
150 # Since slave doesnt have table 't' anymore, worker thread will report an error.
151
152 --connection master
153 use test;
154 create table t(a int primary key);
155 sync_slave_with_master;
156 drop table t;
157 --connection master
158 insert into t values(1);
159 --connection slave
160 let $slave_sql_errno=1146;
161 source include/wait_for_slave_sql_error.inc;
162
163 --echo
164 --echo # Extract the error related fields from SSS and PS table and compare
165 --echo # them for correctness.
166 --echo
167
168 let $sss_value= query_get_value(SHOW SLAVE STATUS, Last_SQL_Errno, 1);
169 let $ps_value= query_get_value(select Last_Error_Number from performance_schema.replication_applier_status_by_worker, Last_Error_Number, 1);
170 let $assert_text= Value returned by SSS and PS table for Last_Error_Number should be same.;
171 let $assert_cond= "$sss_value" = "$ps_value";
172 source include/assert.inc;
173
174 --disable_query_log
175 --replace_regex /master-bin.[0-9]+/FILENAME/ /end_log_pos [0-9]+/end_log_pos POSITION/
176 select Last_Error_Message from performance_schema.replication_applier_status_by_worker;
177 --enable_query_log
178
179 --echo
180 --echo # Verify that the error fields are preserved after STOP SLAVE.
181 --echo
182
183 --echo
184 --echo # 1. Verify that thread_id changes to NULL and service_state to "off" on
185 --echo # STOP SLAVE.
186 --echo
187
188 let $ps_value= query_get_value(select thread_id from performance_schema.replication_applier_status_by_worker, thread_id, 1);
189 let $assert_text= After STOP SLAVE, thread_id should be NULL;
190 let $assert_cond= "$ps_value" = "NULL";
191 source include/assert.inc;
192
193 let $ps_value= query_get_value(select service_state from performance_schema.replication_applier_status_by_worker, service_state, 1);
194 let $assert_text= So, Service_State after STOP SLAVE should be "OFF".;
195 let $assert_cond= "$ps_value"= "OFF";
196 source include/assert.inc;
197
198 --echo
199 --echo # 2. Extract the worker_id and the error related fields from SSS and PS
200 --echo # table and compare them. These fields should preserve their values.
201 --echo
202
203 let $sss_value= query_get_value(SHOW SLAVE STATUS, Last_SQL_Errno, 1);
204 let $ps_value= query_get_value(select Last_Error_Number from performance_schema.replication_applier_status_by_worker, Last_Error_Number, 1);
205 let $assert_text= Value returned by SSS and PS table for Last_Error_Number should be same.;
206 let $assert_cond= "$sss_value" = "$ps_value";
207 source include/assert.inc;
208
209 --disable_query_log
210 --replace_regex /master-bin.[0-9]+/FILENAME/ /end_log_pos [0-9]+/end_log_pos POSITION/
211 select Last_Error_Message from performance_schema.replication_applier_status_by_worker;
212 --enable_query_log
213
214 # The timestamp format is slightly different in SSS and PS.
215 # SSS => YYMMDD HH:MM:SS
216 # PS => YYYY-MM-DD HH:MM:SS
217 # To match the two, we get rid of hyphons from PS output and first two digits
218 # the year field so that it can be matched directly.
219
220 #--- TODO: Can we include Last_SQL_Error_Timestamp as part of SSS
221
222 #let $sss_value= query_get_value(SHOW SLAVE STATUS, Last_SQL_Error_Timestamp, 1);
223 #let $ps_value= query_get_value(select Last_Error_Timestamp from performance_schema.replication_applier_status_by_worker, Last_Error_Timestamp, 1);
224 #let $ps_value_without_hyphons= `SELECT REPLACE("$ps_value", '-', '')`;
225 #let $ps_value_in_sss_format= `select substring("$ps_value_without_hyphons", 3)`;
226 #let $assert_text= Value returned by SSS and PS table for Last_Error_Timestamp should be same.;
227 #let $assert_cond= "$sss_value" = "$ps_value_in_sss_format";
228 #source include/assert.inc;
229
230 --let $rpl_only_running_threads= 1
231 --source include/stop_slave.inc
232 RESET SLAVE;
233 --connection master
234 DROP TABLE t;
235 RESET MASTER;
236
237 --echo
238 --echo # Verify that number of rows in 'replication_applier_status_by_worker' table match with
239 --echo # number of slave_parallel_workers.
240 --echo
241
242 --connection slave
243 SET @@global.slave_parallel_workers=4;
244 --source include/start_slave.inc
245 --let $assert_text= On slave, the table should return 4 rows.
246 --let $assert_cond= count(*) = 4 from performance_schema.replication_applier_status_by_worker
247 --source include/assert.inc
248 --source include/stop_slave.inc
249
250 --echo
251 --echo # Cleanup.
252 --echo
253
254 set @@global.slave_parallel_workers= @save_slave_parallel_workers;
255 set @@global.slave_transaction_retries= @save_slave_transaction_retries;
256 source include/start_slave.inc;
257
258 source include/rpl_end.inc;