php, mysql - Too many connections to database erro

问题:

Good day to all. I have an odd error. I have created a chat that works like this:

• questions/answers are inserted into a db
• every 2 seconds an ajax request is sent to a php script that fetch the new questions/answers

It worked fine until today when I got this error:

Uncaught exception 'PDOException' with message 'SQLSTATE[HY000] [1040] Too many connections' in /var/www/html/dbdev/include/Zend/Db/Adapter/Pdo/Abstract.php:129 
Stack trace: #0 /var/www/html/dbdev/include/Zend/Db/Adapter/Pdo/Abstract.php(129): PDO->__construct('mysql:host=loca...', '', '', Array) 
s#1 /var/www/html/dbdev/include/Zend/Db/Adapter/Pdo/Mysql.php(96): Zend_Db_Adapter_Pdo_Abstract->_connect() 
s#2 /var/www/html/dbdev/include/Zend/Db/Adapter/Abstract.php(448): Zend_Db_Adapter_Pdo_Mysql->_connect() 
s#3 /var/www/html/dbdev/include/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query('select profile_...', Array) 
s#4 /var/www/html/dbdev/include/Zend/Db/Adapter/Abstract.php(782): Zend_Db_Adapter_Pdo_Abstract->query('select profile_...', Array) 
s#5 /var/www/html/dbdev/include/Profile.php(43): Zend_Db_Adapter_Abstract->fetchPairs('select profile_...') 
s#6 /var/www/html/dbdev/public_html/index.php(29): Profile->load() 
s#7 {main} Next exception 'Zend_Db_Adapter_Exception' with in /var/www/html/dbdev/include/Zend/Db/Adapter/Pdo/Abstract.php on line 144

The problem is that we were not that many... (8) and I don't think the db could not support more than 8 to 16 connections simultaneously (on specifications is written that the chat must support 50-100 users so 8 is... small).

So... can any1 tell me why did this happen (yesterday we were 15 and worked fine) and how to solve it? Thank you for your help. If you need any code samples just ask.

回答1:

There are a bunch of different reasons for the "Too Many Connections" error.

Check out this FAQ page on MySQL.com: http://dev.mysql.com/doc/refman/5.5/en/too-many-connections.html

Check your my.cnf file for "max_connections". If none exist try:

[mysqld]
set-variable=max_connections=250

However the default is 151, so you should be okay.

If you are on a shared host, it might be that other users are taking up too many connections.

Other problems to look out for is the use of persistent connections and running out of diskspace.

回答2:

The error SQLSTATE[HY000] [1040] Too many connections is an SQL error, and has to do with the sql server. There could be other applications connecting to the server. The server has a maximum available connections number.

If you have phpmyadmin, you can use the 'variables' tab to check what the setting is.

You can also query the status table like so:

show status like '%onn%';

Or some variance on that. check the manual for what variables there are

(be aware, 'connections' is not the current connections, check that link :) )

回答3:

If you are reaching the mac connection limit go to /etc/my.cnf and under the [mysqld] section add max_connections = 500

and restart MySQL.

回答4:

Please check if you open up a new connection with each of your requests (mysql_connect(...)). If you do so, make sure you close the connection afterwards (using mysql_close($link)).

Also, you should consider changing this behaviour as keeping one steady connection for each user may be a better way to accomplish your task.

If you didn't already, take a look at this obvious, but nonetheless useful information resource: http://php.net/manual/function.mysql-connect.php

回答5:

This can happen due to too many connection same time or many chat at same time. Also it can happen due too many session.

The best way to sort out this issue is restart MySQL.

service mysqld restart

or

service mysql restart

or

 /etc/init.d/mysqld restart