Debian Codegraph @

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

raw
1 # Tests for PERFORMANCE_SCHEMA
2 # Miscelaneous
3
4 --source include/not_embedded.inc
5 --source include/have_perfschema.inc
6 --source include/have_innodb.inc
7 --source include/no_protocol.inc
8
9 #
10 # Bug#12790483 OBJECTS_SUMMARY_GLOBAL_BY_TYPE AND RENAME TABLE
11 #
12 # Rename table leaves old tables names behind in
13 # performance_schema.objects_summary_global_by_type
14 #
15 SELECT object_schema, object_name FROM performance_schema.objects_summary_global_by_type
16 WHERE object_schema='test';
17
18 CREATE TABLE test.t_before(a INT);
19 INSERT INTO test.t_before VALUES (1);
20
21 # The new table should appear in OBJECTS_SUMMARY_GLOBAL_BY_TYPE.
22
23 SELECT object_schema, object_name FROM performance_schema.objects_summary_global_by_type
24 WHERE object_schema='test';
25
26 RENAME TABLE test.t_before TO test.t_after;
27
28 # The renamed table should appear in OBJECTS_SUMMARY_GLOBAL_BY_TYPE, but only
29 # after it is accessed.
30
31 SELECT COUNT(*) FROM test.t_after;
32
33 SELECT object_schema, object_name FROM performance_schema.objects_summary_global_by_type
34 WHERE object_schema='test';
35
36 DROP TABLE test.t_after;
37
38 # The renamed table should not appear in OBJECTS_SUMMARY_GLOBAL_BY_TYPE.
39
40 SELECT object_schema, object_name FROM performance_schema.objects_summary_global_by_type
41 WHERE object_schema='test';
42
43 #
44 # Verify table views are ignored by the table io instrumentation.
45 #
46
47 CREATE TABLE test.t1(a INT);
48 INSERT INTO test.t1 VALUES (1);
49 CREATE VIEW test.v1 AS SELECT * FROM test.t1;
50 SELECT COUNT(*) FROM test.v1;
51
52 # Verify that a PFS table share was not created for the view.
53 SELECT object_schema, object_name FROM performance_schema.objects_summary_global_by_type
54 WHERE object_schema='test';
55
56 DROP VIEW test.v1;
57 DROP TABLE test.t1;
58
59 SELECT object_schema, object_name FROM performance_schema.objects_summary_global_by_type
60 WHERE object_schema='test';
61
62
63 #
64 # Bug#45496 Performance schema: assertion fails in
65 # ha_perfschema::rnd_init:223
66 #
67
68 --disable_result_log
69 SELECT EVENT_ID FROM performance_schema.events_waits_current
70 WHERE THREAD_ID IN
71 (SELECT THREAD_ID FROM performance_schema.threads)
72 AND EVENT_NAME IN
73 (SELECT NAME FROM performance_schema.setup_instruments
74 WHERE NAME LIKE "wait/synch/%")
75 LIMIT 1;
76 --enable_result_log
77
78 #
79 # Bug#45088 Should not be able to create tables of engine PERFORMANCE_SCHEMA
80 #
81
82 --error ER_CANT_CREATE_TABLE
83 create table test.t1(a int) engine=performance_schema;
84
85 # The table should not appear in OBJECTS_SUMMARY_GLOBAL_BY_TYPE
86
87 SELECT object_schema, object_name FROM performance_schema.objects_summary_global_by_type
88 WHERE object_schema='test';
89
90 #
91 # Bug#44897 Performance Schema: can create a ghost table in another database
92 #
93
94 --error ER_CANT_CREATE_TABLE
95 create table test.t1 like performance_schema.events_waits_current;
96
97 # The table should not appear in OBJECTS_SUMMARY_GLOBAL_BY_TYPE
98
99 SELECT object_schema, object_name FROM performance_schema.objects_summary_global_by_type
100 WHERE object_schema='test';
101
102 #
103 # Bug#44898 PerformanceSchema: can create a table in db performance_schema, cannot insert
104 #
105
106 --error ER_TABLEACCESS_DENIED_ERROR
107 create table performance_schema.t1(a int);
108
109 # The table should not appear in OBJECTS_SUMMARY_GLOBAL_BY_TYPE
110
111 SELECT object_schema, object_name FROM performance_schema.objects_summary_global_by_type
112 WHERE object_schema='test';
113
114 #
115 # Bug#51447 performance schema evil twin files
116 #
117
118 --disable_warnings
119 drop table if exists test.ghost;
120 --enable_warnings
121
122 create table test.ghost (a int, b int);
123 alter table test.ghost add index index_a(a);
124 alter table test.ghost add index index_b(b);
125 insert into test.ghost values (1, 3);
126 insert into test.ghost values (2, 4);
127 select * from test.ghost;
128
129 drop table test.ghost;
130
131 # Shoud return nothing
132 select * from performance_schema.file_instances
133 where file_name like "%ghost%";
134
135 #
136 # Bug#52586 Misleading error message on attempt to access
137 # a P_S table using a wrong name
138
139 --error ER_NO_SUCH_TABLE
140 select * from performance_schema.no_such_table;
141
142 #
143 # Bug#12370950 - 60905: TABLE_IO_WAITS_SUMMARY_BY_INDEX_USAGE AGGREGATES NON-INSERT DML WRONGLY
144 #
145
146 --disable_warnings
147 DROP TABLE IF EXISTS t_60905;
148 --enable_warnings
149 CREATE TABLE t_60905 (i INT, j INT, KEY(i)) ENGINE = InnoDB;
150 INSERT INTO t_60905 VALUES
151 (1,2), (3,4), (5,6), (7,8), (9,10);
152
153 # should delete with a "single" PRIMARY lookup (2 PRIMARY fetch, 1 PRIMARY delete)
154 DELETE FROM t_60905 WHERE i = 1;
155
156 # should delete with a full scan (5 NULL fetch, 1 NULL delete)
157 DELETE FROM t_60905 WHERE j = 8;
158
159 # show the instrument data.
160 SELECT object_schema,
161 object_name,
162 index_name,
163 count_fetch,
164 count_insert,
165 count_update,
166 count_delete
167 FROM performance_schema.table_io_waits_summary_by_index_usage
168 WHERE object_schema = 'test'
169 AND object_name = 't_60905';
170
171 DROP TABLE t_60905;
172
173 #
174 # Bug#71278 NUMBER OF ROWS IN PERFORMANCE SCHEMA TABLES
175 #
176
177 show global variables like "performance_schema_max_thread_instances";
178 replace_result 512 256;
179 explain select * from performance_schema.threads;
180
181 #
182 # Bug #73082 SHOW EVENTS in performance_schema gives Access Denied
183 #
184
185 use performance_schema;
186 show events;
187 show events from performance_schema;
188 show events where Db= 'performance_schema';
189 use test;
190
191
192 #
193 # Bug#11929832 - EVENTS_STATEMENTS_HISTORY HAS ERRORS=0 WHEN THERE ARE ERRORS
194 #
195 # Verify that SQL errors are properly counted.
196
197 use test;
198 truncate performance_schema.events_statements_history;
199 truncate performance_schema.events_statements_history_long;
200
201 --error ER_NO_SUCH_TABLE
202 select * from t1;
203
204 --echo
205 select mysql_errno, returned_sqlstate, message_text, errors, warnings
206 from performance_schema.events_statements_history where errors > 0;
207
208 --echo #
209 select mysql_errno, returned_sqlstate, message_text, errors, warnings from
210 performance_schema.events_statements_history_long where errors > 0;
211
212 #
213 # Bug#20519832 - TRUNCATED SQL_TEXT values are not suffixed with '...'
214 #
215 # Verify that truncated SQL statements are suffixed with '...'
216
217 use performance_schema;
218 truncate performance_schema.events_statements_history;
219
220 # Should truncate at 1024 bytes (1024 characters)
221 select 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' AS A;
222
223 # Should truncate at 1024 bytes (487 characters)
224
225 select _utf8mb4 'васÑвасÑвасÑвасÑвасÑвасÑвасÑвасÑвасÑвасÑвасÑвасÑвасÑвасÑвасÑвасÑвасÑвасÑвасÑвасÑвасÑвасÑвасÑвасÑвасÑвасÑвасÑвасÑвасÑвасÑвасÑвасÑвасÑвасÑвасÑвасÑвасÑвасÑвасÑвасÑвасÑвасÑвасÑвасÑвасÑвасÑвасÑвасÑвасÑвасÑвасÑвасÑвасÑвасÑвасÑвасÑвасÑвасÑвасÑвасÑвасÑвасÑвасÑвасÑвасÑвасÑвасÑвасÑвасÑвасÑвасÑвасÑ' as B;
226
227 select count(*) from events_statements_history where sql_text like "%...";
228
229
230 #
231 # MDEV-10486 MariaDB 10.x does not update rows_examined in performance_schema tables
232 # Verify that the rows_examined counter is set properly.
233
234 use test;
235 create table t1 (id int);
236 insert into t1 values (1), (2), (3);
237 truncate performance_schema.events_statements_history;
238 select * from t1;
239 insert into t1 select RAND()*10000 from t1;
240 select sql_text, rows_examined from performance_schema.events_statements_history;
241 drop table t1;
242
243 --echo #
244 --echo # MDEV-17896 Assertion `pfs->get_refcount() > 0' failed
245 --echo # in release_table_share
246 --echo #
247
248 # There must be at least one available slot in PFS table_share_array for
249 # this test to be meaningful. If there are no free slots we must
250 # restart mysqld, it is the only way to reset PFS table_share_array
251 let $query= SELECT COUNT(*)<@@performance_schema_max_table_instances FROM
252 performance_schema.objects_summary_global_by_type WHERE OBJECT_TYPE='TABLE';
253
254 let $free_slots_available= `$query`;
255
256 if (!$free_slots_available)
257 {
258 source include/restart_mysqld.inc;
259 }
260 eval $query;
261
262 CREATE TABLE t0(a INT);
263
264 # TABLE_SHARE must be cached in the table definition cache.
265 SELECT * FROM t0;
266
267 # Dropping t0 using DROP TEMPORARY frees up a slot in table_share_array,
268 # but the persistent table is not correctly dropped, i.e. TABLE_SHARE::m_psi
269 # still points to that slot in table_share_array.
270 DROP TEMPORARY TABLE IF EXISTS t0;
271
272 # Try re-using each and every slot in PFS table_share_array. If bug is
273 # there, we re-use t0 slot.
274 # The newly created table that re-uses the t0 slot ends up
275 # resetting the PFS_table_share refcount.
276 let $i= `SELECT @@performance_schema_max_table_instances`;
277 disable_query_log;
278 while ($i)
279 {
280 # Memory engine is here to reduce disk IO
281 eval CREATE TABLE t$i(a INT) ENGINE=MEMORY;
282 eval DROP TABLE t$i;
283 dec $i;
284 }
285 enable_query_log;
286
287 # FLUSH TABLE crashes the server when PFS_table_share is found with
288 # an unexpected refcount.
289 FLUSH TABLE t0;
290 DROP TABLE t0;
291
292 --echo #
293 --echo # MDEV-24364 Alter rename table does not remove PFS share
294 --echo #
295 create or replace table yt4 (x int) engine innodb;
296 alter table yt4 rename to t1, algorithm=copy;
297 drop tables t1;
298
299 select object_type, object_schema, object_name
300 from performance_schema.objects_summary_global_by_type
301 where object_schema="test";
302
303 --echo #
304 --echo # MDEV-28344: sys.ps_setup_save and dependent procedures fail
305 --echo # with ER_ILLEGAL_HA_CREATE_OPTION
306 --echo #
307
308 # It is not allowed to create temporary tables with performance schema
309 --error ER_ILLEGAL_HA_CREATE_OPTION
310 CREATE TEMPORARY TABLE t1 (t int) ENGINE = PERFORMANCE_SCHEMA;
311
312 --error ER_ILLEGAL_HA_CREATE_OPTION
313 CREATE TEMPORARY TABLE t1 LIKE performance_schema.setup_actors;
314
315 SET @default_storage_engine_old = @@default_storage_engine;
316 SET default_storage_engine = performance_schema;
317
318 --error ER_ILLEGAL_HA_CREATE_OPTION
319 CREATE TEMPORARY TABLE t1 (t int);
320
321 SET @@default_storage_engine = @default_storage_engine_old;