Description:
This is similar to Bug #105933. When we set the config option "slow_query_log" as ON, and "log_output" as TABLE in the config file, MySQL records the related slow logs into the table named "slow_log". However, if we runtime change the option "slow_query_log" into OFF, the table "slow_log" is still keeping open until "flush tables" command is entered.
The ideal situation is that when slow_query_log turns into OFF, the table slow_log should be closed right now. In some situations, the table slow_log may be big and occupy lots of caches, which might affect the performance of the cache-related operations.
We check the open tables by the command "show open tables from mysql;"
How to repeat:
set global slow_query_log = 'ON';
set global log_output = 'TABLE';
set global log_queries_not_using_indexes = 1;
show global variables where variable_name like 'slow_query_log';
show global variables where variable_name like 'log_output';
flush tables;
show open tables from mysql;
create table t1(
a int primary key,
b char(10)
);
insert into t1 values (1,'one'), (2,'two');
create table t2(
a int primary key,
b char(10)
);
insert into t2 values (1,'ONE'), (2,'TWO');
select * from t1 LEFT JOIN t2 on t1.a=t2.a;
show open tables from mysql;
set global slow_query_log = OFF;
show global variables where variable_name like 'slow_query_log';
show open tables from mysql;
flush tables;
show global variables where variable_name like 'slow_query_log';
show open tables from mysql;
Suggested fix:
when runtime change "slow_query_log", the function fix_slow_log_state will be called. Maybe we can close the table slow_log during fix_slow_log_state().
/* /sql/sys_vars.cc */
static bool fix_slow_log_state(sys_var *self, THD *thd, enum_var_type type)
