Debian Codegraph @

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

raw
1 --source include/not_embedded.inc
2 --source include/have_perfschema.inc
3
4 --disable_warnings
5 drop table if exists test.setup_objects;
6 --enable_warnings
7
8 # Make sure only table io / table lock is instrumented and timed
9 update performance_schema.setup_instruments set enabled='NO';
10 update performance_schema.setup_instruments set enabled='YES', timed='YES'
11 where name in ('wait/io/table/sql/handler', 'wait/lock/table/sql/handler');
12
13 # Remove noise from previous tests
14 truncate table performance_schema.events_waits_history_long;
15
16 # Save the setup
17 create table test.setup_objects as select * from performance_schema.setup_objects;
18 truncate table performance_schema.setup_objects;
19
20 insert into performance_schema.setup_objects
21 values ('TABLE', 'db1', 't1', 'YES', 'YES');
22
23 insert into performance_schema.setup_objects
24 values ('TABLE', 'db1', 't2', 'YES', 'NO');
25
26 insert into performance_schema.setup_objects
27 values ('TABLE', 'db1', '%', 'YES', 'YES');
28
29 insert into performance_schema.setup_objects
30 values ('TABLE', 'db2', 't1', 'YES', 'YES');
31
32 insert into performance_schema.setup_objects
33 values ('TABLE', 'db2', 't2', 'YES', 'NO');
34
35 insert into performance_schema.setup_objects
36 values ('TABLE', 'db3', 't1', 'YES', 'YES');
37
38 insert into performance_schema.setup_objects
39 values ('TABLE', 'db3', 't2', 'NO', 'NO');
40
41 insert into performance_schema.setup_objects
42 values ('TABLE', 'db3', '%', 'NO', 'YES');
43
44 insert into performance_schema.setup_objects
45 values ('TABLE', 'db4', 't1', 'NO', 'YES');
46
47 insert into performance_schema.setup_objects
48 values ('TABLE', 'db4', '%', 'YES', 'NO');
49
50 # Also test insert of invalid enum values
51 SET sql_mode = 'NO_ENGINE_SUBSTITUTION';
52 --error ER_NO_REFERENCED_ROW_2
53 insert into performance_schema.setup_objects
54 values ('SOMETHING', 'bad1', 'bad1', 'YES', 'NO');
55 show warnings;
56
57 --error ER_NO_REFERENCED_ROW_2
58 insert into performance_schema.setup_objects
59 values ('TABLE', 'bad2', 'bad2', 'MAYBE', 'NO');
60 show warnings;
61
62 --error ER_NO_REFERENCED_ROW_2
63 insert into performance_schema.setup_objects
64 values ('TABLE', 'bad3', 'bad3', 'YES', 'MAYBE NOT');
65 show warnings;
66
67 select * from performance_schema.setup_objects
68 order by OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME;
69
70 # Also test update of invalid enum values
71 # Note: do not use SHOW WARNINGS in the test,
72 # because error messages such as:
73 # "Data truncated for column 'OBJECT_TYPE' at row <N>"
74 # do not always use the same row number <N>.
75
76 # ER_WRONG_PERFSCHEMA_USAGE because of privileges, expected.
77 --error ER_WRONG_PERFSCHEMA_USAGE
78 update performance_schema.setup_objects
79 set OBJECT_TYPE='SOMETHING' where OBJECT_SCHEMA='db1' and OBJECT_NAME='t1';
80
81 --error ER_NO_REFERENCED_ROW_2
82 update performance_schema.setup_objects
83 set ENABLED='MAYBE' where OBJECT_SCHEMA='db1' and OBJECT_NAME='t1';
84
85 --error ER_NO_REFERENCED_ROW_2
86 update performance_schema.setup_objects
87 set TIMED='MAYBE NOT' where OBJECT_SCHEMA='db1' and OBJECT_NAME='t1';
88
89 select * from performance_schema.setup_objects
90 order by OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME;
91 SET sql_mode = default;
92 --disable_warnings
93 drop database if exists db1;
94 drop database if exists db2;
95 drop database if exists db3;
96 drop database if exists db4;
97 drop database if exists db5;
98 --enable_warnings
99
100 create database db1;
101 create database db2;
102 create database db3;
103 create database db4;
104 create database db5;
105
106 create table db1.t1(a int);
107 create table db1.t2(a int);
108 create table db1.t3(a int);
109 create table db1.t4(a int);
110 create table db1.t5(a int);
111
112 create table db2.t1(a int);
113 create table db2.t2(a int);
114 create table db2.t3(a int);
115 create table db2.t4(a int);
116 create table db2.t5(a int);
117
118 create table db3.t1(a int);
119 create table db3.t2(a int);
120 create table db3.t3(a int);
121 create table db3.t4(a int);
122 create table db3.t5(a int);
123
124 create table db4.t1(a int);
125 create table db4.t2(a int);
126 create table db4.t3(a int);
127 create table db4.t4(a int);
128 create table db4.t5(a int);
129
130 create table db5.t1(a int);
131
132 select * from db1.t1;
133 select * from db1.t2;
134 select * from db1.t3;
135 select * from db1.t4;
136 select * from db1.t5;
137
138 select * from db2.t1;
139 select * from db2.t2;
140 select * from db2.t3;
141 select * from db2.t4;
142 select * from db2.t5;
143
144 select * from db3.t1;
145 select * from db3.t2;
146 select * from db3.t3;
147 select * from db3.t4;
148 select * from db3.t5;
149
150 select * from db4.t1;
151 select * from db4.t2;
152 select * from db4.t3;
153 select * from db4.t4;
154 select * from db4.t5;
155
156 select * from db5.t1;
157
158 create table db5.t2(a int);
159 select * from db3.t2;
160
161 # Verify what is instrumented
162 select distinct OBJECT_TYPE, OBJECT_NAME, OBJECT_SCHEMA
163 from performance_schema.events_waits_history_long
164 where OBJECT_SCHEMA like "db%"
165 group by OBJECT_TYPE, OBJECT_NAME, OBJECT_SCHEMA
166 order by OBJECT_TYPE, OBJECT_NAME, OBJECT_SCHEMA;
167
168 # Verify what is instrumented and timed
169 select distinct OBJECT_TYPE, OBJECT_NAME, OBJECT_SCHEMA
170 from performance_schema.events_waits_history_long
171 where OBJECT_SCHEMA like "db%" and TIMER_END is not NULL
172 group by OBJECT_TYPE, OBJECT_NAME, OBJECT_SCHEMA
173 order by OBJECT_TYPE, OBJECT_NAME, OBJECT_SCHEMA;
174
175 update performance_schema.setup_objects
176 set timed='YES' where OBJECT_SCHEMA = '%';
177
178 create table db5.t3(a int);
179 select * from db3.t3;
180
181 truncate table performance_schema.setup_objects;
182
183 select count(*) from performance_schema.setup_objects;
184
185 drop database db1;
186 drop database db2;
187 drop database db3;
188 drop database db4;
189 drop database db5;
190
191 # Restore the setup
192 truncate table performance_schema.setup_objects;
193 insert into performance_schema.setup_objects select * from test.setup_objects;
194 drop table test.setup_objects;
195
196 update performance_schema.setup_instruments set enabled='YES', timed='YES';
197