Why I could not alter the variable long_query_time variable at runtime

Question

I am using MySQL version 5.1.66. I saw that the long_query_time variable is dynamic, but when I tried

set GLOBAL long_query_time=1; 

After the above operation again I tried

mysql> show variables like 'long_query_time';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.00 sec)

From the mysql console it is not getting altered , why?

Satori

Answer 1

You are setting a GLOBAL system variable, but you querying for the SESSION variable. For the GLOBAL variable setting to take effect for the current session, you need to reconnect, or set the @@SESSION.long_query_time variable. (Note that SHOW VARIABLES by default shows the session variables.)

Here is an example:

mysql> SHOW SESSION VARIABLES LIKE "long_query_time";
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+

mysql> SET @@GLOBAL.long_query_time = 1;

mysql> SHOW GLOBAL VARIABLES LIKE "long_query_time";
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 1.000000 |
+-----------------+----------+

mysql> SHOW VARIABLES LIKE "long_query_time";
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+

---

MySQL 8.0 introduced the SET PERSIST .. syntax which could help persist configuration you are setting dynamically. See the MySQL 8.0 manual

Stereotyped

Thank you , so I got that when we tried show variable like "long_query_time" it is displaying the session variable – Satori

Answer 2

Changing a system variable value in mysql (cfr. http://dev.mysql.com/doc/refman/5.1/en/set-statement.html) does not alter the value for clients already connected to a session.

The change will last until server reboot, subsequent changes or session expiration.

please refer http://bugs.mysql.com/bug.php?id=38704 for more details

Diathesis

So , can't we change that variable long_query_time for this mysql version 5.1.66 – Satori