mySQL 错误 1040：连接过多
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14331032/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
mySQL Error 1040: Too Many Connection
提问by kelvzy
How to fix these, "SQL Error 1040: Too Many Connection" even I try to put
如何解决这些问题，“SQL 错误 1040：连接太多”，即使我尝试放置
max_user_connection=500
still "Too many connection"
仍然“连接太多”
回答by Dulith De Costa
MySQL: ERROR 1040: Too many connections
MySQL：错误 1040：连接过多
This basically tells that MySQL handles the maximum number of connections simultaneously and by default it handles 100connections simultaneously.
这基本上告诉 MySQL 同时处理最大连接数，默认情况下它同时处理100 个连接。
These following reasons cause MySQL to run out connections.
以下这些原因会导致 MySQL 耗尽连接。
Slow Queries
Data Storage Techniques
Bad MySQL configuration
慢查询
数据存储技术
错误的 MySQL 配置
I was able to overcome this issues by doing the followings.
我能够通过执行以下操作来克服这个问题。
Open MySQL command line tooland type,
打开MySQL command line tool并输入，
show variables like "max_connections";
This will return you something like this.
这将返回给你这样的东西。
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| max_connections | 100 |
+-----------------+-------+
You can change the setting to e.g. 200 by issuing the following command without having to restart the MySQL server.
您可以通过发出以下命令将设置更改为例如 200，而无需重新启动 MySQL 服务器。
set global max_connections = 200;
Now when you restart MySQL the next time it will use this setting instead of the default.
现在，当您下次重新启动 MySQL 时，它将使用此设置而不是默认设置。
Keep in mind that increase of the number of connections will increase the amount of RAM required for MySQL to run.
请记住，连接数的增加将增加 MySQL 运行所需的 RAM 量。
回答by Martin
It is worth knowing that if you run out of usable disc space on your server partition or drive, that this will also cause MySQL to return this error. If you're sure it's notthe actual number of users connectedthen the next step is to check that you have free space on your MySQL server drive/partition.
值得知道的是，如果服务器分区或驱动器上的可用磁盘空间不足，这也会导致 MySQL 返回此错误。如果您确定这不是实际连接的用户数，那么下一步是检查您的 MySQL 服务器驱动器/分区上是否有可用空间。
Edit January 2019:
This is true of MySQL 5.7 and 5.8 . I do not know for versions above this.
2019 年 1 月编辑：
MySQL 5.7 和 5.8 也是如此。我不知道高于此的版本。
回答by Eric J.
If you are running out of connections like this, chances are excellent that you are not closing the connections that you have open.
如果您用完这样的连接，那么您很可能没有关闭已打开的连接。
Review code that opens connections and ensure the connections are closed as soon as practical. Typically you want to make use of the usingkeyword around anything that implements IDisposable(including database connections) to ensure that such objects are disposed as soon as they leave the scope where they are needed.
查看打开连接的代码并确保在可行时尽快关闭连接。通常，您希望在实现IDisposable 的任何内容（包括数据库连接）周围使用 using关键字，以确保这些对象在离开需要它们的范围时立即被释放。
You can check the current number of active connections with this query:
您可以使用此查询检查当前活动连接数：
show processlist
回答by Sir Rufo
You have to change max_connectionsto increase total permitted connections.
您必须更改max_connections以增加允许的连接总数。
And set max_user_connectionsback to default 0 => no limitunless you need to limitthis per user connections.
并设置max_user_connections回默认值 0 =>无限制，除非您需要限制每个用户的连接。
回答by Rathish
This error occurs, due to connection limit reaches the maximum limit, defined in the configuration file my.cnf.
出现此错误，是由于连接限制达到了配置文件中定义的最大限制my.cnf。
In order to fix this error, login to MySQL as rootuser (Note: you can login as root, since, mysql will pre-allocate additional one connection for root user) and increase the max_connectionsvariable by using the following command:
为了修复此错误，请以root用户身份登录 MySQL （注意：您可以以 root 身份登录，因为 mysql 将为 预先分配一个额外的连接root user）并使用以下命令增加max_connections变量：
SET GLOBAL max_connections = 500;
This change will be there, until next server restart. In order to make this change permanent, you have to modify in your configuration file. You can do this by,
此更改将存在，直到下一次服务器重新启动。为了使此更改永久化，您必须在配置文件中进行修改。你可以这样做，
vi /etc/my.cnf
[mysqld]
max_connections = 500
This articlehas detailed step by step workaround to fix this error. Have a look at it, I hope it may help you. Thanks.
回答by Mohamed Noordeen
In your program you would have opened the connections and left open without closing it.
在您的程序中，您会打开连接并保持打开状态而不关闭它。
I faced a same problem and finally identified.
我遇到了同样的问题，终于确定了。
I added this code, after try catch.
在尝试捕获之后，我添加了此代码。
finally{
try {
rs.close();
p.close();
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
回答by Praveen Kumar K R
Try this :
尝试这个 ：
open the terminal and type this command : sudo gedit /etc/mysql/my.cnf
打开终端并输入以下命令： sudo gedit /etc/mysql/my.cnf
Paste the line in my.cnf file: set-variable=max_connections=500
在 my.cnf 文件中粘贴以下行： set-variable=max_connections=500
回答by Ryan Shillington
To see how many connections are configured for your DB to use:
要查看为您的数据库配置了多少连接以使用：
select @@max_connections;
To change it:
要改变它：
set global max_connections = 200;
To see how many are connected at the current time:
要查看当前有多少连接：
show processlist;
I also wrote this piece of softwareto help me create a nice spreadsheet of transactions over time so I can track down which queries are the problem:
我还编写了这个软件来帮助我创建一个很好的交易电子表格，以便我可以追踪哪些查询是问题：
回答by X-Coder
The issue noted clearly in https://dev.mysql.com/doc/refman/8.0/en/too-many-connections.html:
该问题在https://dev.mysql.com/doc/refman/8.0/en/too-many-connections.html 中明确指出：
If clients encounter Too many connections errors when attempting to connect to the MySQL server, all available connections are in use by other clients.
如果客户端在尝试连接 MySQL 服务器时遇到太多连接错误，则所有可用连接都被其他客户端使用。
I got resolved the issue after a few minutes, it seems that connection was been released by other clients, also I tried with restarting vs and workbench at same time.
几分钟后我解决了这个问题，似乎其他客户端已经释放了连接，我也尝试同时重新启动 vs 和工作台。
回答by user841657
I was getting this error even though I had all my Connections wrapped in using statements. The thing I was overlooking is how long those connections were staying open.
即使我的所有连接都包含在 using 语句中，我还是收到了这个错误。我忽略的是这些连接保持打开状态的时间。
I was doing something like this:
我正在做这样的事情：
using (MySqlConnection conn = new MySqlConnection(ConnectionString))
{
conn.Open();
foreach(var m in conn.Query<model>(sql))
{
// do something that takes a while, accesses disk or even open up other connections
}
}
Remember that connection will not close until everything in the loop is done and if the loop creates more connections, they can really start adding up.
请记住，在循环中的所有内容都完成之前，连接不会关闭，如果循环创建了更多连接，它们就可以真正开始累加了。
This is better:
这个更好：
List<model> models = null;
using (MySqlConnection conn = new MySqlConnection(ConnectionString))
{
conn.Open();
models = conn.Query<model>(sql).ToList(); // to list is needed here since once the connection is closed you can't step through an IEnumerable
}
foreach(var m in models)
{
// do something that takes a while, accesses disk or even open up other connections
}
That way your connection is allowed to close and is released back to the thread pool before you go to do other things
这样你的连接就可以关闭并在你做其他事情之前释放回线程池
