A change to mysql configuration and my.cnf file is not visible even after mysql restarts

Question

Background: MySQL running in Kubernetes

After changing MySQL's wait_timeout from 28800 to 29800 by using SET GLOBAL wait_timeout = 29800; To my surprise, I can not observe this change by using show variables like 'wait_timeout'. I can only observe this change by using show GLOBAL variables.

mysql> show global variables like 'wait_timeout';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout  | 28800 |
+---------------+-------+
1 row in set (0.01 sec)

mysql> set global wait_timeout=29800;
Query OK, 0 rows affected (0.01 sec)

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

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

I looked up MySQL Documentation and understand that show variables is equal to show session variables. So I decided to restart the server by killing the MySQL container since I think this will refresh the cache and enable a new user session to observe the change.

The pod was re-built by Kubernetes Controller after the docker kill command(my.cnf file is persisted), the server was restarted. But the same thing happens, confusingly, 29800 still can only be viewed with show global variables.

What went wrong? Does a variable modification done by set GLOBAL variables declaration can only be observed with show global variables ? Which doesn't seem to make much sense.

And how come a modification by current session cannot be observed by show session variables like '***'?

Answer 1

wait_timeoout is rather cryptic. There are about 4 values in 2 dimensions:

• GLOBAL vs SESSION: you probably dealt with that by restarting. The GLOBAL value is used to initialize the SESSION value when opening a connection. That is, SET GLOBAL ... has no impact on most SESSION variables of currently running connections.
• batch versus interactive. interactive_timeout is used when using the mysql commandline tool. wait_timeout is for apps (PHP, Java, etc).

Traditionally, SET GLOBAL sticks only until the next restart. However, with MySQL 8.0, there is a PERSIST option to make it stick.

To see the timeouts:

SHOW GLOBAL VARIABLES LIKE '%timeout';
SHOW SESSIOn VARIABLES LIKE '%timeout';

If you are disconnected and reconnected due to wait_timeout, the STATUS value Aborted_clients will be incremented by 1.