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