Debian Codegraph @

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

raw
1 TABLE: "prepared_statements_instances"
2 INDEXES: PK (OBJECT_INSTANCE_BEGIN), KEY (STATEMENT_ID), KEY (STATEMENT_NAME),
3 KEY (OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME), KEY (OWNER_THREAD_ID, OWNER_EVENT_ID)
4
5 ===== SETUP =====
6 ## Connection Default
7 ## Disable events from the default connection
8
9 ## Create user1, user2, user3
10 CREATE USER user1@localhost;
11 CREATE USER user2@localhost;
12 CREATE USER user3@localhost;
13 GRANT ALL ON *.* to 'user1'@localhost;
14 GRANT ALL ON *.* to 'user2'@localhost;
15 GRANT ALL ON *.* to 'user3'@localhost;
16
17 ## Setup test connection 1
18 ## Setup test connection 2
19 ## Setup test connection 3
20
21 ## Default connection
22
23 ## Create data tables
24 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));
25 CREATE TABLE test.t2 LIKE test.t1;
26 CREATE TABLE test.t3 LIKE test.t1;
27
28 ## Generate event data from 3 connections
29 ## Connection 1
30 ## Connection 2
31 ## Connection 3
32 ## Connection 2
33 ## Create prepared statements on connection 2
34 PREPARE st1 FROM 'SELECT SQRT(POW(?,2) + POW(?,2)) AS hypotenuse';
35 PREPARE st3 FROM 'INSERT INTO test.t1 SELECT * FROM test.t1 WHERE a<=?';
36 PREPARE st4 FROM '(SELECT a FROM test.t1) UNION (SELECT a+10 FROM test.t1) ORDER BY RAND()*0+a';
37 ## Create stored procedure connection 2
38 CREATE PROCEDURE SampleProc1()
39 BEGIN
40 SET @table = 'test.t1';
41 SET @s = CONCAT('SELECT * FROM ', @table);
42 PREPARE st2 FROM @s;
43 END|
44
45 Call SampleProc1() to prepare a statement
46 CALL SampleProc1();
47 ## Connection default
48
49 ## Use Connection 2 for default target thread id
50
51 ## Create index_test() procedure
52 SELECT object_instance_begin, statement_id, owner_thread_id, owner_event_id
53 INTO @object_instance_begin, @target_statement_id, @target_thread_id, @target_event_id
54 FROM performance_schema.prepared_statements_instances
55 WHERE STATEMENT_NAME = 'st2' LIMIT 1;
56
57 ## Test OBJECT_INSTANCE_BEGIN
58
59 ===== BEGIN TEST =====
60 ========
61 SELECT * FROM performance_schema.prepared_statements_instances WHERE object_instance_begin is null
62
63 OK: Handler_read_key
64
65 OK: Result set: Empty Expected: Empty
66
67 OK: Extra rows: 0 Missing rows: 0
68 ========
69 SELECT * FROM performance_schema.prepared_statements_instances WHERE object_instance_begin is not null
70
71 OK: Handler_read_key
72
73 OK: Result set: Non-empty Expected: Non-empty
74
75 OK: Extra rows: 0 Missing rows: 0
76 ========
77 SELECT * FROM performance_schema.prepared_statements_instances WHERE object_instance_begin = @object_instance_begin
78
79 OK: Handler_read_key
80
81 OK: Result set: Non-empty Expected: Non-empty
82
83 OK: Extra rows: 0 Missing rows: 0
84 ========
85 SELECT * FROM performance_schema.prepared_statements_instances WHERE object_instance_begin != @object_instance_begin
86
87 OK: Handler_read_key
88
89 OK: Result set: Non-empty Expected: Non-empty
90
91 OK: Extra rows: 0 Missing rows: 0
92 ========
93 SELECT * FROM performance_schema.prepared_statements_instances WHERE object_instance_begin = '9999999999'
94
95 OK: Handler_read_key
96
97 OK: Result set: Empty Expected: Empty
98
99 OK: Extra rows: 0 Missing rows: 0
100
101 ## Test STATEMENT_ID
102 SET @target_id = @target_statement_id;
103
104 ===== BEGIN TEST =====
105 ========
106 SELECT * FROM performance_schema.prepared_statements_instances WHERE STATEMENT_ID is null
107
108 OK: Handler_read_key
109
110 OK: Result set: Empty Expected: Empty
111
112 OK: Extra rows: 0 Missing rows: 0
113 ========
114 SELECT * FROM performance_schema.prepared_statements_instances WHERE STATEMENT_ID is not null
115
116 OK: Handler_read_key
117
118 OK: Result set: Non-empty Expected: Non-empty
119
120 OK: Extra rows: 0 Missing rows: 0
121 ========
122 SELECT * FROM performance_schema.prepared_statements_instances WHERE STATEMENT_ID = @target_id
123
124 OK: Handler_read_key
125
126 OK: Result set: Non-empty Expected: Non-empty
127
128 OK: Extra rows: 0 Missing rows: 0
129 ========
130 SELECT * FROM performance_schema.prepared_statements_instances WHERE STATEMENT_ID = @target_id ORDER BY STATEMENT_ID
131
132 OK: Handler_read_key
133
134 OK: Result set: Non-empty Expected: Non-empty
135
136 OK: Extra rows: 0 Missing rows: 0
137 ========
138 SELECT * FROM performance_schema.prepared_statements_instances WHERE STATEMENT_ID = @target_id ORDER BY STATEMENT_ID DESC
139
140 OK: Handler_read_key
141
142 OK: Result set: Non-empty Expected: Non-empty
143
144 OK: Extra rows: 0 Missing rows: 0
145 ========
146 SELECT * FROM performance_schema.prepared_statements_instances WHERE STATEMENT_ID = 9999999 ORDER BY STATEMENT_ID
147
148 OK: Handler_read_key
149
150 OK: Result set: Empty Expected: Empty
151
152 OK: Extra rows: 0 Missing rows: 0
153
154 ## Test STATEMENT_NAME
155
156 ===== BEGIN TEST =====
157 ========
158 SELECT * FROM performance_schema.prepared_statements_instances WHERE STATEMENT_NAME is null
159
160 OK: Handler_read_key
161
162 OK: Result set: Empty Expected: Empty
163
164 OK: Extra rows: 0 Missing rows: 0
165 ========
166 SELECT * FROM performance_schema.prepared_statements_instances WHERE STATEMENT_NAME is not null
167
168 OK: Handler_read_key
169
170 OK: Result set: Non-empty Expected: Non-empty
171
172 OK: Extra rows: 0 Missing rows: 0
173 ========
174 SELECT * FROM performance_schema.prepared_statements_instances WHERE STATEMENT_NAME = 'st2'
175
176 OK: Handler_read_key
177
178 OK: Result set: Non-empty Expected: Non-empty
179
180 OK: Extra rows: 0 Missing rows: 0
181 ========
182 SELECT * FROM performance_schema.prepared_statements_instances WHERE STATEMENT_NAME != 'st2'
183
184 OK: Handler_read_key
185
186 OK: Result set: Non-empty Expected: Non-empty
187
188 OK: Extra rows: 0 Missing rows: 0
189 ========
190 SELECT * FROM performance_schema.prepared_statements_instances WHERE STATEMENT_NAME = 'impossible'
191
192 OK: Handler_read_key
193
194 OK: Result set: Empty Expected: Empty
195
196 OK: Extra rows: 0 Missing rows: 0
197 ========
198 SELECT * FROM performance_schema.prepared_statements_instances WHERE STATEMENT_NAME != 'impossible' ORDER BY STATEMENT_NAME DESC
199
200 OK: Handler_read_key
201
202 OK: Result set: Non-empty Expected: Non-empty
203
204 OK: Extra rows: 0 Missing rows: 0
205
206 ## Test OWNER_THREAD_ID, OWNER_EVENT_ID
207
208 ===== BEGIN TEST =====
209 ========
210 SELECT * FROM performance_schema.prepared_statements_instances WHERE owner_thread_id is null
211
212 OK: Handler_read_key
213
214 OK: Result set: Empty Expected: Empty
215
216 OK: Extra rows: 0 Missing rows: 0
217 ========
218 SELECT * FROM performance_schema.prepared_statements_instances WHERE owner_thread_id is not null
219
220 OK: Handler_read_key
221
222 OK: Result set: Non-empty Expected: Non-empty
223
224 OK: Extra rows: 0 Missing rows: 0
225 ========
226 SELECT * FROM performance_schema.prepared_statements_instances WHERE owner_thread_id = @target_thread_id
227
228 OK: Handler_read_key
229
230 OK: Result set: Non-empty Expected: Non-empty
231
232 OK: Extra rows: 0 Missing rows: 0
233 ========
234 SELECT * FROM performance_schema.prepared_statements_instances WHERE owner_thread_id = @target_thread_id AND owner_event_id = @target_event_id
235
236 OK: Handler_read_key
237
238 OK: Result set: Non-empty Expected: Non-empty
239
240 OK: Extra rows: 0 Missing rows: 0
241 ========
242 SELECT * FROM performance_schema.prepared_statements_instances WHERE owner_event_id != 9999999
243
244 OK: Handler_read_key
245
246 OK: Result set: Non-empty Expected: Non-empty
247
248 OK: Extra rows: 0 Missing rows: 0
249 ========
250 SELECT * FROM performance_schema.prepared_statements_instances WHERE owner_thread_id = @target_thread_id ORDER BY owner_thread_id
251
252 OK: Handler_read_key
253
254 OK: Result set: Non-empty Expected: Non-empty
255
256 OK: Extra rows: 0 Missing rows: 0
257 ========
258 SELECT * FROM performance_schema.prepared_statements_instances WHERE owner_thread_id = @target_thread_id ORDER BY owner_thread_id DESC
259
260 OK: Handler_read_key
261
262 OK: Result set: Non-empty Expected: Non-empty
263
264 OK: Extra rows: 0 Missing rows: 0
265 ========
266 SELECT * FROM performance_schema.prepared_statements_instances WHERE owner_thread_id = @target_thread_id AND owner_event_id is null
267
268 OK: Handler_read_key
269
270 OK: Result set: Empty Expected: Empty
271
272 OK: Extra rows: 0 Missing rows: 0
273 ========
274 SELECT * FROM performance_schema.prepared_statements_instances WHERE owner_thread_id = @target_thread_id AND owner_event_id is not null
275
276 OK: Handler_read_key
277
278 OK: Result set: Non-empty Expected: Non-empty
279
280 OK: Extra rows: 0 Missing rows: 0
281 ========
282 SELECT * FROM performance_schema.prepared_statements_instances WHERE owner_thread_id = @target_thread_id AND owner_event_id != 99999999
283
284 OK: Handler_read_key
285
286 OK: Result set: Non-empty Expected: Non-empty
287
288 OK: Extra rows: 0 Missing rows: 0
289 ========
290 SELECT * FROM performance_schema.prepared_statements_instances WHERE owner_thread_id = @target_thread_id AND owner_event_id = 99999999
291
292 OK: Handler_read_key
293
294 OK: Result set: Empty Expected: Empty
295
296 OK: Extra rows: 0 Missing rows: 0
297 ========
298 SELECT * FROM performance_schema.prepared_statements_instances WHERE owner_thread_id >= @target_thread_id
299
300 OK: Handler_read_key
301
302 OK: Result set: Non-empty Expected: Non-empty
303
304 OK: Extra rows: 0 Missing rows: 0
305 ========
306 SELECT * FROM performance_schema.prepared_statements_instances WHERE owner_thread_id <= @target_thread_id
307
308 OK: Handler_read_key
309
310 OK: Result set: Non-empty Expected: Non-empty
311
312 OK: Extra rows: 0 Missing rows: 0
313 ========
314 SELECT * FROM performance_schema.prepared_statements_instances WHERE owner_thread_id != 9999999 AND owner_event_id = @target_event_id
315
316 OK: Handler_read_key
317
318 OK: Result set: Non-empty Expected: Non-empty
319
320 OK: Extra rows: 0 Missing rows: 0
321 ========
322 SELECT * FROM performance_schema.prepared_statements_instances WHERE owner_thread_id = 9999999 ORDER BY owner_thread_id
323
324 OK: Handler_read_key
325
326 OK: Result set: Empty Expected: Empty
327
328 OK: Extra rows: 0 Missing rows: 0
329
330 ## Test OWNER_OBJECT_TYPE, OWNER_OBJECT_SCHEMA, OWNER_OBJECT_NAME
331
332 ===== BEGIN TEST =====
333 ========
334 SELECT * FROM performance_schema.prepared_statements_instances WHERE owner_object_type is null
335
336 OK: Handler_read_key
337
338 OK: Result set: Non-empty Expected: Non-empty
339
340 OK: Extra rows: 0 Missing rows: 0
341 ========
342 SELECT * FROM performance_schema.prepared_statements_instances WHERE owner_object_type is not null
343
344 OK: Handler_read_key
345
346 OK: Result set: Non-empty Expected: Non-empty
347
348 OK: Extra rows: 0 Missing rows: 0
349 ========
350 SELECT * FROM performance_schema.prepared_statements_instances WHERE owner_object_type = 'PROCEDURE' and owner_object_schema is null
351
352 OK: Handler_read_key
353
354 OK: Result set: Empty Expected: Empty
355
356 OK: Extra rows: 0 Missing rows: 0
357 ========
358 SELECT * FROM performance_schema.prepared_statements_instances WHERE owner_object_type = 'PROCEDURE' and owner_object_schema is not null
359
360 OK: Handler_read_key
361
362 OK: Result set: Non-empty Expected: Non-empty
363
364 OK: Extra rows: 0 Missing rows: 0
365 ========
366 SELECT * FROM performance_schema.prepared_statements_instances WHERE owner_object_type = 'PROCEDURE' and owner_object_schema = 'test' and owner_object_name is null
367
368 OK: Handler_read_key
369
370 OK: Result set: Empty Expected: Empty
371
372 OK: Extra rows: 0 Missing rows: 0
373 ========
374 SELECT * FROM performance_schema.prepared_statements_instances WHERE owner_object_type = 'PROCEDURE' and owner_object_schema = 'test' and owner_object_name is not null
375
376 OK: Handler_read_key
377
378 OK: Result set: Non-empty Expected: Non-empty
379
380 OK: Extra rows: 0 Missing rows: 0
381 ========
382 SELECT * FROM performance_schema.prepared_statements_instances WHERE owner_object_type = 'PROCEDURE'
383
384 OK: Handler_read_key
385
386 OK: Result set: Non-empty Expected: Non-empty
387
388 OK: Extra rows: 0 Missing rows: 0
389 ========
390 SELECT * FROM performance_schema.prepared_statements_instances WHERE owner_object_type = 'PROCEDURE' AND owner_object_schema = 'test'
391
392 OK: Handler_read_key
393
394 OK: Result set: Non-empty Expected: Non-empty
395
396 OK: Extra rows: 0 Missing rows: 0
397 ========
398 SELECT * FROM performance_schema.prepared_statements_instances WHERE owner_object_type = 'PROCEDURE' AND owner_object_schema = 'test' AND owner_object_name = 'SampleProc1'
399
400 OK: Handler_read_key
401
402 OK: Result set: Non-empty Expected: Non-empty
403
404 OK: Extra rows: 0 Missing rows: 0
405 ========
406 SELECT * FROM performance_schema.prepared_statements_instances WHERE owner_object_type = 'impossible' AND owner_object_schema = 'test' AND owner_object_name = 'SampleProc1'
407
408 OK: Handler_read_key
409
410 OK: Result set: Empty Expected: Empty
411
412 OK: Extra rows: 0 Missing rows: 0
413 ========
414 SELECT * FROM performance_schema.prepared_statements_instances WHERE owner_object_type = 'impossible'
415
416 OK: Handler_read_key
417
418 OK: Result set: Empty Expected: Empty
419
420 OK: Extra rows: 0 Missing rows: 0
421 ========
422 SELECT * FROM performance_schema.prepared_statements_instances WHERE owner_object_type = 'PROCEDURE' AND owner_object_schema = 'impossible'
423
424 OK: Handler_read_key
425
426 OK: Result set: Empty Expected: Empty
427
428 OK: Extra rows: 0 Missing rows: 0
429 ========
430 SELECT * FROM performance_schema.prepared_statements_instances WHERE owner_object_type = 'PROCEDURE' AND owner_object_schema = 'test' AND owner_object_name = 'impossible'
431
432 OK: Handler_read_key
433
434 OK: Result set: Empty Expected: Empty
435
436 OK: Extra rows: 0 Missing rows: 0
437
438 ===== CLEAN UP =====
439 ## Default connection
440 DEALLOCATE PREPARE st1;
441 DEALLOCATE PREPARE st2;
442 DEALLOCATE PREPARE st3;
443 DEALLOCATE PREPARE st4;
444 DROP PROCEDURE SampleProc1;
445 DROP PROCEDURE index_test;
446 DROP TABLE IF EXISTS test.indexes_off;
447 DROP TABLE IF EXISTS test.indexes_on;
448 DROP TABLE test.t1;
449 DROP TABLE test.t2;
450 DROP TABLE test.t3;
451 REVOKE ALL PRIVILEGES, GRANT OPTION FROM user1@localhost;
452 REVOKE ALL PRIVILEGES, GRANT OPTION FROM user2@localhost;
453 REVOKE ALL PRIVILEGES, GRANT OPTION FROM user3@localhost;
454 DROP USER user1@localhost;
455 DROP USER user2@localhost;
456 DROP USER user3@localhost;
457 UPDATE performance_schema.threads SET INSTRUMENTED = 'YES';