Bug #38704 set global long_query_time=X not dynamic enough
Submitted: 10 Aug 2008 18:04 Modified: 11 Aug 2008 9:38
Reporter: Mark Callaghan Email Updates:
Status: Not a Bug Impact on me:
Category:MySQL Server: Logging Severity:S2 (Serious)
Version:5.1.25,5.0.62 OS:Any
Assigned to: CPU Architecture:Any
Tags: log, long_query_time, query, slow

[10 Aug 2008 18:04] Mark Callaghan
The doc states that long_query_time is dynamic, and it is, but not for the sessions that changes it. The value appears to be set when a connection is made, so if 'set global long_query_time' is run within a connection, that change is ignored by the connection.

How to repeat:
Add to my.cnf:

Connect and run:
set global long_query_time=0
show databases

If long_query_time is dynamic, then 'show databases' should not be logged.

Suggested fix:
I assume that this value is cached in a per-connection object.
[11 Aug 2008 9:38] Sveta Smirnova
Thank you for the report.

According to http://dev.mysql.com/doc/refman/5.1/en/set-option.html:

If you change a global system variable, the value is remembered and used for new connections until the server restarts. (To make a global system variable setting permanent, you should set it in an option file.) The change is visible to any client that accesses that global variable. However, the change affects the corresponding session variable only for clients that connect after the change. The global variable change does not affect the session variable for any client that is currently connected (not even that of the client that issues the SET GLOBAL  statement).

So this is not a bug.