php, mysql - 数据库连接过多错误

php, mysql - Too many connections to database error

提问by zozo

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

• 问题/答案被插入到数据库中
• 每 2 秒一个 ajax 请求被发送到一个获取新问题/答案的 php 脚本

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

问题是我们没有那么多......（8）而且我认为数据库不能同时支持超过 8 到 16 个连接（在规范上写的是聊天必须支持 50-100 个用户，所以 8 是... 小的）。

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.

所以......谁能告诉我为什么会发生这种情况（昨天我们 15 岁并且工作正常）以及如何解决它？感谢您的帮助。如果您需要任何代码示例，请询问。

回答by Alexandru Petrescu

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

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

检查 my.cnf 文件中的“max_connections”。如果不存在尝试：

[mysqld]
set-variable=max_connections=250

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

但是默认值是 151，所以你应该没问题。

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.

其他需要注意的问题是使用持久连接和磁盘空间不足。

回答by Nanne

The error SQLSTATE[HY000] [1040] Too many connectionsis 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.

该错误SQLSTATE[HY000] [1040] Too many connections是SQL错误，与sql server有关。可能有其他应用程序连接到服务器。服务器具有最大可用连接数。

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

如果您有 phpmyadmin，您可以使用“变量”选项卡来检查设置是什么。

You can also query the statustable like so:

您还可以像这样查询状态表：

show status like '%onn%';

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

或者在这方面有所不同。检查手册以了解有哪些变量

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

（请注意，“连接”不是当前连接，请检查该链接:)）

回答by Zimbabao

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

如果您达到 mac 连接限制，请转到/etc/my.cnf并在该[mysqld]部分下添加 max_connections = 500

and restart MySQL.

并重新启动 MySQL。

回答by Laduram Vishnoi

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.

解决此问题的最佳方法是重新启动 MySQL。

service mysqld restart

or

或者

service mysql restart

or

或者

 /etc/init.d/mysqld restart

回答by trautwein

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

请检查您是否为每个请求打开了一个新连接（mysql_connect(...)）。如果这样做，请确保之后关闭连接（使用 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

如果你还没有，看看这个明显但仍然有用的信息资源：http: //php.net/manual/function.mysql-connect.php

回答by ManiMaran A

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 的情况下增加 MySQL 连接，请执行以下操作，如果您不知道配置文件，请在下面使用 mysqlworkbench 或 phpmyadmin 或命令提示符运行以下查询。

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.

这些设置将在 MySQL 重启时更改。

---

---

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

对于永久性更改，在 my.cnf 中添加以下行并重新启动 MySQL。

max_connections = 151