Debian Codegraph @

mysql-test/suite/perfschema/r/system_events_plugin.result

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