MySQL wait_timeout global and session setting issue

Question

In MySQL there are two values for some parameters:

1) For session.
2) For global.

We can check the values for such parameters like:

1) show variables like 'wait_timeout'
2) show global variables like 'wait_timeout'

Now it returns the values:
1) for session = 500
2) For global = 28800

I am able to change the variables by command:
set global wait_timeout=100 ;
set session wait_timeout= 200;

But when i logged in again i am getting the following values:
for session = 500
for global = 100.

It means global values retain and session not, which is absolutely correct. But my concern is how we can change the session variables then for all sessions ? Because global is not that values taken by each session in this case.

in docs, i read that each session gets the value from global variables but why it shows different, any idea ? — Aman Aggarwal
– Aman Aggarwal, Commented Jul 7, 2016 at 6:59

Bernd Buffen · Accepted Answer · 2016-07-07 08:22:17Z

1

Yes,

there a 2 timeouts in MySQL. which one is take for your connection depends on the connections type. one is for the BATCH processing and the other for interactive

the second variable is the interactive_timeout.

look at the setting of interactive_timeout

SHOW VARIABLES LIKE 'interactive_timeout';
SHOW GLOBAL VARIABLES LIKE 'interactive_timeout';

sample login via mysql client

# mysql -uroot -p
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 106426
Server version: 10.1.10-MariaDB-log Homebrew

Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> show variables like 'wait_timeout';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout  | 500   |
+---------------+-------+
1 row in set (0.00 sec)

MariaDB [(none)]> show global variables like 'wait_timeout';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout  | 28800 |
+---------------+-------+
1 row in set (0.00 sec)

MariaDB [(none)]> show variables like 'interactive_timeout';
+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| interactive_timeout | 500   |
+---------------------+-------+
1 row in set (0.01 sec)

MariaDB [(none)]> show global variables like 'interactive_timeout';
+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| interactive_timeout | 500   |
+---------------------+-------+
1 row in set (0.00 sec)

MariaDB [(none)]>

now the same in batch mode

# mysql -uroot -p -e "show variables like 'wait_timeout';"
Enter password:
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout  | 28800 |
+---------------+-------+
#

edited Jul 7, 2016 at 8:22

answered Jul 7, 2016 at 7:08

Bernd Buffen

15.1k2 gold badges27 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Aman Aggarwal Over a year ago

So you mean, when we are connecting via shell, it will take the wait_timeout value same as interactive_timeout ?

Bernd Buffen Over a year ago

normal yes, but it is only a parameter in the connection and i can say how your tools connect. verify the settings in the /etc/mysql/my.cnf and test the setting or set bot values to the same in the my.cnf

Aman Aggarwal Over a year ago

i am conecting via normal mysql client in interactive mode in linux.

Bernd Buffen Over a year ago

@Aman Aggarwal - i have add a sample in my answer there you can see the difference

Aman Aggarwal Over a year ago

i got your point.. So it means interactive timeout becomes wait timeout..

Aman Aggarwal · Accepted Answer · 2016-07-07 09:37:42Z

0

I got the exact description:

On thread startup, the session wait_timeout value is initialized from the global wait_timeout value or from the global interactive_timeout value, depending on the type of client (as defined by the CLIENT_INTERACTIVE connect option to mysql_real_connect())

In MySQL doc wait_timeout description

answered Jul 7, 2016 at 9:37

Aman Aggarwal

18.6k10 gold badges55 silver badges84 bronze badges

Collectives™ on Stack Overflow

MySQL wait_timeout global and session setting issue

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related