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?
I checked the mysql.user table and it doesn't look like there is a setting there for it:
+-----------------------+-----------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------------+-----------------------------------+------+-----+---------+-------+
| Host | char(60) | NO | PRI | | |
| User | char(16) | NO | PRI | | |
| Password | char(41) | NO | | | |
| Select_priv | enum('N','Y') | NO | | N | |
| Insert_priv | enum('N','Y') | NO | | N | |
| Update_priv | enum('N','Y') | NO | | N | |
| Delete_priv | enum('N','Y') | NO | | N | |
| Create_priv | enum('N','Y') | NO | | N | |
| Drop_priv | enum('N','Y') | NO | | N | |
| Reload_priv | enum('N','Y') | NO | | N | |
| Shutdown_priv | enum('N','Y') | NO | | N | |
| Process_priv | enum('N','Y') | NO | | N | |
| File_priv | enum('N','Y') | NO | | N | |
| Grant_priv | enum('N','Y') | NO | | N | |
| References_priv | enum('N','Y') | NO | | N | |
| Index_priv | enum('N','Y') | NO | | N | |
| Alter_priv | enum('N','Y') | NO | | N | |
| Show_db_priv | enum('N','Y') | NO | | N | |
| Super_priv | enum('N','Y') | NO | | N | |
| Create_tmp_table_priv | enum('N','Y') | NO | | N | |
| Lock_tables_priv | enum('N','Y') | NO | | N | |
| Execute_priv | enum('N','Y') | NO | | N | |
| Repl_slave_priv | enum('N','Y') | NO | | N | |
| Repl_client_priv | enum('N','Y') | NO | | N | |
| Create_view_priv | enum('N','Y') | NO | | N | |
| Show_view_priv | enum('N','Y') | NO | | N | |
| Create_routine_priv | enum('N','Y') | NO | | N | |
| Alter_routine_priv | enum('N','Y') | NO | | N | |
| Create_user_priv | enum('N','Y') | NO | | N | |
| ssl_type | enum('','ANY','X509','SPECIFIED') | NO | | | |
| ssl_cipher | blob | NO | | | |
| x509_issuer | blob | NO | | | |
| x509_subject | blob | NO | | | |
| max_questions | int(11) unsigned | NO | | 0 | |
| max_updates | int(11) unsigned | NO | | 0 | |
| max_connections | int(11) unsigned | NO | | 0 | |
| max_user_connections | int(11) unsigned | NO | | 0 | |
+-----------------------+-----------------------------------+------+-----+---------+-------+
37 rows in set (0.00 sec)
Depending on whether you're using MySQLi or PDO, your PHP MySQL connections should either hang up when the request does, or be shared in a pool for the Apache process.
For example, with PDO, to turn off persistent connections (I think this is the default), connect to your DB with:
$pdo = new PDO($dsn, $user, $pass, Array(PDO::ATTR_PERSISTENT => false));
If you want your scripts to use persistent connections, but you have too many connections open to your database in sleep mode, you should think about configuring your Apache's MaxServers, MaxSpareServers, MinSpareServers and StartServers so that not so many hang around when they aren't needed.
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.
Another possibility: MySQL supports two different timeout variables, wait_timeout for non-interactive clients, and interactive_timeout for interactive clients.
The difference between interactive and non-interactive clients seems to be simply whether you specified the CLIENT_INTERACTIVE option when connecting.
I don't know if this helps you, because you need to somehow make mysql_real_connect() pass that option in its client_flag parameter. I'm not sure what language or interface you're using, so I don't know if it permits you to specify this connection flag.
Anyway if you can pass that client flag, and you only need two different types of users, then you could configure wait_timeout and interactive_timeout differently in the MySQL server config, and then use the one with the shorter value when you want a given session to time out promptly.
Hi, I have a problem with my program hanging for like 60 minutes when connection is lost from the master database and my hnd->options.connect_timeout = 5 does not work, does anybody have solution for this problem? I am using mysql 5.1 and visual c++.