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
|
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");
Warnings:
Warning 1265 Data truncated for column 'name' at row 1
INSERT INTO t VALUES (5,"manipuri","Guru");
INSERT INTO t VALUES (6,"kathakali","Manavedan");
SET sql_mode= default;
CREATE PROCEDURE p1()
BEGIN
PREPARE st FROM 'SELECT * FROM t WHERE a<=?' ;
SET @a=3;
EXECUTE st using @a;
END|
CALL p1()|
a b name
1 kuchipudi Vempati
2 odissi Mohapatra
3 kathak Maharaj
SELECT STATEMENT_NAME, SQL_TEXT, COUNT_EXECUTE, COUNT_REPREPARE, OWNER_OBJECT_TYPE, OWNER_OBJECT_SCHEMA, OWNER_OBJECT_NAME FROM performance_schema.prepared_statements_instances|
STATEMENT_NAME SQL_TEXT COUNT_EXECUTE COUNT_REPREPARE OWNER_OBJECT_TYPE OWNER_OBJECT_SCHEMA OWNER_OBJECT_NAME
st SELECT * FROM t WHERE a<=? 1 0 PROCEDURE db p1
DEALLOCATE PREPARE st|
SELECT STATEMENT_NAME, SQL_TEXT, COUNT_EXECUTE, COUNT_REPREPARE, OWNER_OBJECT_TYPE, OWNER_OBJECT_SCHEMA, OWNER_OBJECT_NAME FROM performance_schema.prepared_statements_instances|
STATEMENT_NAME SQL_TEXT COUNT_EXECUTE COUNT_REPREPARE OWNER_OBJECT_TYPE OWNER_OBJECT_SCHEMA OWNER_OBJECT_NAME
CREATE PROCEDURE p2()
BEGIN
PREPARE st1 FROM 'INSERT INTO t SELECT * FROM t WHERE a<=?' ;
END|
CALL p2()|
SELECT STATEMENT_NAME, SQL_TEXT, COUNT_EXECUTE, COUNT_REPREPARE, OWNER_OBJECT_TYPE, OWNER_OBJECT_SCHEMA, OWNER_OBJECT_NAME FROM performance_schema.prepared_statements_instances|
STATEMENT_NAME SQL_TEXT COUNT_EXECUTE COUNT_REPREPARE OWNER_OBJECT_TYPE OWNER_OBJECT_SCHEMA OWNER_OBJECT_NAME
st1 INSERT INTO t SELECT * FROM t WHERE a<=? 0 0 PROCEDURE db p2
SET @a=4|
EXECUTE st1 using @a|
SELECT STATEMENT_NAME, SQL_TEXT, COUNT_EXECUTE, COUNT_REPREPARE, OWNER_OBJECT_TYPE, OWNER_OBJECT_SCHEMA, OWNER_OBJECT_NAME FROM performance_schema.prepared_statements_instances|
STATEMENT_NAME SQL_TEXT COUNT_EXECUTE COUNT_REPREPARE OWNER_OBJECT_TYPE OWNER_OBJECT_SCHEMA OWNER_OBJECT_NAME
st1 INSERT INTO t SELECT * FROM t WHERE a<=? 1 0 PROCEDURE db p2
CREATE PROCEDURE p3()
BEGIN
SET @a=2;
EXECUTE st1 using @a;
END|
CALL p3()|
SELECT STATEMENT_NAME, SQL_TEXT, COUNT_EXECUTE, COUNT_REPREPARE, OWNER_OBJECT_TYPE, OWNER_OBJECT_SCHEMA, OWNER_OBJECT_NAME FROM performance_schema.prepared_statements_instances|
STATEMENT_NAME SQL_TEXT COUNT_EXECUTE COUNT_REPREPARE OWNER_OBJECT_TYPE OWNER_OBJECT_SCHEMA OWNER_OBJECT_NAME
st1 INSERT INTO t SELECT * FROM t WHERE a<=? 2 0 PROCEDURE db p2
DEALLOCATE PREPARE st1|
SELECT STATEMENT_NAME, SQL_TEXT, COUNT_EXECUTE, COUNT_REPREPARE, OWNER_OBJECT_TYPE, OWNER_OBJECT_SCHEMA, OWNER_OBJECT_NAME FROM performance_schema.prepared_statements_instances|
STATEMENT_NAME SQL_TEXT COUNT_EXECUTE COUNT_REPREPARE OWNER_OBJECT_TYPE OWNER_OBJECT_SCHEMA OWNER_OBJECT_NAME
PREPARE st2 FROM 'UPDATE t SET a=a+1 WHERE b=?'|
SELECT STATEMENT_NAME, SQL_TEXT, COUNT_EXECUTE, COUNT_REPREPARE, OWNER_OBJECT_TYPE, OWNER_OBJECT_SCHEMA, OWNER_OBJECT_NAME FROM performance_schema.prepared_statements_instances|
STATEMENT_NAME SQL_TEXT COUNT_EXECUTE COUNT_REPREPARE OWNER_OBJECT_TYPE OWNER_OBJECT_SCHEMA OWNER_OBJECT_NAME
st2 UPDATE t SET a=a+1 WHERE b=? 0 0 NULL NULL NULL
CREATE PROCEDURE p4()
BEGIN
SET @b='kuchipudi';
EXECUTE st2 USING @b;
END|
CALL p4()|
SELECT STATEMENT_NAME, SQL_TEXT, COUNT_EXECUTE, COUNT_REPREPARE, OWNER_OBJECT_TYPE, OWNER_OBJECT_SCHEMA, OWNER_OBJECT_NAME FROM performance_schema.prepared_statements_instances|
STATEMENT_NAME SQL_TEXT COUNT_EXECUTE COUNT_REPREPARE OWNER_OBJECT_TYPE OWNER_OBJECT_SCHEMA OWNER_OBJECT_NAME
st2 UPDATE t SET a=a+1 WHERE b=? 1 0 NULL NULL NULL
ALTER TABLE t DROP COLUMN name;
# COUNT_REPREPARE must be 1
CALL p4()|
SELECT STATEMENT_NAME, SQL_TEXT, COUNT_EXECUTE, COUNT_REPREPARE, OWNER_OBJECT_TYPE, OWNER_OBJECT_SCHEMA, OWNER_OBJECT_NAME FROM performance_schema.prepared_statements_instances|
STATEMENT_NAME SQL_TEXT COUNT_EXECUTE COUNT_REPREPARE OWNER_OBJECT_TYPE OWNER_OBJECT_SCHEMA OWNER_OBJECT_NAME
st2 UPDATE t SET a=a+1 WHERE b=? 2 1 NULL NULL NULL
DEALLOCATE PREPARE st2|
SELECT STATEMENT_NAME, SQL_TEXT, COUNT_EXECUTE, COUNT_REPREPARE, OWNER_OBJECT_TYPE, OWNER_OBJECT_SCHEMA, OWNER_OBJECT_NAME FROM performance_schema.prepared_statements_instances|
STATEMENT_NAME SQL_TEXT COUNT_EXECUTE COUNT_REPREPARE OWNER_OBJECT_TYPE OWNER_OBJECT_SCHEMA OWNER_OBJECT_NAME
CREATE PROCEDURE p5()
BEGIN
SET @a=1;
SELECT @a;
END|
PREPARE st3 FROM 'CALL p5()'|
SELECT STATEMENT_NAME, SQL_TEXT, COUNT_EXECUTE, COUNT_REPREPARE, OWNER_OBJECT_TYPE, OWNER_OBJECT_SCHEMA, OWNER_OBJECT_NAME FROM performance_schema.prepared_statements_instances|
STATEMENT_NAME SQL_TEXT COUNT_EXECUTE COUNT_REPREPARE OWNER_OBJECT_TYPE OWNER_OBJECT_SCHEMA OWNER_OBJECT_NAME
st3 CALL p5() 0 0 NULL NULL NULL
EXECUTE st3|
@a
1
SELECT STATEMENT_NAME, SQL_TEXT, COUNT_EXECUTE, COUNT_REPREPARE, OWNER_OBJECT_TYPE, OWNER_OBJECT_SCHEMA, OWNER_OBJECT_NAME FROM performance_schema.prepared_statements_instances|
STATEMENT_NAME SQL_TEXT COUNT_EXECUTE COUNT_REPREPARE OWNER_OBJECT_TYPE OWNER_OBJECT_SCHEMA OWNER_OBJECT_NAME
st3 CALL p5() 1 0 NULL NULL NULL
DEALLOCATE PREPARE st3|
SELECT STATEMENT_NAME, SQL_TEXT, COUNT_EXECUTE, COUNT_REPREPARE, OWNER_OBJECT_TYPE, OWNER_OBJECT_SCHEMA, OWNER_OBJECT_NAME FROM performance_schema.prepared_statements_instances|
STATEMENT_NAME SQL_TEXT COUNT_EXECUTE COUNT_REPREPARE OWNER_OBJECT_TYPE OWNER_OBJECT_SCHEMA OWNER_OBJECT_NAME
CREATE TABLE tab(a INT);
SET GLOBAL event_scheduler=ON;
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|
SELECT * FROM tab LIMIT 1;
a
1
SET GLOBAL event_scheduler=OFF;
SELECT STATEMENT_NAME, SQL_TEXT, COUNT_EXECUTE, COUNT_REPREPARE, OWNER_OBJECT_TYPE, OWNER_OBJECT_SCHEMA, OWNER_OBJECT_NAME FROM performance_schema.prepared_statements_instances;
STATEMENT_NAME SQL_TEXT COUNT_EXECUTE COUNT_REPREPARE OWNER_OBJECT_TYPE OWNER_OBJECT_SCHEMA OWNER_OBJECT_NAME
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;
|
