Feature Request: A way to safely rotate log files

[nsheridan]

In order to prevent unbounded logfile growth it would be nice to support logfile rotation without needing to restart the process. Handling a SIGHUP (or a flag or something) to signal the process to close and reopen files would be very helpful.

[nsheridan]

I just found that PROXYSQL FLUSH LOGS exists. While it will switch log output from one file to another, it doesn't actually close the files.

Using a logrotate config similar to this one:

/var/lib/proxysql/proxysql.log {
  missingok
  daily
  notifempty
  compress
  create 0600 proxysql proxysql
  rotate 5
}

I test this out by running logrotate -f /var/lib/proxysql/proxysql.log, which renames the logfile to proxysql.log.1, gzips the file and the removes it. lsof shows that the logfile is still open but deleted:

proxysql   799746        root    1w      REG              202,1 174478910     394973 /var/lib/proxysql/proxysql.log.1 (deleted)
proxysql   799746        root    2w      REG              202,1 174478910     394973 /var/lib/proxysql/proxysql.log.1 (deleted)
proxysql   799747        root    1w      REG              202,1 174478910     394973 /var/lib/proxysql/proxysql.log.1 (deleted)
proxysql   799747        root    2w      REG              202,1 174478910     394973 /var/lib/proxysql/proxysql.log.1 (deleted)

Next, flush logs with PROXYSQL FLUSH LOGS. lsof shows:

# lsof |grep proxysql.log
proxysql   799746        root    1w      REG              202,1 174482798     394973 /var/lib/proxysql/proxysql.log.1 (deleted)
proxysql   799746        root    2w      REG              202,1 174482798     394973 /var/lib/proxysql/proxysql.log.1 (deleted)
proxysql   799747        root    1w      REG              202,1       472     394854 /var/lib/proxysql/proxysql.log
proxysql   799747        root    2w      REG              202,1       472     394854 /var/lib/proxysql/proxysql.log

Inspecting the log files:

# tail /proc/799746/fd/1
2017-06-14 13:14:46 [INFO] Received PROXYSQL FLUSH LOGS command

# tail /proc/799747/fd/1
2017-06-14 13:14:46 [INFO] ProxySQL version 1.3.6-0-g434b376
2017-06-14 13:14:46 [INFO] Detected OS: Linux intercom-production-api-ping-vpc.i-0c479be1a982e7c67 4.9.20-11.31.amzn1.x86_64 #1 SMP Thu Apr 13 01:53:57 UTC 2017 x86_64

While the old file won't continue to grow, it will continue to occupy space on disk until it is closed.

[renecannao]

When ProxySQL starts as a daemon, it forks immediately.
Both parent and child process have a file descriptor on error log.
PROXYSQL FLUSH LOGS closes and re-open the log file only on child process: parent process still use the old file.
Therefore, this is a bug! Working on it.
Thank you for the report.

[renecannao]

Fixed in future release 1.4.1

[Reiner030]

The here mentioned lograte configuration is also implemented by @EvgeniyPatlan in
https://github.com/percona/proxysql-admin-tool/blob/master/proxysql-logrotate for Percona package (but there is no issue tracker there)...

The problem is that this empty log after logrotate problem is still open in 1.4.3 percona package.with above configuration.

• A SIGHUP/USR1 signal would be still better/easier for logrotate handling but
• It could be also a postrotate script which needs somehow access to proxysql
  to send the command PROXYSQL FLUSH LOGS
• It could be better to use logrotate option copytruncate so that it's not
  needed to reopen the log by proxysql ?

[renecannao]

To clarify, are you saying PROXYSQL FLUSH LOGS doesn't work in 1.4.3 ?

[Reiner030]

no, that is working fine:

2017-10-09 08:50:43 [INFO] Received PROXYSQL FLUSH LOGS command
2017-10-09 08:50:43 [INFO] ProxySQL version v1.4.3-1.1
2017-10-09 08:50:43 [INFO] Detected OS: Linux tool-fs1dc04-01 4.9.0-0.bpo.3-amd64 #1 SMP Debian 4.9.30-2+deb9u5~bpo8+1 (2017-09-28) x86_64

Only the above mentioned logrotate config which is packaged in Perconas

Package: proxysql
Version: 1.4.3-1.1.jessie

