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
11 --source include/have_perfschema.inc
12 --source include/have_innodb.inc
13 --source include/not_embedded.inc
14 --source include/no_protocol.inc
15 --source include/maybe_pool_of_threads.inc
16 --source include/wait_for_pfs_thread_count.inc
17
18 --echo #
19 --echo # ================================================================================
20 --echo # SETUP
21 --echo # ================================================================================
22 SET @@session.sql_log_bin=OFF;
23 --echo #
24 --echo # CREATE 3 CLIENTS, 3 CONNECTIONS, RESULTS TABLE
25 connection default;
26 USE test;
27
28 flush global status;
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 set @orig_sql_mode= @@sql_mode;
51 set sql_mode= (select replace(@@sql_mode,'NO_AUTO_CREATE_USER',''));
52 GRANT ALL ON *.* to 'user1'@localhost;
53 GRANT ALL ON *.* to 'user2'@localhost;
54 GRANT ALL ON *.* to 'user3'@localhost;
55 set sql_mode= @orig_sql_mode;
56
57 --echo #
58 --echo # ================================================================================
59 --echo # CONNECTION 1: DELETE 1, ROLLBACK x 1
60 --echo # ================================================================================
61 connect(con1, localhost, user1,,);
62 SET @@session.sql_log_bin=OFF;
63 DELETE FROM t1 WHERE s1 = 1;
64 START TRANSACTION;
65 INSERT INTO t1 VALUES (1);
66 ROLLBACK;
67
68 --echo #
69 --echo # ================================================================================
70 --echo # CONNECTION 2: DELETE 2, ROLLBACK x 2
71 --echo # ================================================================================
72 connect(con2, localhost, user2,,);
73 SET @@session.sql_log_bin=OFF;
74 DELETE FROM t1 WHERE s1 = 2;
75 --echo #
76 START TRANSACTION;
77 INSERT INTO t1 VALUES (2);
78 ROLLBACK;
79 --echo #
80 START TRANSACTION;
81 INSERT INTO t1 VALUES (2);
82 ROLLBACK;
83
84 --echo #
85 --echo # ================================================================================
86 --echo # CONNECTION 3: DELETE 3, ROLLBACK x 3
87 --echo # ================================================================================
88 connect(con3, localhost, user3,,);
89 SET @@session.sql_log_bin=OFF;
90 DELETE FROM t1 WHERE s1 = 3;
91 --echo #
92 START TRANSACTION;
93 INSERT INTO t1 VALUES (3);
94 ROLLBACK;
95 --echo #
96 START TRANSACTION;
97 INSERT INTO t1 VALUES (3);
98 ROLLBACK;
99 --echo #
100 START TRANSACTION;
101 INSERT INTO t1 VALUES (3);
102 ROLLBACK;
103
104 --echo #
105 --echo # ================================================================================
106 --echo # CONNECTION DEFAULT: Gather results, compare session and global status counts
107 --echo # ================================================================================
108 connection default;
109 --echo # Get thread ids for each connection.
110 USE performance_schema;
111 SELECT thread_id INTO @con1_id FROM threads WHERE processlist_user IN ('user1') and processlist_id;
112 SELECT thread_id INTO @con2_id FROM threads WHERE processlist_user IN ('user2') and processlist_id;
113 SELECT thread_id INTO @con3_id FROM threads WHERE processlist_user IN ('user3') and processlist_id;
114
115 --source ../include/show_aggregate.inc
116
117 #--echo DEBUG
118 #USE test;
119 #SELECT * FROM test.status_results;
120
121 --echo #
122 --echo # ================================================================================
123 --echo # TEST 1: STATUS_BY_THREAD: Verify expected status counts per thread (1,2,3)
124 --echo # ================================================================================
125 --echo #
126 --echo # Review per-thread status counts
127 --echo #
128 USE performance_schema;
129
130 --echo #
131 --echo # Verify expected counts for 'handler_delete' per thread
132 --echo #
133 --replace_column 1 con_1
134 SELECT *, IF (variable_value = 1,'OK1','ERROR1') AS Expected FROM status_by_thread WHERE thread_id = @con1_id AND variable_name IN ('handler_delete', 'handler_rollback');
135 --echo #
136 --replace_column 1 con_2
137 SELECT *, IF (variable_value = 2,'OK2','ERROR2') AS Expected FROM status_by_thread WHERE thread_id = @con2_id AND variable_name IN ('handler_delete', 'handler_rollback');
138 --echo #
139 --replace_column 1 con_3
140 SELECT *, IF (variable_value = 3,'OK3','ERROR3') AS Expected FROM status_by_thread WHERE thread_id = @con3_id AND variable_name IN ('handler_delete', 'handler_rollback');
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,'OK4','ERROR4') 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,'OK5','ERROR5') AS Expected FROM status_by_user WHERE user IN ('user1') AND variable_name IN ('handler_delete');
154 --echo #
155 SELECT *, IF (variable_value = 2,'OK6','ERROR6') AS Expected FROM status_by_user WHERE user IN ('user2') AND variable_name IN ('handler_delete');
156 --echo #
157 SELECT *, IF (variable_value = 3,'OK7','ERROR7') 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,'OK8','ERROR8') 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,'OK9','ERROR9') AS Expected FROM status_by_account WHERE user IN ('user1') AND variable_name IN ('handler_delete');
171 --echo #
172 SELECT *, IF (variable_value = 2,'OKa','ERRORa') AS Expected FROM status_by_account WHERE user IN ('user2') AND variable_name IN ('handler_delete');
173 --echo #
174 SELECT *, IF (variable_value = 3,'OKb','ERRORb') 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,'OKc','ERRORc') 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,'OKd','ERRORd') 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,'OKe','ERRORe') 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 disconnect con1;
203 disconnect con2;
204 disconnect con3;
205
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 let $wait_condition= select count(*) = 0 from performance_schema.threads where processlist_user like 'user%' and processlist_id;
218 --source include/wait_condition.inc
219
220 #--echo DEBUG
221 #SELECT * FROM test.status_results;
222 --echo #
223 --source ../include/show_aggregate.inc
224
225 USE performance_schema;
226 --echo #
227 --echo # ================================================================================
228 --echo # TEST X: STATUS_BY_THREAD: Connections are gone--nothing to verify.
229 --echo # ================================================================================
230 --echo #
231 --echo # ================================================================================
232 --echo # TEST 5: STATUS_BY_USER: Verify expected status counts per user (1,2,3)
233 --echo # ================================================================================
234 SELECT *, IF (variable_value = 1,'OKf','ERRORf') AS Expected FROM status_by_user WHERE user IN ('user1') AND variable_name IN ('handler_delete');
235 --echo #
236 SELECT *, IF (variable_value = 2,'OKg','ERRORg') AS Expected FROM status_by_user WHERE user IN ('user2') AND variable_name IN ('handler_delete');
237 --echo #
238 SELECT *, IF (variable_value = 3,'OKh','ERRORh') AS Expected FROM status_by_user WHERE user IN ('user3') AND variable_name IN ('handler_delete');
239
240 --echo #
241 --echo # STATUS_BY_USER vs. GLOBAL_STATUS
242 --echo #
243 SELECT variable_name, u1, u2, u3, delta, user, IF(user=delta,'OKi','ERRORi') Expected
244 FROM test.status_results
245 ORDER BY variable_name;
246
247 --echo #
248 --echo # ================================================================================
249 --echo # TEST 6: STATUS_BY_ACCOUNT: Verify expected status counts per user:host (1,2,3)
250 --echo # ================================================================================
251 SELECT *, IF (variable_value = 1,'OKj','ERRORj') AS Expected FROM status_by_account WHERE user IN ('user1') AND variable_name IN ('handler_delete');
252 --echo #
253 SELECT *, IF (variable_value = 2,'OKk','ERRORk') AS Expected FROM status_by_account WHERE user IN ('user2') AND variable_name IN ('handler_delete');
254 --echo #
255 SELECT *, IF (variable_value = 3,'OKl','ERRORl') AS Expected FROM status_by_account WHERE user IN ('user3') AND variable_name IN ('handler_delete');
256
257 --echo #
258 --echo # STATUS_BY_ACCOUNT vs. GLOBAL_STATUS
259 --echo #
260 SELECT variable_name, a1, a2, a3, delta, acct, IF(acct=delta,'OKn','ERRORn') Expected
261 FROM test.status_results
262 ORDER BY variable_name;
263
264 --echo # ================================================================================
265 --echo # TEST 7: STATUS_BY_HOST: Verify expected status counts per host (6)
266 --echo # ================================================================================
267 SELECT *, IF (variable_value = 6,'OKo','ERRORo') AS Expected FROM status_by_host WHERE host IN ('localhost') AND variable_name IN ('handler_delete');
268
269 --echo #
270 --echo # STATUS_BY_HOST vs. GLOBAL_STATUS
271 --echo #
272 --echo # Special case: No way to isolate pre-existing 'localhost' activity, so
273 --echo # just check global totals = sum(status_by_host).
274 --echo #
275 SELECT variable_name, h1, h2, h3, delta, host, IF(host=delta,'OKp','ERRORp') Expected
276 FROM test.status_results
277 ORDER BY variable_name;
278
279 --echo # ================================================================================
280 --echo # TEST 8: FLUSH GLOBAL STATUS should clear account, host and user status
281 --echo # ================================================================================
282 --echo #
283 FLUSH GLOBAL STATUS;
284 --echo #
285 SELECT * FROM status_by_account WHERE user IN ('user1', 'user2', 'user3') AND variable_name IN ('handler_delete');
286 --echo #
287 SELECT * FROM status_by_host WHERE host IN ('localhost') AND variable_name IN ('handler_delete');
288 --echo #
289 SELECT * FROM status_by_user WHERE user IN ('user1', 'user2', 'user3') AND variable_name IN ('handler_delete');
290
291 --echo # ================================================================================
292 --echo # CLEANUP
293 --echo # ================================================================================
294 DROP TABLE test.t1;
295 DROP TABLE test.t2;
296 DROP TABLE test.t3;
297 DROP TABLE test.status_results;
298 DROP USER 'user1'@localhost;
299 DROP USER 'user2'@localhost;
300 DROP USER 'user3'@localhost;
301 --echo #