Why I could not alter the variable long_query_time variable at runtime
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
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