mirror of
https://github.com/MariaDB/server.git
synced 2025-08-25 20:01:50 +02:00
56ce9e72a1
For all "UNION ALL" queries, the field rows_examined in the slow query log is always being set to 0. However, this is not the case with UNION queries although both UNION, and UNION ALL share the same code. The problem is that for UNION ALL queries, rows_examined field in the thd object is not updated to the actual processed rows, although they are being tracked in the sql_union.cc Later, when the thd object is used to dump the examined rows to the slow query log, it was only writing 0, as thd object was never updated with the processed rows count. This PR addresses the concern to write rows_examined field correctly for UNION_ALL queries to the slow query log.
401 lines
13 KiB
Text
401 lines
13 KiB
Text
call mtr.add_suppression("options .* --log_slow_queries is not set");
|
|
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;
|
|
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';
|
|
Variable_name Value
|
|
general_log 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 = 'general_log' or Variable_name = 'slow_query_log';
|
|
Variable_name Value
|
|
general_log ON
|
|
slow_query_log OFF
|
|
connect con1,localhost,root,,;
|
|
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 thread_id rows_affected
|
|
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);
|
|
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 thread_id rows_affected
|
|
set local slow_query_log= ON;
|
|
select sleep(@long_query_time + 2);
|
|
sleep(@long_query_time + 2)
|
|
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 thread_id rows_affected
|
|
TIMESTAMP USER_HOST QUERY_TIME 00:00:00.000000 1 0 test 0 0 1 select sleep(@long_query_time + 2) THREAD_ID 0
|
|
connection default;
|
|
show global variables
|
|
where Variable_name = 'general_log' or Variable_name = 'slow_query_log';
|
|
Variable_name Value
|
|
general_log 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 local slow_query_log= ON;
|
|
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 = 'general_log' or Variable_name = 'slow_query_log';
|
|
Variable_name Value
|
|
general_log ON
|
|
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 ON
|
|
show global 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_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.
|
|
#
|
|
# 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);
|
|
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;
|
|
rows_examined sql_text
|
|
0 INSERT INTO t1 VALUES (1+sleep(.02)),(2)
|
|
2 INSERT INTO t1 SELECT b+sleep(.02) from t2
|
|
4 UPDATE t1 SET a=a+sleep(.02) WHERE a>2
|
|
8 UPDATE t1 SET a=a+sleep(.02) ORDER BY a DESC
|
|
1 UPDATE t2 set b=b+sleep(.02) limit 1
|
|
4 UPDATE t1 SET a=a+sleep(.02) WHERE a in (SELECT b from t2)
|
|
6 DELETE FROM t1 WHERE a=a+sleep(.02) ORDER BY a LIMIT 2
|
|
disconnect con2;
|
|
connection default;
|
|
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;
|
|
#
|
|
# MDEV-35720 Add query_time to statistics
|
|
#
|
|
connect con2,localhost,root,,;
|
|
set @s1=(select variable_value from information_schema.session_status where
|
|
variable_name='query_time');
|
|
select sleep(3) into @a;
|
|
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";
|
|
should be true
|
|
1
|
|
select @s2 >= 2.00 and @s2 < 10.00 as "should be true";
|
|
should be true
|
|
1
|
|
select @s3 >= 3.00 as "should be true";
|
|
should be true
|
|
1
|
|
disconnect con2;
|
|
connection default;
|
|
create procedure p1()
|
|
begin
|
|
select sleep(1) into @a;
|
|
select sleep(2) into @a;
|
|
end|
|
|
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";
|
|
should be true
|
|
1
|
|
disconnect con2;
|
|
disconnect con1;
|
|
connection default;
|
|
drop procedure p1;
|
|
#
|
|
# MDEV-35353 Rows_examined is always 0 in the slow query log
|
|
# for union all queries
|
|
#
|
|
SET GLOBAL log_output = "TABLE";
|
|
SET GLOBAL slow_query_log = ON;
|
|
SET GLOBAL long_query_time = 0.0;
|
|
TRUNCATE TABLE mysql.slow_log;
|
|
create table t1(a int, b int);
|
|
insert into t1 select seq, seq from seq_1_to_20;
|
|
connect con2,localhost,root,,;
|
|
select sum(a) from t1
|
|
union all
|
|
select sum(b) from t1;
|
|
sum(a)
|
|
210
|
|
210
|
|
SELECT rows_examined,sql_text FROM mysql.slow_log where sql_text LIKE '%union all%';
|
|
rows_examined sql_text
|
|
40 select sum(a) from t1
|
|
union all
|
|
select sum(b) from t1
|
|
TRUNCATE TABLE mysql.slow_log;
|
|
select sum(a) from t1
|
|
union
|
|
select sum(b) from t1;
|
|
sum(a)
|
|
210
|
|
SELECT rows_examined,sql_text FROM mysql.slow_log where sql_text LIKE '%union%';
|
|
rows_examined sql_text
|
|
41 select sum(a) from t1
|
|
union
|
|
select sum(b) from t1
|
|
TRUNCATE TABLE mysql.slow_log;
|
|
select sum(a) from t1
|
|
except all
|
|
select sum(b) from t1;
|
|
sum(a)
|
|
SELECT rows_examined,sql_text FROM mysql.slow_log where sql_text LIKE '%except all%';
|
|
rows_examined sql_text
|
|
40 select sum(a) from t1
|
|
except all
|
|
select sum(b) from t1
|
|
TRUNCATE TABLE mysql.slow_log;
|
|
select sum(a) from t1
|
|
except
|
|
select sum(b) from t1;
|
|
sum(a)
|
|
SELECT rows_examined,sql_text FROM mysql.slow_log where sql_text LIKE '%except%';
|
|
rows_examined sql_text
|
|
40 select sum(a) from t1
|
|
except
|
|
select sum(b) from t1
|
|
TRUNCATE TABLE mysql.slow_log;
|
|
select sum(a) from t1
|
|
intersect all
|
|
select sum(b) from t1;
|
|
sum(a)
|
|
210
|
|
SELECT rows_examined,sql_text FROM mysql.slow_log where sql_text LIKE '%intersect all%';
|
|
rows_examined sql_text
|
|
41 select sum(a) from t1
|
|
intersect all
|
|
select sum(b) from t1
|
|
TRUNCATE TABLE mysql.slow_log;
|
|
select sum(a) from t1
|
|
intersect
|
|
select sum(b) from t1;
|
|
sum(a)
|
|
210
|
|
SELECT rows_examined,sql_text FROM mysql.slow_log where sql_text LIKE '%intersect%';
|
|
rows_examined sql_text
|
|
41 select sum(a) from t1
|
|
intersect
|
|
select sum(b) from t1
|
|
disconnect con2;
|
|
connection default;
|
|
DROP TABLE t1;
|
|
TRUNCATE TABLE mysql.slow_log;
|
|
End of 10.11 tests
|
|
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;
|