Logging all queries #561

sschueller · 2016-05-03T13:24:35Z

Is it currently possible to log all queries to file?

I am having an issue where my application when going via proxysql (works fine going directly to mariadb) uses the wrong database in one query but I don't know what is causing it. I would like to log everything and then figure out what exactly is going on.

renecannao · 2016-05-04T16:56:04Z

Hi.

Setting mysql-eventslog_filename , for example:

SET mysql-eventslog_filename='queries.log'

will cause all the queries to be logged together with a lot of metadata like destination hostgroup, execution time, etc.
Although, the queries are logged in binary format and currently isn't easy to parse.
This functionality is still not documented because there is still no client application able to read the log file, but it is in the roadmap.
Please feel free to drop me an email with details and I will be happy to help you debugging this issue.

Thank you

renecannao · 2016-05-07T00:03:49Z

Prioritizing this issue, and setting next week as target.

- added new function realtime_time() to get real time - added support for multiple events, default PROXYSQL_QUERY - MySQL_Event::write() will perfom a different action depending from log_event_type - rewrote part of eventslog_reader_sample.cpp: - it had few bugs - MySQL_Event::read() will perform a different action depending from log_event_type - time are printed in real time and not monotonic time - all info about a query are printed in one line

nishitm · 2017-02-13T07:11:00Z

Hi @renecannao ,

Is it now possible to log all queries to file? also is it possible to read logfile normally? Also I am not able to log to the "quesries.log" as you described above.

renecannao · 2017-02-13T09:44:18Z

Hi @nishitm .

Yes, it is possible to log all queries to file.
When this issue was created (almost a year ago) it was enough to enable logging globally (SET mysql-eventslog_filename='queries.log'): in this way all queries were logged.
This could be a lot of overhead for very busy systems, so the implementation was changed (I will try to update the wiki this week). To enable logging you should:

enable logging globally (SET mysql-eventslog_filename='queries.log')
define what queries you want to log setting mysql_query_rules.log=1

This allows you to defines which queries to log, and which queries not to log, therefore to be very granular.
If you want to log ALL queries, you can create a simple rules (perhaps the first one) that just enable logging of all queries:

INSERT INTO mysql_query_rules (rule_id, active, query_digest, log,apply) VALUES (1,1,'.',1,0);

Queries can be read using the example tool eventslog_reader_sample. Here an example:

$ ./tools/eventslog_reader_sample /var/lib/proxysql/file1.log.00001258
ProxySQL LOG QUERY: thread_id="2" username="root" schemaname=information_schema" client="127.0.0.1:58307" HID=0 server="127.0.0.1:3306" starttime="2016-10-23 12:34:37.132509" endtime="2016-10-23 12:34:38.347527" duration=1215018us digest="0xC5C3C490CA0825C1"
select sleep(1)
ProxySQL LOG QUERY: thread_id="2" username="root" schemaname=information_schema" client="127.0.0.1:58307" HID=0 server="127.0.0.1:3306" starttime="2016-10-23 12:41:38.604244" endtime="2016-10-23 12:41:38.813587" duration=209343us digest="0xE9D6D71A620B328F"
SELECT DATABASE()
ProxySQL LOG QUERY: thread_id="2" username="root" schemaname=test" client="127.0.0.1:58307" HID=0 server="127.0.0.1:3306" starttime="2016-10-23 12:42:38.511849" endtime="2016-10-23 12:42:38.712609" duration=200760us digest="0x524DB8D7A9B4C132"
select aaaaaaa

The tool eventslog_reader_sample is just a sample: you can modify it to have the output in whatever format you wish.

nishitm · 2017-02-13T09:50:56Z

Thanx @renecannao .. It was a great help !!

leeparayno · 2017-03-23T02:59:04Z

@renecannao I was trying to follow these instructions to log queries on CentOS 6, but am getting this when trying to compile the eventslog_reader_sample:

