20

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.

Charles
  • 50,943
  • 13
  • 104
  • 142
zozo
  • 8,230
  • 19
  • 79
  • 134
  • Whats out put of "show variables like 'max_connections';" on mysql client – Zimbabao Feb 18 '11 at 10:19
  • This error message can also occur when mysql is unable to open the connection due to the disk containing the `data-dir` being full. – Will B. Dec 18 '18 at 14:50

6 Answers6

40

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.

Alexandru Petrescu
  • 3,449
  • 2
  • 23
  • 23
17

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 :) )

Nanne
  • 64,065
  • 16
  • 119
  • 163
8

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.

Muhammad Hassaan
  • 7,296
  • 6
  • 30
  • 50
Zimbabao
  • 8,150
  • 3
  • 29
  • 36
5

If you need to increase MySQL Connections without MySQL restart do like below, also if you don't know configuration file, below use the mysqlworkbench or phpmyadmin or command prompt to run below queries.

mysql> show variables like 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 151   |
+-----------------+-------+
1 row in set (0.00 sec)

mysql> SET GLOBAL max_connections = 250;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 250   |
+-----------------+-------+
1 row in set (0.00 sec)

These settings will change at MySQL Restart.

For permanent changes add below line in my.cnf and restart MySQL.

max_connections = 151
ManiMaran A
  • 309
  • 3
  • 6
2

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

trautwein
  • 480
  • 1
  • 5
  • 14
1

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
Laduram Vishnoi
  • 19
  • 4