Debian Codegraph @

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

raw
1 TABLE: "table_handles"
2 INDEXES: PK (OBJECT_INSTANCE_BEGIN), KEY (OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME), KEY (OWNER_THREAD_ID, OWNER_EVENT_ID)
3
4 ===== SETUP =====
5 ## Connection Default
6 ## Disable events from the default connection
7
8 ## Create user1, user2, user3
9 CREATE USER user1@localhost;
10 CREATE USER user2@localhost;
11 CREATE USER user3@localhost;
12 GRANT ALL ON *.* to 'user1'@localhost;
13 GRANT ALL ON *.* to 'user2'@localhost;
14 GRANT ALL ON *.* to 'user3'@localhost;
15
16 ## Setup test connection 1
17 ## Setup test connection 2
18 ## Setup test connection 3
19
20 ## Default connection
21
22 ## Create data tables
23 CREATE TABLE test.t1(a INT, b INT, c INT, d INT DEFAULT 0, PRIMARY KEY(a), INDEX index_b(b), INDEX index_cb(c, b));
24 CREATE TABLE test.t2 LIKE test.t1;
25 CREATE TABLE test.t3 LIKE test.t1;
26
27 ## Generate event data from 3 connections
28 ## Connection 1
29 ## Connection 2
30 ## Connection 3
31 ## Connection 2
32 ## Create prepared statements on connection 2
33 PREPARE st1 FROM 'SELECT SQRT(POW(?,2) + POW(?,2)) AS hypotenuse';
34 PREPARE st3 FROM 'INSERT INTO test.t1 SELECT * FROM test.t1 WHERE a<=?';
35 PREPARE st4 FROM '(SELECT a FROM test.t1) UNION (SELECT a+10 FROM test.t1) ORDER BY RAND()*0+a';
36 ## Create stored procedure connection 2
37 CREATE PROCEDURE SampleProc1()
38 BEGIN
39 SET @table = 'test.t1';
40 SET @s = CONCAT('SELECT * FROM ', @table);
41 PREPARE st2 FROM @s;
42 END|
43
44 Call SampleProc1() to prepare a statement
45 CALL SampleProc1();
46 ## Connection default
47
48 ## Use Connection 2 for default target thread id
49
50 ## Create index_test() procedure
51
52 ## Test OBJECT_INSTANCE_BEGIN
53
54 ===== BEGIN TEST =====
55 ========
56 SELECT * FROM performance_schema.table_handles WHERE object_instance_begin is null
57
58 OK: Handler_read_key
59
60 OK: Result set: Empty Expected: Empty
61
62 OK: Extra rows: 0 Missing rows: 0
63 ========
64 SELECT * FROM performance_schema.table_handles WHERE object_instance_begin is not null
65
66 OK: Handler_read_key
67
68 OK: Result set: Non-empty Expected: Non-empty
69
70 OK: Extra rows: 0 Missing rows: 0
71 ========
72 SELECT * FROM performance_schema.table_handles WHERE object_instance_begin = @object_instance_begin
73
74 OK: Handler_read_key
75
76 OK: Result set: Non-empty Expected: Non-empty
77
78 OK: Extra rows: 0 Missing rows: 0
79 ========
80 SELECT * FROM performance_schema.table_handles WHERE object_instance_begin != @object_instance_begin
81
82 OK: Handler_read_key
83
84 OK: Result set: Non-empty Expected: Non-empty
85
86 OK: Extra rows: 0 Missing rows: 0
87 ========
88 SELECT * FROM performance_schema.table_handles WHERE object_instance_begin = '9999999999'
89
90 OK: Handler_read_key
91
92 OK: Result set: Empty Expected: Empty
93
94 OK: Extra rows: 0 Missing rows: 0
95
96 ## Test OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME
97
98 ===== BEGIN TEST =====
99 ========
100 SELECT * FROM performance_schema.table_handles WHERE object_type is null
101
102 OK: Handler_read_key
103
104 OK: Result set: Empty Expected: Empty
105
106 OK: Extra rows: 0 Missing rows: 0
107 ========
108 SELECT * FROM performance_schema.table_handles WHERE object_type is not null
109
110 OK: Handler_read_key
111
112 OK: Result set: Non-empty Expected: Non-empty
113
114 OK: Extra rows: 0 Missing rows: 0
115 ========
116 SELECT * FROM performance_schema.table_handles WHERE object_type = 'TABLE' and object_schema is null
117
118 OK: Handler_read_key
119
120 OK: Result set: Empty Expected: Empty
121
122 OK: Extra rows: 0 Missing rows: 0
123 ========
124 SELECT * FROM performance_schema.table_handles WHERE object_type = 'TABLE' and object_schema is not null
125
126 OK: Handler_read_key
127
128 OK: Result set: Non-empty Expected: Non-empty
129
130 OK: Extra rows: 0 Missing rows: 0
131 ========
132 SELECT * FROM performance_schema.table_handles WHERE object_type = 'TABLE' and object_schema = 'test' and object_name is null
133
134 OK: Handler_read_key
135
136 OK: Result set: Empty Expected: Empty
137
138 OK: Extra rows: 0 Missing rows: 0
139 ========
140 SELECT * FROM performance_schema.table_handles WHERE object_type = 'TABLE' and object_schema = 'test' and object_name is not null
141
142 OK: Handler_read_key
143
144 OK: Result set: Non-empty Expected: Non-empty
145
146 OK: Extra rows: 0 Missing rows: 0
147 ========
148 SELECT * FROM performance_schema.table_handles WHERE object_type = 'TABLE'
149
150 OK: Handler_read_key
151
152 OK: Result set: Non-empty Expected: Non-empty
153
154 OK: Extra rows: 0 Missing rows: 0
155 ========
156 SELECT * FROM performance_schema.table_handles WHERE object_type = 'TABLE' and object_schema = 'test'
157
158 OK: Handler_read_key
159
160 OK: Result set: Non-empty Expected: Non-empty
161
162 OK: Extra rows: 0 Missing rows: 0
163 ========
164 SELECT * FROM performance_schema.table_handles WHERE object_type = 'TABLE' AND object_schema = 'test' AND object_name = 't1'
165
166 OK: Handler_read_key
167
168 OK: Result set: Non-empty Expected: Non-empty
169
170 OK: Extra rows: 0 Missing rows: 0
171 ========
172 SELECT * FROM performance_schema.table_handles WHERE object_type = 'impossible' AND object_schema = 'test' AND object_name = 't1'
173
174 OK: Handler_read_key
175
176 OK: Result set: Empty Expected: Empty
177
178 OK: Extra rows: 0 Missing rows: 0
179 ========
180 SELECT * FROM performance_schema.table_handles WHERE object_type = 'impossible'
181
182 OK: Handler_read_key
183
184 OK: Result set: Empty Expected: Empty
185
186 OK: Extra rows: 0 Missing rows: 0
187 ========
188 SELECT * FROM performance_schema.table_handles WHERE object_type = 'TABLE' AND object_schema = 'impossible'
189
190 OK: Handler_read_key
191
192 OK: Result set: Empty Expected: Empty
193
194 OK: Extra rows: 0 Missing rows: 0
195 ========
196 SELECT * FROM performance_schema.table_handles WHERE object_type = 'TABLE' AND object_schema = 'test' AND object_name = 'impossible'
197
198 OK: Handler_read_key
199
200 OK: Result set: Empty Expected: Empty
201
202 OK: Extra rows: 0 Missing rows: 0
203
204 ## Test OWNER_THREAD_ID, OWNER_EVENT_ID
205
206 ===== BEGIN TEST =====
207 ========
208 SELECT * FROM performance_schema.table_handles WHERE owner_thread_id is null
209
210 OK: Handler_read_key
211
212 OK: Result set: Non-empty Expected: Non-empty
213
214 OK: Extra rows: 0 Missing rows: 0
215 ========
216 SELECT * FROM performance_schema.table_handles WHERE owner_thread_id is not null
217
218 OK: Handler_read_key
219
220 OK: Result set: Non-empty Expected: Non-empty
221
222 OK: Extra rows: 0 Missing rows: 0
223 ========
224 SELECT * FROM performance_schema.table_handles WHERE owner_thread_id = @target_thread_id
225
226 OK: Handler_read_key
227
228 OK: Result set: Non-empty Expected: Non-empty
229
230 OK: Extra rows: 0 Missing rows: 0
231 ========
232 SELECT * FROM performance_schema.table_handles WHERE owner_thread_id = @target_thread_id AND owner_event_id = @target_event_id
233
234 OK: Handler_read_key
235
236 OK: Result set: Non-empty Expected: Non-empty
237
238 OK: Extra rows: 0 Missing rows: 0
239 ========
240 SELECT * FROM performance_schema.table_handles WHERE owner_event_id != 9999999
241
242 OK: Handler_read_key
243
244 OK: Result set: Non-empty Expected: Non-empty
245
246 OK: Extra rows: 0 Missing rows: 0
247 ========
248 SELECT * FROM performance_schema.table_handles WHERE owner_thread_id = @target_thread_id ORDER BY owner_thread_id
249
250 OK: Handler_read_key
251
252 OK: Result set: Non-empty Expected: Non-empty
253
254 OK: Extra rows: 0 Missing rows: 0
255 ========
256 SELECT * FROM performance_schema.table_handles WHERE owner_thread_id = @target_thread_id ORDER BY owner_thread_id DESC
257
258 OK: Handler_read_key
259
260 OK: Result set: Non-empty Expected: Non-empty
261
262 OK: Extra rows: 0 Missing rows: 0
263 ========
264 SELECT * FROM performance_schema.table_handles WHERE owner_thread_id = @target_thread_id AND owner_event_id is null
265
266 OK: Handler_read_key
267
268 OK: Result set: Empty Expected: Empty
269
270 OK: Extra rows: 0 Missing rows: 0
271 ========
272 SELECT * FROM performance_schema.table_handles WHERE owner_thread_id = @target_thread_id AND owner_event_id is not null
273
274 OK: Handler_read_key
275
276 OK: Result set: Non-empty Expected: Non-empty
277
278 OK: Extra rows: 0 Missing rows: 0
279 ========
280 SELECT * FROM performance_schema.table_handles WHERE owner_thread_id = @target_thread_id AND owner_event_id != 99999999
281
282 OK: Handler_read_key
283
284 OK: Result set: Non-empty Expected: Non-empty
285
286 OK: Extra rows: 0 Missing rows: 0
287 ========
288 SELECT * FROM performance_schema.table_handles WHERE owner_thread_id = @target_thread_id AND owner_event_id = 99999999
289
290 OK: Handler_read_key
291
292 OK: Result set: Empty Expected: Empty
293
294 OK: Extra rows: 0 Missing rows: 0
295 ========
296 SELECT * FROM performance_schema.table_handles WHERE owner_thread_id >= @target_thread_id
297
298 OK: Handler_read_key
299
300 OK: Result set: Non-empty Expected: Non-empty
301
302 OK: Extra rows: 0 Missing rows: 0
303 ========
304 SELECT * FROM performance_schema.table_handles WHERE owner_thread_id <= @target_thread_id
305
306 OK: Handler_read_key
307
308 OK: Result set: Non-empty Expected: Non-empty
309
310 OK: Extra rows: 0 Missing rows: 0
311 ========
312 SELECT * FROM performance_schema.table_handles WHERE owner_thread_id != 9999999 AND owner_event_id = @target_event_id
313
314 OK: Handler_read_key
315
316 OK: Result set: Non-empty Expected: Non-empty
317
318 OK: Extra rows: 0 Missing rows: 0
319 ========
320 SELECT * FROM performance_schema.table_handles WHERE owner_thread_id = 9999999 ORDER BY owner_thread_id
321
322 OK: Handler_read_key
323
324 OK: Result set: Empty Expected: Empty
325
326 OK: Extra rows: 0 Missing rows: 0
327 UNLOCK TABLES;
328 UNLOCK TABLES;
329
330 ===== CLEAN UP =====
331 ## Default connection
332 DEALLOCATE PREPARE st1;
333 DEALLOCATE PREPARE st2;
334 DEALLOCATE PREPARE st3;
335 DEALLOCATE PREPARE st4;
336 DROP PROCEDURE SampleProc1;
337 DROP PROCEDURE index_test;
338 DROP TABLE IF EXISTS test.indexes_off;
339 DROP TABLE IF EXISTS test.indexes_on;
340 DROP TABLE test.t1;
341 DROP TABLE test.t2;
342 DROP TABLE test.t3;
343 REVOKE ALL PRIVILEGES, GRANT OPTION FROM user1@localhost;
344 REVOKE ALL PRIVILEGES, GRANT OPTION FROM user2@localhost;
345 REVOKE ALL PRIVILEGES, GRANT OPTION FROM user3@localhost;
346 DROP USER user1@localhost;
347 DROP USER user2@localhost;
348 DROP USER user3@localhost;
349 UPDATE performance_schema.threads SET INSTRUMENTED = 'YES';