MySQL

MySQL’s wait_timeout and interactive_timeout Variables

Ever get these errors?

Mysql reporting error when accessing plesk. Zend_Db_Adapter_Exception: SQLSTATE[08004] [1040] Too many connections.

You can set-variable=max_connections=250 to /etc/my.cnf and restart and use mysql close statements for php code.

Or, set up mysql to close sleeping processes.

Mysql Config. (/etc/my.cnf)
wait_timeout variable represents the amount of time that MySQL will wait before killing an idle connection.
The default wait_timeout variable is 28800 seconds, which is 8 hours. That’s a lot.

Sometimes, putting wait_timeout too low (e.g. 30, 60, 90) can result in MySQL has gone away error messages. You’ll have to decide for your configuration.

How to change it.

Step 1) Edit your /etc/my.cnf file and enter the following 2 values.


[mysqld]
interactive_timeout=300
wait_timeout=300

Step 2) run the command and enter your root password


mysql -uroot -p -e"SET GLOBAL wait_timeout=300; SET GLOBAL interactive_timeout=300;"

If you are connected from the mysql console
e.g. mysql> you can run this command which will show you global and session variables.


SELECT @@global.wait_timeout, @@global.interactive_timeout, @@session.wait_timeout, @@session.interactive_timeout;

+-----------------------+------------------------------+------------------------+-------------------------------+
| @@global.wait_timeout | @@global.interactive_timeout | @@session.wait_timeout | @@session.interactive_timeout |
+-----------------------+------------------------------+------------------------+-------------------------------+
|                   300 |                          300 |                  28800 |                         28800 |
+-----------------------+------------------------------+------------------------+-------------------------------+
1 row in set (0.00 sec)

To see the the current values you can run this command


mysql> show global variables like '%timeout%';

+----------------------------+----------+
| Variable_name              | Value    |
+----------------------------+----------+
| connect_timeout            | 10       |
| delayed_insert_timeout     | 300      |
| innodb_lock_wait_timeout   | 50       |
| innodb_rollback_on_timeout | OFF      |
| interactive_timeout        | 300      |
| lock_wait_timeout          | 31536000 |
| net_read_timeout           | 30       |
| net_write_timeout          | 60       |
| slave_net_timeout          | 3600     |
| wait_timeout               | 300      |
+----------------------------+----------+
10 rows in set (0.00 sec)