$ make
g++ -ggdb -o eventslog_reader_sample eventslog_reader_sample.cpp
eventslog_reader_sample.cpp: In member function ‘void MySQL_Event::read_query(std::fstream*)’:
eventslog_reader_sample.cpp:115: error: ‘UINT64_MAX’ was not declared in this scope
make: *** [eventslog_reader_sample] Error 1

I tried to to download the Boost libraries to try to compile with including this:

#include <boost/cstdint.hpp>

but I get the same error.

renecannao · 2017-03-23T08:50:45Z

@leeparayno : created #964 for this specific compiling issue.
Closing #561

shanthibyesmail · 2017-10-27T14:41:05Z

I am trying to log all queries to a file. Here is what i did so far

--Update global variable mysql-eventslog_filename to /tmp/psnew1.log
-- Save setting to disk and loaded into runtime.

update global_variables set variable_value = '/tmp/psnew1.log' where variable_name = 'mysql-eventslog_filename’;

SELECT * FROM global_variables WHERE variable_name LIKE '%event%';

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

--Trying to add rule to log all queries
Delete from mysql_query_rules;

INSERT INTO mysql_query_rules (rule_id, active, query_digest, log,apply) VALUES (1,1,'.',1,0);

Seems like query_digest column is not there in mysql_query_rules table. What is the equivalent column ?

Ulrar · 2017-11-08T10:43:56Z

Same problem here. I tried using "digest" instead of "query_digest", and I get no errors, but the file in /tmp just never gets created.

KevinBott · 2017-11-09T18:57:41Z

@shanthibyesmail -- Did you load the rules from Memory to Runtime? Also don't forget to save them to disk.

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

https://github.com/sysown/proxysql/wiki/Multi-layer-configuration-system

harunkucuk5 · 2018-07-11T12:11:44Z

@Ulrar , @shanthibyesmail ,

you could use below command to activate logging and then log file will be created proxysql folder. not /tmp folder.

Query: SET mysql-eventslog_filename='queries.log'

Log File: /var/lib/proxysql/queries.log.00000001

hf200012 · 2021-04-14T03:36:04Z

