Debian Codegraph @

mysql-test/suite/perfschema/t/rpl_statements.test

raw
1 #
2 # Check statement instrumentation of replicated statements
3 #
4 # Test requires: sp-protocol/ps-protocol/view-protocol/cursor-protocol disabled
5 --source include/no_protocol.inc
6 --source include/have_binlog_format_mixed.inc
7 --source include/master-slave.inc
8
9 #==============================================================
10 # Execute a variety of dml and ddl statements on the master.
11 # Verify that the corresponding statement events are generated
12 # on the slave.
13 #
14 # 1. Setup test files on master
15 # 2. Replicate test files to slave
16 # 3. Perform dml and ddl statements on master
17 # 4. Copy statement events on master into a temporary table
18 # 4. Replicate to slave
19 # 5. Compare statement events on slave to those from the master
20 # 6. Disable statement/abstract/relay_log on slave
21 # 7. Update some tables on the master then replicate
22 # 8. Verify that the updates were replicated but no statement
23 # events were recorded
24 #==============================================================
25
26 #
27 # UTILITY QUERIES
28 #
29 let $get_thread_id=
30 select thread_id into @my_thread_id
31 from performance_schema.threads
32 where processlist_id = connection_id();
33
34 let $disable_instruments=
35 update performance_schema.setup_instruments
36 set enabled='no', timed='no'
37 where name like '%statement/%';
38
39 let $enable_instruments= ../include
40 update performance_schema.setup_instruments
41 set enabled='yes', timed='yes'
42 where name like '%statement/%';
43
44 let $column_list=
45 thread_id, event_id, rpad(event_name, 28, ' ') event_name, rpad(current_schema, 10, ' ') current_schema, rpad(digest_text, 72, ' ') digest_text, sql_text;
46
47 # Define instrument name for enable/disable instruments
48 let $pfs_instrument='%statement/%';
49
50 --echo
51 --echo #
52 --echo # STEP 1 - CREATE AND REPLICATE TEST TABLES
53 --echo #
54
55 connection master;
56
57 --echo
58 --echo **************
59 --echo *** MASTER ***
60 --echo **************
61 --echo
62 --echo *** Create test tables
63 --echo
64
65 show global variables like '%binlog_format%';
66
67 --disable_warnings
68 drop table if exists test.marker;
69 --enable_warnings
70
71 eval $get_thread_id;
72
73 create table test.marker(s1 int) engine=innodb;
74
75 sync_slave_with_master;
76
77 --echo **************
78 --echo *** SLAVE ***
79 --echo **************
80 --echo
81 --echo *** Clear statement events
82 --source ../include/rpl_statements_truncate.inc
83
84 --echo
85 --echo #
86 --echo # STEP 2 - REPLICATE ONE ROW ON MASTER TO GET REPLICATION THREAD ID ON SLAVE
87 --echo #
88
89 connection master;
90
91 --echo **************
92 --echo *** MASTER ***
93 --echo **************
94 --echo
95 insert into test.marker values (0);
96 --echo
97
98 sync_slave_with_master;
99
100 --echo **************
101 --echo *** SLAVE ***
102 --echo **************
103 --echo
104 --echo *** Verify row, get replication thread id, clear statement events
105 --echo
106
107 # TODO: Get slave thread id from threads using thread/sql/replica_sql event name
108
109 select thread_id into @slave_thread_id from performance_schema.events_statements_history
110 where sql_text like '%marker%';
111 let $slave_thread_id= `select @slave_thread_id`;
112
113 --echo *** Verify row inserted on master was replicated
114 select count(*) = 1 as 'Expect 1' from test.marker;
115
116 --echo *** Clear statement events
117 --source ../include/rpl_statements_truncate.inc
118
119 --echo
120 --echo #
121 --echo # STEP 3 - PERFORM DML STATEMENTS ON MASTER
122 --echo #
123
124 connection master;
125
126 --echo **************
127 --echo *** MASTER ***
128 --echo **************
129 --echo
130 show global variables like '%binlog_format%';
131
132 --echo *** Clear statement events
133 --source ../include/rpl_statements_truncate.inc
134
135 --echo
136 --echo *** Create/drop table, create/drop database
137 --echo
138 create database marker1_db;
139 create database marker2_db;
140 create table marker1_db.table1 (s1 int) engine=innodb;
141 create table marker2_db.table1 (s1 int) engine=innodb;
142 create table marker2_db.table2 (s1 int) engine=innodb;
143
144 --echo
145 --echo *** Transaction
146 start transaction;
147 insert into marker1_db.table1 values (1), (2), (3);
148 insert into marker2_db.table1 values (1), (2), (3);
149 commit;
150
151 --echo
152 --echo *** Alter
153 alter table marker1_db.table1 add column (s2 varchar(32));
154
155 --echo
156 --echo *** Insert, Update
157 start transaction;
158 insert into marker1_db.table1 values (4, 'four'), (5, 'five'), (6, 'six');
159 update marker1_db.table1 set s1 = s1 + 1;
160 commit;
161
162 --echo
163 --echo *** Rollback
164 start transaction;
165 insert into marker1_db.table1 values (7, 'seven'), (8, 'eight'), (9, 'nine');
166 rollback;
167
168 --echo
169 --echo *** Autocommit, Delete, Drop
170 delete from marker1_db.table1 where s1 > 4;
171 drop table marker2_db.table1;
172 drop database marker2_db;
173
174 --source ../include/disable_instruments.inc
175
176 --echo
177 --echo *** Examine statements events that will be compared on the slave
178 --echo
179
180 --replace_column 1 [THREAD_ID] 2 [EVENT_ID]
181
182 eval select $column_list from performance_schema.events_statements_history_long
183 where sql_text like '%marker%' order by event_id;
184
185 --echo
186 --echo #
187 --echo # STEP 4 - REPLICATE STATEMENT EVENTS ON MASTER TO SLAVE
188 --echo #
189 --echo
190 --echo *** Store statement events in holding table, then replicate
191 --echo
192
193 --source ../include/disable_instruments.inc
194
195 --echo #
196 --echo # Create table to hold statement events for later comparison on the slave
197 --echo #
198
199 create table test.master_events_statements_history_long as
200 (select thread_id, event_id, event_name, sql_text, digest, digest_text, current_schema, rows_affected
201 from performance_schema.events_statements_history_long
202 where (thread_id=@my_thread_id and digest_text like '%marker%'));
203
204 --source ../include/enable_instruments.inc
205
206 --echo
207 --echo #
208 --echo # STEP 5 - VERIFY DML AND DDL STATEMENT EVENTS ON SLAVE
209 --echo #
210
211 sync_slave_with_master;
212
213 --echo **************
214 --echo *** SLAVE ***
215 --echo **************
216
217 --source ../include/disable_instruments.inc
218
219 --echo
220 --echo *** List statement events from master
221 --echo
222 --replace_column 1 [THREAD_ID] 2 [EVENT_ID]
223 eval select $column_list from master_events_statements_history_long order by event_id;
224
225 --echo
226 --echo *** List statement events on slave
227 --echo
228
229 --replace_column 1 [THREAD_ID] 2 [EVENT_ID]
230
231 eval select $column_list from performance_schema.events_statements_history_long
232 where thread_id = @slave_thread_id and sql_text like '%marker%' order by event_id;
233
234 --echo
235 --echo *** Compare master and slave events
236 --echo
237
238 # Note: The statement digest provides a more robust comparison than the
239 # event name. However, in some cases, e.g. DROP TABLE, the server generates
240 # its own version of the statement which includes additional quotes and a
241 # comment. A digest comparison is therefore impractical for server-generated
242 # statements, so we use both methods to ensure coverage.
243
244 --echo
245 --echo *** Event name comparison - expect 0 mismatches
246 --echo
247
248 select thread_id, event_id, event_name, digest_text, sql_text from performance_schema.events_statements_history_long t1
249 where t1.thread_id = @slave_thread_id and
250 sql_text like '%marker%' and
251 not exists (select * from master_events_statements_history_long t2 where t2.event_name = t1.event_name);
252
253 --echo
254 --echo *** Statement digest comparison - expect 1 mismatch for DROP TABLE
255 --echo
256
257 --replace_column 1 [THREAD_ID] 2 [EVENT_ID] 4 [DIGEST]
258
259 select thread_id, event_id, event_name, digest, digest_text, sql_text from performance_schema.events_statements_history_long t1
260 where t1.thread_id = @slave_thread_id and
261 sql_text like '%marker%' and
262 not exists (select * from master_events_statements_history_long t2 where t2.digest = t1.digest);
263
264 --echo
265 --echo #
266 --echo # STEP 6 - DISABLE REPLICATED STATEMENT EVENTS ON SLAVE
267 --echo #
268 --source ../include/rpl_statements_truncate.inc
269 --source ../include/enable_instruments.inc
270
271 update performance_schema.setup_instruments set enabled='no', timed='no'
272 where name like '%statement/abstract/relay_log%';
273
274 select * from performance_schema.setup_instruments where name like '%statement/abstract/relay_log%';
275
276 --echo
277 --echo #
278 --echo # STEP 7 - UPDATE TABLES ON MASTER, REPLICATE
279 --echo #
280
281 connection master;
282
283 --echo **************
284 --echo *** MASTER ***
285 --echo **************
286 --echo
287 --echo *** Clear statement events
288 --source ../include/rpl_statements_truncate.inc
289
290 --echo *** Update some tables, then replicate
291 --echo
292
293 insert into marker1_db.table1 values (999, '999'), (998, '998'), (997, '997');
294
295 --echo
296 --echo #
297 --echo # STEP 8 - VERIFY TABLE UPDATES FROM MASTER, EXPECT NO STATEMENT EVENTS ON SLAVE
298 --echo #
299
300 sync_slave_with_master;
301
302 --echo **************
303 --echo *** SLAVE ***
304 --echo **************
305 --echo
306 --echo *** Confirm rows were replicated
307 --echo
308
309 select * from marker1_db.table1 where s1 > 900 order by s1;
310
311 --echo
312 --echo *** Confirm that are no statements events from the replication thread
313 --echo
314
315 select * from performance_schema.events_statements_history_long
316 where thread_id = @slave_thread_id;
317
318 --source ../include/enable_instruments.inc
319
320 --echo
321 --echo #
322 --echo # STEP 9 - CLEAN UP
323 --echo #
324 --echo
325 --disable_query_log
326 --disable_warnings
327
328 connection master;
329 drop table test.marker;
330 drop table test.master_events_statements_history_long;
331 drop database marker1_db;
332
333 sync_slave_with_master;
334
335 --enable_warnings
336 --enable_query_log
337 --source include/rpl_end.inc