mariadb/mysql-test/main/log_state.test

### 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;
set @save_long_query_time=@@long_query_time;

--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

--echo #
--echo # MDEV-35720 Add query_time to statistics
--echo #

connect (con2,localhost,root,,);
set @s1=(select variable_value from information_schema.session_status where
variable_name='query_time');
--disable_cursor_protocol
select sleep(3) into @a;
--enable_cursor_protocol
set @s2=(select variable_value from information_schema.session_status where
variable_name='query_time');
set @s3=(select variable_value from information_schema.global_status where
variable_name='query_time');

select @s1 >= 0.00 and @s1 <= 2.00 as "should be true";
select @s2 >= 2.00 and @s2 < 10.00 as "should be true";
select @s3 >= 3.00 as "should be true";

# select @s1,@s2,@s3;
disconnect con2;
connection default;

delimiter |;
create procedure p1()
begin
select sleep(1) into @a;
select sleep(2) into @a;
end|
delimiter ;|

connect (con2,localhost,root,,);
call p1();
set @s1=(select variable_value from information_schema.session_status where
variable_name='query_time');
select @s1 > 2.00 and @s1 < 10.00 as "should be true";

# select @s1;
disconnect con2;

#
# Cleanup
#
# Disconnect must be done last to avoid delayed 'Quit' message in general log
disconnect con1;
# set back the saved default values
connection default;
drop procedure p1;

# Reset global system variables to initial values if forgotten somewhere above.
SET GLOBAL long_query_time = @save_long_query_time;
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