Changing a variable using SET GLOBAL doesn't impacts sessions variable?

I am bit confused between setting the global and session parameters , I am trying to set wait_timeout

The default settings are

SELECT @@global.wait_timeout , @@session.wait_timeout;
+-----------------------+------------------------+
| @@global.wait_timeout | @@session.wait_timeout |
+-----------------------+------------------------+
|                 28800 |                  28800 |
+-----------------------+------------------------+
1 row in set (0.00 sec)

The below SQL will change the session setting

SET wait_timeout = 10;
Query OK, 0 rows affected (0.00 sec)

Check the values , which is pretty clear as well

SELECT @@global.wait_timeout , @@session.wait_timeout;
+-----------------------+------------------------+
| @@global.wait_timeout | @@session.wait_timeout |
+-----------------------+------------------------+
|                 28800 |                     10 |
+-----------------------+------------------------+
1 row in set (0.00 sec)

The below SQL will change it globally

SET GLOBAL wait_timeout = 10;
Query OK, 0 rows affected (0.00 sec)

Check the values , which is pretty clear as well

SELECT @@global.wait_timeout , @@session.wait_timeout;
+-----------------------+------------------------+
| @@global.wait_timeout | @@session.wait_timeout |
+-----------------------+------------------------+
|                    10 |                     10 |
+-----------------------+------------------------+
1 row in set (0.00 sec)

Now , I am closing the MySQL thread and opening a new one , I find the below data

SELECT @@global.wait_timeout , @@session.wait_timeout;
+-----------------------+------------------------+
| @@global.wait_timeout | @@session.wait_timeout |
+-----------------------+------------------------+
|                    10 |                  28800 |
+-----------------------+------------------------+
1 row in set (0.00 sec)

I am not able to relate at this point why the session settings have been changed

In order to change wait_timeout we should also change interactive_timeout variables.

The definition of wait_timeout : The number of seconds the server waits for activity on a noninteractive connection before closing it. On thread startup, the session wait_timeout value is initialized from the global wait_timeout value or from the global interactive_timeout value.

Here is the test

Query without changing any parameter , all the values are set to 8 hours by default

SELECT @@global.wait_timeout , @@session.wait_timeout , @@global.interactive_timeout , @@session.interactive_timeout;
+-----------------------+------------------------+------------------------------+-------------------------------+
| @@global.wait_timeout | @@session.wait_timeout | @@global.interactive_timeout | @@session.interactive_timeout |
+-----------------------+------------------------+------------------------------+-------------------------------+
|                 28800 |                  28800 |                        28800 |                         28800 |
+-----------------------+------------------------+------------------------------+-------------------------------+
1 row in set (0.00 sec)

Change the parameter at session level

SET wait_timeout = 10 , interactive_timeout = 10;
Query OK, 0 rows affected (0.00 sec)

Results , all looks good as expected , global setting stay same and session settings are changed

SELECT @@global.wait_timeout , @@session.wait_timeout , @@global.interactive_timeout , @@session.interactive_timeout;
+-----------------------+------------------------+------------------------------+-------------------------------+
| @@global.wait_timeout | @@session.wait_timeout | @@global.interactive_timeout | @@session.interactive_timeout |
+-----------------------+------------------------+------------------------------+-------------------------------+
|                 28800 |                     10 |                        28800 |                            10 |
+-----------------------+------------------------+------------------------------+-------------------------------+
1 row in set (0.00 sec)

Change the parameter at global level

SET GLOBAL wait_timeout = 10 ;
Query OK, 0 rows affected (0.00 sec)

SET GLOBAL interactive_timeout = 10;
Query OK, 0 rows affected (0.00 sec)

Results , all looks good as expected

SELECT @@global.wait_timeout , @@session.wait_timeout , @@global.interactive_timeout , @@session.interactive_timeout;
+-----------------------+------------------------+------------------------------+-------------------------------+
| @@global.wait_timeout | @@session.wait_timeout | @@global.interactive_timeout | @@session.interactive_timeout |
+-----------------------+------------------------+------------------------------+-------------------------------+
|                    10 |                     10 |                           10 |                            10 |
+-----------------------+------------------------+------------------------------+-------------------------------+
1 row in set (0.00 sec)

Disconnect MySQL thread and connect again , and all looks good again

SELECT @@global.wait_timeout , @@session.wait_timeout , @@global.interactive_timeout , @@session.interactive_timeout;
+-----------------------+------------------------+------------------------------+-------------------------------+
| @@global.wait_timeout | @@session.wait_timeout | @@global.interactive_timeout | @@session.interactive_timeout |
+-----------------------+------------------------+------------------------------+-------------------------------+
|                    10 |                     10 |                           10 |                            10 |
+-----------------------+------------------------+------------------------------+-------------------------------+
1 row in set (0.00 sec)

StackExchange Database Administrators Q#174636, answer score: 8