Bug #105933 The table "general_log" not close when config option general_log set into OFF
Submitted: 19 Dec 2021 8:11 Modified: 21 Dec 2021 8:01
Reporter: teng wang (OCA) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Options Severity:S3 (Non-critical)
Version:5.7.35, 8.0.27 OS:Linux
Assigned to: CPU Architecture:Any

[19 Dec 2021 8:11] teng wang
Description:
When we set the config option "general_log" as ON, and "log_output" as TABLE in the config file, MySQL records the related general logs into the table named "general_log". However, if we runtime change the option "general_log" into OFF, the table "general_log" is still keeping open until "flush tables" command is entered.

The ideal situation is that when general_log turns into OFF, the table general_log should be closed right now. Otherwise, the big table would occupy lots of cache, 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 general_log = 'ON';
set global log_output = 'TABLE';
show global variables where variable_name like 'general_log';
show global variables where variable_name like 'log_output';

create table t1
(
  a int primary key,
  b char(10)
);
insert into t1 values (1,'one');
insert into t1 values (2,'two');
show open tables from mysql;

set global general_log = OFF;
show global variables where variable_name like 'general_log';
show open tables from mysql;

flush tables;
show global variables where variable_name like 'general_log';
show open tables from mysql;

Suggested fix:
when runtime change "general_log", the function fix_general_log_state will be called. Maybe we can close the table general_log during fix_general_log_state.

static bool fix_general_log_state(sys_var *self, THD *thd, enum_var_type type)
[21 Dec 2021 8:01] MySQL Verification Team
Hello!

Thank you for the report and feedback.

regards,
Umesh