Debian Codegraph @

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

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