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