MySQL Bugs: #33065: Mysql not writing general query logs after sending SIGHUP

Bug #33065	Mysql not writing general query logs after sending SIGHUP
Submitted:	7 Dec 2007 15:58	Modified:	17 Mar 2008 20:09
Reporter:	Laszlo KAROLYI	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.1.22-rc-log	OS:	FreeBSD
Assigned to:	Alexander Nozdrin	CPU Architecture:	Any
Tags:	general log sighup

Description:
Hi,

i have set up mysql in a freebsd jail, and wanted to use newsyslog to rotate logs. the newsyslog is working correctly, and sends a sighup to the mysqld process, it's id is specified in the mysqld pid file.

after the sighup, mysqld creates the new general log file, but stops writing any further logs to it.

How to repeat:
my.cnf is:

[mysqld]
general-log
user            = mysql
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /tmp/mysql.sock
port            = 3306
basedir         = /usr
datadir         = /var/db/mysql
tmpdir          = /tmp
language        = /usr/local/share/mysql/english
default-character-set   = latin2
log-slow-queries = /var/log/mysql/mysql-slow.log
bind-address            = 127.0.0.3
max_allowed_packet      = 16M
thread_stack            = 128K
thread_cache_size       = 8
max_connections        = 100

skip-locking
key_buffer = 384M
table_cache = 512
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_limit       = 2M
query_cache_size        = 32M
thread_concurrency = 8

log-output      = FILE
log             = /var/log/mysql/mysql.log
long_query_time = 5

Suggested fix:
maybe fixing that part in the mysql server code?

Verified as described.

my.cnf:
...
log-output=FILE
log=/var/db/mysql/my.log
...

started with mysql_safe ...

$ mysql -u myuser test
mysql> Do some stuff like creating a table, inserting, updating, deleting some data

# tail -f /var/db/mysql/my.log
/usr/local/libexec/mysqld, Version: 5.1.22-rc-log (FreeBSD port: mysql-server-5.1.22). started with:
Tcp port: 0  Unix socket: (null)
Time                 Id Command    Argument
071210 15:15:33    1 Connect    miracee@localhost on miracee
                    1 Query     show databases
                    1 Query     show tables
                    1 Query     select @@version_comment limit 1
071210 15:15:38    1 Query      show tables
071210 15:16:18    1 Query      create table t1(id serial, num integer, primary key(id))
071210 15:16:53    1 Query      insert into t1(num) values (1),(2),(3),(4),(5)
071210 15:17:03    1 Query      delete from t1 where id=3
071210 15:17:13    1 Query      update t1 set num=7 where id=2
071210 15:17:19    1 Query      select * from t1
...

# kill -1PID FROM MYSQLD

mysql> Do some stuff like creating a table, inserting, updating, deleting some data

# tail -f /var/db/mysql/my.log
/usr/local/libexec/mysqld, Version: 5.1.22-rc-log (FreeBSD port: mysql-server-5.1.22). started with:
Tcp port: 3306  Unix socket: /tmp/mysql.sock
Time                 Id Command    Argument

nothing else happens here, it doesn't matter what you do at the cli, nothing will be logged.

To be sure, this is not a FreeBSD ports problem:
The same occurs with self compiled 5.1.22-rc-debug-log.

Bug #33651 is set as duplicate of this bug here.

triage: escalated by supprt

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/42743

ChangeSet@1.2567, 2008-02-21 14:49:27+03:00, anozdrin@quad. +2 -0
  Fix for Bug#33065: Mysql not writing general query logs after
  sending SIGHUP.
  
  There were two problems:
    - after some recent fix, the server started to crash after
      receiving SIGHUP. That happened because LEX of new THD-object
      was not properly initialized.
    - user-specified log options were ignored when logs were reopened.
  
  The fix is to 1) initialize LEX and 2) take user-specified options
  into account.
  
  There is no test case in this CS, because our test suite does not
  support sending SIGHUP to the server.

Pushed into 5.1-runtime.

Pushed into 5.1.24-rc

Pushed into 6.0.5-alpha

Noted in 5.1.24, 6.0.5 changelogs.

After receiving a SIGHUP signal, the server could crash, and
user-specified log options were ignored when reopening the logs.

Also documented in the 5.1.23-ndb-6.3.11 changelog.