diff options
Diffstat (limited to 'mysql-test/main/log_state.test')
| -rw-r--r-- | mysql-test/main/log_state.test | 342 |
1 files changed, 342 insertions, 0 deletions
diff --git a/mysql-test/main/log_state.test b/mysql-test/main/log_state.test new file mode 100644 index 00000000000..5a08b8015f0 --- /dev/null +++ b/mysql-test/main/log_state.test @@ -0,0 +1,342 @@ +### t/log_state.test ### +# + +--source include/not_embedded.inc +--source include/have_csv.inc + +call mtr.add_suppression("options .* --log_slow_queries is not set"); + +# Several subtests modify global variables. Save the initial values only here, +# but reset to the initial values per subtest. +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; + + +--disable_ps_protocol + +set global general_log= OFF; +truncate table mysql.general_log; +truncate table mysql.slow_log; +show global variables +where Variable_name = 'general_log' or Variable_name = 'slow_query_log'; +flush logs; +set global general_log= ON; +create table t1(f1 int); +--replace_column 1 TIMESTAMP 2 USER_HOST 3 # +select * from mysql.general_log; +set global general_log= OFF; +drop table t1; +--replace_column 1 TIMESTAMP 2 USER_HOST 3 # +select * from mysql.general_log; +set global general_log= ON; +flush logs; +show global variables +where Variable_name = 'general_log' or Variable_name = 'slow_query_log'; + +connect (con1,localhost,root,,); +connection con1; +# Please increase @long_query_time if the corresponding selects show an +# additional unexpected entry like +# start_time ... sql_text +# TIMESTAMP ... set session long_query_time=... +# (Bug#40377 sporadic pushbuild failure in log_state: result mismatch) +--replace_result 2 <long_query_time> +set @long_query_time = 2; +set session long_query_time = @long_query_time; +select sleep(@long_query_time + 1); +--replace_column 1 TIMESTAMP 2 USER_HOST 3 QUERY_TIME 12 THREAD_ID +select * from mysql.slow_log where sql_text NOT LIKE '%slow_log%'; +connection default; + +set global slow_query_log= ON; +set local slow_query_log= ON; +connection con1; +set session long_query_time = @long_query_time; +select sleep(@long_query_time + 1); +--replace_column 1 TIMESTAMP 2 USER_HOST 3 QUERY_TIME 12 THREAD_ID +select * from mysql.slow_log where sql_text NOT LIKE '%slow_log%'; +set local slow_query_log= ON; +select sleep(@long_query_time + 2); +--replace_column 1 TIMESTAMP 2 USER_HOST 3 QUERY_TIME 12 THREAD_ID +select * from mysql.slow_log where sql_text NOT LIKE '%slow_log%'; + +connection default; +show global variables +where Variable_name = 'general_log' or Variable_name = 'slow_query_log'; + +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 local slow_query_log= ON; + +set global general_log= ON; +truncate table mysql.general_log; +create table t1(f1 int); +drop table t1; +--replace_column 1 TIMESTAMP 2 USER_HOST 3 # +select * from mysql.general_log; +set global general_log= OFF; +truncate table mysql.general_log; +--replace_column 1 TIMESTAMP 2 USER_HOST 3 # +select * from mysql.general_log; +set global general_log= ON; +show global variables +where Variable_name = 'general_log' or Variable_name = 'slow_query_log'; + +--replace_column 2 # +show variables like 'general_log_file'; +--replace_column 2 # +show variables like 'slow_query_log_file'; +show variables like 'log_output'; + +# Can't set general_log_file to a non existing file +--error ER_WRONG_VALUE_FOR_VAR +set global general_log_file='/not existing path/log.master'; + +# Can't set general_log_file to a directory +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +--error ER_WRONG_VALUE_FOR_VAR +eval set global general_log_file='$MYSQLTEST_VARDIR'; + +# Can't set general_log_file to empty string +--error ER_WRONG_VALUE_FOR_VAR +set global general_log_file=''; + +--replace_column 2 # +show variables like 'general_log_file'; +set global general_log= OFF; +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +eval 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; +--replace_column 2 # +show variables like 'general_log_file'; +--replace_column 2 # +show variables like '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'; +show variables like 'slow_query_log'; +show global variables like 'slow_query_log'; +set global general_log=ON; +set global log_output=default; +show variables like 'log_output'; +set global general_log=OFF; +set global log_output=FILE; +truncate table mysql.general_log; +show variables like 'log_output'; +set global general_log=ON; +create table t1(f1 int); +--replace_column 1 TIMESTAMP 2 USER_HOST 3 # +select * from mysql.general_log; +set global general_log=OFF; +set global log_output="FILE,TABLE"; +show variables like 'log_output'; +set global general_log=ON; +drop table t1; +--replace_column 1 TIMESTAMP 2 USER_HOST 3 # +select * from mysql.general_log; + +# Reset to initial values +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#29129 (Resetting general_log while the GLOBAL READ LOCK is set causes +# a deadlock) +# + +# Test ON->OFF transition under a GLOBAL READ LOCK +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; + +# Test OFF->ON transition under a GLOBAL READ LOCK +FLUSH TABLES WITH READ LOCK; + +SET GLOBAL general_log = ON; +SET GLOBAL slow_query_log = ON; + +UNLOCK TABLES; + +# Test ON->OFF transition under a GLOBAL READ_ONLY +SET GLOBAL READ_ONLY = ON; + +SET GLOBAL general_log = OFF; +SET GLOBAL slow_query_log = OFF; + +SET GLOBAL READ_ONLY = OFF; + +# Test OFF->ON transition under a GLOBAL READ_ONLY +SET GLOBAL READ_ONLY = ON; + +SET GLOBAL general_log = ON; +SET GLOBAL slow_query_log = ON; + +SET GLOBAL READ_ONLY = OFF; + +# Reset to initial values +SET GLOBAL general_log = @old_general_log; +SET GLOBAL slow_query_log = @old_slow_query_log; + + +########################################################################### + +# +# Bug#31604: server crash when setting slow_query_log_file/general_log_file +# + +--error ER_WRONG_VALUE_FOR_VAR +SET GLOBAL general_log_file= CONCAT('/not existing path/log.maste', 'r'); +--error ER_WRONG_VALUE_FOR_VAR +SET GLOBAL general_log_file= NULL; +--error ER_WRONG_VALUE_FOR_VAR +SET GLOBAL slow_query_log_file= CONCAT('/not existing path/log.maste', 'r'); +--error ER_WRONG_VALUE_FOR_VAR +SET GLOBAL slow_query_log_file= NULL; + +# Reset to initial values in case a setting above was successful. +SET GLOBAL general_log_file= @old_general_log_file; +SET GLOBAL slow_query_log_file= @old_slow_query_log_file; + +########################################################################### + +--echo +--echo # -- +--echo # -- Bug#32748: Inconsistent handling of assignments to +--echo # -- general_log_file/slow_query_log_file. +--echo # -- + +--echo +SET GLOBAL general_log_file = 'bug32748.query.log'; +SET GLOBAL slow_query_log_file = 'bug32748.slow.log'; + +--echo +SHOW VARIABLES LIKE '%log_file'; + +# Reset to initial values +--echo +SET GLOBAL general_log_file = @old_general_log_file; +SET GLOBAL slow_query_log_file = @old_slow_query_log_file; + +--echo +--echo # -- End of Bug#32748. + + +########################################################################### + +--echo # +--echo # Bug #49756 Rows_examined is always 0 in the slow query log +--echo # for update statements +--echo # + +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; + +# clear slow_log of any residual slow queries +TRUNCATE TABLE mysql.slow_log; +CREATE TABLE t1 (a INT); +CREATE TABLE t2 (b INT, PRIMARY KEY (b)); +INSERT INTO t2 VALUES (3),(4); + +connect (con2,localhost,root,,); +INSERT INTO t1 VALUES (1+sleep(.02)),(2); +INSERT INTO t1 SELECT b+sleep(.02) from t2; +UPDATE t1 SET a=a+sleep(.02) WHERE a>2; +UPDATE t1 SET a=a+sleep(.02) ORDER BY a DESC; +UPDATE t2 set b=b+sleep(.02) limit 1; +UPDATE t1 SET a=a+sleep(.02) WHERE a in (SELECT b from t2); +DELETE FROM t1 WHERE a=a+sleep(.02) ORDER BY a LIMIT 2; + +SELECT rows_examined,sql_text FROM mysql.slow_log; +disconnect con2; +connection default; +DROP TABLE t1,t2; +TRUNCATE TABLE mysql.slow_log; + +--echo # end of bug#49756 + + +--echo End of 5.1 tests + + +########################################################################### + +--echo +--echo # -- +--echo # -- Bug#38124: "general_log_file" variable silently unset when +--echo # -- using expression +--echo # -- + +# Store away the special DEFAULT value so we +# can compare it later, then try to set the +# general_log_file using different functions +# and expressions. + +SET GLOBAL general_log_file = DEFAULT; +SELECT @@general_log_file INTO @my_glf; + +SET GLOBAL general_log_file = 'BUG38124.LOG'; +SELECT @@general_log_file; + +SET GLOBAL general_log_file = concat('BUG38124-2.LOG'); +SELECT @@general_log_file; + +SET GLOBAL general_log_file = substr('BUG38124-2.LOG',3,6); +SELECT @@general_log_file; + +SET GLOBAL general_log_file = DEFAULT; +SELECT @@general_log_file = @my_glf; + + +## Reset to initial values +SET GLOBAL general_log_file = @old_general_log_file; + + +--enable_ps_protocol + +# +# Cleanup +# +# Disconnect must be done last to avoid delayed 'Quit' message in general log +disconnect con1; +# set back the saved default values +connection default; + +# Reset global system variables to initial values if forgotten somewhere above. +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; + +# Remove the log file that was created in the "default location" +# i.e var/run +--remove_file $MYSQLTEST_VARDIR/tmp/log.master |