4

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 as I already changed the value using SET GLOBAL.

  • Session variable values are established at 'connection' event. Running processes are not impacted in any way by SET GLOBAL gvn=value. New 'connect' events/processes will pick up the current GLOBAL VALUE for the duration of the session connection. You can within your query SET SESSION vn=value and additional processes in the query will have your requested value. This is how you can make that 'long running' task be acceptable for 5 minutes when you might have a typical 60 second limit. – Wilson Hauck Sep 26 '19 at 18:58
7

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)
1

And if you do not change your my.cnf-ini [mysqld] section, after your next shutdown/crash restart, you will be back to the defaults mentioned above.

0

wait_timeout is somewhat abnormal since it has two variants -- batch and interactive.

Others come in two flavors: Just a global, which is immediately reflected in the session, versus globals that are copied into the session when you connect. Which is which? It seems to be a closely guarded secret that is best discovered by experimentation.

  • There are two global variables wait_timeout and interactive_timeout. You may wish to think of interactive_timeout as batch since it seems to be in charge of timeout when you SSH into MySQL for command line processing activities inactivity tolerance. – Wilson Hauck Sep 26 '19 at 18:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.