MySQL Bugs: #33651: Slow queries logged to table instead of file after SIGHUP

Bug #33651	Slow queries logged to table instead of file after SIGHUP
Submitted:	3 Jan 2008 13:46	Modified:	25 Jan 2008 15:53
Reporter:	Colin Dean	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Logging	Severity:	S2 (Serious)
Version:	5.1.22-rc	OS:	Linux (CentOS 5.0 x86_64)
Assigned to:		CPU Architecture:	Any
Tags:	file, log, SIGHUP, slow, table
Triage:	D2 (Serious)

Description:
With MySQL configured to log slow queries to a file, it does so until receiving SIGHUP, and thereafter logs to database instead.  On full restart, it reverts to logging to file.

We use SIGHUP as part of daily log rotation for slow and error logs, and have a large user base who generate a lot of slow queries, which we want to be able to examine without filling up the database.

Perhaps related to Bug #33065 Mysql not writing general query logs after sending SIGHUP?

How to repeat:
Relevant settings:

mysql> show variables like '%slow%';
+---------------------+--------------------------------+
| Variable_name       | Value                          |
+---------------------+--------------------------------+
| log_slow_queries    | ON                             | 
| slow_launch_time    | 2                              | 
| slow_query_log      | ON                             | 
| slow_query_log_file | /var/lib/myitsql/logs/slow.log | 
+---------------------+--------------------------------+
4 rows in set (0.00 sec)

mysql> show variables like '%long%';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 1.000000 | 
+-----------------+----------+
1 row in set (0.00 sec)

mysql> show variables like '%log%';
+---------------------------------+---------------------------------+
| Variable_name                   | Value                           |
+---------------------------------+---------------------------------+
| back_log                        | 50                              | 
| binlog_cache_size               | 32768                           | 
| binlog_format                   | MIXED                           | 
| expire_logs_days                | 7                               | 
| general_log                     | OFF                             | 
| general_log_file                | /var/run/mysqld/myitsqld.log    | 
| innodb_flush_log_at_trx_commit  | 1                               | 
| innodb_locks_unsafe_for_binlog  | OFF                             | 
| innodb_log_buffer_size          | 8388608                         | 
| innodb_log_file_size            | 134217728                       | 
| innodb_log_files_in_group       | 2                               | 
| innodb_log_group_home_dir       | /var/lib/myitsql/logs/          | 
| innodb_mirrored_log_groups      | 1                               | 
| log                             | OFF                             | 
| log_bin                         | ON                              | 
| log_bin_trust_function_creators | OFF                             | 
| log_bin_trust_routine_creators  | OFF                             | 
| log_error                       | /var/lib/myitsql/logs/error.log | 
| log_output                      | FILE                            | 
| log_queries_not_using_indexes   | OFF                             | 
| log_slave_updates               | OFF                             | 
| log_slow_queries                | ON                              | 
| log_warnings                    | 2                               | 
| max_binlog_cache_size           | 18446744073709551615            | 
| max_binlog_size                 | 268435456                       | 
| max_relay_log_size              | 0                               | 
| relay_log_purge                 | ON                              | 
| relay_log_space_limit           | 0                               | 
| slow_query_log                  | ON                              | 
| slow_query_log_file             | /var/lib/myitsql/logs/slow.log  | 
| sql_log_bin                     | ON                              | 
| sql_log_off                     | OFF                             | 
| sql_log_update                  | ON                              | 
| sync_binlog                     | 1                               | 
+---------------------------------+---------------------------------+
34 rows in set (0.00 sec)

Before SIGHUP, slow queries are logged to /var/lib/myitsql/logs/slow.log only, as expected.  After SIGHUP, they are logged to mysql.slow_log database table only.

Also, how do I delete these unwanted table entries?  This doesn't work:

mysql> delete from mysql.slow_log;
ERROR 1553 (HY000): You can't use locks with log tables.
mysql>

I've figured out the answer to my supplementary question, how to empty the mysql.slow_log table.  This seems to work:

  USE mysql;
  DROP TABLE IF EXISTS new_slow_log;
  CREATE TABLE new_slow_log like slow_log;
  RENAME TABLE slow_log to old_slow_log, new_slow_log to slow_log;
  DROP TABLE old_slow_log;

Perhaps this could be documented in the manual?

I note that in a master-slave setup, these commands propagate from master to slave, but entries are not replicated (either before or after emptying), which I guess makes sense.

I think, this is a duplicate of bug #33065

I've tried the following 2 line patch, and it fixes the bug for me.
I suspect it fixes Bug #33065 too, but I haven't tested that. 

diff -Nrup a/sql/mysqld.cc b/sql/mysqld.cc
--- a/sql/mysqld.cc	2007-09-24 11:29:40 +01:00
+++ b/sql/mysqld.cc	2008-01-25 11:24:16 +00:00
@@ -2518,8 +2518,8 @@ pthread_handler_t signal_hand(void *arg 
 			     (TABLE_LIST*) 0, &not_used); // Flush logs
       }
       /* reenable logs after the options were reloaded */
-      logger.set_handlers(LOG_FILE, opt_slow_log ? LOG_TABLE:LOG_NONE,
-                          opt_log ? LOG_TABLE:LOG_NONE);
+      logger.set_handlers(LOG_FILE, opt_slow_log ? log_output_options:LOG_NONE,
+                          opt_log ? log_output_options:LOG_NONE);
       break;
 #ifdef USE_ONE_SIGNAL_HAND
     case THR_SERVER_ALARM:

Many thanks for your help.