Debian Codegraph @

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

raw
1 # Test to check instrumentation of prepared statements created by
2 # stored programs. SQL prepared statements (PREPARE, EXECUTE, DEALLOCATE PREPARE)
3 # can be used in stored procedures, but not stored functions or triggers.
4
5 --source include/not_embedded.inc
6 --source include/have_perfschema.inc
7 --source include/no_protocol.inc
8
9 --let $psi_select = SELECT STATEMENT_NAME, SQL_TEXT, COUNT_EXECUTE, COUNT_REPREPARE, OWNER_OBJECT_TYPE, OWNER_OBJECT_SCHEMA, OWNER_OBJECT_NAME FROM performance_schema.prepared_statements_instances
10 TRUNCATE TABLE performance_schema.prepared_statements_instances;
11
12 CREATE DATABASE db;
13 USE db;
14
15 CREATE TABLE t
16 (
17 a INT,
18 b CHAR(10),
19 name CHAR(10)
20 );
21 SET sql_mode = 'NO_ENGINE_SUBSTITUTION';
22 INSERT INTO t VALUES (1,"kuchipudi","Vempati");
23 INSERT INTO t VALUES (2,"odissi","Mohapatra");
24 INSERT INTO t VALUES (3,"kathak","Maharaj");
25 INSERT INTO t VALUES (4,"mohiyattam","Kalyanikutty");
26 INSERT INTO t VALUES (5,"manipuri","Guru");
27 INSERT INTO t VALUES (6,"kathakali","Manavedan");
28 SET sql_mode= default;
29 # Stored Procedure
30 DELIMITER |;
31 CREATE PROCEDURE p1()
32 BEGIN
33 PREPARE st FROM 'SELECT * FROM t WHERE a<=?' ;
34 SET @a=3;
35 EXECUTE st using @a;
36 END|
37
38 CALL p1()|
39 --eval $psi_select
40
41 DEALLOCATE PREPARE st|
42 --eval $psi_select
43
44 CREATE PROCEDURE p2()
45 BEGIN
46 PREPARE st1 FROM 'INSERT INTO t SELECT * FROM t WHERE a<=?' ;
47 END|
48
49 CALL p2()|
50 --eval $psi_select
51
52 SET @a=4|
53 EXECUTE st1 using @a|
54 --eval $psi_select
55
56 CREATE PROCEDURE p3()
57 BEGIN
58 SET @a=2;
59 EXECUTE st1 using @a;
60 END|
61
62 CALL p3()|
63 --eval $psi_select
64
65 DEALLOCATE PREPARE st1|
66 --eval $psi_select
67
68 PREPARE st2 FROM 'UPDATE t SET a=a+1 WHERE b=?'|
69 --eval $psi_select
70
71 CREATE PROCEDURE p4()
72 BEGIN
73 SET @b='kuchipudi';
74 EXECUTE st2 USING @b;
75 END|
76
77 CALL p4()|
78 --eval $psi_select
79
80 ALTER TABLE t DROP COLUMN name;
81
82 # COUNT_REPREPARE must be 1
83 CALL p4()|
84 --eval $psi_select
85
86 DEALLOCATE PREPARE st2|
87 --eval $psi_select
88
89 CREATE PROCEDURE p5()
90 BEGIN
91 SET @a=1;
92 SELECT @a;
93 END|
94
95 PREPARE st3 FROM 'CALL p5()'|
96 --eval $psi_select
97 EXECUTE st3|
98 --eval $psi_select
99 DEALLOCATE PREPARE st3|
100 --eval $psi_select
101
102 DELIMITER ;|
103
104 # Events
105
106 CREATE TABLE tab(a INT);
107
108 SET GLOBAL event_scheduler=ON;
109
110 DELIMITER |;
111 CREATE EVENT e1 ON SCHEDULE EVERY 10 HOUR DO
112 BEGIN
113 PREPARE st FROM 'INSERT INTO tab VALUES(?)';
114 SET @a=1;
115 EXECUTE st USING @a;
116 END|
117
118 DELIMITER ;|
119
120 # Let e1 insert 1 records into the table tab
121
122 --let $wait_condition= select count(*) >= 1 from tab
123 --source include/wait_condition.inc
124 SELECT * FROM tab LIMIT 1;
125
126 # Wait till the above one execution of event is instrumented.
127
128 --let $wait_condition= select count_star >= 1 from performance_schema.events_statements_summary_by_program where object_type='EVENT'
129 --source include/wait_condition.inc
130
131 SET GLOBAL event_scheduler=OFF;
132 --source include/no_running_event_scheduler.inc
133
134 # The following should return empty set as the instrumented prepared statement
135 # row is removed as de allocation of the statement happens automatically as
136 # event thread is cleaned up.
137 --eval $psi_select
138
139 # clean-up
140 TRUNCATE TABLE performance_schema.prepared_statements_instances;
141 TRUNCATE TABLE performance_schema.events_statements_history_long;
142
143 DROP TABLE t;
144 DROP TABLE tab;
145 DROP PROCEDURE p1;
146 DROP PROCEDURE p2;
147 DROP PROCEDURE p3;
148 DROP PROCEDURE p4;
149 DROP PROCEDURE p5;
150 DROP EVENT IF EXISTS e1;
151 DROP DATABASE db;