1SET @old_general_log= @@global.general_log; 2SET @old_general_log_file= @@global.general_log_file; 3SET @old_slow_query_log= @@global.slow_query_log; 4SET @old_slow_query_log_file= @@global.slow_query_log_file; 5set global general_log= OFF; 6truncate table mysql.general_log; 7truncate table mysql.slow_log; 8show global variables 9where Variable_name = 'log' or Variable_name = 'log_slow_queries' or 10Variable_name = 'general_log' or Variable_name = 'slow_query_log'; 11Variable_name Value 12general_log OFF 13slow_query_log OFF 14flush logs; 15set global general_log= ON; 16create table t1(f1 int); 17select * from mysql.general_log; 18event_time user_host thread_id server_id command_type argument 19TIMESTAMP USER_HOST # 1 Query create table t1(f1 int) 20TIMESTAMP USER_HOST # 1 Query select * from mysql.general_log 21set global general_log= OFF; 22drop table t1; 23select * from mysql.general_log; 24event_time user_host thread_id server_id command_type argument 25TIMESTAMP USER_HOST # 1 Query create table t1(f1 int) 26TIMESTAMP USER_HOST # 1 Query select * from mysql.general_log 27TIMESTAMP USER_HOST # 1 Query set global general_log= OFF 28set global general_log= ON; 29flush logs; 30show global variables 31where Variable_name = 'log' or Variable_name = 'log_slow_queries' or 32Variable_name = 'general_log' or Variable_name = 'slow_query_log'; 33Variable_name Value 34general_log ON 35slow_query_log OFF 36# Establish connection con1 (user=root) 37# Switch to connection con1 38set @long_query_time = <long_query_time>; 39set session long_query_time = @long_query_time; 40select sleep(@long_query_time + 1); 41sleep(@long_query_time + 1) 420 43select * from mysql.slow_log where sql_text NOT LIKE '%slow_log%'; 44start_time user_host query_time lock_time rows_sent rows_examined db last_insert_id insert_id server_id sql_text thread_id 45# Switch to connection default 46set global slow_query_log= ON; 47# Switch to connection con1 48set session long_query_time = @long_query_time; 49select sleep(@long_query_time + 1); 50sleep(@long_query_time + 1) 510 52select * from mysql.slow_log where sql_text NOT LIKE '%slow_log%'; 53start_time user_host query_time lock_time rows_sent rows_examined db last_insert_id insert_id server_id sql_text thread_id 54TIMESTAMP USER_HOST QUERY_TIME 00:00:00.000000 1 0 test 0 0 1 QUERY THREAD_ID 55# Switch to connection default 56show global variables 57where Variable_name = 'log' or Variable_name = 'log_slow_queries' or 58Variable_name = 'general_log' or Variable_name = 'slow_query_log'; 59Variable_name Value 60general_log ON 61slow_query_log ON 62set global general_log= ON; 63set global general_log= OFF; 64set global general_log= OFF; 65set global slow_query_log= ON; 66set global slow_query_log= OFF; 67set global slow_query_log= OFF; 68set global general_log= ON; 69truncate table mysql.general_log; 70create table t1(f1 int); 71drop table t1; 72select * from mysql.general_log; 73event_time user_host thread_id server_id command_type argument 74TIMESTAMP USER_HOST # 1 Query create table t1(f1 int) 75TIMESTAMP USER_HOST # 1 Query drop table t1 76TIMESTAMP USER_HOST # 1 Query select * from mysql.general_log 77set global general_log= OFF; 78truncate table mysql.general_log; 79select * from mysql.general_log; 80event_time user_host thread_id server_id command_type argument 81set global general_log= ON; 82show global variables 83where Variable_name = 'log' or Variable_name = 'log_slow_queries' or 84Variable_name = 'general_log' or Variable_name = 'slow_query_log'; 85Variable_name Value 86general_log ON 87slow_query_log OFF 88show variables like 'general_log_file'; 89Variable_name Value 90general_log_file # 91show variables like 'slow_query_log_file'; 92Variable_name Value 93slow_query_log_file # 94show variables like 'log_output'; 95Variable_name Value 96log_output FILE,TABLE 97set global general_log_file='/not existing path/log.master'; 98ERROR 42000: Variable 'general_log_file' can't be set to the value of '/not existing path/log.master' 99set global general_log_file='MYSQLTEST_VARDIR'; 100ERROR 42000: Variable 'general_log_file' can't be set to the value of 'MYSQLTEST_VARDIR' 101set global general_log_file=''; 102ERROR 42000: Variable 'general_log_file' can't be set to the value of '' 103show variables like 'general_log_file'; 104Variable_name Value 105general_log_file # 106set global general_log= OFF; 107set global general_log_file='MYSQLTEST_VARDIR/tmp/log.master'; 108set global general_log= ON; 109create table t1(f1 int); 110drop table t1; 111set global general_log= OFF; 112set global general_log_file=default; 113set global general_log= ON; 114create table t1(f1 int); 115drop table t1; 116show variables like 'general_log_file'; 117Variable_name Value 118general_log_file # 119show variables like 'slow_query_log_file'; 120Variable_name Value 121slow_query_log_file # 122set global general_log= default; 123set global slow_query_log= default; 124set global general_log_file= default; 125set global slow_query_log_file= default; 126show variables like 'general_log'; 127Variable_name Value 128general_log OFF 129show variables like 'slow_query_log'; 130Variable_name Value 131slow_query_log OFF 132set global general_log=ON; 133set global log_output=default; 134show variables like 'log_output'; 135Variable_name Value 136log_output FILE 137set global general_log=OFF; 138set global log_output=FILE; 139truncate table mysql.general_log; 140show variables like 'log_output'; 141Variable_name Value 142log_output FILE 143set global general_log=ON; 144create table t1(f1 int); 145select * from mysql.general_log; 146event_time user_host thread_id server_id command_type argument 147set global general_log=OFF; 148set global log_output="FILE,TABLE"; 149show variables like 'log_output'; 150Variable_name Value 151log_output FILE,TABLE 152set global general_log=ON; 153drop table t1; 154select * from mysql.general_log; 155event_time user_host thread_id server_id command_type argument 156TIMESTAMP USER_HOST # 1 Query drop table t1 157TIMESTAMP USER_HOST # 1 Query select * from mysql.general_log 158SET @@global.general_log = @old_general_log; 159SET @@global.general_log_file = @old_general_log_file; 160SET @@global.slow_query_log = @old_slow_query_log; 161SET @@global.slow_query_log_file = @old_slow_query_log_file; 162SET GLOBAL general_log = ON; 163SET GLOBAL slow_query_log = ON; 164FLUSH TABLES WITH READ LOCK; 165SET GLOBAL general_log = OFF; 166SET GLOBAL slow_query_log = OFF; 167UNLOCK TABLES; 168FLUSH TABLES WITH READ LOCK; 169SET GLOBAL general_log = ON; 170SET GLOBAL slow_query_log = ON; 171UNLOCK TABLES; 172SET GLOBAL READ_ONLY = ON; 173SET GLOBAL general_log = OFF; 174SET GLOBAL slow_query_log = OFF; 175SET GLOBAL READ_ONLY = OFF; 176SET GLOBAL READ_ONLY = ON; 177SET GLOBAL general_log = ON; 178SET GLOBAL slow_query_log = ON; 179SET GLOBAL READ_ONLY = OFF; 180SET GLOBAL general_log = @old_general_log; 181SET GLOBAL slow_query_log = @old_slow_query_log; 182SET GLOBAL general_log_file= CONCAT('/not existing path/log.maste', 'r'); 183ERROR 42000: Variable 'general_log_file' can't be set to the value of '/not existing path/log.master' 184SET GLOBAL general_log_file= NULL; 185ERROR 42000: Variable 'general_log_file' can't be set to the value of 'NULL' 186SET GLOBAL slow_query_log_file= CONCAT('/not existing path/log.maste', 'r'); 187ERROR 42000: Variable 'slow_query_log_file' can't be set to the value of '/not existing path/log.master' 188SET GLOBAL slow_query_log_file= NULL; 189ERROR 42000: Variable 'slow_query_log_file' can't be set to the value of 'NULL' 190SET GLOBAL general_log_file= @old_general_log_file; 191SET GLOBAL slow_query_log_file= @old_slow_query_log_file; 192 193# -- 194# -- Bug#32748: Inconsistent handling of assignments to 195# -- general_log_file/slow_query_log_file. 196# -- 197 198SET GLOBAL general_log_file = 'bug32748.query.log'; 199SET GLOBAL slow_query_log_file = 'bug32748.slow.log'; 200 201SHOW VARIABLES LIKE '%log_file'; 202Variable_name Value 203general_log_file bug32748.query.log 204slow_query_log_file bug32748.slow.log 205 206SET GLOBAL general_log_file = @old_general_log_file; 207SET GLOBAL slow_query_log_file = @old_slow_query_log_file; 208 209# -- End of Bug#32748. 210# 211# Bug #49756 Rows_examined is always 0 in the slow query log 212# for update statements 213# 214SET @old_log_output = @@global.log_output; 215SET GLOBAL log_output = "TABLE"; 216SET GLOBAL slow_query_log = ON; 217SET GLOBAL long_query_time = 0.001; 218TRUNCATE TABLE mysql.slow_log; 219CREATE TABLE t1 (a INT); 220CREATE TABLE t2 (b INT, PRIMARY KEY (b)); 221INSERT INTO t2 VALUES (3),(4); 222INSERT INTO t1 VALUES (1+sleep(.01)),(2); 223INSERT INTO t1 SELECT b+sleep(.01) from t2; 224UPDATE t1 SET a=a+sleep(.01) WHERE a>2; 225UPDATE t1 SET a=a+sleep(.01) ORDER BY a DESC; 226UPDATE t2 set b=b+sleep(.01) limit 1; 227UPDATE t1 SET a=a+sleep(.01) WHERE a in (SELECT b from t2); 228DELETE FROM t1 WHERE a=a+sleep(.01) ORDER BY a LIMIT 2; 229SELECT rows_examined,sql_text FROM mysql.slow_log; 230rows_examined sql_text 2310 INSERT INTO t1 VALUES (1+sleep(.01)),(2) 2322 INSERT INTO t1 SELECT b+sleep(.01) from t2 2334 UPDATE t1 SET a=a+sleep(.01) WHERE a>2 2348 UPDATE t1 SET a=a+sleep(.01) ORDER BY a DESC 2351 UPDATE t2 set b=b+sleep(.01) limit 1 2364 UPDATE t1 SET a=a+sleep(.01) WHERE a in (SELECT b from t2) 2376 DELETE FROM t1 WHERE a=a+sleep(.01) ORDER BY a LIMIT 2 238DROP TABLE t1,t2; 239TRUNCATE TABLE mysql.slow_log; 240# end of bug#49756 241End of 5.1 tests 242 243# -- 244# -- Bug#38124: "general_log_file" variable silently unset when 245# -- using expression 246# -- 247SET GLOBAL general_log_file = DEFAULT; 248SELECT @@general_log_file INTO @my_glf; 249SET GLOBAL general_log_file = 'BUG38124.LOG'; 250SELECT @@general_log_file; 251@@general_log_file 252BUG38124.LOG 253SET GLOBAL general_log_file = concat('BUG38124-2.LOG'); 254SELECT @@general_log_file; 255@@general_log_file 256BUG38124-2.LOG 257SET GLOBAL general_log_file = substr('BUG38124-2.LOG',3,6); 258SELECT @@general_log_file; 259@@general_log_file 260G38124 261SET GLOBAL general_log_file = DEFAULT; 262SELECT @@general_log_file = @my_glf; 263@@general_log_file = @my_glf 2641 265SET GLOBAL general_log_file = @old_general_log_file; 266# Close connection con1 267SET GLOBAL long_query_time = DEFAULT; 268SET GLOBAL log_output = @old_log_output; 269SET global general_log = @old_general_log; 270SET global general_log_file = @old_general_log_file; 271SET global slow_query_log = @old_slow_query_log; 272SET global slow_query_log_file = @old_slow_query_log_file; 273