long_query_time Changes Fail to Take Effect

Scenario

The value of long_query_time was successfully changed on the console, but changed value failed to be applied.

Possible Causes

When you change the long_query_time value on the console, the system actually uses set global <variable name> = <new variable value> to modify global parameters.

The new parameter value cannot be applied for the current connection and other connections that have been connected to the database. It means that the new parameter value is applied only for new connections. After you disconnect and reconnect all connections, the new parameter value is applied.

Example

Commands in this example explain how to apply the changed parameter value.

1. Create session 1.

   Check the value of long_query_time.
   show variables like 'long_query_time'; 
   +-----------------+-----------+ 
   | Variable_name   | Value     | 
   +-----------------+-----------+ 
   | long_query_time | 10.000000 | 
   +-----------------+-----------+ 
   1 row in set (0.08 sec)  
   Change the value of long_query_time.
   set global long_query_time=1; 
   Query OK, 0 rows affected (0.02 sec)  
   # View the value of long_query_time. The changed value is not applied.
   show variables like 'long_query_time'; 
   +-----------------+-----------+ 
   | Variable_name   | Value     | 
   +-----------------+-----------+ 
   | long_query_time | 10.000000 | 
   +-----------------+-----------+ 
   1 row in set (0.01 sec)  

2. Create session 2.

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

3. Execute the following commands in session 1.

   # After the set global command is executed in connection 1, the changed parameter value is not applied.
   show variables like 'long_query_time'; 
   +-----------------+-----------+ 
   | Variable_name   | Value     | 
   +-----------------+-----------+ 
   | long_query_time | 10.000000 | 
   +-----------------+-----------+ 
   1 row in set (0.01 sec)  

   # After you disconnect session 1 and reconnect it, the new parameter value is applied.
   show variables like 'long_query_time'; 
   +-----------------+----------+ 
   | Variable_name   | Value    | 
   +-----------------+----------+ 
   | long_query_time | 1.000000 | 
   +-----------------+----------+ 
   1 row in set (0.00 sec)  

4. Disconnect session 2 and reconnect it. The new parameter value is applied.

   show variables like 'long_query_time'; 
   +-----------------+----------+ 
   | Variable_name   | Value    | 
   +-----------------+----------+ 
   | long_query_time | 1.000000 | 
   +-----------------+----------+ 
   1 row in set (0.01 sec)