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
|
####################################
# SETUP
####################################
CREATE DATABASE statements_digest;
USE statements_digest;
CREATE TABLE t1(a int);
CREATE TABLE t2(a int);
CREATE TABLE t3(a int, b int);
CREATE TABLE t4(a int, b int);
CREATE TABLE t5(a int, b int, c int);
CREATE TABLE t6(a int, b int, c int, d int);
CREATE TABLE t11 (c CHAR(4));
CREATE TABLE t12 (c CHAR(4));
SELECT * FROM performance_schema.setup_consumers;
NAME ENABLED
events_stages_current YES
events_stages_history YES
events_stages_history_long YES
events_statements_current YES
events_statements_history YES
events_statements_history_long YES
events_transactions_current YES
events_transactions_history YES
events_transactions_history_long YES
events_waits_current YES
events_waits_history YES
events_waits_history_long YES
global_instrumentation YES
thread_instrumentation YES
statements_digest NO
TRUNCATE TABLE performance_schema.events_statements_summary_by_digest;
####################################
# EXECUTION
####################################
SELECT 1 FROM t1;
1
SELECT 1 FROM `t1`;
1
SELECT 1,2 FROM t1;
1 2
SELECT 1, 2, 3, 4 FROM t1;
1 2 3 4
SELECT 1 FROM t2;
1
SELECT 1,2 FROM t2;
1 2
SELECT 1, 2, 3, 4 FROM t2;
1 2 3 4
INSERT INTO t1 VALUES (1);
INSERT INTO t2 VALUES (1);
INSERT INTO t3 VALUES (1, 2);
INSERT INTO t4 VALUES (1, 2);
INSERT INTO t5 VALUES (1, 2, 3);
INSERT INTO t1 VALUES (1), (2), (3);
INSERT INTO t1 VALUES (1), (2), (3), (4);
INSERT INTO t3 VALUES (1, 2), (3, 4), (5, 6);
INSERT INTO t5 VALUES (1, 2, 3), (4, 5, 6), (7, 8, 9);
INSERT INTO t1 VALUES (NULL);
INSERT INTO t3 VALUES (NULL,NULL);
INSERT INTO t3 VALUES (1,NULL);
INSERT INTO t3 VALUES (NULL,1);
INSERT INTO t6 VALUES (NULL, NULL, NULL, NULL);
INSERT INTO t6 VALUES (1, NULL, NULL, NULL);
INSERT INTO t6 VALUES (NULL, 2, NULL, NULL);
INSERT INTO t6 VALUES (1, 2, 3, NULL);
INSERT INTO t6 VALUES (1, 2, NULL, 4);
SELECT 1 + 1;
1 + 1
2
SELECT 1;
1
1
SELECT 1 /* This is an inline comment */ + 1;
1 /* This is an inline comment */ + 1
2
SELECT 1+
/*
this is a
multiple-line comment
*/
1;
1+
/*
this is a
multiple-line comment
*/
1
2
CREATE SCHEMA statements_digest_temp;
DROP SCHEMA statements_digest_temp;
CREATE DATABASE statements_digest_temp;
DROP DATABASE statements_digest_temp;
SELECT 1 FROM no_such_table;
ERROR 42S02: Table 'statements_digest.no_such_table' doesn't exist
CREATE TABLE dup_table (c char(4));
CREATE TABLE dup_table (c char(4));
ERROR 42S01: Table 'dup_table' already exists
DROP TABLE dup_table;
INSERT IGNORE INTO t11 VALUES("MySQL");
Warnings:
Warning 1265 Data truncated for column 'c' at row 1
PREPARE stmt FROM "SELECT * FROM t12";
EXECUTE stmt;
c
EXECUTE stmt;
c
DEALLOCATE PREPARE stmt;
CREATE PROCEDURE p1() BEGIN SELECT * FROM t12; END//
CALL p1();
c
CALL p1();
c
DROP PROCEDURE p1;
CREATE FUNCTION `func`(a INT, b INT) RETURNS int(11) RETURN a+b //
select func(3,4);
func(3,4)
7
select func(13,42);
func(13,42)
55
DROP FUNCTION func;
CREATE TRIGGER trg BEFORE INSERT ON t12 FOR EACH ROW SET @a:=1;
INSERT INTO t12 VALUES ("abc");
INSERT INTO t12 VALUES ("def");
DROP TRIGGER trg;
####################################
# QUERYING PS STATEMENT DIGEST
####################################
SELECT schema_name, digest, digest_text, count_star FROM performance_schema.events_statements_summary_by_digest;
schema_name digest digest_text count_star
SELECT digest, digest_text FROM performance_schema.events_statements_current;
digest digest_text
NULL NULL
####################################
# CLEANUP
####################################
DROP TABLE IF EXISTS t1;
DROP TABLE IF EXISTS t2;
DROP TABLE IF EXISTS t3;
DROP TABLE IF EXISTS t4;
DROP TABLE IF EXISTS t5;
DROP TABLE IF EXISTS t6;
DROP TABLE IF EXISTS t11;
DROP TABLE IF EXISTS t12;
DROP DATABASE IF EXISTS statements_digest;
|
