Debian Codegraph @

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

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