1### t/log_state.test ### 2# 3 4--source include/not_embedded.inc 5--source include/have_csv.inc 6 7call mtr.add_suppression("options .* --log_slow_queries is not set"); 8 9# Several subtests modify global variables. Save the initial values only here, 10# but reset to the initial values per subtest. 11SET @old_general_log= @@global.general_log; 12SET @old_general_log_file= @@global.general_log_file; 13SET @old_slow_query_log= @@global.slow_query_log; 14SET @old_slow_query_log_file= @@global.slow_query_log_file; 15set @save_long_query_time=@@long_query_time; 16 17--disable_ps_protocol 18 19set global general_log= OFF; 20truncate table mysql.general_log; 21truncate table mysql.slow_log; 22show global variables 23where Variable_name = 'general_log' or Variable_name = 'slow_query_log'; 24flush logs; 25set global general_log= ON; 26create table t1(f1 int); 27--replace_column 1 TIMESTAMP 2 USER_HOST 3 # 28select * from mysql.general_log; 29set global general_log= OFF; 30drop table t1; 31--replace_column 1 TIMESTAMP 2 USER_HOST 3 # 32select * from mysql.general_log; 33set global general_log= ON; 34flush logs; 35show global variables 36where Variable_name = 'general_log' or Variable_name = 'slow_query_log'; 37 38connect (con1,localhost,root,,); 39connection con1; 40# Please increase @long_query_time if the corresponding selects show an 41# additional unexpected entry like 42# start_time ... sql_text 43# TIMESTAMP ... set session long_query_time=... 44# (Bug#40377 sporadic pushbuild failure in log_state: result mismatch) 45--replace_result 2 <long_query_time> 46set @long_query_time = 2; 47set session long_query_time = @long_query_time; 48select sleep(@long_query_time + 1); 49--replace_column 1 TIMESTAMP 2 USER_HOST 3 QUERY_TIME 12 THREAD_ID 50select * from mysql.slow_log where sql_text NOT LIKE '%slow_log%'; 51connection default; 52 53set global slow_query_log= ON; 54set local slow_query_log= ON; 55connection con1; 56set session long_query_time = @long_query_time; 57select sleep(@long_query_time + 1); 58--replace_column 1 TIMESTAMP 2 USER_HOST 3 QUERY_TIME 12 THREAD_ID 59select * from mysql.slow_log where sql_text NOT LIKE '%slow_log%'; 60set local slow_query_log= ON; 61select sleep(@long_query_time + 2); 62--replace_column 1 TIMESTAMP 2 USER_HOST 3 QUERY_TIME 12 THREAD_ID 63select * from mysql.slow_log where sql_text NOT LIKE '%slow_log%'; 64 65connection default; 66show global variables 67where Variable_name = 'general_log' or Variable_name = 'slow_query_log'; 68 69set global general_log= ON; 70set global general_log= OFF; 71set global general_log= OFF; 72set global slow_query_log= ON; 73set global slow_query_log= OFF; 74set global slow_query_log= OFF; 75set local slow_query_log= ON; 76 77set global general_log= ON; 78truncate table mysql.general_log; 79create table t1(f1 int); 80drop table t1; 81--replace_column 1 TIMESTAMP 2 USER_HOST 3 # 82select * from mysql.general_log; 83set global general_log= OFF; 84truncate table mysql.general_log; 85--replace_column 1 TIMESTAMP 2 USER_HOST 3 # 86select * from mysql.general_log; 87set global general_log= ON; 88show global variables 89where Variable_name = 'general_log' or Variable_name = 'slow_query_log'; 90 91--replace_column 2 # 92show variables like 'general_log_file'; 93--replace_column 2 # 94show variables like 'slow_query_log_file'; 95show variables like 'log_output'; 96 97# Can't set general_log_file to a non existing file 98--error ER_WRONG_VALUE_FOR_VAR 99set global general_log_file='/not existing path/log.master'; 100 101# Can't set general_log_file to a directory 102--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR 103--error ER_WRONG_VALUE_FOR_VAR 104eval set global general_log_file='$MYSQLTEST_VARDIR'; 105 106# Can't set general_log_file to empty string 107--error ER_WRONG_VALUE_FOR_VAR 108set global general_log_file=''; 109 110--replace_column 2 # 111show variables like 'general_log_file'; 112set global general_log= OFF; 113--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR 114eval set global general_log_file='$MYSQLTEST_VARDIR/tmp/log.master'; 115set global general_log= ON; 116create table t1(f1 int); 117drop table t1; 118set global general_log= OFF; 119set global general_log_file=default; 120set global general_log= ON; 121create table t1(f1 int); 122drop table t1; 123--replace_column 2 # 124show variables like 'general_log_file'; 125--replace_column 2 # 126show variables like 'slow_query_log_file'; 127 128set global general_log= default; 129set global slow_query_log= default; 130set global general_log_file= default; 131set global slow_query_log_file= default; 132show variables like 'general_log'; 133show variables like 'slow_query_log'; 134show global variables like 'slow_query_log'; 135set global general_log=ON; 136set global log_output=default; 137show variables like 'log_output'; 138set global general_log=OFF; 139set global log_output=FILE; 140truncate table mysql.general_log; 141show variables like 'log_output'; 142set global general_log=ON; 143create table t1(f1 int); 144--replace_column 1 TIMESTAMP 2 USER_HOST 3 # 145select * from mysql.general_log; 146set global general_log=OFF; 147set global log_output="FILE,TABLE"; 148show variables like 'log_output'; 149set global general_log=ON; 150drop table t1; 151--replace_column 1 TIMESTAMP 2 USER_HOST 3 # 152select * from mysql.general_log; 153 154# Reset to initial values 155SET @@global.general_log = @old_general_log; 156SET @@global.general_log_file = @old_general_log_file; 157SET @@global.slow_query_log = @old_slow_query_log; 158SET @@global.slow_query_log_file = @old_slow_query_log_file; 159 160########################################################################### 161 162# 163# Bug#29129 (Resetting general_log while the GLOBAL READ LOCK is set causes 164# a deadlock) 165# 166 167# Test ON->OFF transition under a GLOBAL READ LOCK 168SET GLOBAL general_log = ON; 169SET GLOBAL slow_query_log = ON; 170 171FLUSH TABLES WITH READ LOCK; 172 173SET GLOBAL general_log = OFF; 174SET GLOBAL slow_query_log = OFF; 175 176UNLOCK TABLES; 177 178# Test OFF->ON transition under a GLOBAL READ LOCK 179FLUSH TABLES WITH READ LOCK; 180 181SET GLOBAL general_log = ON; 182SET GLOBAL slow_query_log = ON; 183 184UNLOCK TABLES; 185 186# Test ON->OFF transition under a GLOBAL READ_ONLY 187SET GLOBAL READ_ONLY = ON; 188 189SET GLOBAL general_log = OFF; 190SET GLOBAL slow_query_log = OFF; 191 192SET GLOBAL READ_ONLY = OFF; 193 194# Test OFF->ON transition under a GLOBAL READ_ONLY 195SET GLOBAL READ_ONLY = ON; 196 197SET GLOBAL general_log = ON; 198SET GLOBAL slow_query_log = ON; 199 200SET GLOBAL READ_ONLY = OFF; 201 202# Reset to initial values 203SET GLOBAL general_log = @old_general_log; 204SET GLOBAL slow_query_log = @old_slow_query_log; 205 206 207########################################################################### 208 209# 210# Bug#31604: server crash when setting slow_query_log_file/general_log_file 211# 212 213--error ER_WRONG_VALUE_FOR_VAR 214SET GLOBAL general_log_file= CONCAT('/not existing path/log.maste', 'r'); 215--error ER_WRONG_VALUE_FOR_VAR 216SET GLOBAL general_log_file= NULL; 217--error ER_WRONG_VALUE_FOR_VAR 218SET GLOBAL slow_query_log_file= CONCAT('/not existing path/log.maste', 'r'); 219--error ER_WRONG_VALUE_FOR_VAR 220SET GLOBAL slow_query_log_file= NULL; 221 222# Reset to initial values in case a setting above was successful. 223SET GLOBAL general_log_file= @old_general_log_file; 224SET GLOBAL slow_query_log_file= @old_slow_query_log_file; 225 226########################################################################### 227 228--echo 229--echo # -- 230--echo # -- Bug#32748: Inconsistent handling of assignments to 231--echo # -- general_log_file/slow_query_log_file. 232--echo # -- 233 234--echo 235SET GLOBAL general_log_file = 'bug32748.query.log'; 236SET GLOBAL slow_query_log_file = 'bug32748.slow.log'; 237 238--echo 239SHOW VARIABLES LIKE '%log_file'; 240 241# Reset to initial values 242--echo 243SET GLOBAL general_log_file = @old_general_log_file; 244SET GLOBAL slow_query_log_file = @old_slow_query_log_file; 245 246--echo 247--echo # -- End of Bug#32748. 248 249 250########################################################################### 251 252--echo # 253--echo # Bug #49756 Rows_examined is always 0 in the slow query log 254--echo # for update statements 255--echo # 256 257SET @old_log_output = @@global.log_output; 258SET GLOBAL log_output = "TABLE"; 259SET GLOBAL slow_query_log = ON; 260SET GLOBAL long_query_time = 0.001; 261 262# clear slow_log of any residual slow queries 263TRUNCATE TABLE mysql.slow_log; 264CREATE TABLE t1 (a INT); 265CREATE TABLE t2 (b INT, PRIMARY KEY (b)); 266INSERT INTO t2 VALUES (3),(4); 267 268connect (con2,localhost,root,,); 269INSERT INTO t1 VALUES (1+sleep(.02)),(2); 270INSERT INTO t1 SELECT b+sleep(.02) from t2; 271UPDATE t1 SET a=a+sleep(.02) WHERE a>2; 272UPDATE t1 SET a=a+sleep(.02) ORDER BY a DESC; 273UPDATE t2 set b=b+sleep(.02) limit 1; 274UPDATE t1 SET a=a+sleep(.02) WHERE a in (SELECT b from t2); 275DELETE FROM t1 WHERE a=a+sleep(.02) ORDER BY a LIMIT 2; 276 277SELECT rows_examined,sql_text FROM mysql.slow_log; 278disconnect con2; 279connection default; 280DROP TABLE t1,t2; 281TRUNCATE TABLE mysql.slow_log; 282 283--echo # end of bug#49756 284 285 286--echo End of 5.1 tests 287 288 289########################################################################### 290 291--echo 292--echo # -- 293--echo # -- Bug#38124: "general_log_file" variable silently unset when 294--echo # -- using expression 295--echo # -- 296 297# Store away the special DEFAULT value so we 298# can compare it later, then try to set the 299# general_log_file using different functions 300# and expressions. 301 302SET GLOBAL general_log_file = DEFAULT; 303SELECT @@general_log_file INTO @my_glf; 304 305SET GLOBAL general_log_file = 'BUG38124.LOG'; 306SELECT @@general_log_file; 307 308SET GLOBAL general_log_file = concat('BUG38124-2.LOG'); 309SELECT @@general_log_file; 310 311SET GLOBAL general_log_file = substr('BUG38124-2.LOG',3,6); 312SELECT @@general_log_file; 313 314SET GLOBAL general_log_file = DEFAULT; 315SELECT @@general_log_file = @my_glf; 316 317 318## Reset to initial values 319SET GLOBAL general_log_file = @old_general_log_file; 320 321 322--enable_ps_protocol 323 324# 325# Cleanup 326# 327# Disconnect must be done last to avoid delayed 'Quit' message in general log 328disconnect con1; 329# set back the saved default values 330connection default; 331 332# Reset global system variables to initial values if forgotten somewhere above. 333SET GLOBAL long_query_time = @save_long_query_time; 334SET GLOBAL log_output = @old_log_output; 335SET global general_log = @old_general_log; 336SET global general_log_file = @old_general_log_file; 337SET global slow_query_log = @old_slow_query_log; 338SET global slow_query_log_file = @old_slow_query_log_file; 339 340# Remove the log file that was created in the "default location" 341# i.e var/run 342--remove_file $MYSQLTEST_VARDIR/tmp/log.master 343