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?

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