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?

geertjanvdk · Accepted Answer · 2020-05-07T17:37:22.247

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

Thank you , so I got that when we tried show variable like "long_query_time" it is displaying the session variable — neotam, Mar 21 '13 at 07:35

score 5 · Answer 2 · edited Mar 21 '13 at 08:16

5

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

edited Mar 21 '13 at 08:16

Eineki

14,773
6
50
59

answered Mar 21 '13 at 07:25

shola

704
4
11

So , can't we change that variable long_query_time for this mysql version 5.1.66 – neotam Mar 21 '13 at 07:30

Why I could not alter the variable long_query_time variable at runtime

2 Answers2