3

I'm running a Galera Cluster with a HAProxy-LB in front. However my users reporting that the session variable wait_timeout is set to 60.

I checked that with SHOW SESSION VARIABLES LIKE "%wait_timeout%" and for me the result is wait_timeout 610.

In my /etc/mysql/my.cnf (on every cluster node) the value "28800" is set (as default) I can confirm that this is used by running: SHOW GLOBAL VARIABLES LIKE "%wait_timeout%" as the result is wait_timeout 28800

Any ideas why this does not apply to new sessions? The HAProxy is afaik just a stupid forwarder on port 3306..

Edit: Updated all the packages on all cluster nodes. Still the same issue. I even tried to check it with the mysql socket connection for root and the new session spawns with a wait_timeout value of 60. New session for user root

moTHO.
  • 364
  • 3
  • 21
  • Session variable value can be altered during connection process according to the connector settings, for example... – Akina Nov 18 '20 at 07:27
  • I used MySQL Workbench to check the variables, but couldn't find settings for setting the session variables on connection. The user who reported this behavior can connect to another cluster with the session variable set to the right value so I am not sure that this is a connector problem. – moTHO. Nov 18 '20 at 07:39
  • 1
    AFAIR Workbench takes the variable values from the server (config file, [mysql] section). – Akina Nov 18 '20 at 07:54
  • That's what I thought too. I have set the "wait_timeout" under [mysqld] (and if I change it there it affects the global vars). I tried adding it to [mysql] but no changes :( – moTHO. Nov 18 '20 at 09:00
  • *I have set the "wait_timeout" under [mysqld]* This is server settings section. See [mysql] and [client] sections. – Akina Nov 18 '20 at 09:45
  • I tried adding the wait_timeout there but it doesn't affect anything.. – moTHO. Nov 18 '20 at 09:49
  • Look User Manual for complete list of server settings configuration files. Find and investigate them all (additionally look for service/daemon command line options). I do not see another option.. – Akina Nov 18 '20 at 09:52

2 Answers2

1

In general the SESSION VARIABLES are initialized to the GLOBAL settings at the time of establishing the connection. After that, either set of settings could be changed.

However, wait_timeout is especially tricky. Not only are there SESSION and GLOBAL, but there are also interactive and batch. Also, InnoDB has a similar value.

610 is an unusual value. Some person or some program must have changed it.

Are you hitting an unexpected limit?

A "ping" can be used to keep the connection alive.

You can check for the connection having gone away, and restart it.

More specifics for you case, please.

Rick James
  • 135,179
  • 13
  • 127
  • 222
  • Hi and thanks for your answer. I am not having a problem with the application but with the configuration with the Galera Cluster. The GLOBAL variables are all good but the invoked session is not. After restarting the node I connected with Workbench am getting `wait_timeout` 60 and after a reconnect it's 610. It's really just the value of this one session variable I don't get. I just updated all nodes but still the same problem. – moTHO. Nov 20 '20 at 09:11
0

I found the issue. In a different configuration (some admin had made a separate config for "fine tuning" under /etc/mysql/conf.d/finetuning.cnf) was a variable named interactive_timeout which was set to 60. It seems that this one set the session variable of wait_timeout to 60 instead of using the wait_timeout (28800) from the global variables. Commenting out the interactive_timeout solved the problem for me.

moTHO.
  • 364
  • 3
  • 21