Bug #52497 MySQL slow query log not working

• Submitted: 31 Mar 2010 11:02
• Modified: 30 Apr 2010 13:27
• Reporter: shishu naik
• Status: No Feedback
• Category: MySQL Server: Logging
• Severity: S3 (Non-critical)
• Version: 5.0.45-log
• OS: Linux
• CPU Architecture: Any
• Tags: slow query log

[31 Mar 2010 11:02] shishu naik

Description:
Hi,

The slow query log is enabled with the following options

long_query_time = 10
log-queries-not-using-indexes
sync-binlog=1
log-bin=/var/log/mysql/host
log-error=/var/log/mysql/host.err
log-slow-queries=/var/log/mysql/host-slow-queries.log 

As log-queries-not-using-indexes will also log queries in the slow query log . We disable it using set global log-queries-not-using-indexes=0;
and change the long_query time to 2 seconds.
But in the slow query log we are getting queries which have query time for 0 seconds.

Please find the last ten lines of slow query log
# User@Host: user1[user1] @  [XX.XX.XX.XX]
# Query_time: 0  Lock_time: 0  Rows_sent: 3  Rows_examined: 736
SELECT alias,title,id FROM tab1 WHERE catid = 162 ORDER BY id ASC;
# User@Host: user1[user1] @  [XX.XX.XX.XX]
# Query_time: 0  Lock_time: 0  Rows_sent: 3  Rows_examined: 736
SELECT alias,title,id FROM tab1 WHERE catid = 165 ORDER BY id ASC;
# User@Host: user1[user1] @  [XX.XX.XX.XX]
# Query_time: 0  Lock_time: 0  Rows_sent: 8  Rows_examined: 741
SELECT alias,title,id FROM tab1 WHERE catid = 166 ORDER BY id ASC;
# User@Host: user1[user1] @  [XX.XX.XX.XX]
# Query_time: 0  Lock_time: 0  Rows_sent: 1  Rows_examined: 734
SELECT alias,title,id FROM tab1 WHERE catid = 167 ORDER BY id ASC;
# User@Host: user1[user1] @  [XX.XX.XX.XX]
# Query_time: 0  Lock_time: 0  Rows_sent: 1  Rows_examined: 734
SELECT title,id,extra_fields FROM tab1 WHERE catid = 164 ORDER BY id DESC;

Regards,
Shishu

How to repeat:

Enable and disable log-queries-not-using-indexes to identify the query logging.

Suggested fix:

set global log-queries-not-using-indexes=0; should work without restarting the mysql service.

[31 Mar 2010 13:27] Valeriy Kravchuk

Thank you for the problem report. Please, check if the same problem happens with a newer version, 5.0.90, and inform about the results.

[1 May 2010 23:00] Bugs System

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".