Gentoo Websites Logo
Go to: Gentoo Home Documentation Forums Lists Bugs Planet Store Wiki Get Gentoo!
Bug 522024 - dev-db/mysql-init-scripts logrotate.mysql sends HUP signal which can terminate long queries
Summary: dev-db/mysql-init-scripts logrotate.mysql sends HUP signal which can terminat...
Status: CONFIRMED
Alias: None
Product: Gentoo Linux
Classification: Unclassified
Component: Current packages (show other bugs)
Hardware: All Linux
: Normal normal (vote)
Assignee: Gentoo Linux MySQL bugs team
URL: http://bugs.mysql.com/bug.php?id=65481
Whiteboard:
Keywords:
Depends on:
Blocks:
 
Reported: 2014-09-03 08:14 UTC by Zoltán Halassy
Modified: 2015-01-18 13:54 UTC (History)
1 user (show)

See Also:
Package list:
Runtime testing required: ---


Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description Zoltán Halassy 2014-09-03 08:14:39 UTC
According to MySQL documentation ( http://dev.mysql.com/doc/refman/5.5/en/server-signal-response.html http://dev.mysql.com/doc/refman/5.5/en/flush.html ), when MySQL receives the HUP signal, it performs a FLUSH TABLES; command. FLUSH LOGS; should be enough for logrotating, instead of the HUP signal. The problem is explained in detail at the bug URL.

Reproducible: Always

Steps to Reproduce:
See bug URL.
Actual Results:  
When logrotate sends HUP signal to MySQL, long queries can exit with "MySQL server has gone away" message.

Expected Results:  
Logs being rotated with FLUSH LOGS, and long queries can continue without problems.
Comment 1 Brian Evans Gentoo Infrastructure gentoo-dev 2014-09-03 13:28:32 UTC
How would you suggest we go about this another way?

The only way I can think of is to use 'mysqladmin flush-logs'.
This would require giving the logrotate cron job access to your database.
To do that, means teaching admins to create a /root/.my.cnf file for each install with a user and password that has the RELOAD permissions and potentially more.
This could be an even bigger security risk.
Comment 2 Zoltán Halassy 2014-09-03 14:57:06 UTC
Well, three things came to my mind:

Suggestion A)

Modify the e-build which warns the user about this problem, but does nothig else. At least a heads up, when she wants to do long queries when the logrotate happens. We spent two years (!) to debug this problem.

Suggestion B)

implement /etc/init.d/mysql reload in way, that it checks, if /root/.my.cnf (or something else) exists or not. If not, send HUP signal to the mysql process. If it does exist, check the permissions of the file, it must be owned by root, and have rights of 600. Then invoke mysqladmin flush-logs. Still notify the upgrading user about this option, and the risks with .my.cnf .

This would be totally backwards compatible, but the ebuild would give the user the option to decide if she want's a stable SQL connection or opt-out from storing a root (or user with reload right) plantext password.

Suggestion C)

Patch MySQL with not-yet-in-use signal (say, USR1), which only does the FLUSH LOGS operation.
Comment 3 Zoltán Halassy 2014-09-03 15:08:06 UTC
Suggestion D)

Forget file based logging, and use syslog instead. This could be controlled with a syslog USE flag, and warn the user about the problem when she installs it without it.
Comment 4 Robin Johnson archtester Gentoo Infrastructure gentoo-dev Security 2014-09-04 02:58:41 UTC
I like option B, but request that it be implemented like debian's mysql, which has /etc/mysql/debian.cnf as root:root/600, and creates a user debian-sys-maint, that uses the socket and has full perms via localhost only.

Option D is nice, but problematic for things like the slow query log.
Comment 5 David Sparks 2014-11-27 23:26:18 UTC
I recently ran into this issue.  The logrotate job sent mysqld a HUP at 3am while a long running export query was in progress.  The effect was not that the long running query was killed, rather all insert queries were blocked until the long running query finished.

Unless you have something in place to kill long running queries I think the server stall is the expected behavior when this happens.

I also like Debian's solution using a dedicated localhost user for maintenance tasks.
Comment 6 frank 2014-12-30 09:03:23 UTC
the HUP signal may also cause table corruption and doesn't really reopen file descriptors to the new log file as lsof tells me mysqld is still writing to deleted files.
my workaround was adding copytruncate to logrotate's mysql file and removing the postrotate section completely (yes i know i can lose some log entries, but i'm more concerned about database integrity).
Comment 7 Brian Evans Gentoo Infrastructure gentoo-dev 2015-01-16 03:29:26 UTC
I am currently testing another solution.

Both mysql-5.6 and mariadb-10.0 include an unix socket authentication plugin.
This allows someone as that user on the system to connect to mysql without a password and perform what ever actions they have access to (RELOAD permission only in this case).

with MariaDB, the setup is:

INSTALL SONAME 'auth_socket.so';
CREATE USER mysql IDENTIFIED VIA unix_socket;
GRANT RELOAD on *.* TO 'mysql';


So dropping to the unprivileged mysql user and running 'mysqladmin flush-logs' should work without a password.

That's the theory.  Still need to work it out.
Comment 8 Brian Evans Gentoo Infrastructure gentoo-dev 2015-01-16 17:44:47 UTC
(Adding perfinion by irc request)

Ok.. preliminary tests show this is possible..

On MariaDB:
INSTALL SONAME 'auth_socket.so';
CREATE USER mysql@localhost IDENTIFIED VIA unix_socket;
GRANT RELOAD on *.* TO 'mysql'@'localhost';

On MySQL:
INSTALL PLUGIN auth_socket SONAME 'auth_socket.so';
CREATE USER mysql@localhost IDENTIFIED WITH auth_socket;
GRANT RELOAD on *.* TO 'mysql'@'localhost';


Then the logrotate script becomes 

[ -f /var/run/mysqld/mysqld.pid ] && /bin/su -c '/usr/bin/mysqladmin flush-logs -u mysql' -s /bin/sh mysql



comments?