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