pmm always change my log_output variables!

We are building our own slow query platform. we set global log_output=FILE,TABLE, then slow query will log into mysql.slow_log table and slow log file. The mysql.slow_log table is a CSV engine table. so we can use filebeat to collect all mysql instances slow query to kafka , then we consume the slow query messgae…

But !, we found the log_output variable always changed back to FILE… So I set general log on and finally found the “criminal” – pmm . by the way we use pmm 1.17.1 pmm-client is 1.17.0

2019-07-25T06:32:13.001278Z 17853344 Connect pmm@localhost on using Socket
2019-07-25T06:32:13.001384Z 17853345 Connect pmm@localhost on using Socket
2019-07-25T06:32:13.001438Z 17853344 Query SET time_zone=’+00:00’
2019-07-25T06:32:13.001543Z 17853345 Query SET time_zone=’+00:00’
2019-07-25T06:32:13.001653Z 17853344 Query SELECT /* percona-qan-agent */ CONCAT_WS(’.’, @@hostname, IF(@@port=‘3306’,NULL,@@port)) AS Hostname, @@version_comment AS Distro, @@version AS Version
2019-07-25T06:32:13.001818Z 17853345 Query SET GLOBAL slow_query_log=OFF
2019-07-25T06:32:13.001871Z 17853344 Quit
2019-07-25T06:32:13.002268Z 17853345 Query SET GLOBAL log_output=‘file’
2019-07-25T06:32:13.002426Z 17853345 Query SET GLOBAL slow_query_log=ON
2019-07-25T06:32:13.002704Z 17853345 Query SET time_zone=’+0:00’
2019-07-25T06:32:13.002800Z 17853345 Quit

can pmm stop changing my log_output variable?
I am also confused why excute “SET GLOBAL slow_query_log=OFF;SET GLOBAL log_output=‘file’;SET GLOBAL slow_query_log=ON”? What is the purpose of executing these statement?

Peter
@Michael Coburn

I guess “SET GLOBAL slow_query_log=OFF;SET GLOBAL log_output=‘file’;SET GLOBAL slow_query_log=ON” is used for log rotate? we use the default settin of --slow-log-rotation for mysql:queries.

I manually run : cd /usr/local/percona/qan-agent/config && sed -i ‘s/“SlowLogRotation”:\ true/“SlowLogRotation”:\ false/g’ qan-*.conf. to change slow_log_rotation to false


SERVICE TYPE NAME LOCAL PORT RUNNING DATA SOURCE OPTIONS


mysql:queries 192.168.x.30_3306 - YES pmm:***@unix(/data/xtradb/mysql.sock) query_source=slowlog, query_examples=true, slow_log_rotation=false, retain_slow_logs=1

and I set global log_output=FILE,TABLE.
I will see if log_output change back to ‘FILE’ again tomorrow

the log_output changed back to ‘FILE’ again . :frowning:

I had similar problem. When you add mysql install by pmm-admin client, try this “–query-source=perfschema”. The default is to get slow query from slow log. PMM client retains a few files.

this is a way to solve the problem , but i do not want to use perfschema. (i used a crontab change log_output=FILE,TABLE every 1 minute…)
this bug is sucks!, pmm team should fix it !
Michael Coburn
Peter

still no answer

Hey there, let me check in with the team in case this post has passed them by… We always appreciate your input Fan :slight_smile:

Hi Fan .Yes this is a known problem. We are planning to fix this in PMM 2.0.0. version

Thanks , all best