Debian Codegraph @

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

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