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