slow_query_log missing queries

[Jonathan Hartley]

Trying to get ALL queries in a small period logged, general log does not have enough info
So using
SET GLOBAL log_output='TABLE';
SET GLOBAL slow_query_log=0;
SET GLOBAL general_log=0;
TRUNCATE TABLE mysql.slow_log;
TRUNCATE TABLE mysql.general_log;
SET GLOBAL long_query_time=0;
SET GLOBAL slow_query_log=1;
SET GLOBAL general_log=1;
SELECT SLEEP(120);
SET GLOBAL slow_query_log=0;
SET GLOBAL general_log=0;

Then using
SELECT (
SELECT COUNT(*) FROM slow_log WHERE lcase(sql_text) LIKE '%update%'
) slow,
(
SELECT COUNT(*) FROM general_log WHERE lcase(argument) LIKE '%update%'
) general;

You get different results, the slow log has alot less queries in than the general log even if you limit the query times to exclude the beginning and end of the period

[Jonathan Hartley (jon.hartley)]

mysql> SELECT VERSION();
+-----------------+
| VERSION() |
+-----------------+
| 5.6.32-78.0-log |
+-----------------+
1 row in set (0.00 sec)

[Jonathan Hartley (jon.hartley)]

Current settings

| log_slow_admin_statements | OFF |
| log_slow_filter | |
| log_slow_rate_limit | 1 |
| log_slow_rate_type | session |
| log_slow_slave_statements | ON |
| log_slow_sp_statements | ON |
| log_slow_verbosity | |
| log_throttle_queries_not_using_indexes | 0 |

[Jonathan Hartley (jon.hartley)]

mysql> show variables like 'min%';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| min_examined_row_limit | 0 |
+------------------------+-------+

[Sveta Smirnova (svetasmirnova)]

Thank you for the report.

You have option

| log_slow_admin_statements | OFF |

Could you please turn it ON, try again and if issue still repeatable provide example of queries which were not logged?

[Jonathan Hartley (jon.hartley)]

SET GLOBAL log_output='TABLE';
SET GLOBAL slow_query_log=0;
SET GLOBAL general_log=0;
TRUNCATE TABLE mysql.slow_log;
TRUNCATE TABLE mysql.general_log;
SET GLOBAL long_query_time=0;
SET GLOBAL log_slow_rate_type = 'query';
SET GLOBAL slow_query_log=1;
SET GLOBAL general_log=1;
SET GLOBAL log_slow_admin_statements = 1;
SELECT SLEEP(120);
SET GLOBAL slow_query_log=0;
SET GLOBAL general_log=0;

SELECT (
SELECT COUNT(*) FROM slow_log WHERE lcase(sql_text) LIKE '%update%'
) slow,
(
SELECT COUNT(*) FROM general_log WHERE lcase(argument) LIKE '%update%'
) general;
mysql> SELECT (
    -> SELECT COUNT(*) FROM slow_log WHERE lcase(sql_text) LIKE '%update%'
    -> ) slow,
    -> (
    -> SELECT COUNT(*) FROM general_log WHERE lcase(argument) LIKE '%update%'
    -> ) general;
+------+---------+
| slow | general |
+------+---------+
| 645 | 923 |
+------+---------+
1 row in set (0.88 sec)

[Jonathan Hartley (jon.hartley)]

Queries missing are all of the same format, very simple primary key single row updates
e.g.
UPDATE tevoc SET `ev_oc_id` = '60750331',`ev_mkt_id` = '9675871',...... WHERE ev_oc_id = '60750331'

[Shahriyar Rzayev (rzayev-sehriyar)]

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PS-3735