Re: [Maria-discuss] mysqladmin - flush failed: Unknown error
On 30/03/17 20:46, Matthew Pash wrote:
Morning Daniel,
Thanks for your reply, I've been doing some more testing. The logrotate script (below) currently runs fine when executed manually (sudo logrotate -v -f /etc/logrotate.d/mysql) but fails when run overnight by cron.
/var/log/mysqld.log { compress daily missingok notifempty rotate 3 postrotate if test -x /usr/bin/mysqladmin && /usr/bin/mysqladmin ping &>/dev/null then /usr/bin/mysqladmin flush-logs fi endscript }
I tweaked it last night to capture the output of the mysqladmin command and it did this:
/usr/bin/mysqladmin: connect to server at 'localhost' failed error: 'Access denied for user 'root'@'localhost' (using password: NO)'
Which makes me think the problem is more about cron than MariaDB.
No, MariaDB doesn't have a passwordless root user that mysqladmin needs to issue the flush-logs command. I suspect that there was originally a root user with socket authentication this system. Even after fixing this the mysqld may not be able to create new logs in /var/log/. I'd normally expect /var/log/mysql to be owned by the mysql user and its configuration to log the error to a file in this directory.
I've added some extra information gathering, so we'll see how it runs tonight.
On 27 March 2017 at 22:34, Daniel Black <daniel.black@au1.ibm.com <mailto:daniel.black@au1.ibm.com>> wrote:
I think you'll find that the mysql user don't have permissions to create a new file in /var/log/. As such logrotate renames the file which is still open by mysqld. As such it is now writing to mysqld.log.1.
On 28/03/17 00:13, Matthew Pash wrote: > Hi, > > We've got some database servers running MariaDB 10.1.22, and are having > issues with logrotate. Specifically, after it runs, it leaves a 0 byte > file and continues writing log data to mysqld.log.1 > > Manually running logrotate in verbose mode shows this error from > mysqladmin when it tries to run -flush_logs: > > /usr/bin/mysqladmin: flush failed; error: 'Unknown error' > error: error running non-shared postrotate script for > /var/log/mysqld.log of '/var/log/mysqld.log ' > set default create context
What does the logrotate script look like? It really should be a "shared" (in a logrotate way) script.
> Has anyone seen this before? It doesn't look like a permissions error - > the logs are created fine if you run systemctl restart mariadb.
Is the log actually writing to /var/log/mysqld.log using systemd? It normally writes to the journal because with systemd redirects stderr there
From lsof, it looks like /var/log/mysqld.log is being written to by the mysql process itself: $ sudo lsof | grep mysqld.log mysqld 38803 mysql 1w REG 8,3 31781 537216904 /var/log/mysqld.log.1 mysqld 38803 mysql 2w REG 8,3 31781 537216904 /var/log/mysqld.log.1 ...
> Thanks, > ~Matt > > -- > *Matthew* > Senior Systems Administrator, IT Services > University of Bristol > > > _______________________________________________ > Mailing list: https://launchpad.net/~maria-discuss <https://launchpad.net/%7Emaria-discuss> > Post to : maria-discuss@lists.launchpad.net <mailto:maria-discuss@lists.launchpad.net> > Unsubscribe : https://launchpad.net/~maria-discuss <https://launchpad.net/%7Emaria-discuss> > More help : https://help.launchpad.net/ListHelp <https://help.launchpad.net/ListHelp> >
-- *Matthew Pash* Senior Systems Administrator, IT Services University of Bristol
I've managed to resolve this, and it appears to have been a problem with mysqladmin not reading it's defaults when run from cron. For the benefit of anyone else with the same issue, /etc/logrotate.d/mysql now looks like this: /var/log/mysql/mysqld.log { compress daily missingok notifempty rotate 3 postrotate if test -x /usr/bin/mysqladmin && /usr/bin/mysqladmin ping &>/dev/null then /usr/bin/mysqladmin --defaults-file=/root/.my.cnf flush-logs fi endscript } The only change (from RPM new) is adding the defaults-file flag to the mysqladmin command. Since adding that everything seems to be working fine. Thanks for your help. ~Matt On 31 March 2017 at 00:23, Daniel Black <daniel.black@au1.ibm.com> wrote:
On 30/03/17 20:46, Matthew Pash wrote:
Morning Daniel,
Thanks for your reply, I've been doing some more testing. The logrotate script (below) currently runs fine when executed manually (sudo logrotate -v -f /etc/logrotate.d/mysql) but fails when run overnight by cron.
/var/log/mysqld.log { compress daily missingok notifempty rotate 3 postrotate if test -x /usr/bin/mysqladmin && /usr/bin/mysqladmin ping &>/dev/null then /usr/bin/mysqladmin flush-logs fi endscript }
I tweaked it last night to capture the output of the mysqladmin command and it did this:
/usr/bin/mysqladmin: connect to server at 'localhost' failed error: 'Access denied for user 'root'@'localhost' (using password: NO)'
Which makes me think the problem is more about cron than MariaDB.
No, MariaDB doesn't have a passwordless root user that mysqladmin needs to issue the flush-logs command.
I suspect that there was originally a root user with socket authentication this system.
Even after fixing this the mysqld may not be able to create new logs in /var/log/. I'd normally expect /var/log/mysql to be owned by the mysql user and its configuration to log the error to a file in this directory.
I've added some extra information gathering, so we'll see how it runs tonight.
On 27 March 2017 at 22:34, Daniel Black <daniel.black@au1.ibm.com <mailto:daniel.black@au1.ibm.com>> wrote:
I think you'll find that the mysql user don't have permissions to create a new file in /var/log/. As such logrotate renames the file which is still open by mysqld. As such it is now writing to mysqld.log.1.
On 28/03/17 00:13, Matthew Pash wrote: > Hi, > > We've got some database servers running MariaDB 10.1.22, and are having > issues with logrotate. Specifically, after it runs, it leaves a 0 byte > file and continues writing log data to mysqld.log.1 > > Manually running logrotate in verbose mode shows this error from > mysqladmin when it tries to run -flush_logs: > > /usr/bin/mysqladmin: flush failed; error: 'Unknown error' > error: error running non-shared postrotate script for > /var/log/mysqld.log of '/var/log/mysqld.log ' > set default create context
What does the logrotate script look like? It really should be a "shared" (in a logrotate way) script.
> Has anyone seen this before? It doesn't look like a permissions error - > the logs are created fine if you run systemctl restart mariadb.
Is the log actually writing to /var/log/mysqld.log using systemd? It normally writes to the journal because with systemd redirects stderr there
From lsof, it looks like /var/log/mysqld.log is being written to by the mysql process itself: $ sudo lsof | grep mysqld.log mysqld 38803 mysql 1w REG 8,3 31781 537216904 /var/log/mysqld.log.1 mysqld 38803 mysql 2w REG 8,3 31781 537216904 /var/log/mysqld.log.1 ...
> Thanks, > ~Matt > > -- > *Matthew* > Senior Systems Administrator, IT Services > University of Bristol > > > _______________________________________________ > Mailing list: https://launchpad.net/~maria-discuss <https://launchpad.net/%7Emaria-discuss> > Post to : maria-discuss@lists.launchpad.net <mailto:maria-discuss@lists.launchpad.net> > Unsubscribe : https://launchpad.net/~maria-discuss <https://launchpad.net/%7Emaria-discuss> > More help : https://help.launchpad.net/ListHelp <https://help.launchpad.net/ListHelp> >
-- *Matthew Pash* Senior Systems Administrator, IT Services University of Bristol
-- *Matthew Pash* Senior Systems Administrator, IT Services University of Bristol
participants (2)
-
Daniel Black -
Matthew Pash