Debian Codegraph @

mysql-test/suite/perfschema/r/idx_compare_threads.result

raw
1 TABLE: "threads"
2 INDEXES: PK (THREAD_ID), KEY (NAME), KEY (PROCESSLIST_ID),
3 KEY (PROCESSLIST_USER, PROCESSLIST_HOST),
4 KEY (PROCESSLIST_HOST), KEY (THREAD_OS_ID)
5
6 ===== SETUP =====
7 ## Connection Default
8 ## Disable events from the default connection
9
10 ## Create user1, user2, user3
11 CREATE USER user1@localhost;
12 CREATE USER user2@localhost;
13 CREATE USER user3@localhost;
14 GRANT ALL ON *.* to 'user1'@localhost;
15 GRANT ALL ON *.* to 'user2'@localhost;
16 GRANT ALL ON *.* to 'user3'@localhost;
17
18 ## Setup test connection 1
19 ## Setup test connection 2
20 ## Setup test connection 3
21
22 ## Default connection
23
24 ## Create data tables
25 CREATE TABLE test.t1(a INT, b INT, c INT, d INT DEFAULT 0, PRIMARY KEY(a), INDEX index_b(b), INDEX index_cb(c, b));
26 CREATE TABLE test.t2 LIKE test.t1;
27 CREATE TABLE test.t3 LIKE test.t1;
28
29 ## Generate event data from 3 connections
30 ## Connection 1
31 ## Connection 2
32 ## Connection 3
33 ## Connection 2
34 ## Create prepared statements on connection 2
35 PREPARE st1 FROM 'SELECT SQRT(POW(?,2) + POW(?,2)) AS hypotenuse';
36 PREPARE st3 FROM 'INSERT INTO test.t1 SELECT * FROM test.t1 WHERE a<=?';
37 PREPARE st4 FROM '(SELECT a FROM test.t1) UNION (SELECT a+10 FROM test.t1) ORDER BY RAND()*0+a';
38 ## Create stored procedure connection 2
39 CREATE PROCEDURE SampleProc1()
40 BEGIN
41 SET @table = 'test.t1';
42 SET @s = CONCAT('SELECT * FROM ', @table);
43 PREPARE st2 FROM @s;
44 END|
45
46 Call SampleProc1() to prepare a statement
47 CALL SampleProc1();
48 ## Connection default
49
50 ## Use Connection 2 for default target thread id
51
52 ## Create index_test() procedure
53
54 ## Test THREAD_ID
55
56 ===== BEGIN TEST =====
57 ========
58 SELECT * FROM performance_schema.threads WHERE THREAD_ID is null
59
60 OK: Handler_read_key
61
62 OK: Result set: Empty Expected: Empty
63
64 OK: Extra rows: 0 Missing rows: 0
65 ========
66 SELECT * FROM performance_schema.threads WHERE THREAD_ID is not null
67
68 OK: Handler_read_key
69
70 OK: Result set: Non-empty Expected: Non-empty
71
72 OK: Extra rows: 0 Missing rows: 0
73 ========
74 SELECT * FROM performance_schema.threads WHERE THREAD_ID = @target_id
75
76 OK: Handler_read_key
77
78 OK: Result set: Non-empty Expected: Non-empty
79
80 OK: Extra rows: 0 Missing rows: 0
81 ========
82 SELECT * FROM performance_schema.threads WHERE THREAD_ID = @target_id ORDER BY THREAD_ID
83
84 OK: Handler_read_key
85
86 OK: Result set: Non-empty Expected: Non-empty
87
88 OK: Extra rows: 0 Missing rows: 0
89 ========
90 SELECT * FROM performance_schema.threads WHERE THREAD_ID = @target_id ORDER BY THREAD_ID DESC
91
92 OK: Handler_read_key
93
94 OK: Result set: Non-empty Expected: Non-empty
95
96 OK: Extra rows: 0 Missing rows: 0
97 ========
98 SELECT * FROM performance_schema.threads WHERE THREAD_ID = 9999999 ORDER BY THREAD_ID
99
100 OK: Handler_read_key
101
102 OK: Result set: Empty Expected: Empty
103
104 OK: Extra rows: 0 Missing rows: 0
105
106 ## Test NAME
107
108 ===== BEGIN TEST =====
109 ========
110 SELECT * FROM performance_schema.threads WHERE NAME is null
111
112 OK: Handler_read_key
113
114 OK: Result set: Empty Expected: Empty
115
116 OK: Extra rows: 0 Missing rows: 0
117 ========
118 SELECT * FROM performance_schema.threads WHERE NAME is not null
119
120 OK: Handler_read_key
121
122 OK: Result set: Non-empty Expected: Non-empty
123
124 OK: Extra rows: 0 Missing rows: 0
125 ========
126 SELECT * FROM performance_schema.threads WHERE NAME = 'thread/sql/one_connection'
127
128 OK: Handler_read_key
129
130 OK: Result set: Non-empty Expected: Non-empty
131
132 OK: Extra rows: 0 Missing rows: 0
133 ========
134 SELECT * FROM performance_schema.threads WHERE NAME != 'thread/sql/one_connection'
135
136 OK: Handler_read_key
137
138 OK: Result set: Non-empty Expected: Non-empty
139
140 OK: Extra rows: 0 Missing rows: 0
141 ========
142 SELECT * FROM performance_schema.threads WHERE NAME = 'impossible'
143
144 OK: Handler_read_key
145
146 OK: Result set: Empty Expected: Empty
147
148 OK: Extra rows: 0 Missing rows: 0
149 ========
150 SELECT * FROM performance_schema.threads WHERE NAME != 'impossible' ORDER BY NAME DESC
151
152 OK: Handler_read_key
153
154 OK: Result set: Non-empty Expected: Non-empty
155
156 OK: Extra rows: 0 Missing rows: 0
157
158 ## Test PROCESSLIST_USER, PROCESSLIST_HOST
159
160 ===== BEGIN TEST =====
161 ========
162 SELECT * FROM performance_schema.threads WHERE processlist_user is null
163
164 OK: Handler_read_key
165
166 OK: Result set: Non-empty Expected: Non-empty
167
168 OK: Extra rows: 0 Missing rows: 0
169 ========
170 SELECT * FROM performance_schema.threads WHERE processlist_user is not null
171
172 OK: Handler_read_key
173
174 OK: Result set: Non-empty Expected: Non-empty
175
176 OK: Extra rows: 0 Missing rows: 0
177 ========
178 SELECT * FROM performance_schema.threads WHERE processlist_user = 'user1' and processlist_host is null
179
180 OK: Handler_read_key
181
182 OK: Result set: Empty Expected: Empty
183
184 OK: Extra rows: 0 Missing rows: 0
185 ========
186 SELECT * FROM performance_schema.threads WHERE processlist_user = 'user1' and processlist_host is not null
187
188 OK: Handler_read_key
189
190 OK: Result set: Non-empty Expected: Non-empty
191
192 OK: Extra rows: 0 Missing rows: 0
193 ========
194 SELECT * FROM performance_schema.threads WHERE processlist_host = 'localhost'
195
196 OK: Handler_read_key
197
198 OK: Result set: Non-empty Expected: Non-empty
199
200 OK: Extra rows: 0 Missing rows: 0
201 ========
202 SELECT * FROM performance_schema.threads WHERE processlist_host = 'localhost' AND processlist_user= ''
203
204 OK: Handler_read_key
205
206 OK: Result set: Empty Expected: Empty
207
208 OK: Extra rows: 0 Missing rows: 0
209 ========
210 SELECT * FROM performance_schema.threads WHERE processlist_host != 'localhost' AND processlist_user != ''
211
212 OK: Handler_read_key
213
214 OK: Result set: Empty Expected: Empty
215
216 OK: Extra rows: 0 Missing rows: 0
217 ========
218 SELECT * FROM performance_schema.threads WHERE processlist_host = 'nohost' AND processlist_user= 'nouser'
219
220 OK: Handler_read_key
221
222 OK: Result set: Empty Expected: Empty
223
224 OK: Extra rows: 0 Missing rows: 0
225 ========
226 SELECT * FROM performance_schema.threads WHERE processlist_host = 'localhost' AND processlist_user= 'nouser'
227
228 OK: Handler_read_key
229
230 OK: Result set: Empty Expected: Empty
231
232 OK: Extra rows: 0 Missing rows: 0
233 ========
234 SELECT * FROM performance_schema.threads WHERE processlist_host = 'nohost' AND processlist_user= ''
235
236 OK: Handler_read_key
237
238 OK: Result set: Empty Expected: Empty
239
240 OK: Extra rows: 0 Missing rows: 0
241
242 ## Test PROCESSLIST_HOST
243
244 ===== BEGIN TEST =====
245 ========
246 SELECT * FROM performance_schema.threads WHERE processlist_host = 'localhost'
247
248 OK: Handler_read_key
249
250 OK: Result set: Non-empty Expected: Non-empty
251
252 OK: Extra rows: 0 Missing rows: 0
253 ========
254 SELECT * FROM performance_schema.threads WHERE processlist_host IS NULL
255
256 OK: Handler_read_key
257
258 OK: Result set: Non-empty Expected: Non-empty
259
260 OK: Extra rows: 0 Missing rows: 0
261 ========
262 SELECT * FROM performance_schema.threads WHERE processlist_host IS NOT NULL
263
264 OK: Handler_read_key
265
266 OK: Result set: Non-empty Expected: Non-empty
267
268 OK: Extra rows: 0 Missing rows: 0
269 ========
270 SELECT * FROM performance_schema.threads WHERE processlist_host = 'nohost'
271
272 OK: Handler_read_key
273
274 OK: Result set: Empty Expected: Empty
275
276 OK: Extra rows: 0 Missing rows: 0
277
278 ## Test THREAD_OS_ID
279
280 ===== BEGIN TEST =====
281 ========
282 SELECT * FROM performance_schema.threads WHERE THREAD_OS_ID is null
283
284 OK: Handler_read_key
285
286 OK: Result set: Empty Expected: Empty
287
288 OK: Extra rows: 0 Missing rows: 0
289 ========
290 SELECT * FROM performance_schema.threads WHERE THREAD_OS_ID is not null
291
292 OK: Handler_read_key
293
294 OK: Result set: Non-empty Expected: Non-empty
295
296 OK: Extra rows: 0 Missing rows: 0
297 ========
298 SELECT * FROM performance_schema.threads WHERE THREAD_OS_ID = @target_id
299
300 OK: Handler_read_key
301
302 OK: Result set: Non-empty Expected: Non-empty
303
304 OK: Extra rows: 0 Missing rows: 0
305 ========
306 SELECT * FROM performance_schema.threads WHERE THREAD_OS_ID = @target_id ORDER BY THREAD_OS_ID
307
308 OK: Handler_read_key
309
310 OK: Result set: Non-empty Expected: Non-empty
311
312 OK: Extra rows: 0 Missing rows: 0
313 ========
314 SELECT * FROM performance_schema.threads WHERE THREAD_OS_ID = @target_id ORDER BY THREAD_OS_ID DESC
315
316 OK: Handler_read_key
317
318 OK: Result set: Non-empty Expected: Non-empty
319
320 OK: Extra rows: 0 Missing rows: 0
321 ========
322 SELECT * FROM performance_schema.threads WHERE THREAD_OS_ID = 9999999 ORDER BY THREAD_OS_ID
323
324 OK: Handler_read_key
325
326 OK: Result set: Empty Expected: Empty
327
328 OK: Extra rows: 0 Missing rows: 0
329
330 ===== CLEAN UP =====
331 ## Default connection
332 DEALLOCATE PREPARE st1;
333 DEALLOCATE PREPARE st2;
334 DEALLOCATE PREPARE st3;
335 DEALLOCATE PREPARE st4;
336 DROP PROCEDURE SampleProc1;
337 DROP PROCEDURE index_test;
338 DROP TABLE IF EXISTS test.indexes_off;
339 DROP TABLE IF EXISTS test.indexes_on;
340 DROP TABLE test.t1;
341 DROP TABLE test.t2;
342 DROP TABLE test.t3;
343 REVOKE ALL PRIVILEGES, GRANT OPTION FROM user1@localhost;
344 REVOKE ALL PRIVILEGES, GRANT OPTION FROM user2@localhost;
345 REVOKE ALL PRIVILEGES, GRANT OPTION FROM user3@localhost;
346 DROP USER user1@localhost;
347 DROP USER user2@localhost;
348 DROP USER user3@localhost;
349 UPDATE performance_schema.threads SET INSTRUMENTED = 'YES';