MySQL Bugs: #65481: Sending SIGHUP does not work as explained in the manual

Bug #65481	Sending SIGHUP does not work as explained in the manual
Submitted:	1 Jun 2012 0:16	Modified:	25 Apr 2013 11:24
Reporter:	Fernando Ipar (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	5.5.17	OS:	Linux
Assigned to:	Paul DuBois	CPU Architecture:	Any

Description:
The manual for 5.1 and 5.5 says that on Unix, FLUSH LOGS is equivalent to sending SIGHUP to mysqld: 

- http://dev.mysql.com/doc/refman/5.1/en/flush.html
- http://dev.mysql.com/doc/refman/5.5/en/flush.html

However, on 5.5.17 and 5.1.41 SIGHUP seems to flush tables too, which can have unexpected consequences if there are long running queries in the system. 

How to repeat:
mysql> show create table locktest\G
*************************** 1. row ***************************
       Table: locktest
Create Table: CREATE TABLE `locktest` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `c` char(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=118204 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

write-load.sh: 
#!/bin/bash

no=10
i=0
while [ $i -lt 10000 ]; do
        mysql test -e "insert into locktest values (null,'$RANDOM')"
        i=$((i+1))
        no=$((no+1))
done

read-load.sh: 
#!/bin/bash

i=0
while [ $i -lt 10000 ]; do
        mysql test -e 'select * from locktest limit 1' > /dev/null
        i=$((i+1))
done

intentionally badly performing query: 

select id from locktest join (select id from locktest) tmp0 using (id) join (select id,c from locktest order by c desc) tmp1 using (id) join (select id,c from locktest order by c desc) tmp2 using (id) left join (select id,c from locktest order by c) tmp3 using(id) limit 10;

While write-load.sh and read-load.sh are running, execute the 'bad' query and then do kill -HUP `pidof mysqld`

This is the result on 5.1: 

mysql> show processlist\G
*************************** 1. row ***************************
     Id: 114862
   User: root   Host: localhost
     db: testCommand: Query
   Time: 76  State: Sending data
   Info: select id from locktest join (select id from locktest) tmp0 using (id) join (select id,c from lockte
*************************** 2. row ***************************
     Id: 144432   User: root
   Host: localhost     db: NULL
Command: Query   Time: 0
  State: NULL   Info: show processlist
*************************** 3. row ***************************
     Id: 185999
   User: root
   Host: localhost     db: test
Command: Query   Time: 72
  State: Waiting for table   Info: select * from locktest limit 1
*************************** 4. row ***************************     Id: 186000
   User: root   Host: localhost
     db: testCommand: Query
   Time: 72  State: Waiting for table
   Info: insert into locktest values (null,'19538')4 rows in set (0.00 sec)

And this is the result for 5.5: 

  State: Sending data
   Info: select id from locktest join (select id from locktest) tmp0 using (id) join (select id,c from locktest order by c desc) tmp1 using (id) join (select id,c from locktest order by c desc) tmp2 using (id) left join (select id,c from locktest order by c) tmp3 using(id) limit 10
*************************** 2. row ***************************
     Id: 448
   User: root
   Host: localhost
     db: test
Command: Query
   Time: 24
  State: Waiting for table flush
   Info: select * from locktest limit 1
*************************** 3. row ***************************
     Id: 449
   User: root
   Host: localhost
     db: test
Command: Query
   Time: 24
  State: Waiting for table flush
   Info: insert into locktest values (null,'28431')
*************************** 4. row ***************************
     Id: 450
   User: root
   Host: localhost
     db: NULL
Command: Query
   Time: 0
  State: NULL
   Info: show full processlist
4 rows in set (0.01 sec)

On either version, if I do 'FLUSH LOGS' instead of sending SIGHUP, this does not happen. 

I think this is potentially dangerous as many logrotate scripts for mysql send SIGHUP after rotating the slow/error logs, and this can lead to a global server stall if a long running query is executing concurrently. 

Suggested fix:
Either fix the code that handles the HUP signal so that this does not happen, or fix the manual so that it's clear that SIGHUP is not the same as FLUSH LOGS.

http://dev.mysql.com/doc/refman/5.5/en/server-signal-response.html also says nothing about flushing tables by SIGHUP.

This is what I see in current mysql-5.5 (sql/mysqld.cc starting at line 2722):

...
    case SIGHUP:
      if (!abort_loop)
      {
        int not_used;
        mysql_print_status();           // Print some debug info
        reload_acl_and_cache((THD*) 0,
                             (REFRESH_LOG | REFRESH_TABLES | REFRESH_FAST |
                              REFRESH_GRANT |
                              REFRESH_THREADS | REFRESH_HOSTS),
                             (TABLE_LIST*) 0, &not_used); // Flush logs
      }
...

So, we do pass REFRESH_TABLES option and thus equivalent of FLUSH TABLES happens. I let documentation team to find out if this is intended and process this bug accordingly. For now I consider this a documentation problem.

Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.

Modified FLUSH and server-signals sections as follows:

http://dev.mysql.com/doc/refman/5.5/en/flush.html
Sending a SIGHUP signal to the server causes several flush operations to occur that are similar to various forms of the FLUSH statement. See Section 5.1.11, “Server Response to Signals”.

http://dev.mysql.com/doc/refman/5.5/en/server-signal-response.html
SIGHUP causes the server to reload the grant tables and to flush tables, logs, the thread cache, and the host cache. These actions are like various forms of the FLUSH statement.