Proxysql: Logging all queries

[sschueller]

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]

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]

Prioritizing this issue, and setting next week as target.

[nishitm]

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]

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]

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

[leeparayno]

@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

but I get the same error.

[renecannao]

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

[shanthibyesmail]

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]

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]

@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]

@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]

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