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
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
|
################# suite/perfschema/t/show_aggregate.test #######################
# #
# This test verifies: #
# 1. Status variable values are consistent with corresponding server events. #
# 2. Status variable values are consistent across session, thread, account, #
# host, user and global tables. #
# 3. Status totals for user, host and account are retained after related #
# threads disconnect. #
################################################################################
--source include/have_perfschema.inc
--source include/have_innodb.inc
--source include/not_embedded.inc
--source include/no_protocol.inc
--source include/maybe_pool_of_threads.inc
--source include/wait_for_pfs_thread_count.inc
--echo #
--echo # ================================================================================
--echo # SETUP
--echo # ================================================================================
SET @@session.sql_log_bin=OFF;
--echo #
--echo # CREATE 3 CLIENTS, 3 CONNECTIONS, RESULTS TABLE
connection default;
USE test;
flush global status;
--echo #
--echo # Create results table
CREATE TABLE test.status_results
(variable_name VARCHAR(64), start INT DEFAULT 0, stop INT DEFAULT 0, delta INT DEFAULT 0,
t1 INT DEFAULT 0, t2 INT DEFAULT 0, t3 INT DEFAULT 0, thread INT DEFAULT 0,
u1 INT DEFAULT 0, u2 INT DEFAULT 0, u3 INT DEFAULT 0, user INT DEFAULT 0,
h1 INT DEFAULT 0, h2 INT DEFAULT 0, h3 INT DEFAULT 0, host INT DEFAULT 0,
a1 INT DEFAULT 0, a2 INT DEFAULT 0, a3 INT DEFAULT 0, acct INT DEFAULT 0);
INSERT INTO test.status_results (variable_name, start)
SELECT sg.variable_name, sg.variable_value+0 FROM performance_schema.global_status sg
WHERE variable_name IN ('handler_delete', 'handler_rollback');
--echo
--echo # Create test tables
CREATE TABLE t1 (s1 int) ENGINE=InnoDB;
INSERT INTO t1 VALUES (1), (2), (2), (3), (3), (3);
CREATE TABLE t2 (s1 int) ENGINE=InnoDB;
CREATE TABLE t3 (s1 int) ENGINE=InnoDB;
--echo #
set @orig_sql_mode= @@sql_mode;
set sql_mode= (select replace(@@sql_mode,'NO_AUTO_CREATE_USER',''));
GRANT ALL ON *.* to 'user1'@localhost;
GRANT ALL ON *.* to 'user2'@localhost;
GRANT ALL ON *.* to 'user3'@localhost;
set sql_mode= @orig_sql_mode;
--echo #
--echo # ================================================================================
--echo # CONNECTION 1: DELETE 1, ROLLBACK x 1
--echo # ================================================================================
connect(con1, localhost, user1,,);
SET @@session.sql_log_bin=OFF;
DELETE FROM t1 WHERE s1 = 1;
START TRANSACTION;
INSERT INTO t1 VALUES (1);
ROLLBACK;
--echo #
--echo # ================================================================================
--echo # CONNECTION 2: DELETE 2, ROLLBACK x 2
--echo # ================================================================================
connect(con2, localhost, user2,,);
SET @@session.sql_log_bin=OFF;
DELETE FROM t1 WHERE s1 = 2;
--echo #
START TRANSACTION;
INSERT INTO t1 VALUES (2);
ROLLBACK;
--echo #
START TRANSACTION;
INSERT INTO t1 VALUES (2);
ROLLBACK;
--echo #
--echo # ================================================================================
--echo # CONNECTION 3: DELETE 3, ROLLBACK x 3
--echo # ================================================================================
connect(con3, localhost, user3,,);
SET @@session.sql_log_bin=OFF;
DELETE FROM t1 WHERE s1 = 3;
--echo #
START TRANSACTION;
INSERT INTO t1 VALUES (3);
ROLLBACK;
--echo #
START TRANSACTION;
INSERT INTO t1 VALUES (3);
ROLLBACK;
--echo #
START TRANSACTION;
INSERT INTO t1 VALUES (3);
ROLLBACK;
--echo #
--echo # ================================================================================
--echo # CONNECTION DEFAULT: Gather results, compare session and global status counts
--echo # ================================================================================
connection default;
--echo # Get thread ids for each connection.
USE performance_schema;
SELECT thread_id INTO @con1_id FROM threads WHERE processlist_user IN ('user1') and processlist_id;
SELECT thread_id INTO @con2_id FROM threads WHERE processlist_user IN ('user2') and processlist_id;
SELECT thread_id INTO @con3_id FROM threads WHERE processlist_user IN ('user3') and processlist_id;
--source ../include/show_aggregate.inc
#--echo DEBUG
#USE test;
#SELECT * FROM test.status_results;
--echo #
--echo # ================================================================================
--echo # TEST 1: STATUS_BY_THREAD: Verify expected status counts per thread (1,2,3)
--echo # ================================================================================
--echo #
--echo # Review per-thread status counts
--echo #
USE performance_schema;
--echo #
--echo # Verify expected counts for 'handler_delete' per thread
--echo #
--replace_column 1 con_1
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');
--echo #
--replace_column 1 con_2
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');
--echo #
--replace_column 1 con_3
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');
--echo #
--echo # STATUS_BY_THREAD vs. GLOBAL_STATUS
--echo #
SELECT variable_name, t1, t2, t3, delta, thread, IF(thread=delta,'OK4','ERROR4') Expected
FROM test.status_results
ORDER BY variable_name;
--echo #
--echo # ================================================================================
--echo # TEST 2: STATUS_BY_USER: Verify expected status counts per user (1,2,3)
--echo # ================================================================================
SELECT *, IF (variable_value = 1,'OK5','ERROR5') AS Expected FROM status_by_user WHERE user IN ('user1') AND variable_name IN ('handler_delete');
--echo #
SELECT *, IF (variable_value = 2,'OK6','ERROR6') AS Expected FROM status_by_user WHERE user IN ('user2') AND variable_name IN ('handler_delete');
--echo #
SELECT *, IF (variable_value = 3,'OK7','ERROR7') AS Expected FROM status_by_user WHERE user IN ('user3') AND variable_name IN ('handler_delete');
--echo #
--echo # STATUS_BY_USER vs. GLOBAL_STATUS
--echo #
SELECT variable_name, u1, u2, u3, delta, user, IF(user=delta,'OK8','ERROR8') Expected
FROM test.status_results
ORDER BY variable_name;
--echo #
--echo # ================================================================================
--echo # TEST 3: STATUS_BY_ACCOUNT: Verify expected status counts per user, host (1,2,3)
--echo # ================================================================================
SELECT *, IF (variable_value = 1,'OK9','ERROR9') AS Expected FROM status_by_account WHERE user IN ('user1') AND variable_name IN ('handler_delete');
--echo #
SELECT *, IF (variable_value = 2,'OKa','ERRORa') AS Expected FROM status_by_account WHERE user IN ('user2') AND variable_name IN ('handler_delete');
--echo #
SELECT *, IF (variable_value = 3,'OKb','ERRORb') AS Expected FROM status_by_account WHERE user IN ('user3') AND variable_name IN ('handler_delete');
--echo #
--echo # STATUS_BY_ACCOUNT vs. GLOBAL_STATUS
--echo #
SELECT variable_name, a1, a2, a3, delta, acct, IF(acct=delta,'OKc','ERRORc') Expected
FROM test.status_results
ORDER BY variable_name;
--echo # ================================================================================
--echo # TEST 4: STATUS_BY_HOST: Verify expected status counts per host (6)
--echo # ================================================================================
SELECT *, IF (variable_value = 6,'OKd','ERRORd') AS Expected FROM status_by_host WHERE host IN ('localhost') AND variable_name IN ('handler_delete');
--echo #
--echo # STATUS_BY_HOST vs. GLOBAL_STATUS
--echo #
--echo # Special case: No way to isolate pre-existing 'localhost' activity, so
--echo # just check global totals = sum(status_by_host).
--echo #
SELECT variable_name, h1, h2, h3, delta, host, IF(host=delta,'OKe','ERRORe') Expected
FROM test.status_results
ORDER BY variable_name;
--echo #
--echo # ================================================================================
--echo # DISCONNECT ALL USERS AND RUN THE TESTS AGAIN. RESULTS SHOULD NOT CHANGE.
--echo # ================================================================================
disconnect con1;
disconnect con2;
disconnect con3;
--echo #
USE test;
--echo #
--echo # Clear results table, leave initial global_status counts
UPDATE test.status_results
SET stop=0, delta=0,
t1=0, t2=0, t3=0, thread=0,
u1=0, u2=0, u3=0, user=0,
h1=0, h2=0, h3=0, host=0,
a1=0, a2=0, a3=0, acct=0;
let $wait_condition= select count(*) = 0 from performance_schema.threads where processlist_user like 'user%' and processlist_id;
--source include/wait_condition.inc
#--echo DEBUG
#SELECT * FROM test.status_results;
--echo #
--source ../include/show_aggregate.inc
USE performance_schema;
--echo #
--echo # ================================================================================
--echo # TEST X: STATUS_BY_THREAD: Connections are gone--nothing to verify.
--echo # ================================================================================
--echo #
--echo # ================================================================================
--echo # TEST 5: STATUS_BY_USER: Verify expected status counts per user (1,2,3)
--echo # ================================================================================
SELECT *, IF (variable_value = 1,'OKf','ERRORf') AS Expected FROM status_by_user WHERE user IN ('user1') AND variable_name IN ('handler_delete');
--echo #
SELECT *, IF (variable_value = 2,'OKg','ERRORg') AS Expected FROM status_by_user WHERE user IN ('user2') AND variable_name IN ('handler_delete');
--echo #
SELECT *, IF (variable_value = 3,'OKh','ERRORh') AS Expected FROM status_by_user WHERE user IN ('user3') AND variable_name IN ('handler_delete');
--echo #
--echo # STATUS_BY_USER vs. GLOBAL_STATUS
--echo #
SELECT variable_name, u1, u2, u3, delta, user, IF(user=delta,'OKi','ERRORi') Expected
FROM test.status_results
ORDER BY variable_name;
--echo #
--echo # ================================================================================
--echo # TEST 6: STATUS_BY_ACCOUNT: Verify expected status counts per user:host (1,2,3)
--echo # ================================================================================
SELECT *, IF (variable_value = 1,'OKj','ERRORj') AS Expected FROM status_by_account WHERE user IN ('user1') AND variable_name IN ('handler_delete');
--echo #
SELECT *, IF (variable_value = 2,'OKk','ERRORk') AS Expected FROM status_by_account WHERE user IN ('user2') AND variable_name IN ('handler_delete');
--echo #
SELECT *, IF (variable_value = 3,'OKl','ERRORl') AS Expected FROM status_by_account WHERE user IN ('user3') AND variable_name IN ('handler_delete');
--echo #
--echo # STATUS_BY_ACCOUNT vs. GLOBAL_STATUS
--echo #
SELECT variable_name, a1, a2, a3, delta, acct, IF(acct=delta,'OKn','ERRORn') Expected
FROM test.status_results
ORDER BY variable_name;
--echo # ================================================================================
--echo # TEST 7: STATUS_BY_HOST: Verify expected status counts per host (6)
--echo # ================================================================================
SELECT *, IF (variable_value = 6,'OKo','ERRORo') AS Expected FROM status_by_host WHERE host IN ('localhost') AND variable_name IN ('handler_delete');
--echo #
--echo # STATUS_BY_HOST vs. GLOBAL_STATUS
--echo #
--echo # Special case: No way to isolate pre-existing 'localhost' activity, so
--echo # just check global totals = sum(status_by_host).
--echo #
SELECT variable_name, h1, h2, h3, delta, host, IF(host=delta,'OKp','ERRORp') Expected
FROM test.status_results
ORDER BY variable_name;
--echo # ================================================================================
--echo # TEST 8: FLUSH GLOBAL STATUS should clear account, host and user status
--echo # ================================================================================
--echo #
FLUSH GLOBAL STATUS;
--echo #
SELECT * FROM status_by_account WHERE user IN ('user1', 'user2', 'user3') AND variable_name IN ('handler_delete');
--echo #
SELECT * FROM status_by_host WHERE host IN ('localhost') AND variable_name IN ('handler_delete');
--echo #
SELECT * FROM status_by_user WHERE user IN ('user1', 'user2', 'user3') AND variable_name IN ('handler_delete');
--echo # ================================================================================
--echo # CLEANUP
--echo # ================================================================================
DROP TABLE test.t1;
DROP TABLE test.t2;
DROP TABLE test.t3;
DROP TABLE test.status_results;
DROP USER 'user1'@localhost;
DROP USER 'user2'@localhost;
DROP USER 'user3'@localhost;
--echo #
|