gets empty log after each daily logrotate.

Only adding it here because it fits to the logrotate config file and there is no issue tracker on the other repo but both are (more or less) from Percona offered/supported.

[EvgeniyPatlan]

@Reiner030 we have changed logrotate config file for 1.4.3 so am I right that the issue still exists?

[vrrc]

Hi,

Fresh ProxySQL setup using Percona's repo v1.4.3-1.1.xenial. The logrotate script does not work for /var/lib/proxysql/proxysql.log, but works for the other logs.

After setting passwordless login with:

mysql_config_editor set --login-path=proxysql --host=10.10.10.11 -P 6032 --user=admin --password

To make it work, I have changed the logrotate file to do a "PROXYSQL FLUSH LOGS" after the rotation:

/var/lib/proxysql/*.log {
missingok
daily
notifempty
compress
create 0600 proxysql proxysql
rotate 15
postrotate
/usr/bin/mysql --login-path=proxysql -e "PROXYSQL FLUSH LOGS"
endscript
}

Maybe there are other ways to solve this without having to set login_path.

[EvgeniyPatlan]

thanks for the info @vrrc

[ewangzenefits]

Still see the error after logrotate and so failed to log into error log on v1.4.10

1. create the file /etc/logrotate.d/rproxylog as

/var/lib/proxysql/proxysql.log {
  missingok
  daily
  notifempty
  compress
  create 0600 root root
  rotate 5
}

2. logrotate -f /etc/logrotate.d/rproxysqllog
3. the log file never grow and see the following error

2018-10-29 19:11:25 [INFO] ProxySQL version v1.4.10-1-g5eb0f3e
2018-10-29 19:11:25 [INFO] Detected OS: Linux ProxySQL1C.prd.pdx.znfts.net 4.4.0-1065-aws #75-Ubuntu SMP Fri Aug 10 11:14:32 UTC 2018 x86_64
2018-10-29 19:13:34 [INFO] Received PROXYSQL FLUSH LOGS command
2018-10-29 19:13:34 [INFO] Starting new mysql log file /var/lib/proxysql/queries.log.00000002
2018-10-29 19:13:34 [INFO] ProxySQL version v1.4.10-1-g5eb0f3e
2018-10-29 19:13:34 [INFO] Detected OS: Linux ProxySQL1C.prd.pdx.znfts.net 4.4.0-1065-aws #75-Ubuntu SMP Fri Aug 10 11:14:32 UTC 2018 x86_64
Can't initialize character set (null) (path: compiled_in)
2018-10-29 19:21:41 MySQL_Session.cpp:1847:handler_again___status_CHANGING_CHARSET(): [ERROR] Detected a broken connection during SET NAMES on 10.0.32.8 , 3306 : 2019, Can't initialize character set (null) (path: compiled_in)```

[brianr-dk]

I need to ask, if there's a guide somewhere to make logrotate work for ProxySQL?

I'm running Ubuntu 16.04 and ProxySQL 1.4.9, and it just creates a zipped copy of the latest logfile - but never creates a new /var/lib/proxysql/proxysql.log ?
If I (as root) manually run the "PROXYSQL FLUSH LOGS" command, it works just dandy.

I can't get logrotate to reveal why it's not working for it.

( My latest atttempt is this, using the mysql_config_editor and "--login-path" - and I will know tomorrow morning if THAT'S the way to succeed :) )

[MichalisDBA]

Hi,

Fresh ProxySQL setup using Percona's repo v1.4.3-1.1.xenial. The logrotate script does not work for /var/lib/proxysql/proxysql.log, but works for the other logs.

After setting passwordless login with:

mysql_config_editor set --login-path=proxysql --host=10.10.10.11 -P 6032 --user=admin --password

To make it work, I have changed the logrotate file to do a "PROXYSQL FLUSH LOGS" after the rotation:

/var/lib/proxysql/*.log {
missingok
daily
notifempty
compress
create 0600 proxysql proxysql
rotate 15
postrotate
/usr/bin/mysql --login-path=proxysql -e "PROXYSQL FLUSH LOGS"
endscript
}

Maybe there are other ways to solve this without having to set login_path.

Thank you so much. Log rotation was driving me mad. The official documentation is not correct. Your way of doing logrotate worked as expected.