Query Logging Requires Restart

[Danny]

I read over the query logging documentation https://proxysql.com/documentation/query-logging/ and I didn't see any mention of it requiring a restart to create the initial /var/lib/proxysql/queries.log.00000001 but, unless I fully restart the ProxySQL service, that file never shows up. Is that a bug?

Steps to reproduce:

Clean Rocky Linux 8 install

RPM install proxysql-2.7.1-1.x86_64

Double checked all settings in place using the above documentation link, and verified:

> SELECT * FROM global_variables WHERE variable_name LIKE 'mysql-eventslog_%' ORDER BY variable_name;
+-----------------------------+----------------+
| variable_name               | variable_value |
+-----------------------------+----------------+
| mysql-eventslog_default_log | 0              |
| mysql-eventslog_filename    | queries.log    |
| mysql-eventslog_filesize    | 104857600      |
| mysql-eventslog_format      | 2              |
+-----------------------------+----------------+

Insert the query rules for a sort of catch all log for a specific database:

> INSERT INTO mysql_query_rules (rule_id,active,schemaname,match_digest,log,apply) VALUES (1,1,'test-database','.',1,0);

LOAD MYSQL QUERY RULES TO RUNTIME; SAVE MYSQL QUERY RULES TO DISK;

Watch the /var/lib/proxysql, and /var/lib/proxysql/queries.log.00000001 isn't there. I restart ProxySQL:

sudo systemctl restart proxysql

...and the file instantly shows up.

Now, with the file finally there, I can delete the above query rule, add a different one, change to a different database, do various other edits and all continues to work. However, that file has to initially be there. Meaning, if I delete it to clean up space. Then ProxySQL will not recreate it even with an updated rule and the LOAD/SAVE commands. Only a restart will get the file(s) to come back.

So, even though not documented, a restart is required? Or is this a bug?

[René Cannaò]

Hi Danny,

What you wrote is absolutely incorrect.

Nonetheless, I suspect you even wrote down what causes the behavior you noticed.

" ... if I delete it to clean up space ... " : if you "delete" a file that is open by a process, the file is not deleted, it is only marked as "unlinked" from the directory.

So it is not that ProxySQL doesn't create the file: you never deleted the file, the file is there and ProxySQL is using it, but you do not "see it" using "ls".

This is a very basic fact about files in Linux. Please refer to unlink manpage: https://man7.org/linux/man-pages/man2/unlink.2.html

And please, do not rm files opened by a process ....

Thanks,

René

--
You received this message because you are subscribed to the Google Groups "proxysql" group.
To unsubscribe from this group and stop receiving emails from it, send an email to proxysql+u...@googlegroups.com.
To view this discussion visit https://groups.google.com/d/msgid/proxysql/667e1a47-25cd-410e-8822-5b6690c38d26n%40googlegroups.com.

[Danny]

René,

Thank you for the quick reply, and I see now that running "SET mysql-eventslog_filename='queries.log';" turns on/off the logging.

So, I should have paid closer attention to my incorrect steps to reproduce. I should have lsof the file, and I would've seen that ProxySQL still had it's lock on it.

SET mysql-eventslog_filename=' queries.log ';

LOAD MYSQL VARIABLES TO RUNTIME; SAVE MYSQL VARIABLES TO DISK;

sudo lsof /var/lib/proxysql/queries.log.00000001
COMMAND      PID     USER   FD   TYPE DEVICE SIZE/OFF    NODE NAME
proxysql 3738303 proxysql   52w   REG  253,3        0 8438531 /var/lib/proxysql/queries.log.00000001

Now shut it off with:

SET mysql-eventslog_filename=' ';

LOAD MYSQL VARIABLES TO RUNTIME; SAVE MYSQL VARIABLES TO DISK;

sudo lsof /var/lib/proxysql/queries.log.00000001

...as expected, comes back blank.

So, if I really want to properly clean up after an investigation, "stop" the logging with the above " SET mysql-eventslog_filename=' '; ", then clean up the file(s) as needed. Then, if I need to investigate something else, rerun the proper " SET mysql-eventslog_filename=' queries.log ';" and I noted that the file is instantly created again after the LOAD/SAVE. Doing that, future investigations will have the new (blank) /var/lib/proxysql/queries.log.00000001 ready to review. I incorrectly thought that, simply deleting the query rule stopped the logging, but that is not the case (and lsof would've showed me that, had I used it). I have to adjust the mysql-eventslog_filename. Makes sense! Wow, it's even the first step in the documentation "First, enable logging globally". I was too focused on the query rules. :-(

Apologies for the incorrectness and confusion, but all good now.

Thank you.