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:
Tags: file, log, SIGHUP, slow, table
Triage: D2 (Serious)

[3 Jan 2008 13:46] Colin Dean
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>
[4 Jan 2008 12:39] Colin Dean
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.
[4 Jan 2008 16:30] Susanne Ebrecht
I think, this is a duplicate of bug #33065
[25 Jan 2008 15:53] Colin Dean
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:
[14 Feb 2008 10:40] Susanne Ebrecht
Many thanks for your help.