Edit File: log_state.result
SET @old_general_log= @@global.general_log; SET @old_general_log_file= @@global.general_log_file; SET @old_slow_query_log= @@global.slow_query_log; SET @old_slow_query_log_file= @@global.slow_query_log_file; set global general_log= OFF; truncate table mysql.general_log; truncate table mysql.slow_log; show global variables where Variable_name = 'log' or Variable_name = 'log_slow_queries' or Variable_name = 'general_log' or Variable_name = 'slow_query_log'; Variable_name Value general_log OFF log OFF log_slow_queries OFF slow_query_log OFF flush logs; set global general_log= ON; create table t1(f1 int); select * from mysql.general_log; event_time user_host thread_id server_id command_type argument TIMESTAMP USER_HOST # 1 Query create table t1(f1 int) TIMESTAMP USER_HOST # 1 Query select * from mysql.general_log set global general_log= OFF; drop table t1; select * from mysql.general_log; event_time user_host thread_id server_id command_type argument TIMESTAMP USER_HOST # 1 Query create table t1(f1 int) TIMESTAMP USER_HOST # 1 Query select * from mysql.general_log TIMESTAMP USER_HOST # 1 Query set global general_log= OFF set global general_log= ON; flush logs; show global variables where Variable_name = 'log' or Variable_name = 'log_slow_queries' or Variable_name = 'general_log' or Variable_name = 'slow_query_log'; Variable_name Value general_log ON log ON log_slow_queries OFF slow_query_log OFF # Establish connection con1 (user=root) # Switch to connection con1 set @long_query_time = <long_query_time>; set session long_query_time = @long_query_time; select sleep(@long_query_time + 1); sleep(@long_query_time + 1) 0 select * from mysql.slow_log where sql_text NOT LIKE '%slow_log%'; start_time user_host query_time lock_time rows_sent rows_examined db last_insert_id insert_id server_id sql_text # Switch to connection default set global slow_query_log= ON; # Switch to connection con1 set session long_query_time = @long_query_time; select sleep(@long_query_time + 1); sleep(@long_query_time + 1) 0 select * from mysql.slow_log where sql_text NOT LIKE '%slow_log%'; start_time user_host query_time lock_time rows_sent rows_examined db last_insert_id insert_id server_id sql_text TIMESTAMP USER_HOST QUERY_TIME 00:00:00 1 0 test 0 0 1 select sleep(@long_query_time + 1) # Switch to connection default show global variables where Variable_name = 'log' or Variable_name = 'log_slow_queries' or Variable_name = 'general_log' or Variable_name = 'slow_query_log'; Variable_name Value general_log ON log ON log_slow_queries ON slow_query_log ON set global general_log= ON; set global general_log= OFF; set global general_log= OFF; set global slow_query_log= ON; set global slow_query_log= OFF; set global slow_query_log= OFF; set global general_log= ON; truncate table mysql.general_log; create table t1(f1 int); drop table t1; select * from mysql.general_log; event_time user_host thread_id server_id command_type argument TIMESTAMP USER_HOST # 1 Query create table t1(f1 int) TIMESTAMP USER_HOST # 1 Query drop table t1 TIMESTAMP USER_HOST # 1 Query select * from mysql.general_log set global general_log= OFF; truncate table mysql.general_log; select * from mysql.general_log; event_time user_host thread_id server_id command_type argument set global general_log= ON; show global variables where Variable_name = 'log' or Variable_name = 'log_slow_queries' or Variable_name = 'general_log' or Variable_name = 'slow_query_log'; Variable_name Value general_log ON log ON log_slow_queries OFF slow_query_log OFF show variables like 'general_log_file'; Variable_name Value general_log_file # show variables like 'slow_query_log_file'; Variable_name Value slow_query_log_file # show variables like 'log_output'; Variable_name Value log_output FILE,TABLE set global general_log_file='/not existing path/log.master'; ERROR 42000: Variable 'general_log_file' can't be set to the value of '/not existing path/log.master' set global general_log_file='MYSQLTEST_VARDIR'; ERROR 42000: Variable 'general_log_file' can't be set to the value of 'MYSQLTEST_VARDIR' set global general_log_file=''; ERROR 42000: Variable 'general_log_file' can't be set to the value of '' show variables like 'general_log_file'; Variable_name Value general_log_file # set global general_log= OFF; set global general_log_file='MYSQLTEST_VARDIR/tmp/log.master'; set global general_log= ON; create table t1(f1 int); drop table t1; set global general_log= OFF; set global general_log_file=default; set global general_log= ON; create table t1(f1 int); drop table t1; show variables like 'general_log_file'; Variable_name Value general_log_file # show variables like 'slow_query_log_file'; Variable_name Value slow_query_log_file # set global general_log= default; set global slow_query_log= default; set global general_log_file= default; set global slow_query_log_file= default; show variables like 'general_log'; Variable_name Value general_log OFF show variables like 'slow_query_log'; Variable_name Value slow_query_log OFF set global general_log=ON; set global log_output=default; show variables like 'log_output'; Variable_name Value log_output FILE set global general_log=OFF; set global log_output=FILE; truncate table mysql.general_log; show variables like 'log_output'; Variable_name Value log_output FILE set global general_log=ON; create table t1(f1 int); select * from mysql.general_log; event_time user_host thread_id server_id command_type argument set global general_log=OFF; set global log_output="FILE,TABLE"; show variables like 'log_output'; Variable_name Value log_output FILE,TABLE set global general_log=ON; drop table t1; select * from mysql.general_log; event_time user_host thread_id server_id command_type argument TIMESTAMP USER_HOST # 1 Query drop table t1 TIMESTAMP USER_HOST # 1 Query select * from mysql.general_log SET @@global.general_log = @old_general_log; SET @@global.general_log_file = @old_general_log_file; SET @@global.slow_query_log = @old_slow_query_log; SET @@global.slow_query_log_file = @old_slow_query_log_file; SET GLOBAL general_log = ON; SET GLOBAL slow_query_log = ON; FLUSH TABLES WITH READ LOCK; SET GLOBAL general_log = OFF; SET GLOBAL slow_query_log = OFF; UNLOCK TABLES; FLUSH TABLES WITH READ LOCK; SET GLOBAL general_log = ON; SET GLOBAL slow_query_log = ON; UNLOCK TABLES; SET GLOBAL READ_ONLY = ON; SET GLOBAL general_log = OFF; SET GLOBAL slow_query_log = OFF; SET GLOBAL READ_ONLY = OFF; SET GLOBAL READ_ONLY = ON; SET GLOBAL general_log = ON; SET GLOBAL slow_query_log = ON; SET GLOBAL READ_ONLY = OFF; SET GLOBAL general_log = @old_general_log; SET GLOBAL slow_query_log = @old_slow_query_log; SET GLOBAL general_log = ON; SHOW VARIABLES LIKE 'general_log'; Variable_name Value general_log ON SHOW VARIABLES LIKE 'log'; Variable_name Value log ON SELECT @@general_log, @@log; @@general_log @@log 1 1 SET GLOBAL log = 0; Warnings: Warning 1287 '@@log' is deprecated and will be removed in a future release. Please use '@@general_log' instead SHOW VARIABLES LIKE 'general_log'; Variable_name Value general_log OFF SHOW VARIABLES LIKE 'log'; Variable_name Value log OFF SELECT @@general_log, @@log; @@general_log @@log 0 0 SET GLOBAL general_log = 1; SHOW VARIABLES LIKE 'general_log'; Variable_name Value general_log ON SHOW VARIABLES LIKE 'log'; Variable_name Value log ON SELECT @@general_log, @@log; @@general_log @@log 1 1 SHOW VARIABLES LIKE 'slow_query_log'; Variable_name Value slow_query_log OFF SHOW VARIABLES LIKE 'log_slow_queries'; Variable_name Value log_slow_queries OFF SELECT @@slow_query_log, @@log_slow_queries; @@slow_query_log @@log_slow_queries 0 0 SET GLOBAL log_slow_queries = 0; Warnings: Warning 1287 '@@log_slow_queries' is deprecated and will be removed in a future release. Please use '@@slow_query_log' instead SHOW VARIABLES LIKE 'slow_query_log'; Variable_name Value slow_query_log OFF SHOW VARIABLES LIKE 'log_slow_queries'; Variable_name Value log_slow_queries OFF SELECT @@slow_query_log, @@log_slow_queries; @@slow_query_log @@log_slow_queries 0 0 SET GLOBAL slow_query_log = 1; SHOW VARIABLES LIKE 'slow_query_log'; Variable_name Value slow_query_log ON SHOW VARIABLES LIKE 'log_slow_queries'; Variable_name Value log_slow_queries ON SELECT @@slow_query_log, @@log_slow_queries; @@slow_query_log @@log_slow_queries 1 1 SET GLOBAL general_log = @old_general_log; SET GLOBAL slow_query_log = @old_slow_query_log; SET GLOBAL general_log_file= CONCAT('/not existing path/log.maste', 'r'); ERROR 42000: Variable 'general_log_file' can't be set to the value of '/not existing path/log.master' SET GLOBAL general_log_file= NULL; ERROR 42000: Variable 'general_log_file' can't be set to the value of 'NULL' SET GLOBAL slow_query_log_file= CONCAT('/not existing path/log.maste', 'r'); ERROR 42000: Variable 'slow_query_log_file' can't be set to the value of '/not existing path/log.master' SET GLOBAL slow_query_log_file= NULL; ERROR 42000: Variable 'slow_query_log_file' can't be set to the value of 'NULL' SET GLOBAL general_log_file= @old_general_log_file; SET GLOBAL slow_query_log_file= @old_slow_query_log_file; # -- # -- Bug#32748: Inconsistent handling of assignments to # -- general_log_file/slow_query_log_file. # -- SET GLOBAL general_log_file = 'bug32748.query.log'; SET GLOBAL slow_query_log_file = 'bug32748.slow.log'; SHOW VARIABLES LIKE '%log_file'; Variable_name Value general_log_file bug32748.query.log slow_query_log_file bug32748.slow.log SET GLOBAL general_log_file = @old_general_log_file; SET GLOBAL slow_query_log_file = @old_slow_query_log_file; # -- End of Bug#32748. deprecated: SET GLOBAL log = 0; Warnings: Warning 1287 '@@log' is deprecated and will be removed in a future release. Please use '@@general_log' instead SET GLOBAL log_slow_queries = 0; Warnings: Warning 1287 '@@log_slow_queries' is deprecated and will be removed in a future release. Please use '@@slow_query_log' instead SET GLOBAL log = DEFAULT; Warnings: Warning 1287 '@@log' is deprecated and will be removed in a future release. Please use '@@general_log' instead SET GLOBAL log_slow_queries = DEFAULT; Warnings: Warning 1287 '@@log_slow_queries' is deprecated and will be removed in a future release. Please use '@@slow_query_log' instead not deprecated: SELECT @@global.general_log_file INTO @my_glf; SELECT @@global.slow_query_log_file INTO @my_sqlf; SET GLOBAL general_log = 0; SET GLOBAL slow_query_log = 0; SET GLOBAL general_log_file = 'WL4403_G.log'; SET GLOBAL slow_query_log_file = 'WL4403_SQ.log'; SET GLOBAL general_log_file = @my_glf; SET GLOBAL slow_query_log_file = @my_sqlf; SET GLOBAL general_log = DEFAULT; SET GLOBAL slow_query_log = DEFAULT; SET @@global.general_log = @old_general_log; SET @@global.general_log_file = @old_general_log_file; SET @@global.slow_query_log = @old_slow_query_log; SET @@global.slow_query_log_file = @old_slow_query_log_file; # # Bug #49756 Rows_examined is always 0 in the slow query log # for update statements # SET @old_log_output = @@global.log_output; SET GLOBAL log_output = "TABLE"; SET GLOBAL slow_query_log = ON; SET GLOBAL long_query_time = 0.001; TRUNCATE TABLE mysql.slow_log; CREATE TABLE t1 (a INT); CREATE TABLE t2 (b INT, PRIMARY KEY (b)); INSERT INTO t2 VALUES (3),(4); INSERT INTO t1 VALUES (1+sleep(.01)),(2); INSERT INTO t1 SELECT b+sleep(.01) from t2; UPDATE t1 SET a=a+sleep(.01) WHERE a>2; UPDATE t1 SET a=a+sleep(.01) ORDER BY a DESC; UPDATE t2 set b=b+sleep(.01) limit 1; UPDATE t1 SET a=a+sleep(.01) WHERE a in (SELECT b from t2); DELETE FROM t1 WHERE a=a+sleep(.01) ORDER BY a LIMIT 2; SELECT rows_examined,sql_text FROM mysql.slow_log; rows_examined sql_text 0 INSERT INTO t1 VALUES (1+sleep(.01)),(2) 2 INSERT INTO t1 SELECT b+sleep(.01) from t2 4 UPDATE t1 SET a=a+sleep(.01) WHERE a>2 8 UPDATE t1 SET a=a+sleep(.01) ORDER BY a DESC 1 UPDATE t2 set b=b+sleep(.01) limit 1 4 UPDATE t1 SET a=a+sleep(.01) WHERE a in (SELECT b from t2) 6 DELETE FROM t1 WHERE a=a+sleep(.01) ORDER BY a LIMIT 2 DROP TABLE t1,t2; TRUNCATE TABLE mysql.slow_log; # end of bug#49756 End of 5.1 tests # -- # -- Bug#38124: "general_log_file" variable silently unset when # -- using expression # -- SET GLOBAL general_log_file = DEFAULT; SELECT @@general_log_file INTO @my_glf; SET GLOBAL general_log_file = 'BUG38124.LOG'; SELECT @@general_log_file; @@general_log_file BUG38124.LOG SET GLOBAL general_log_file = concat('BUG38124-2.LOG'); SELECT @@general_log_file; @@general_log_file BUG38124-2.LOG SET GLOBAL general_log_file = substr('BUG38124-2.LOG',3,6); SELECT @@general_log_file; @@general_log_file G38124 SET GLOBAL general_log_file = DEFAULT; SELECT @@general_log_file = @my_glf; @@general_log_file = @my_glf 1 SET GLOBAL general_log_file = @old_general_log_file; # Close connection con1 SET GLOBAL long_query_time = DEFAULT; SET GLOBAL log_output = @old_log_output; SET global general_log = @old_general_log; SET global general_log_file = @old_general_log_file; SET global slow_query_log = @old_slow_query_log; SET global slow_query_log_file = @old_slow_query_log_file;
Back to File Manager