*** Error in `./eventslog_reader_sample': double ======= Backtrace: =========
/lib64/libc.so.6(+0x81299)[0x7f3d6c942299]
./eventslog_reader_sample[0x401ae4]
./eventslog_reader_sample[0x401288]
/lib64/libc.so.6(__libc_start_main+0xf5)[0x7f3d6c8e355 ./eventslog_reader_sample[0x400ec9]
======= Memory map: ========
00400000-00403000 r-xp 00000000 08:13 85459018 00602000-00603000 r--p 00002000 08:13 85459018 00603000-00604000 rw-p 00003000 08:13 85459018 02159000-0217a000 rw-p 00000000 00:00 0 7f3d68000000-7f3d68021000 rw-p 00000000 00:00 0
7f3d68021000-7f3d6c000000 ---p 00000000 00:00 0
7f3d6c8c1000-7f3d6ca85000 r-xp 00000000 08:13 83627622 7f3d6ca85000-7f3d6cc84000 ---p 001c4000 08:13 83627622 7f3d6cc84000-7f3d6cc88000 r--p 001c3000 08:13 83627622 7f3d6cc88000-7f3d6cc8a000 rw-p 001c7000 08:13 83627622 7f3d6cc8a000-7f3d6cc8f000 rw-p 00000000 00:00 0
7f3d6cc8f000-7f3d6cca4000 r-xp 00000000 08:13 83635491 7f3d6cca4000-7f3d6cea3000 ---p 00015000 08:13 83635491 7f3d6cea3000-7f3d6cea4000 r--p 00014000 08:13 83635491 7f3d6cea4000-7f3d6cea5000 rw-p 00015000 08:13 83635491 7f3d6cea5000-7f3d6cfa6000 r-xp 00000000 08:13 83627630 7f3d6cfa6000-7f3d6d1a5000 ---p 00101000 08:13 83627630 7f3d6d1a5000-7f3d6d1a6000 r--p 00100000 08:13 83627630 7f3d6d1a6000-7f3d6d1a7000 rw-p 00101000 08:13 83627630 7f3d6d1a7000-7f3d6d290000 r-xp 00000000 08:13 83627668 7f3d6d290000-7f3d6d490000 ---p 000e9000 08:13 83627668 7f3d6d490000-7f3d6d498000 r--p 000e9000 08:13 83627668 7f3d6d498000-7f3d6d49a000 rw-p 000f1000 08:13 83627668 7f3d6d49a000-7f3d6d4af000 rw-p 00000000 00:00 0
7f3d6d4af000-7f3d6d4d1000 r-xp 00000000 08:13 83623962 7f3d6d6c1000-7f3d6d6c6000 rw-p 00000000 00:00 0
7f3d6d6cd000-7f3d6d6d0000 rw-p 00000000 00:00 0
7f3d6d6d0000-7f3d6d6d1000 r--p 00021000 08:13 83623962 7f3d6d6d1000-7f3d6d6d2000 rw-p 00022000 08:13 83623962 7f3d6d6d2000-7f3d6d6d3000 rw-p 00000000 00:00 0
7ffedce57000-7ffedce78000 rw-p 00000000 00:00 0 7ffedcf2f000-7ffedcf31000 r-xp 00000000 00:00 0 ffffffffff600000-ffffffffff601000 r-xp 00000000 00:00 0 free or corruption (fasttop): 0x00000000021592d0 ***
5]
/soft/tools/eventslog_reader_sample
/soft/tools/eventslog_reader_sample
/soft/tools/eventslog_reader_sample
[heap]
/usr/lib64/libc-2.17.so
/usr/lib64/libc-2.17.so
/usr/lib64/libc-2.17.so
/usr/lib64/libc-2.17.so
/usr/lib64/libgcc_s-4.8.5-20150702.so.1
/usr/lib64/libgcc_s-4.8.5-20150702.so.1
/usr/lib64/libgcc_s-4.8.5-20150702.so.1
/usr/lib64/libgcc_s-4.8.5-20150702.so.1
/usr/lib64/libm-2.17.so
/usr/lib64/libm-2.17.so
/usr/lib64/libm-2.17.so
/usr/lib64/libm-2.17.so
/usr/lib64/libstdc++.so.6.0.19
/usr/lib64/libstdc++.so.6.0.19
/usr/lib64/libstdc++.so.6.0.19
/usr/lib64/libstdc++.so.6.0.19
/usr/lib64/ld-2.17.so
/usr/lib64/ld-2.17.so
/usr/lib64/ld-2.17.so
[stack]
[vdso]
[vsyscall]

qadqdew · 2021-08-01T04:10:27Z

@Ulrar , @shanthibyesmail ,

you could use below command to activate logging and then log file will be created proxysql folder. not /tmp folder.

Query: SET mysql-eventslog_filename='queries.log'

Log File: /var/lib/proxysql/queries.log.00000001

Hi @nishitm .

Yes, it is possible to log all queries to file.
When this issue was created (almost a year ago) it was enough to enable logging globally (SET mysql-eventslog_filename='queries.log'): in this way all queries were logged.
This could be a lot of overhead for very busy systems, so the implementation was changed (I will try to update the wiki this week). To enable logging you should:
* enable logging globally (`SET mysql-eventslog_filename='queries.log'`)

* define what queries you want to log setting `mysql_query_rules`.`log`=`1`
This allows you to defines which queries to log, and which queries not to log, therefore to be very granular.
If you want to log ALL queries, you can create a simple rules (perhaps the first one) that just enable logging of all queries:
INSERT INTO mysql_query_rules (rule_id, active, query_digest, log,apply) VALUES (1,1,'.',1,0);
Queries can be read using the example tool eventslog_reader_sample. Here an example:
$ ./tools/eventslog_reader_sample /var/lib/proxysql/file1.log.00001258
ProxySQL LOG QUERY: thread_id="2" username="root" schemaname=information_schema" client="127.0.0.1:58307" HID=0 server="127.0.0.1:3306" starttime="2016-10-23 12:34:37.132509" endtime="2016-10-23 12:34:38.347527" duration=1215018us digest="0xC5C3C490CA0825C1"
select sleep(1)
ProxySQL LOG QUERY: thread_id="2" username="root" schemaname=information_schema" client="127.0.0.1:58307" HID=0 server="127.0.0.1:3306" starttime="2016-10-23 12:41:38.604244" endtime="2016-10-23 12:41:38.813587" duration=209343us digest="0xE9D6D71A620B328F"
SELECT DATABASE()
ProxySQL LOG QUERY: thread_id="2" username="root" schemaname=test" client="127.0.0.1:58307" HID=0 server="127.0.0.1:3306" starttime="2016-10-23 12:42:38.511849" endtime="2016-10-23 12:42:38.712609" duration=200760us digest="0x524DB8D7A9B4C132"
select aaaaaaa
The tool eventslog_reader_sample is just a sample: you can modify it to have the output in whatever format you wish.

I would like to check the number of rows sent for specific query in proxysql. I am using proxysql-1.4.8 & have enabled default eventslog_reader_sample to read the query log which didn't show the rows returned. Any other possibility to check row count ?

Krishnakumarklk · 2023-02-15T09:03:40Z

Hi Team,

In ProxySQL, I have 4 users like user1,..user4 and i have enabled queries log also, i want only configure queries log for user1 and user3 and i want to deny queries log for user2 and user 4.

Can someone help me to how to do this? i have followed some blogs but it's not working as expected.

swaroopak · 2023-02-23T17:27:34Z

Hi Team, is there a way to regularly purge the query logs? Lets say delete query logs after 7 days.

renecannao self-assigned this May 4, 2016

renecannao added this to the Truls milestone May 4, 2016

renecannao added the GLOBAL label May 4, 2016

renecannao mentioned this issue Oct 9, 2016

mysql_query_rules log not working #723

Closed

renecannao mentioned this issue Jan 7, 2017

Log queries in JSON format #871

Closed

renecannao closed this as completed Mar 23, 2017

renecannao mentioned this issue Apr 16, 2017

temporary table is lost #939

Open

dtest mentioned this issue Sep 19, 2017

Feature Request: Query logging to embedded database #1184

Open

renecannao mentioned this issue Nov 10, 2017

How to setup logging of the proxysql #1235

Closed

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Logging all queries #561

Logging all queries #561

sschueller commented May 3, 2016

renecannao commented May 4, 2016

renecannao commented May 7, 2016

nishitm commented Feb 13, 2017 •

edited

Loading

renecannao commented Feb 13, 2017

nishitm commented Feb 13, 2017

leeparayno commented Mar 23, 2017

renecannao commented Mar 23, 2017

shanthibyesmail commented Oct 27, 2017

Ulrar commented Nov 8, 2017

KevinBott commented Nov 9, 2017

harunkucuk5 commented Jul 11, 2018

hf200012 commented Apr 14, 2021

qadqdew commented Aug 1, 2021

Krishnakumarklk commented Feb 15, 2023

swaroopak commented Feb 23, 2023

Logging all queries #561

Logging all queries #561

Comments

sschueller commented May 3, 2016

renecannao commented May 4, 2016

renecannao commented May 7, 2016

nishitm commented Feb 13, 2017 • edited Loading

renecannao commented Feb 13, 2017

nishitm commented Feb 13, 2017

leeparayno commented Mar 23, 2017

renecannao commented Mar 23, 2017

shanthibyesmail commented Oct 27, 2017

Ulrar commented Nov 8, 2017

KevinBott commented Nov 9, 2017

harunkucuk5 commented Jul 11, 2018

hf200012 commented Apr 14, 2021

qadqdew commented Aug 1, 2021

Krishnakumarklk commented Feb 15, 2023

swaroopak commented Feb 23, 2023

nishitm commented Feb 13, 2017 •

edited

Loading