Not the answer you need?
Register and ask your own question!

pmm always change my log_output variables!

FanFan ContributorCurrent User Role Patron
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 [email protected] on using Socket
2019-07-25T06:32:13.001384Z 17853345 Connect [email protected] 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?

Comments

  • FanFan Contributor Current User Role Patron
  • FanFan Contributor Current User Role Patron
    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
  • FanFan Contributor Current User Role Patron
    Fan wrote: »
    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 . :(
  • Dillon_luDillon_lu Contributor Current User Role Novice
    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.
  • FanFan Contributor Current User Role Patron
    Dillon_lu wrote: »
    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
  • FanFan Contributor Current User Role Patron
    still no answer
  • lorraine.pocklingtonlorraine.pocklington Percona Community Manager Legacy User Role Patron
    Hey there, let me check in with the team in case this post has passed them by... We always appreciate your input Fan :)
  • roma.novikovroma.novikov Percona Percona Staff Role
    Hi Fan .Yes this is a known problem. We are planning to fix this in PMM 2.0.0. version
  • FanFan Contributor Current User Role Patron
    Hi Fan .Yes this is a known problem. We are planning to fix this in PMM 2.0.0. version

    Thanks , all best
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.