Debian Codegraph @

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

raw
1 ################# suite/perfschema/t/show_aggregate.test #######################
2 # #
3 # This test verifies: #
4 # 1. Status variable values are consistent with corresponding server events. #
5 # 2. Status variable values are consistent across session, thread, account, #
6 # host, user and global tables. #
7 # 3. Status totals for user, host and account are retained after related #
8 # threads disconnect. #
9 ################################################################################
10 # Test requires: sp-protocol/ps-protocol/view-protocol/cursor-protocol disabled
11 --source include/no_protocol.inc
12
13 --enable_connect_log
14
15 --echo
16 --echo ================================================================================
17 --echo SETUP
18 --echo ================================================================================
19 SET @@session.sql_log_bin=OFF;
20 --echo
21 --echo # CREATE 3 CLIENTS, 3 CONNECTIONS, RESULTS TABLE
22 connection default;
23 USE test;
24
25 # Clear user, host and account status accumulated from preliminary checks by mtr.
26 --disable_query_log
27 flush status;
28 --enable_query_log
29
30 --echo
31 --echo # Create results table
32 CREATE TABLE test.status_results
33 (variable_name VARCHAR(64), start INT DEFAULT 0, stop INT DEFAULT 0, delta INT DEFAULT 0,
34 t1 INT DEFAULT 0, t2 INT DEFAULT 0, t3 INT DEFAULT 0, thread INT DEFAULT 0,
35 u1 INT DEFAULT 0, u2 INT DEFAULT 0, u3 INT DEFAULT 0, user INT DEFAULT 0,
36 h1 INT DEFAULT 0, h2 INT DEFAULT 0, h3 INT DEFAULT 0, host INT DEFAULT 0,
37 a1 INT DEFAULT 0, a2 INT DEFAULT 0, a3 INT DEFAULT 0, acct INT DEFAULT 0);
38
39 INSERT INTO test.status_results (variable_name, start)
40 SELECT sg.variable_name, sg.variable_value+0 FROM performance_schema.global_status sg
41 WHERE variable_name IN ('handler_delete', 'handler_rollback');
42
43 --echo
44 --echo # Create test tables
45 CREATE TABLE t1 (s1 int) ENGINE=InnoDB;
46 INSERT INTO t1 VALUES (1), (2), (2), (3), (3), (3);
47 CREATE TABLE t2 (s1 int) ENGINE=InnoDB;
48 CREATE TABLE t3 (s1 int) ENGINE=InnoDB;
49 --echo
50 CREATE USER 'user1'@localhost, 'user2'@localhost, 'user3'@localhost;
51 GRANT ALL ON *.* to 'user1'@localhost;
52 GRANT ALL ON *.* to 'user2'@localhost;
53 GRANT ALL ON *.* to 'user3'@localhost;
54
55 --echo
56 --echo ================================================================================
57 --echo CONNECTION 1: DELETE 1, ROLLBACK x 1
58 --echo ================================================================================
59 connect(con1, localhost, user1,,);
60 SET @@session.sql_log_bin=OFF;
61 DELETE FROM t1 WHERE s1 = 1;
62 START TRANSACTION;
63 INSERT INTO t1 VALUES (1);
64 ROLLBACK;
65
66 --echo
67 --echo ================================================================================
68 --echo CONNECTION 2: DELETE 2, ROLLBACK x 2
69 --echo ================================================================================
70 connect(con2, localhost, user2,,);
71 SET @@session.sql_log_bin=OFF;
72 DELETE FROM t1 WHERE s1 = 2;
73 --echo
74 START TRANSACTION;
75 INSERT INTO t1 VALUES (2);
76 ROLLBACK;
77 --echo
78 START TRANSACTION;
79 INSERT INTO t1 VALUES (2);
80 ROLLBACK;
81
82 --echo
83 --echo ================================================================================
84 --echo CONNECTION 3: DELETE 3, ROLLBACK x 3
85 --echo ================================================================================
86 connect(con3, localhost, user3,,);
87 SET @@session.sql_log_bin=OFF;
88 DELETE FROM t1 WHERE s1 = 3;
89 --echo
90 START TRANSACTION;
91 INSERT INTO t1 VALUES (3);
92 ROLLBACK;
93 --echo
94 START TRANSACTION;
95 INSERT INTO t1 VALUES (3);
96 ROLLBACK;
97 --echo
98 START TRANSACTION;
99 INSERT INTO t1 VALUES (3);
100 ROLLBACK;
101
102 --echo
103 --echo ================================================================================
104 --echo CONNECTION DEFAULT: Gather results, compare session and global status counts
105 --echo ================================================================================
106 connection default;
107 --echo # Get thread ids for each connection.
108 USE performance_schema;
109 SELECT thread_id INTO @con1_id FROM threads WHERE processlist_user IN ('user1');
110 SELECT thread_id INTO @con2_id FROM threads WHERE processlist_user IN ('user2');
111 SELECT thread_id INTO @con3_id FROM threads WHERE processlist_user IN ('user3');
112
113 --source ../include/show_aggregate.inc
114
115 #--echo DEBUG
116 #USE test;
117 #SELECT * FROM test.status_results;
118
119 --echo
120 --echo ================================================================================
121 --echo TEST 1: STATUS_BY_THREAD: Verify expected status counts per thread (1,2,3)
122 --echo ================================================================================
123 --echo #
124 --echo # Review per-thread status counts
125 --echo #
126 USE performance_schema;
127 --replace_column 1 thread_id
128 SELECT * FROM status_by_thread WHERE thread_id IN (@con1_id, @con2_id, @con3_id) AND variable_name IN ('handler_delete', 'handler_rollback') ORDER BY thread_id;
129
130 --echo #
131 --echo # Verify expected counts for 'handler_delete' per thread
132 --echo #
133 --replace_column 1 connect_1
134 SELECT *, IF (variable_value = 1,'OK','ERROR') AS Expected FROM status_by_thread WHERE thread_id = @con1_id AND variable_name IN ('handler_delete');
135 --echo
136 --replace_column 1 connnect_2
137 SELECT *, IF (variable_value = 2,'OK','ERROR') AS Expected FROM status_by_thread WHERE thread_id = @con2_id AND variable_name IN ('handler_delete');
138 --echo
139 --replace_column 1 connnect_3
140 SELECT *, IF (variable_value = 3,'OK','ERROR') AS Expected FROM status_by_thread WHERE thread_id = @con3_id AND variable_name IN ('handler_delete');
141
142 --echo #
143 --echo # STATUS_BY_THREAD vs. GLOBAL_STATUS
144 --echo #
145 SELECT variable_name, t1, t2, t3, delta, thread, IF(thread=delta,'OK','ERROR') Expected
146 FROM test.status_results
147 ORDER BY variable_name;
148
149 --echo
150 --echo ================================================================================
151 --echo TEST 2: STATUS_BY_USER: Verify expected status counts per user (1,2,3)
152 --echo ================================================================================
153 SELECT *, IF (variable_value = 1,'OK','ERROR') AS Expected FROM status_by_user WHERE user IN ('user1') AND variable_name IN ('handler_delete');
154 --echo
155 SELECT *, IF (variable_value = 2,'OK','ERROR') AS Expected FROM status_by_user WHERE user IN ('user2') AND variable_name IN ('handler_delete');
156 --echo
157 SELECT *, IF (variable_value = 3,'OK','ERROR') AS Expected FROM status_by_user WHERE user IN ('user3') AND variable_name IN ('handler_delete');
158
159 --echo #
160 --echo # STATUS_BY_USER vs. GLOBAL_STATUS
161 --echo #
162 SELECT variable_name, u1, u2, u3, delta, user, IF(user=delta,'OK','ERROR') Expected
163 FROM test.status_results
164 ORDER BY variable_name;
165
166 --echo
167 --echo ================================================================================
168 --echo TEST 3: STATUS_BY_ACCOUNT: Verify expected status counts per user, host (1,2,3)
169 --echo ================================================================================
170 SELECT *, IF (variable_value = 1,'OK','ERROR') AS Expected FROM status_by_account WHERE user IN ('user1') AND variable_name IN ('handler_delete');
171 --echo
172 SELECT *, IF (variable_value = 2,'OK','ERROR') AS Expected FROM status_by_account WHERE user IN ('user2') AND variable_name IN ('handler_delete');
173 --echo
174 SELECT *, IF (variable_value = 3,'OK','ERROR') AS Expected FROM status_by_account WHERE user IN ('user3') AND variable_name IN ('handler_delete');
175
176 --echo #
177 --echo # STATUS_BY_ACCOUNT vs. GLOBAL_STATUS
178 --echo #
179 SELECT variable_name, a1, a2, a3, delta, acct, IF(acct=delta,'OK','ERROR') Expected
180 FROM test.status_results
181 ORDER BY variable_name;
182
183 --echo ================================================================================
184 --echo TEST 4: STATUS_BY_HOST: Verify expected status counts per host (6)
185 --echo ================================================================================
186 SELECT *, IF (variable_value = 6,'OK','ERROR') AS Expected FROM status_by_host WHERE host IN ('localhost') AND variable_name IN ('handler_delete');
187
188 --echo #
189 --echo # STATUS_BY_HOST vs. GLOBAL_STATUS
190 --echo #
191 --echo # Special case: No way to isolate pre-existing 'localhost' activity, so
192 --echo # just check global totals = sum(status_by_host).
193 --echo #
194 SELECT variable_name, h1, h2, h3, delta, host, IF(host=delta,'OK','ERROR') Expected
195 FROM test.status_results
196 ORDER BY variable_name;
197
198 --echo
199 --echo ================================================================================
200 --echo DISCONNECT ALL USERS AND RUN THE TESTS AGAIN. RESULTS SHOULD NOT CHANGE.
201 --echo ================================================================================
202 connection default;
203 disconnect con1;
204 disconnect con2;
205 disconnect con3;
206 --echo
207 USE test;
208 --echo
209 --echo # Clear results table, leave initial global_status counts
210 UPDATE test.status_results
211 SET stop=0, delta=0,
212 t1=0, t2=0, t3=0, thread=0,
213 u1=0, u2=0, u3=0, user=0,
214 h1=0, h2=0, h3=0, host=0,
215 a1=0, a2=0, a3=0, acct=0;
216
217 #--echo DEBUG
218 #SELECT * FROM test.status_results;
219 --echo
220 --source ../include/show_aggregate.inc
221
222 USE performance_schema;
223 --echo
224 --echo ================================================================================
225 --echo TEST X: STATUS_BY_THREAD: Connections are gone--nothing to verify.
226 --echo ================================================================================
227 --echo
228 --echo ================================================================================
229 --echo TEST 5: STATUS_BY_USER: Verify expected status counts per user (1,2,3)
230 --echo ================================================================================
231 SELECT *, IF (variable_value = 1,'OK','ERROR') AS Expected FROM status_by_user WHERE user IN ('user1') AND variable_name IN ('handler_delete');
232 --echo
233 SELECT *, IF (variable_value = 2,'OK','ERROR') AS Expected FROM status_by_user WHERE user IN ('user2') AND variable_name IN ('handler_delete');
234 --echo
235 SELECT *, IF (variable_value = 3,'OK','ERROR') AS Expected FROM status_by_user WHERE user IN ('user3') AND variable_name IN ('handler_delete');
236
237 --echo #
238 --echo # STATUS_BY_USER vs. GLOBAL_STATUS
239 --echo #
240 SELECT variable_name, u1, u2, u3, delta, user, IF(user=delta,'OK','ERROR') Expected
241 FROM test.status_results
242 ORDER BY variable_name;
243
244 --echo
245 --echo ================================================================================
246 --echo TEST 6: STATUS_BY_ACCOUNT: Verify expected status counts per user:host (1,2,3)
247 --echo ================================================================================
248 SELECT *, IF (variable_value = 1,'OK','ERROR') AS Expected FROM status_by_account WHERE user IN ('user1') AND variable_name IN ('handler_delete');
249 --echo
250 SELECT *, IF (variable_value = 2,'OK','ERROR') AS Expected FROM status_by_account WHERE user IN ('user2') AND variable_name IN ('handler_delete');
251 --echo
252 SELECT *, IF (variable_value = 3,'OK','ERROR') AS Expected FROM status_by_account WHERE user IN ('user3') AND variable_name IN ('handler_delete');
253
254 --echo #
255 --echo # STATUS_BY_ACCOUNT vs. GLOBAL_STATUS
256 --echo #
257 SELECT variable_name, a1, a2, a3, delta, acct, IF(acct=delta,'OK','ERROR') Expected
258 FROM test.status_results
259 ORDER BY variable_name;
260
261 --echo ================================================================================
262 --echo TEST 7: STATUS_BY_HOST: Verify expected status counts per host (6)
263 --echo ================================================================================
264 SELECT *, IF (variable_value = 6,'OK','ERROR') AS Expected FROM status_by_host WHERE host IN ('localhost') AND variable_name IN ('handler_delete');
265
266 --echo #
267 --echo # STATUS_BY_HOST vs. GLOBAL_STATUS
268 --echo #
269 --echo # Special case: No way to isolate pre-existing 'localhost' activity, so
270 --echo # just check global totals = sum(status_by_host).
271 --echo #
272 SELECT variable_name, h1, h2, h3, delta, host, IF(host=delta,'OK','ERROR') Expected
273 FROM test.status_results
274 ORDER BY variable_name;
275
276
277 --echo ================================================================================
278 --echo TEST 8: FLUSH STATUS should clear account, host and user status
279 --echo ================================================================================
280 --echo
281 FLUSH STATUS;
282 --echo
283 SELECT * FROM status_by_account WHERE user LIKE 'user%' AND variable_name IN ('handler_delete');
284 --echo
285 SELECT * FROM status_by_host WHERE host IN ('localhost') AND variable_name IN ('handler_delete');
286 --echo
287 SELECT * FROM status_by_user WHERE user LIKE 'user%' AND variable_name IN ('handler_delete');
288
289
290 --echo ================================================================================
291 --echo CLEANUP
292 --echo ================================================================================
293 DROP TABLE test.t1;
294 DROP TABLE test.t2;
295 DROP TABLE test.t3;
296 DROP TABLE test.status_results;
297 DROP USER 'user1'@localhost;
298 DROP USER 'user2'@localhost;
299 DROP USER 'user3'@localhost;
300