Debian Codegraph @

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

raw
1 # Tests for PERFORMANCE_SCHEMA
2 # Miscelaneous
3
4 # Test requires: sp-protocol/ps-protocol/view-protocol/cursor-protocol disabled
5 --source include/no_protocol.inc
6
7 #
8 # Bug#12790483 OBJECTS_SUMMARY_GLOBAL_BY_TYPE AND RENAME TABLE
9 #
10 # Rename table leaves old tables names behind in
11 # performance_schema.objects_summary_global_by_type
12 #
13 SELECT object_type, object_schema, object_name FROM performance_schema.objects_summary_global_by_type
14 WHERE object_schema='test';
15
16 CREATE TABLE test.t_before(a INT);
17 INSERT INTO test.t_before VALUES (1);
18
19 # The new table should appear in OBJECTS_SUMMARY_GLOBAL_BY_TYPE.
20
21 SELECT object_type, object_schema, object_name FROM performance_schema.objects_summary_global_by_type
22 WHERE object_schema='test';
23
24 RENAME TABLE test.t_before TO test.t_after;
25
26 # The renamed table should appear in OBJECTS_SUMMARY_GLOBAL_BY_TYPE, but only
27 # after it is accessed.
28
29 SELECT COUNT(*) FROM test.t_after;
30
31 SELECT object_type, object_schema, object_name FROM performance_schema.objects_summary_global_by_type
32 WHERE object_schema='test';
33
34 DROP TABLE test.t_after;
35
36 # The renamed table should not appear in OBJECTS_SUMMARY_GLOBAL_BY_TYPE.
37
38 SELECT object_type, object_schema, object_name FROM performance_schema.objects_summary_global_by_type
39 WHERE object_schema='test';
40
41 #
42 # Bug#33059358 ALTER EVENT RENAME LEAVES OLD EVENT NAME IN PERFORMANCE_SCHEMA
43 #
44 # ALTER EVENT RENAME leaves old event names behind in
45 # performance_schema.objects_summary_global_by_type
46 #
47 SELECT object_type, object_schema, object_name FROM performance_schema.objects_summary_global_by_type
48 WHERE object_schema='test';
49
50 CREATE EVENT test.e_before ON SCHEDULE EVERY 1 SECOND DO SELECT 1;
51
52 SELECT sleep(5);
53
54 # The new event should appear in OBJECTS_SUMMARY_GLOBAL_BY_TYPE.
55
56 SELECT object_type, object_schema, object_name FROM performance_schema.objects_summary_global_by_type
57 WHERE object_schema='test';
58
59 ALTER EVENT test.e_before RENAME TO test.e_after;
60
61 # The renamed event should appear in OBJECTS_SUMMARY_GLOBAL_BY_TYPE, but only
62 # after it is accessed.
63
64 SELECT sleep(5);
65
66 SELECT object_type, object_schema, object_name FROM performance_schema.objects_summary_global_by_type
67 WHERE object_schema='test';
68
69 DROP EVENT test.e_after;
70
71 # The renamed event should not appear in OBJECTS_SUMMARY_GLOBAL_BY_TYPE.
72
73 SELECT object_type, object_schema, object_name FROM performance_schema.objects_summary_global_by_type
74 WHERE object_schema='test';
75
76 #
77 # Verify table views are ignored by the table io instrumentation.
78 #
79
80 CREATE TABLE test.t1(a INT);
81 INSERT INTO test.t1 VALUES (1);
82 CREATE VIEW test.v1 AS SELECT * FROM test.t1;
83 SELECT COUNT(*) FROM test.v1;
84
85 # Verify that a PFS table share was not created for the view.
86 SELECT object_schema, object_name FROM performance_schema.objects_summary_global_by_type
87 WHERE object_schema='test';
88
89 DROP VIEW test.v1;
90 DROP TABLE test.t1;
91
92 SELECT object_schema, object_name FROM performance_schema.objects_summary_global_by_type
93 WHERE object_schema='test';
94
95
96 #
97 # Bug#45496 Performance schema: assertion fails in
98 # ha_perfschema::rnd_init:223
99 #
100
101 --disable_result_log
102 SELECT EVENT_ID FROM performance_schema.events_waits_current
103 WHERE THREAD_ID IN
104 (SELECT THREAD_ID FROM performance_schema.threads)
105 AND EVENT_NAME IN
106 (SELECT NAME FROM performance_schema.setup_instruments
107 WHERE NAME LIKE "wait/synch/%")
108 LIMIT 1;
109 --enable_result_log
110
111 #
112 # Bug#45088 Should not be able to create tables of engine PERFORMANCE_SCHEMA
113 #
114
115 --error ER_WRONG_PERFSCHEMA_USAGE
116 create table test.t1(a int) engine=performance_schema;
117
118 # The table should not appear in OBJECTS_SUMMARY_GLOBAL_BY_TYPE
119
120 SELECT object_schema, object_name FROM performance_schema.objects_summary_global_by_type
121 WHERE object_schema='test';
122
123 #
124 # Bug#44897 Performance Schema: can create a ghost table in another database
125 #
126
127 --error ER_WRONG_PERFSCHEMA_USAGE
128 create table test.t1 like performance_schema.events_waits_current;
129
130 # The table should not appear in OBJECTS_SUMMARY_GLOBAL_BY_TYPE
131
132 SELECT object_schema, object_name FROM performance_schema.objects_summary_global_by_type
133 WHERE object_schema='test';
134
135 #
136 # Bug#44898 PerformanceSchema: can create a table in db performance_schema, cannot insert
137 #
138
139 --error ER_DBACCESS_DENIED_ERROR
140 create table performance_schema.t1(a int);
141
142 # The table should not appear in OBJECTS_SUMMARY_GLOBAL_BY_TYPE
143
144 SELECT object_schema, object_name FROM performance_schema.objects_summary_global_by_type
145 WHERE object_schema='test';
146
147 #
148 # Bug#51447 performance schema evil twin files
149 #
150
151 --disable_warnings
152 drop table if exists test.ghost;
153 --enable_warnings
154
155 create table test.ghost (a int, b int);
156 alter table test.ghost add index index_a(a);
157 alter table test.ghost add index index_b(b);
158 insert into test.ghost values (1, 3);
159 insert into test.ghost values (2, 4);
160 select * from test.ghost;
161
162 drop table test.ghost;
163
164 # Should return nothing
165 select * from performance_schema.file_instances
166 where file_name like "%ghost%";
167
168 #
169 # Bug#52586 Misleading error message on attempt to access
170 # a P_S table using a wrong name
171
172 --error ER_NO_SUCH_TABLE
173 select * from performance_schema.no_such_table;
174
175 #
176 # Bug#30701047 SELECT FOR UPDATE NOT-EXIST TABLE
177 # IN PERFORMANCE SCHEMA REPORTS CONFUSING ERROR
178
179 --error ER_NO_SUCH_TABLE
180 select * from performance_schema.no_such_table for update;
181
182 --error ER_NO_SUCH_TABLE
183 insert into performance_schema.no_such_table values(1);
184
185 --error ER_NO_SUCH_TABLE
186 update performance_schema.no_such_table set no_such_col = 2;
187
188 --error ER_NO_SUCH_TABLE
189 delete from performance_schema.no_such_table where no_such_col = 3;
190
191 --error ER_NO_SUCH_TABLE
192 lock table performance_schema.no_such_table read;
193
194 --error ER_NO_SUCH_TABLE
195 lock table performance_schema.no_such_table write;
196
197 --error ER_NO_SUCH_TABLE
198 show create table performance_schema.no_such_table;
199
200 --error ER_NO_SUCH_TABLE
201 show create view performance_schema.no_such_view;
202
203 # Supported
204 drop table if exists performance_schema.no_such_table;
205
206 #
207 # Bug#12370950 - 60905: TABLE_IO_WAITS_SUMMARY_BY_INDEX_USAGE AGGREGATES NON-INSERT DML WRONGLY
208 #
209
210 --disable_warnings
211 DROP TABLE IF EXISTS t_60905;
212 --enable_warnings
213 CREATE TABLE t_60905 (i INT, j INT, KEY(i)) ENGINE = InnoDB;
214 INSERT INTO t_60905 VALUES
215 (1,2), (3,4), (5,6), (7,8), (9,10);
216
217 # should delete with a "single" PRIMARY lookup (2 PRIMARY fetch, 1 PRIMARY delete)
218 DELETE FROM t_60905 WHERE i = 1;
219
220 # should delete with a full scan (5 NULL fetch, 1 NULL delete)
221 DELETE FROM t_60905 WHERE j = 8;
222
223 # show the instrument data.
224 --skip_if_hypergraph # Uses PSI batch mode for DELETE, so the DELETEs are
225 # not counted. The old optimizer uses PSI batch mode
226 # only in multi-table DELETE.
227 SELECT object_schema,
228 object_name,
229 index_name,
230 count_fetch,
231 count_insert,
232 count_update,
233 count_delete
234 FROM performance_schema.table_io_waits_summary_by_index_usage
235 WHERE object_schema = 'test'
236 AND object_name = 't_60905';
237
238 DROP TABLE t_60905;
239
240 #
241 # Bug#71278 NUMBER OF ROWS IN PERFORMANCE SCHEMA TABLES
242 #
243
244 show global variables like "performance_schema_max_thread_instances";
245 explain select * from performance_schema.threads;
246
247 #
248 # Bug #73082 SHOW EVENTS in performance_schema gives Access Denied
249 #
250
251 use performance_schema;
252 show events;
253 show events from performance_schema;
254 show events where Db= 'performance_schema';
255 use test;
256
257
258 #
259 # Bug#11929832 - EVENTS_STATEMENTS_HISTORY HAS ERRORS=0 WHEN THERE ARE ERRORS
260 #
261 # Verify that SQL errors are properly counted.
262
263 use test;
264 truncate performance_schema.events_statements_history;
265 truncate performance_schema.events_statements_history_long;
266
267 --error ER_NO_SUCH_TABLE
268 select * from t1;
269
270 --echo
271 select mysql_errno, returned_sqlstate, message_text, errors, warnings
272 from performance_schema.events_statements_history where errors > 0;
273
274 --echo
275 select mysql_errno, returned_sqlstate, message_text, errors, warnings from
276 performance_schema.events_statements_history_long where errors > 0;
277
278 #
279 # Bug#20519832 - TRUNCATED SQL_TEXT values are not suffixed with '...'
280 #
281 # Verify that truncated SQL statements are suffixed with '...'
282
283 use performance_schema;
284 truncate performance_schema.events_statements_history;
285
286 set names latin1;
287 # Should truncate at 1024 bytes (1024 characters)
288 select 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' AS A;
289
290 # Should truncate at 1024 bytes (487 characters)
291
292 select _utf8mb4 'васÑвасÑвасÑвасÑвасÑвасÑвасÑвасÑвасÑвасÑвасÑвасÑвасÑвасÑвасÑвасÑвасÑвасÑвасÑвасÑвасÑвасÑвасÑвасÑвасÑвасÑвасÑвасÑвасÑвасÑвасÑвасÑвасÑвасÑвасÑвасÑвасÑвасÑвасÑвасÑвасÑвасÑвасÑвасÑвасÑвасÑвасÑвасÑвасÑвасÑвасÑвасÑвасÑвасÑвасÑвасÑвасÑвасÑвасÑвасÑвасÑвасÑвасÑвасÑвасÑвасÑвасÑвасÑвасÑвасÑвасÑвасÑ' as B;
293
294 select count(*) from events_statements_history where sql_text like "%...";
295
296 #
297 # Bug#26162562 INDEX NAME IS NOT CORRECT AFTER DELETE OPERATION ON PFS
298 # TABLE
299
300 --disable_warnings
301 drop table if exists test.t1;
302 --enable_warnings
303
304 insert into performance_schema.setup_objects
305 values ('TABLE', 'test', 't1', 'YES', 'YES');
306
307 create table test.t1 (a int, b int, primary key (a), key my_index(b));
308
309 insert into test.t1 values (1, 2), (3, 4), (5, 6);
310
311 flush table test.t1;
312
313 select * from test.t1 where a = 3;
314 select * from test.t1 where b = 4;
315
316 select OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME, COUNT_STAR
317 from performance_schema.table_lock_waits_summary_by_table
318 where OBJECT_NAME = 't1';
319
320 select OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME, COUNT_STAR
321 from performance_schema.table_io_waits_summary_by_index_usage
322 where OBJECT_NAME = 't1';
323
324 update performance_schema.setup_objects
325 set timed = 'NO' where OBJECT_NAME = 't1';
326
327 # Lock stats should still be here
328 select OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME, COUNT_STAR
329 from performance_schema.table_lock_waits_summary_by_table
330 where OBJECT_NAME = 't1';
331
332 # Index stats should still be here
333 select OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME, COUNT_STAR
334 from performance_schema.table_io_waits_summary_by_index_usage
335 where OBJECT_NAME = 't1';
336
337 update performance_schema.setup_objects
338 set enabled = 'NO' where OBJECT_NAME = 't1';
339
340 # Lock stats not printed
341 select OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME, COUNT_STAR
342 from performance_schema.table_lock_waits_summary_by_table
343 where OBJECT_NAME = 't1';
344
345 # Index stats not printed
346 select OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME, COUNT_STAR
347 from performance_schema.table_io_waits_summary_by_index_usage
348 where OBJECT_NAME = 't1';
349
350 update performance_schema.setup_objects
351 set enabled = 'YES' where OBJECT_NAME = 't1';
352
353 # Lock stats available again
354 select OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME, COUNT_STAR
355 from performance_schema.table_lock_waits_summary_by_table
356 where OBJECT_NAME = 't1';
357
358 # Index stats available again
359 select OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME, COUNT_STAR
360 from performance_schema.table_io_waits_summary_by_index_usage
361 where OBJECT_NAME = 't1';
362
363 drop table test.t1;
364
365 delete from performance_schema.setup_objects
366 where (OBJECT_TYPE = 'TABLE')
367 and (OBJECT_SCHEMA = 'test')
368 and (OBJECT_NAME = 't1');
369
370 --echo
371 --echo #
372 --echo # Bug#31544023 INCONSISTENCY JOINING PERFSCHEMA TABLES WHEN PK IS > 48 CHARS
373 --echo #
374 --echo # String key comparisons sometimes failed with fields defined with utf8mb4.
375 --echo #
376 --echo # Test key comparisons on varying CHANNEL_NAME lengths.
377 --echo # Both tables are defined with CHARSET = utf8mb4. The CHANNEL_NAME column is char(64).
378
379 --echo
380 --echo # 20 characters
381 change replication source to source_host = 'test1' for channel '12345678901234567890';
382
383 --echo
384 --echo # 50 characters
385 change replication source to source_host = 'test2' for channel '12345678901234567890123456789012345678901234567890';
386
387 --echo
388 --echo # 64 characters
389 change replication source to source_host = 'test3' for channel '123456789012345678901234567890123456789012345678901234567890123';
390
391 --echo
392 --echo # 70 characters (too long)
393 --error ER_REPLICA_CHANNEL_NAME_INVALID_OR_TOO_LONG
394 change replication source to source_host = 'test3' for channel '1234567890123456789012345678901234567890123456789012345678901234567890';
395
396 --echo
397 select channel_name from performance_schema.replication_connection_configuration;
398 --echo
399 select channel_name from performance_schema.replication_connection_status;
400
401 --echo
402 select rpad(rcc.channel_name, 64, ' ') 'CHANNEL_RCC', rpad(rcs.channel_name, 64, ' ') 'CHANNEL_RCS', rcc.channel_name = rcs.channel_name 'EXPECT 1'
403 from performance_schema.replication_connection_configuration rcc
404 join performance_schema.replication_connection_status rcs on rcc.channel_name = rcs.channel_name;
405
406 --echo
407 select rpad(rcc.channel_name, 64, ' ') 'CHANNEL_RCC', rpad(rcs.channel_name, 64, ' ') 'CHANNEL_RCS', rcc.channel_name = rcs.channel_name 'EXPECT 1'
408 from performance_schema.replication_connection_configuration rcc
409 left join replication_connection_status rcs on rcc.channel_name = rcs.channel_name;
410
411 --echo
412 --echo # Clean up
413 reset replica all for channel '12345678901234567890';
414 reset replica all for channel '12345678901234567890123456789012345678901234567890';
415 reset replica all for channel '123456789012345678901234567890123456789012345678901234567890123';