How to setup a connection timeout depending of the user login in MySQL

Question

I have currently more than 100 connections in Sleep state.

Some connection must stay in Sleep state (and don't close) because it's permanent connection but some others (with a different user name) are from some php script and I want them to timeout very fast.

Is it possible to setup a wait_timeout per user? and if yes, How?

Answer 1

There's no per-user timeout configuration, but you can set the wait_timeout value dynamically. That is, after you make a connection as a given user, you can issue a statement to change the timeout value to what you want it to be for that user's session.

Try the following experiment in the mysql command-line client:

mysql> SHOW VARIABLES LIKE 'wait_timeout';

...shows 28800 (i.e. 8 hours), which is the default wait_timout.

mysql> SET SESSION wait_timeout = 60;
mysql> SHOW VARIABLES LIKE 'wait_timeout';

...shows 60.

Then you can quit the session, reconnect, and again the default wait_timeout is 28800. So it's limited to the scope of the current session.

You can also open a second window and start a separate mysql client session, to prove that changing the wait_timeout in one session does not affect other concurrent sessions.

Answer 2

You should set the following variables in your my.conf:

[mysqld]
interactive_timeout=180
wait_timeout=180

wait_timeout is a timeout for automated connections (in my opinion more than 30 on a web server is too much).
interactive_timeout is a console interaction timeout for idle session.