Debian Codegraph @

mysql-test/suite/funcs_1/datadict/processlist_val.inc

raw
1 ########### suite/funcs_1/datadict/processlist_val.inc #########################
2 # #
3 # Testing of values within INFORMATION_SCHEMA.PROCESSLIST #
4 # #
5 # Ensure that the values fit to the current state of the connection and #
6 # especially that they change if a connection does nothing or runs some SQL. #
7 # runs some SQL. #
8 # Examples: #
9 # - change the default database #
10 # - send some period of time no SQL command to the server #
11 # - send a long running query #
12 # #
13 # Note(mleich): #
14 # 1. Please inform me if this test fails because of timing problems. #
15 # 2. Storage engine variants of this test do not make sense. #
16 # - I_S tables use the MEMORY storage engine whenever possible. #
17 # - There are some I_S tables which need column data types which are not #
18 # supported by MEMORY. Example: LONGTEXT/BLOB #
19 # MyISAM will be used for such tables. #
20 # The column PROCESSLIST.INFO is of data type LONGTEXT ----> MyISAM #
21 # - There is no impact of the GLOBAL(server) or SESSION default storage #
22 # engine setting on the engine used for I_S tables. #
23 # That means we cannot get NDB or InnoDB instead. #
24 # 3. The SHOW (FULL) PROCESSLIST command are for comparison. #
25 # The main test target is INFORMATION_SCHEMA.PROCESSLIST ! #
26 # 4. Attention: #
27 # The values of the PROCESSLIST columns HOST and TIME tend to cause #
28 # problems and therefore their printing has to be suppressed. #
29 # Examples of the exact values: #
30 # HOST: 'localhost' (UNIX derivates) #
31 # 'localhost:<varying_port>' (WINDOWS) #
32 # TIME: In many cases within this test 0 seconds but if the testing box is #
33 # overloaded we might get up to 2 seconds. #
34 # Solution: #
35 # --replace_column 1 <ID> 3 <HOST_NAME> 6 <TIME> #
36 # 5. How to debug the script? #
37 # <graphical diff tool> \ #
38 # suite/funcs_1/datadict/processlist_val.inc \ #
39 # <Result|Reject|Log file> #
40 # I tweaked a lot of the script lines around "echo" so that you will get a #
41 # lot of useful synchronisation. #
42 # #
43 # Creation: #
44 # 2007-08-09 mleich Implement this test as part of #
45 # WL#3982 Test information_schema.processlist #
46 # #
47 # Last Modification: #
48 # 2008-07-04 mleich Fix for #
49 # Bug#37853 Test "funcs_1.processlist_val_ps" fails in #
50 # various ways #
51 # - issues with printing of port (Win only) #
52 # - too optimistic assumptions about timing #
53 # + corrections of logic in poll routines #
54 # + minor improvements #
55 ################################################################################
56
57 # Basic preparations
58 --error 0, ER_CANNOT_USER
59 DROP USER test_user@'localhost';
60 CREATE USER test_user@'localhost';
61 GRANT ALL ON *.* TO test_user@'localhost';
62 REVOKE PROCESS ON *.* FROM test_user@'localhost';
63 SET PASSWORD FOR test_user@'localhost' = 'ddictpass';
64 --disable_warnings
65 DROP TABLE IF EXISTS test.t1;
66 --enable_warnings
67 CREATE TABLE test.t1 (f1 BIGINT);
68 USE test;
69
70
71 echo
72 # Show the definition of the PROCESSLIST table
73 #--------------------------------------------------------------------------
74 ;
75 --replace_result InnoDB TMP_TABLE_ENGINE MyISAM TMP_TABLE_ENGINE
76 SHOW CREATE TABLE INFORMATION_SCHEMA.PROCESSLIST;
77
78 echo
79 # Ensure that the information about the own connection is correct.
80 #--------------------------------------------------------------------------
81 ;
82 # Expected values
83 # - USER = 'root'
84 # - HOST (printed value is unified), the exact values are often like
85 # UNIX: 'localhost'
86 # WIN: 'localhost:<some port>'
87 # - DB = 'test'
88 # - Command IN (no protocol -> 'Query', ps-protocol -> 'Execute')
89 # - TIME (printed value will be unified), the exact values are like
90 # "normal" load: 0 (seconds)
91 # "heavy" load: 1 or more (seconds)
92 # - State 'executing'
93 # - INFO must contain the corresponding SHOW/SELECT PROCESSLIST
94 #
95 # 1. Just dump what we get
96 --replace_column 1 <ID> 3 <HOST_NAME> 6 <TIME>
97 --replace_result "init" STATE "starting" STATE "cleaning up" STATE
98 --sorted_result
99 SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST;
100 --replace_column 1 <ID> 3 <HOST_NAME> 6 <TIME>
101 --replace_result "init" STATE "starting" STATE "cleaning up" STATE
102 --sorted_result
103 SHOW FULL PROCESSLIST;
104 #
105 # Determine the connection id of the current connection (default)
106 SET @default_id = CONNECTION_ID();
107 #
108 # 2. There must be exact one connection with @default_id;
109 SELECT COUNT(*) = 1 AS "Expect exact one connection with this id"
110 FROM INFORMATION_SCHEMA.PROCESSLIST WHERE ID = @default_id;
111 #
112 # 3. Check the remaining stuff
113 SELECT COUNT(*) = 1 AS "Expect 1"
114 FROM INFORMATION_SCHEMA.PROCESSLIST WHERE ID = @default_id
115 AND USER = 'root' AND DB = 'test' AND Command IN('Query','Execute')
116 AND State = 'executing';
117 #
118 # 4. Change the DB
119 USE information_schema;
120 SELECT COUNT(*) = 1 AS "Is the DB correct?"
121 FROM INFORMATION_SCHEMA.PROCESSLIST
122 WHERE ID = @default_id AND DB = 'information_schema';
123 #
124 # 5. Change the statement
125 let $my_statement =
126 SELECT @my_info := INFO FROM INFORMATION_SCHEMA.PROCESSLIST
127 WHERE ID = @default_id;
128 eval $my_statement;
129 eval
130 SELECT @my_info = '$my_statement'
131 AS 'Is the content of PROCESSLIST.INFO correct?';
132 #
133 # 6. TIME must have a reasonable value
134 SELECT COUNT(*) = 1 AS "Has TIME a reasonable value?"
135 FROM INFORMATION_SCHEMA.PROCESSLIST
136 WHERE ID = @default_id AND 0 <= TIME < 10;
137
138
139 echo
140 # Ensure that the information about an inactive connection is correct.
141 #--------------------------------------------------------------------------
142 ;
143 echo
144 # ----- establish connection con1 (user = test_user) -----
145 ;
146 connect (con1,localhost,test_user,ddictpass,information_schema);
147 #
148 echo
149 # ----- switch to connection default (user = root) -----
150 ;
151 connection default;
152 # We have now a second connection.
153 # First working phase for the new connection is with Command = 'Connect'.
154 # This is a very short phase and the likelihood to meet it is
155 # - nearly zero on average boxes with low parallel load
156 # - around some percent on weak or overloaded boxes
157 # (Bug#32153 Status output differs - scheduling ?)
158 # Therefore we do not try to catch this state.
159 # We poll till we reach the long lasting phase with Command = 'Sleep'.
160 # - USER = 'test_user'
161 # - DB = 'information_schema'
162 # - Command = 'Sleep'
163 # - State is empty
164 # - INFO IS NULL
165 echo
166 # Poll till the connection con1 is in state COMMAND = 'Sleep'.
167 ;
168 let $wait_timeout= 10;
169 let $wait_condition= SELECT COUNT(*) = 1 FROM INFORMATION_SCHEMA.PROCESSLIST
170 WHERE COMMAND = 'Sleep' AND USER = 'test_user';
171 --source include/wait_condition.inc
172 # 1. Just dump what we get
173 --replace_column 1 <ID> 3 <HOST_NAME> 6 <TIME>
174 --replace_result "init" STATE "starting" STATE "cleaning up" STATE
175 --sorted_result
176 SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST;
177 --replace_column 1 <ID> 3 <HOST_NAME> 6 <TIME>
178 --replace_result "init" STATE "starting" STATE "cleaning up" STATE
179 --sorted_result
180 SHOW FULL PROCESSLIST;
181 #
182 # Pull ID and TIME of the second connection
183 SELECT ID,TIME INTO @test_user_con1_id,@time FROM INFORMATION_SCHEMA.PROCESSLIST
184 WHERE COMMAND = 'Sleep' AND USER = 'test_user';
185 #
186 # 2. The second connection must (behaviour at least since 2007) have an
187 # ID = ID_of_previous_connection + 1
188 SELECT @test_user_con1_id = @default_id + 1
189 AS "Did we got the next higher PROCESSLIST ID?";
190 #
191 # 3. TIME must have a reasonable value
192 SELECT 0 <= @time < 10 AS "Has TIME a reasonable value?";
193 #
194 # 4. HOST must be for both connections similar (varying port on Win)
195 SELECT COUNT(*) = 3 AS "Is HOST LIKE 'localhost%'?"
196 FROM INFORMATION_SCHEMA.PROCESSLIST
197 WHERE HOST LIKE 'localhost%';
198 #
199 # 5. Check the remaining stuff
200 SELECT COUNT(*) = 1 AS "Expect 1"
201 FROM INFORMATION_SCHEMA.PROCESSLIST WHERE ID = @test_user_con1_id
202 AND USER = 'test_user' AND DB = 'information_schema'
203 AND Command = 'Sleep' AND State = '' AND INFO IS NULL;
204 #
205 # 6. Check that TIME increases
206 let $wait_timeout= 10;
207 let $wait_condition= SELECT COUNT(*) = 1 FROM INFORMATION_SCHEMA.PROCESSLIST
208 WHERE COMMAND = 'Sleep' AND USER = 'test_user'
209 AND TIME > @time;
210 --source include/wait_condition.inc
211
212
213 echo
214 # Ensure that the user test_user sees only connections with his username
215 # because he has not the PROCESS privilege.
216 #----------------------------------------------------------------------------
217 ;
218 echo
219 # ----- switch to connection con1 (user = test_user) -----
220 ;
221 connection con1;
222 --replace_column 1 <ID> 3 <HOST_NAME> 6 <TIME>
223 --replace_result "init" STATE "starting" STATE "cleaning up" STATE
224 --sorted_result
225 SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST;
226 --replace_column 1 <ID> 3 <HOST_NAME> 6 <TIME>
227 --replace_result "init" STATE "starting" STATE "cleaning up" STATE
228 --sorted_result
229 SHOW FULL PROCESSLIST;
230
231
232 echo
233 # Ensure that the user test_user sees all connections with his username.
234 #----------------------------------------------------------------------------
235 ;
236 echo
237 ----- establish connection con2 (user = test_user) ------
238 ;
239 connect (con2,localhost,test_user,ddictpass,information_schema);
240 echo
241 # ----- switch to connection default (user = root) -----
242 ;
243 connection default;
244 # If the testing box is under heavy load we might see within some of the
245 # next queries connection
246 # con2 with Command = 'Connect'
247 # con1 with INFO = 'SHOW FULL PROCESSLIST' and STATE = 'Writing to net'
248 # Both phases are too short to be checked.
249 echo
250 # Poll till all connections of 'test_user' are in a state with COMMAND = 'Sleep'
251 ;
252 let $wait_condition= SELECT COUNT(*) = 2 FROM INFORMATION_SCHEMA.PROCESSLIST
253 WHERE USER = 'test_user' AND COMMAND = 'Sleep';
254 --source include/wait_condition.inc
255 echo
256 # ----- switch to connection con2 (user = test_user) -----
257 ;
258 connection con2;
259 # Just dump what we get
260 --replace_column 1 <ID> 3 <HOST_NAME> 6 <TIME>
261 --replace_result "init" STATE "starting" STATE "cleaning up" STATE
262 --sorted_result
263 SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST;
264 --replace_column 1 <ID> 3 <HOST_NAME> 6 <TIME>
265 --replace_result "init" STATE "starting" STATE "cleaning up" STATE
266 --sorted_result
267 SHOW FULL PROCESSLIST;
268 #
269 echo
270 # ----- switch to connection default (user = root) -----
271 ;
272 connection default;
273 # Pull the ID of con2, we will need it later
274 SELECT ID INTO @test_user_con2_id FROM INFORMATION_SCHEMA.PROCESSLIST
275 WHERE ID <> @test_user_con1_id
276 AND USER = 'test_user' AND DB = 'information_schema';
277
278
279 echo
280 # Ensure we get correct information about a connection during work
281 #----------------------------------------------------------------------------
282 ;
283 echo
284 # ----- switch to connection con2 (user = test_user) -----
285 ;
286 connection con2;
287 # "Organise" a long running command to be observed by the root user.
288 echo
289 # Send a long enough running statement to the server, but do not
290 # wait till the result comes back.
291 ;
292 # Worst case scenario (=high I/O load on testing box):
293 # - My experience:
294 # Up to 2 seconds runtime per SELECT ... FROM INFORMATION_SCHEMA.PROCESSLIST
295 # in rare cases.
296 # - The following sequence contains ~ 4 of such SELECTs
297 # Therefore we sleep 10 seconds.
298 let $sleep_command =
299 SELECT sleep(10), 17;
300 send;
301 eval $sleep_command;
302 #
303 echo
304 # ----- switch to connection default (user = root) -----
305 ;
306 connection default;
307 echo
308 # Poll till connection con2 is in state 'User sleep'.
309 ;
310 # Expect to see within the processlist the other connection just during
311 # statement execution.
312 # - USER = 'test_user'
313 # - DB = 'information_schema'
314 # - Command = 'Query'(run without --ps-protocol)/'Execute' (run --ps-protocol)
315 # - TIME >= 0
316 # - State = 'User sleep'
317 # - INFO = $sleep_command
318 let $wait_condition=
319 SELECT COUNT(*) FROM INFORMATION_SCHEMA.PROCESSLIST
320 WHERE ID = @test_user_con2_id AND Command IN('Query','Execute')
321 AND State = 'User sleep' AND INFO IS NOT NULL ;
322 --source include/wait_condition.inc
323 # 1. Just dump what we get
324 --replace_column 1 <ID> 3 <HOST_NAME> 6 <TIME>
325 --replace_result "init" STATE "starting" STATE "cleaning up" STATE
326 --sorted_result
327 SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST;
328 --replace_column 1 <ID> 3 <HOST_NAME> 6 <TIME>
329 --replace_result "init" STATE "starting" STATE "cleaning up" STATE
330 --sorted_result
331 SHOW FULL PROCESSLIST;
332 #
333 # Pull some information about the connection con2
334 SELECT STATE, TIME, INFO INTO @state, @time, @info
335 FROM INFORMATION_SCHEMA.PROCESSLIST
336 WHERE ID = @test_user_con2_id;
337 # 2. TIME must have a reasonable value
338 SELECT 0 <= @time < 10 AS "Has TIME a reasonable value?";
339 # 3. STATE must be 'User sleep'
340 SELECT @state = 'User sleep' AS "Has STATE the expected value?";
341 # 4. INFO must fit
342 eval SELECT @info = '$sleep_command' AS "Has INFO the expected value?";
343 # 5. Check that TIME increases
344 let $wait_timeout= 10;
345 let $wait_condition= SELECT COUNT(*) = 1 FROM INFORMATION_SCHEMA.PROCESSLIST
346 WHERE ID = @test_user_con2_id AND INFO IS NOT NULL AND TIME > @time;
347 --source include/wait_condition.inc
348 echo
349 # ----- switch to connection con2 (user = testuser) -----
350 ;
351 connection con2;
352 echo
353 # Pull("reap") the result set from the statement executed with "send".
354 ;
355 reap;
356 echo
357 # ----- switch to connection default (user = root) -----
358 ;
359 connection default;
360 echo
361 # Poll till all connections of 'test_user' are in a state with COMMAND = 'Sleep'
362 ;
363 let $wait_timeout= 10;
364 let $wait_condition= SELECT COUNT(*) = 2 FROM INFORMATION_SCHEMA.PROCESSLIST
365 WHERE COMMAND = 'Sleep' AND USER = 'test_user';
366 --source include/wait_condition.inc
367
368
369 echo
370 # Ensure that we see that a connection "hangs" when colliding with a
371 # WRITE TABLE LOCK
372 #----------------------------------------------------------------------------
373 ;
374 LOCK TABLE test.t1 WRITE;
375 #
376 echo
377 # ----- switch to connection con2 (user = test_user) -----
378 ;
379 connection con2;
380 echo
381 # Send a statement to the server, but do not wait till the result
382 # comes back. We will pull this later.
383 ;
384 send
385 SELECT COUNT(*) FROM test.t1;
386 echo
387 # ----- switch to connection default (user = root) -----
388 ;
389 connection default;
390 echo
391 # Poll till INFO is no more NULL and State = 'Waiting for table metadata lock'.
392 ;
393 let $wait_condition= SELECT COUNT(*) FROM INFORMATION_SCHEMA.PROCESSLIST
394 WHERE INFO IS NOT NULL AND STATE = 'Waiting for table metadata lock';
395 --source include/wait_condition.inc
396 #
397 # Expect to see the state 'Waiting for table metadata lock' for the third
398 # connection because the SELECT collides with the WRITE TABLE LOCK.
399 --replace_column 1 <ID> 3 <HOST_NAME> 6 <TIME>
400 --replace_result "init" STATE "starting" STATE "cleaning up" STATE
401 --sorted_result
402 SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST;
403 UNLOCK TABLES;
404 #
405 echo
406 # ----- switch to connection con2 (user = test_user) -----
407 ;
408 connection con2;
409 echo
410 # Pull("reap") the result set from the statement executed with "send".
411 ;
412 reap;
413
414
415 echo
416 # Ensure that SHOW/SELECT processlist can handle extreme long commands
417 #----------------------------------------------------------------------------
418 ;
419 # We do not want to waste runtime, therefore we run the following test based
420 # on "Lock collision" and not with some "sleep(10)".
421 echo
422 # ----- switch to connection default (user = root) -----
423 ;
424 connection default;
425 LOCK TABLE test.t1 WRITE;
426 #
427 echo
428 # ----- switch to connection con2 (user = test_user) -----
429 ;
430 connection con2;
431 echo
432 # Send a long (~20 KB code) statement to the server, but do not wait
433 # till the result comes back. We will pull this later.
434 ;
435 let $string=
436 `SELECT CONCAT('BEGIN-',
437 REPEAT('This is the representative of a very long statement.',400),
438 '-END')`;
439 let $my_statement =
440 SELECT count(*),'$string' AS "Long string" FROM test.t1;
441 send;
442 eval $my_statement;
443 echo
444 # ----- switch to connection default (user = root) -----
445 ;
446 connection default;
447 echo
448 # Poll till INFO is no more NULL and State = 'Waiting for table metadata lock'.
449 ;
450 let $wait_condition= SELECT COUNT(*) FROM INFORMATION_SCHEMA.PROCESSLIST
451 WHERE INFO IS NOT NULL AND STATE = 'Waiting for table metadata lock';
452 --source include/wait_condition.inc
453 echo
454 # Expect result:
455 # Statement Content of INFO
456 # SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST Complete statement
457 # SHOW FULL PROCESSLIST Complete statement
458 # SHOW PROCESSLIST statement truncated after 100 char
459 ;
460 --replace_column 1 <ID> 3 <HOST_NAME> 5 <COMMAND> 6 <TIME> 7 <STATE>
461 --replace_result "init" STATE "starting" STATE "cleaning up" STATE
462 --sorted_result
463 SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST;
464 --replace_column 1 <ID> 3 <HOST_NAME> 5 <COMMAND> 6 <TIME> 7 <STATE>
465 --replace_result "init" STATE "starting" STATE "cleaning up" STATE
466 --sorted_result
467 SHOW FULL PROCESSLIST;
468 --replace_column 1 <ID> 3 <HOST_NAME> 5 <COMMAND> 6 <TIME> 7 <STATE>
469 --replace_result "init" STATE "starting" STATE "cleaning up" STATE
470 --sorted_result
471 SHOW PROCESSLIST;
472 UNLOCK TABLES;
473 echo
474 # ----- switch to connection con2 (user = test_user) -----
475 ;
476 connection con2;
477 echo
478 # Pull("reap") the result set from the monster statement executed with "send".
479 ;
480 reap;
481
482
483 # Cleanup
484 echo
485 # ----- switch to connection default (user = root) -----
486 ;
487 connection default;
488 echo
489 ----- disconnect con1 and con2 -----
490 ;
491 disconnect con1;
492 disconnect con2;
493 DROP USER test_user@'localhost';
494 DROP TABLE test.t1;