Debian Codegraph @

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

raw
1 TRUNCATE TABLE performance_schema.prepared_statements_instances;
2 CREATE DATABASE db;
3 USE db;
4 CREATE TABLE t
5 (
6 a INT,
7 b CHAR(10),
8 name CHAR(10)
9 );
10 SET sql_mode = 'NO_ENGINE_SUBSTITUTION';
11 INSERT INTO t VALUES (1,"kuchipudi","Vempati");
12 INSERT INTO t VALUES (2,"odissi","Mohapatra");
13 INSERT INTO t VALUES (3,"kathak","Maharaj");
14 INSERT INTO t VALUES (4,"mohiyattam","Kalyanikutty");
15 Warnings:
16 Warning 1265 Data truncated for column 'name' at row 1
17 INSERT INTO t VALUES (5,"manipuri","Guru");
18 INSERT INTO t VALUES (6,"kathakali","Manavedan");
19 SET sql_mode= default;
20 CREATE PROCEDURE p1()
21 BEGIN
22 PREPARE st FROM 'SELECT * FROM t WHERE a<=?' ;
23 SET @a=3;
24 EXECUTE st using @a;
25 END|
26 CALL p1()|
27 a b name
28 1 kuchipudi Vempati
29 2 odissi Mohapatra
30 3 kathak Maharaj
31 SELECT STATEMENT_NAME, SQL_TEXT, COUNT_EXECUTE, COUNT_REPREPARE, OWNER_OBJECT_TYPE, OWNER_OBJECT_SCHEMA, OWNER_OBJECT_NAME FROM performance_schema.prepared_statements_instances|
32 STATEMENT_NAME SQL_TEXT COUNT_EXECUTE COUNT_REPREPARE OWNER_OBJECT_TYPE OWNER_OBJECT_SCHEMA OWNER_OBJECT_NAME
33 st SELECT * FROM t WHERE a<=? 1 0 PROCEDURE db p1
34 DEALLOCATE PREPARE st|
35 SELECT STATEMENT_NAME, SQL_TEXT, COUNT_EXECUTE, COUNT_REPREPARE, OWNER_OBJECT_TYPE, OWNER_OBJECT_SCHEMA, OWNER_OBJECT_NAME FROM performance_schema.prepared_statements_instances|
36 STATEMENT_NAME SQL_TEXT COUNT_EXECUTE COUNT_REPREPARE OWNER_OBJECT_TYPE OWNER_OBJECT_SCHEMA OWNER_OBJECT_NAME
37 CREATE PROCEDURE p2()
38 BEGIN
39 PREPARE st1 FROM 'INSERT INTO t SELECT * FROM t WHERE a<=?' ;
40 END|
41 CALL p2()|
42 SELECT STATEMENT_NAME, SQL_TEXT, COUNT_EXECUTE, COUNT_REPREPARE, OWNER_OBJECT_TYPE, OWNER_OBJECT_SCHEMA, OWNER_OBJECT_NAME FROM performance_schema.prepared_statements_instances|
43 STATEMENT_NAME SQL_TEXT COUNT_EXECUTE COUNT_REPREPARE OWNER_OBJECT_TYPE OWNER_OBJECT_SCHEMA OWNER_OBJECT_NAME
44 st1 INSERT INTO t SELECT * FROM t WHERE a<=? 0 0 PROCEDURE db p2
45 SET @a=4|
46 EXECUTE st1 using @a|
47 SELECT STATEMENT_NAME, SQL_TEXT, COUNT_EXECUTE, COUNT_REPREPARE, OWNER_OBJECT_TYPE, OWNER_OBJECT_SCHEMA, OWNER_OBJECT_NAME FROM performance_schema.prepared_statements_instances|
48 STATEMENT_NAME SQL_TEXT COUNT_EXECUTE COUNT_REPREPARE OWNER_OBJECT_TYPE OWNER_OBJECT_SCHEMA OWNER_OBJECT_NAME
49 st1 INSERT INTO t SELECT * FROM t WHERE a<=? 1 0 PROCEDURE db p2
50 CREATE PROCEDURE p3()
51 BEGIN
52 SET @a=2;
53 EXECUTE st1 using @a;
54 END|
55 CALL p3()|
56 SELECT STATEMENT_NAME, SQL_TEXT, COUNT_EXECUTE, COUNT_REPREPARE, OWNER_OBJECT_TYPE, OWNER_OBJECT_SCHEMA, OWNER_OBJECT_NAME FROM performance_schema.prepared_statements_instances|
57 STATEMENT_NAME SQL_TEXT COUNT_EXECUTE COUNT_REPREPARE OWNER_OBJECT_TYPE OWNER_OBJECT_SCHEMA OWNER_OBJECT_NAME
58 st1 INSERT INTO t SELECT * FROM t WHERE a<=? 2 0 PROCEDURE db p2
59 DEALLOCATE PREPARE st1|
60 SELECT STATEMENT_NAME, SQL_TEXT, COUNT_EXECUTE, COUNT_REPREPARE, OWNER_OBJECT_TYPE, OWNER_OBJECT_SCHEMA, OWNER_OBJECT_NAME FROM performance_schema.prepared_statements_instances|
61 STATEMENT_NAME SQL_TEXT COUNT_EXECUTE COUNT_REPREPARE OWNER_OBJECT_TYPE OWNER_OBJECT_SCHEMA OWNER_OBJECT_NAME
62 PREPARE st2 FROM 'UPDATE t SET a=a+1 WHERE b=?'|
63 SELECT STATEMENT_NAME, SQL_TEXT, COUNT_EXECUTE, COUNT_REPREPARE, OWNER_OBJECT_TYPE, OWNER_OBJECT_SCHEMA, OWNER_OBJECT_NAME FROM performance_schema.prepared_statements_instances|
64 STATEMENT_NAME SQL_TEXT COUNT_EXECUTE COUNT_REPREPARE OWNER_OBJECT_TYPE OWNER_OBJECT_SCHEMA OWNER_OBJECT_NAME
65 st2 UPDATE t SET a=a+1 WHERE b=? 0 0 NULL NULL NULL
66 CREATE PROCEDURE p4()
67 BEGIN
68 SET @b='kuchipudi';
69 EXECUTE st2 USING @b;
70 END|
71 CALL p4()|
72 SELECT STATEMENT_NAME, SQL_TEXT, COUNT_EXECUTE, COUNT_REPREPARE, OWNER_OBJECT_TYPE, OWNER_OBJECT_SCHEMA, OWNER_OBJECT_NAME FROM performance_schema.prepared_statements_instances|
73 STATEMENT_NAME SQL_TEXT COUNT_EXECUTE COUNT_REPREPARE OWNER_OBJECT_TYPE OWNER_OBJECT_SCHEMA OWNER_OBJECT_NAME
74 st2 UPDATE t SET a=a+1 WHERE b=? 1 0 NULL NULL NULL
75 ALTER TABLE t DROP COLUMN name;
76 # COUNT_REPREPARE must be 1
77 CALL p4()|
78 SELECT STATEMENT_NAME, SQL_TEXT, COUNT_EXECUTE, COUNT_REPREPARE, OWNER_OBJECT_TYPE, OWNER_OBJECT_SCHEMA, OWNER_OBJECT_NAME FROM performance_schema.prepared_statements_instances|
79 STATEMENT_NAME SQL_TEXT COUNT_EXECUTE COUNT_REPREPARE OWNER_OBJECT_TYPE OWNER_OBJECT_SCHEMA OWNER_OBJECT_NAME
80 st2 UPDATE t SET a=a+1 WHERE b=? 2 1 NULL NULL NULL
81 DEALLOCATE PREPARE st2|
82 SELECT STATEMENT_NAME, SQL_TEXT, COUNT_EXECUTE, COUNT_REPREPARE, OWNER_OBJECT_TYPE, OWNER_OBJECT_SCHEMA, OWNER_OBJECT_NAME FROM performance_schema.prepared_statements_instances|
83 STATEMENT_NAME SQL_TEXT COUNT_EXECUTE COUNT_REPREPARE OWNER_OBJECT_TYPE OWNER_OBJECT_SCHEMA OWNER_OBJECT_NAME
84 CREATE PROCEDURE p5()
85 BEGIN
86 SET @a=1;
87 SELECT @a;
88 END|
89 PREPARE st3 FROM 'CALL p5()'|
90 SELECT STATEMENT_NAME, SQL_TEXT, COUNT_EXECUTE, COUNT_REPREPARE, OWNER_OBJECT_TYPE, OWNER_OBJECT_SCHEMA, OWNER_OBJECT_NAME FROM performance_schema.prepared_statements_instances|
91 STATEMENT_NAME SQL_TEXT COUNT_EXECUTE COUNT_REPREPARE OWNER_OBJECT_TYPE OWNER_OBJECT_SCHEMA OWNER_OBJECT_NAME
92 st3 CALL p5() 0 0 NULL NULL NULL
93 EXECUTE st3|
94 @a
95 1
96 SELECT STATEMENT_NAME, SQL_TEXT, COUNT_EXECUTE, COUNT_REPREPARE, OWNER_OBJECT_TYPE, OWNER_OBJECT_SCHEMA, OWNER_OBJECT_NAME FROM performance_schema.prepared_statements_instances|
97 STATEMENT_NAME SQL_TEXT COUNT_EXECUTE COUNT_REPREPARE OWNER_OBJECT_TYPE OWNER_OBJECT_SCHEMA OWNER_OBJECT_NAME
98 st3 CALL p5() 1 0 NULL NULL NULL
99 DEALLOCATE PREPARE st3|
100 SELECT STATEMENT_NAME, SQL_TEXT, COUNT_EXECUTE, COUNT_REPREPARE, OWNER_OBJECT_TYPE, OWNER_OBJECT_SCHEMA, OWNER_OBJECT_NAME FROM performance_schema.prepared_statements_instances|
101 STATEMENT_NAME SQL_TEXT COUNT_EXECUTE COUNT_REPREPARE OWNER_OBJECT_TYPE OWNER_OBJECT_SCHEMA OWNER_OBJECT_NAME
102 CREATE TABLE tab(a INT);
103 SELECT COUNT(*) = 1 FROM information_schema.processlist
104 WHERE user = 'event_scheduler' AND command = 'Daemon';
105 COUNT(*) = 1
106 1
107 Warnings:
108 Warning 1287 'INFORMATION_SCHEMA.PROCESSLIST' is deprecated and will be removed in a future release. Please use performance_schema.processlist instead
109 CREATE EVENT e1 ON SCHEDULE EVERY 10 HOUR DO
110 BEGIN
111 PREPARE st FROM 'INSERT INTO tab VALUES(?)';
112 SET @a=1;
113 EXECUTE st USING @a;
114 END|
115 SELECT * FROM tab LIMIT 1;
116 a
117 1
118 SELECT STATEMENT_NAME, SQL_TEXT, COUNT_EXECUTE, COUNT_REPREPARE, OWNER_OBJECT_TYPE, OWNER_OBJECT_SCHEMA, OWNER_OBJECT_NAME FROM performance_schema.prepared_statements_instances;
119 STATEMENT_NAME SQL_TEXT COUNT_EXECUTE COUNT_REPREPARE OWNER_OBJECT_TYPE OWNER_OBJECT_SCHEMA OWNER_OBJECT_NAME
120 TRUNCATE TABLE performance_schema.prepared_statements_instances;
121 TRUNCATE TABLE performance_schema.events_statements_history_long;
122 DROP TABLE t;
123 DROP TABLE tab;
124 DROP PROCEDURE p1;
125 DROP PROCEDURE p2;
126 DROP PROCEDURE p3;
127 DROP PROCEDURE p4;
128 DROP PROCEDURE p5;
129 DROP EVENT IF EXISTS e1;
130 DROP DATABASE db;