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
|
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);
SELECT COUNT(*) = 1 FROM information_schema.processlist
WHERE user = 'event_scheduler' AND command = 'Daemon';
COUNT(*) = 1
1
Warnings:
Warning 1287 'INFORMATION_SCHEMA.PROCESSLIST' is deprecated and will be removed in a future release. Please use performance_schema.processlist instead
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
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;
|
