Debian Codegraph @

mysql-test/suite/perfschema/t/prepared_statements.test

raw
1 # Performance schema tests generally depend on specific query plans
2 # and behavior, and much of the perfschema suite is therefore disabled
3 # if the hypergraph optimizer is active.
4 --source include/not_hypergraph.inc
5
6 # Test for prepared statement instrumentation
7
8 # Test requires: sp-protocol/ps-protocol/view-protocol/cursor-protocol disabled
9 --source include/no_protocol.inc
10
11 CREATE DATABASE db;
12 USE db;
13
14 --let $psi_select = SELECT STATEMENT_NAME, SQL_TEXT, COUNT_REPREPARE, COUNT_EXECUTE, SUM_ROWS_SENT, SUM_SELECT_SCAN, SUM_NO_INDEX_USED FROM performance_schema.prepared_statements_instances
15 --let $psi_truncate = TRUNCATE TABLE performance_schema.prepared_statements_instances
16 --let $eshl_select = SELECT EVENT_NAME, SQL_TEXT, OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME FROM performance_schema.events_statements_history_long WHERE CURRENT_SCHEMA='db'
17 --let $eshl_truncate = TRUNCATE TABLE performance_schema.events_statements_history_long
18
19 --eval $psi_truncate
20 --eval $eshl_truncate
21
22 --source suite/perfschema/include/prepared_stmts_setup.inc
23
24 --vertical_results
25 --eval $psi_select
26 --eval $eshl_select
27 --horizontal_results
28
29 --source suite/perfschema/include/prepared_stmts_execution.inc
30
31 --vertical_results
32 --eval $psi_select
33 --eval $eshl_select
34 --horizontal_results
35
36 # Truncate to reset the statistics
37 --eval $psi_truncate
38
39 # check whether the statistics are reset
40 --vertical_results
41 --eval $psi_select
42 --horizontal_results
43
44 --source suite/perfschema/include/prepared_stmts_deallocation.inc
45
46 --vertical_results
47 # select query on prepared_statements_instances table must return empty set
48 --eval $psi_select
49 --eval $eshl_select
50 --horizontal_results
51
52 # truncate
53 --eval $eshl_truncate
54
55 #
56 # Test to check the instrumentation of prepared statements
57 # when all consumers in setup_consumers are disabled.
58 #
59
60 # Disable all consumers
61 UPDATE performance_schema.setup_consumers SET ENABLED = 'NO';
62
63 --source suite/perfschema/include/prepared_stmts_setup.inc
64
65 --vertical_results
66 --eval $psi_select
67 --eval $eshl_select
68 --horizontal_results
69
70 --source suite/perfschema/include/prepared_stmts_execution.inc
71
72 --vertical_results
73 --eval $psi_select
74 --eval $eshl_select
75 --horizontal_results
76
77 --source suite/perfschema/include/prepared_stmts_deallocation.inc
78
79 --vertical_results
80 --eval $psi_select
81 --eval $eshl_select
82 --horizontal_results
83
84 # truncate
85 --eval $eshl_truncate
86
87 # restore the initial set-up of consumers table
88 UPDATE performance_schema.setup_consumers SET ENABLED = 'YES';
89
90 #
91 # Aggregation
92 #
93
94 PREPARE st FROM 'SELECT SUM(1000 + ?) AS total';
95
96 --vertical_results
97 --eval $psi_select
98 --horizontal_results
99
100 SET @d=100;
101 EXECUTE st USING @d;
102
103 --vertical_results
104 --eval $psi_select
105 --horizontal_results
106
107 let $i=5;
108 while($i)
109 {
110 SET @d = @d + 100;
111 EXECUTE st USING @d;
112 --vertical_results
113 --eval $psi_select
114 --horizontal_results
115 dec $i;
116 }
117
118 # truncate
119 --eval $psi_truncate
120 --vertical_results
121 --eval $psi_select
122 --horizontal_results
123
124 #
125 # check whether the instrumented prepared statement is removed from the
126 # prepared_statement_instances table after de allocation of the prepared
127 # statement even if instrumentation is disabled
128 #
129
130 # Disable prepared statement instrumentation
131 UPDATE performance_schema.setup_instruments SET ENABLED = 'NO'
132 WHERE NAME like "statement/sql/execute%";
133
134 SET @d=3274;
135 # COUNT_EXECUTE should remain 0. No increment must be seen.
136 EXECUTE st USING @d;
137 --vertical_results
138 --eval $psi_select
139 --horizontal_results
140
141 UPDATE performance_schema.setup_instruments SET ENABLED = 'NO'
142 WHERE NAME like "statement/sql/prepare%";
143
144 DEALLOCATE PREPARE st;
145 # should return empty set.
146 --vertical_results
147 --eval $psi_select
148 --horizontal_results
149
150 # Restore back teh initial set-up of setup_instruments table
151
152 UPDATE performance_schema.setup_instruments SET ENABLED = 'YES'
153 WHERE NAME like "statement/sql/prepare%";
154 UPDATE performance_schema.setup_instruments SET ENABLED = 'YES'
155 WHERE NAME like "statement/sql/execute%";
156
157
158 # Reprepare
159
160 CREATE TABLE tab(
161 Id INT,
162 name CHAR(10),
163 age INT
164 );
165
166 INSERT INTO tab VALUES(1,"Nakshatr",25),(2,"chanda",24),(3,"tejas",78);
167
168 PREPARE st FROM 'SELECT * FROM tab';
169
170 --vertical_results
171 --eval $psi_select
172 --horizontal_results
173
174 EXECUTE st;
175
176 --vertical_results
177 --eval $psi_select
178 --horizontal_results
179
180 ALTER TABLE tab DROP COLUMN age;
181
182 EXECUTE st;
183
184 --vertical_results
185 --eval $psi_select
186 --horizontal_results
187
188 ALTER TABLE tab ADD COLUMN age INT NULL;
189
190 EXECUTE st;
191
192 --vertical_results
193 --eval $psi_select
194 --horizontal_results
195
196 # check if the statistics are reset
197 --eval $psi_truncate
198 --vertical_results
199 --eval $psi_select
200 --horizontal_results
201
202 DEALLOCATE PREPARE st;
203
204 --vertical_results
205 --eval $psi_select
206 --horizontal_results
207
208 # clean up
209 # truncate
210 --eval $psi_truncate
211 --eval $eshl_truncate
212
213 DROP TABLE tab;
214 DROP DATABASE db;