Debian Codegraph @

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

raw
1 --echo ## Test the Performance Schema System service
2 --echo ##
3 --echo ## Verify that the SOURCE column in all wait, stage, statement,
4 --echo ## transaction and metadata lock tables is set to NULL after a plugin
5 --echo ## or component is unloaded.
6 --echo ##
7 --echo ## 0. Setup
8 --echo ## 1. Install an example plugin to generate wait events
9 --echo ## 2. Generate stage, statement and transaction events
10 --echo ## 3. Unload the component / plugin
11 --echo ## 4. Verify that the SOURCE column is null in relevant tables
12 --echo ## 5. Re-install the test plugin
13 --echo ## 6. Verify that the SOURCE column is re-enabled
14 --echo ## 7. Cleanup
15
16 # Note: MTR allows only one plugin or component loaded per test, so there
17 # are two versions of this test--one for plugins and one for components.
18
19 --source include/no_protocol.inc
20
21 --echo
22 --echo #
23 --echo # 0) Setup
24 --echo #
25
26 # Create con0 for the test session, use default as the control.
27 connect(con0, localhost, root,,);
28
29 --echo Connection default
30 --connection default
31 USE performance_schema;
32
33 --echo
34 --echo # Disable all events from the control thread.
35 --echo UPDATE performance_schema.threads SET instrumented = 'NO' WHERE processlist_id = CONNECTION_ID();
36 --disable_query_log
37 UPDATE performance_schema.threads SET instrumented = 'NO' WHERE processlist_id = CONNECTION_ID();
38 --enable_query_log
39
40 --echo
41 --echo # Disable all events.
42 UPDATE performance_schema.setup_instruments SET enabled = 'NO', timed = 'NO';
43
44 --echo
45 --echo # Clear the tables that have a SOURCE column.
46 TRUNCATE events_waits_current;
47 TRUNCATE events_waits_history;
48 TRUNCATE events_waits_history_long;
49 TRUNCATE events_stages_current;
50 TRUNCATE events_stages_history;
51 TRUNCATE events_stages_history_long;
52 TRUNCATE events_statements_current;
53 TRUNCATE events_statements_history;
54 TRUNCATE events_statements_history_long;
55 TRUNCATE events_transactions_current;
56 TRUNCATE events_transactions_history;
57 TRUNCATE events_transactions_history_long;
58 --echo # metadata_locks is not truncatable
59
60 --echo
61 --echo #
62 --echo # 1) Install a plugin that generates its own wait events.
63 --echo #
64
65 --echo Connection con0
66 --connection con0
67 USE test;
68
69 --replace_regex /\.dll/.so/
70 eval INSTALL PLUGIN pfs_example_plugin_employee SONAME '$PFS_EXAMPLE_PLUGIN_EMPLOYEE';
71
72 --echo
73 --echo Connection default
74 --connection default
75
76 --echo
77 --echo # Enable events that record the source file and line.
78 UPDATE performance_schema.setup_instruments SET enabled = 'yes', timed = 'yes'
79 WHERE name LIKE 'stage/%' OR name LIKE 'statement/%' OR name = 'transaction' OR
80 name LIKE '%metadata/sql%' OR name LIKE 'wait/synch/mutex/sql/THD::%';
81
82 --echo
83 --echo Connection con0
84 --connection con0
85
86 --echo
87 --echo #
88 --echo # 2) Generate wait, stage, statement, transaction and metadata lock events. Keep the transaction open for now.
89 --echo #
90 INSERT INTO performance_schema.pfs_example_employee_name VALUES (99, "First", "Last");
91 UPDATE performance_schema.pfs_example_employee_name SET employee_number = 100 where employee_number = 99;
92 DELETE FROM performance_schema.pfs_example_employee_name;
93 --echo
94 CREATE TABLE t1 (s1 INT);
95 START TRANSACTION;
96 INSERT INTO t1 VALUES (1), (2), (3);
97
98 --echo
99 --echo Connection default
100 --connection default
101
102 #--echo DEBUG
103 # SELECT * FROM performance_schema.events_waits_current;
104 # SELECT * FROM performance_schema.events_waits_history;
105 # SELECT * FROM performance_schema.events_waits_history_long;
106 #--echo /DEBUG
107
108 --echo
109 --echo # Verify that the plugin registered and generated the wait events.
110 --echo ## TODO Resolve timing issues with events_waits_history. ##
111 --echo # SELECT (COUNT(*) > 0) AS "Expect 1" FROM performance_schema.events_waits_history WHERE event_name LIKE "%pfs_example%";
112 --echo # SELECT (COUNT(*) > 0) AS "Expect 1" FROM performance_schema.events_waits_history_long WHERE event_name LIKE "%pfs_example%";
113
114 --echo
115 --echo # Verify that each table is non-empty and that the SOURCE columns are populated.
116 --echo
117 SELECT (COUNT(*) > 0) AS "Expect 1" FROM events_waits_current AS t1 WHERE source <> "";
118 SELECT (COUNT(*) > 0) AS "Expect 1" FROM events_waits_history WHERE source <> "";
119 SELECT (COUNT(*) > 0) AS "Expect 1" FROM events_waits_history_long WHERE source <> "";
120 --echo # May or may not contain rows, but shares same code path as _history and _history_long
121 --echo # SELECT (COUNT(*) > 0) AS "Expect 1" FROM events_stages_current WHERE source <> "";
122 --echo
123 SELECT (COUNT(*) > 0) AS "Expect 1" FROM events_stages_history WHERE source <> "";
124 SELECT (COUNT(*) > 0) AS "Expect 1" FROM events_stages_history_long WHERE source <> "";
125 SELECT (COUNT(*) > 0) AS "Expect 1" FROM events_statements_current WHERE source <> "";
126 SELECT (COUNT(*) > 0) AS "Expect 1" FROM events_statements_history WHERE source <> "";
127 SELECT (COUNT(*) > 0) AS "Expect 1" FROM events_statements_history_long WHERE source <> "";
128 SELECT (COUNT(*) > 0) AS "Expect 1" FROM events_transactions_current WHERE source <> "";
129 SELECT (COUNT(*) > 0) AS "Expect 1" FROM events_transactions_history WHERE source <> "";
130 SELECT (COUNT(*) > 0) AS "Expect 1" FROM events_transactions_history_long WHERE source <> "";
131 SELECT (COUNT(*) > 0) AS "Expect 1" FROM metadata_locks WHERE object_schema = "test" AND source <> "";
132
133 --echo
134 --echo #
135 --echo # 3) Uninstall the test plugin to force a reset of the SOURCE columns.
136 --echo #
137 UNINSTALL PLUGIN pfs_example_plugin_employee;
138
139 --echo
140 --echo #
141 --echo # 4) Verify that the SOURCE column was reset for all events.
142 --echo #
143 SELECT (COUNT(*) = 0) AS "Expect 1" FROM events_waits_current WHERE source <> "";
144 SELECT (COUNT(*) = 0) AS "Expect 1" FROM events_waits_history WHERE source <> "";
145 SELECT (COUNT(*) = 0) AS "Expect 1" FROM events_waits_history_long WHERE source <> "";
146 --echo # May or may not contain rows, but shares same code path as _history and _history_long
147 --echo # SELECT (COUNT(*) > 0) AS "Expect 1" FROM events_stages_current WHERE source = "";
148 --echo
149 --echo # Some stage events occur after the plugin is unloaded, so confirm that at least some of the SOURCE columns were reset.
150 SELECT (COUNT(*) > 0) AS "Expect 1" FROM events_stages_history WHERE source = "";
151 SELECT (COUNT(*) > 0) AS "Expect 1" FROM events_stages_history_long WHERE source = "";
152 --echo
153 SELECT (COUNT(*) = 0) AS "Expect 1" FROM events_statements_current WHERE source <> "";
154 SELECT (COUNT(*) = 0) AS "Expect 1" FROM events_statements_history WHERE source <> "";
155 SELECT (COUNT(*) = 0) AS "Expect 1" FROM events_statements_history_long WHERE source <> "";
156 SELECT (COUNT(*) = 0) AS "Expect 1" FROM events_transactions_current WHERE source <> "";
157 SELECT (COUNT(*) = 0) AS "Expect 1" FROM events_transactions_history WHERE source <> "";
158 SELECT (COUNT(*) = 0) AS "Expect 1" FROM events_transactions_history_long WHERE source <> "";
159 SELECT (COUNT(*) = 0) AS "Expect 1" FROM metadata_locks WHERE object_schema = "test" AND source <> "";
160
161 --echo
162 --echo Connection con0
163 --connection con0
164
165 --echo
166 --echo # Commit the open transaction from step 2.
167 COMMIT;
168
169 --echo
170 --echo Connection default
171 --connection default
172
173 --echo
174 --echo # Clear tables with SOURCE columns.
175 TRUNCATE events_waits_current;
176 TRUNCATE events_waits_history;
177 TRUNCATE events_waits_history_long;
178 TRUNCATE events_stages_current;
179 TRUNCATE events_stages_history;
180 TRUNCATE events_stages_history_long;
181 TRUNCATE events_statements_current;
182 TRUNCATE events_statements_history;
183 TRUNCATE events_statements_history_long;
184 TRUNCATE events_transactions_current;
185 TRUNCATE events_transactions_history;
186 TRUNCATE events_transactions_history_long;
187 --echo # metadata_locks is not truncatable
188
189 --echo
190 --echo Connection con0
191 --connection con0
192
193 --echo
194 --echo #
195 --echo # 5) Verify that the SOURCE columns are re-enabled after the reset.
196 --echo #
197 --echo
198
199 --echo # Re-install the plugin.
200 --replace_regex /\.dll/.so/
201 eval INSTALL PLUGIN pfs_example_plugin_employee SONAME '$PFS_EXAMPLE_PLUGIN_EMPLOYEE';
202
203 --echo
204 --echo # Generate new wait, stage, statement and transaction events. Leave the transaction open.
205 INSERT INTO performance_schema.pfs_example_employee_name VALUES (99, "First", "Last");
206 UPDATE performance_schema.pfs_example_employee_name SET employee_number = 100 where employee_number = 99;
207 DELETE FROM performance_schema.pfs_example_employee_name;
208 --echo
209 START TRANSACTION;
210 INSERT INTO t1 VALUES (4), (5), (6);
211
212
213 --echo
214 --echo Connection default
215 --connection default
216
217 --echo
218 --echo #
219 --echo # 6) Verify that each table is non-empty and that the SOURCE columns are populated.
220 --echo #
221 SELECT (COUNT(*) > 0) AS "Expect 1" FROM events_waits_current AS t1 WHERE source <> "";
222 SELECT (COUNT(*) > 0) AS "Expect 1" FROM events_waits_history WHERE source <> "";
223 SELECT (COUNT(*) > 0) AS "Expect 1" FROM events_waits_history_long WHERE source <> "";
224 --echo # May or may not contain rows, but shares same code path as _history and _history_long
225 --echo # SELECT (COUNT(*) > 0) AS "Expect 1" FROM events_stages_current WHERE source <> "";
226 --echo
227 SELECT (COUNT(*) > 0) AS "Expect 1" FROM events_stages_history WHERE source <> "";
228 SELECT (COUNT(*) > 0) AS "Expect 1" FROM events_stages_history_long WHERE source <> "";
229 SELECT (COUNT(*) > 0) AS "Expect 1" FROM events_statements_current WHERE source <> "";
230 SELECT (COUNT(*) > 0) AS "Expect 1" FROM events_statements_history WHERE source <> "";
231 SELECT (COUNT(*) > 0) AS "Expect 1" FROM events_statements_history_long WHERE source <> "";
232 SELECT (COUNT(*) > 0) AS "Expect 1" FROM events_transactions_current WHERE source <> "";
233 SELECT (COUNT(*) > 0) AS "Expect 1" FROM events_transactions_history WHERE source <> "";
234 SELECT (COUNT(*) > 0) AS "Expect 1" FROM events_transactions_history_long WHERE source <> "";
235 SELECT (COUNT(*) > 0) AS "Expect 1" FROM metadata_locks WHERE object_schema = "test" AND source <> "";
236
237 --echo
238 --echo Connection con0
239 --connection con0
240
241 --echo
242 --echo #
243 --echo # 7) Clean up
244 --echo #
245
246 --echo
247 --echo # Commit the open transaction from step 5.
248 COMMIT;
249
250 --connection con0
251 --disconnect con0
252 --source include/wait_until_disconnected.inc
253
254 --echo
255 --echo Connection default
256 --connection default
257 DROP TABLE test.t1;
258 UNINSTALL PLUGIN pfs_example_plugin_employee;
259 UPDATE performance_schema.setup_instruments SET enabled = 'YES', timed = 'YES';
260 --disable_query_log
261 UPDATE performance_schema.threads SET instrumented = 'YES' WHERE processlist_id = CONNECTION_ID();
262 --enable_query_log