Java 数据源拒绝建立连接，来自服务器的消息：“连接太多”
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3203673/
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
Data source rejected establishment of connection, message from server: "Too many connections"
提问by Dexter
I am trying to make connections to my database using connection pooling library: DBPool. Here's my source code.
我正在尝试使用连接池库DBPool连接到我的数据库。这是我的源代码。
DBUtils.java
数据库实用程序
package DB;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import javax.sql.ConnectionPoolDataSource;
import snaq.db.ConnectionPool;
import com.mysql.jdbc.Driver;
/**
* @author decorrea
*/
public class DBUtils {
public static String jdbc_driver_name = "com.mysql.jdbc.Driver";
private static String server_name ;
private static String database;
private static String username;
private static String password;
public String getServer_name() {
return server_name;
}
public void setServer_name(String serverName) {
server_name = serverName;
}
public String getDatabase() {
return database;
}
public void setDatabase(String database) {
this.database = database;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
/*
* Creates a MySQL DB connection from a pool
*/
public Connection createConnection(ConnectionPool pool){
Connection connection = null;
try {
// Load the JDBC driver
Class driver_class = Class.forName(jdbc_driver_name);
Driver driver = (Driver)driver_class.newInstance();
DriverManager.registerDriver(driver);
connection = pool.getConnection();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
} catch (InstantiationException e) {
e.printStackTrace();
}
return connection;
}
/*
* Creates a MySQL DB connection
*/
public Connection createConnection(){
Connection connection = null;
try {
// Load the JDBC driver
Class driver_class = Class.forName(jdbc_driver_name);
Driver driver = (Driver)driver_class.newInstance();
DriverManager.registerDriver(driver);
String url = "jdbc:mysql://" + server_name + "/" + database;
connection = DriverManager.getConnection(url);
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
} catch (InstantiationException e) {
e.printStackTrace();
}
return connection;
}
}
TwitterAPI.java
推特API.java
/**
* @author decorrea
*/
public class TwitterAPI {
private static String server_name = "127.0.0.1";
private static String twitter_databse = "twitter";
private static String username = "root";
private static String password = "password";
public static Connection startDBConnection(String server_name, String database, String username, String password) {
//Set DB parameters
DBUtils mysql_obj = setDBParams(server_name, database, username, password);
String url = "jdbc:mysql://" + server_name + "/" + database;
ConnectionPool pool = new ConnectionPool("local",1, 1, 1, 180000, url, username, password);
Connection connection = mysql_obj.createConnection(pool);
return connection;
}
public static DBUtils setDBParams(String server_name, String database, String username, String password){
DBUtils mysql_obj = new DBUtils();
mysql_obj.setServer_name(server_name);
mysql_obj.setDatabase(database);
mysql_obj.setUsername(username);
mysql_obj.setPassword(password);
return mysql_obj;
}
public static String getTweets(BigInteger id){
Connection connection = startDBConnection(server_name,twitter_databse,username,password);
ResultSet resultSet = null;
String tweet = new String();
try {
Statement statement = connection.createStatement();
String query = SQL_queries.get_tweets_on_id + id.toString();
//Execute the query
resultSet = statement.executeQuery(query);
while(resultSet.next()){
tweet = resultSet.getString("content");
}
resultSet.close();
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
finally{
try {
connection.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
return tweet;
}
}
I am new to the business of connection pooling and decided to do so only because I was receiving a "Communications Link failure" without it.
我是连接池业务的新手，决定这样做只是因为我在没有它的情况下收到“通信链接失败”。
Update 1:To add I also tried Apache DBCP and tried this examplebut still receive the same error.
更新 1：添加我还尝试了 Apache DBCP并尝试了这个示例，但仍然收到相同的错误。
org.apache.commons.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory (Data source rejected establishment of connection, message from server: "Too many connections")
at org.apache.commons.dbcp.BasicDataSource.createPoolableConnectionFactory(BasicDataSource.java:1549)
at org.apache.commons.dbcp.BasicDataSource.createDataSource(BasicDataSource.java:1388)
at org.apache.commons.dbcp.BasicDataSource.getConnection(BasicDataSource.java:1044)
at Twitter.TwitterAPI.startDBConnection(TwitterAPI.java:55)
at Twitter.TwitterAPI.getTweets(TwitterAPI.java:84)
at Twitter.TwitterAPI.main(TwitterAPI.java:235)
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Data source rejected establishment of connection, message from server: "Too many connections"
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:45)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
at java.lang.reflect.Constructor.newInstance(Constructor.java:528)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:409)
at com.mysql.jdbc.Util.getInstance(Util.java:384)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1015)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:989)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:984)
at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1105)
at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2186)
at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:787)
at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:49)
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:45)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
at java.lang.reflect.Constructor.newInstance(Constructor.java:528)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:409)
at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:357)
at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:285)
at org.apache.commons.dbcp.DriverConnectionFactory.createConnection(DriverConnectionFactory.java:38)
at org.apache.commons.dbcp.PoolableConnectionFactory.makeObject(PoolableConnectionFactory.java:582)
at org.apache.commons.dbcp.BasicDataSource.validateConnectionFactory(BasicDataSource.java:1556)
at org.apache.commons.dbcp.BasicDataSource.createPoolableConnectionFactory(BasicDataSource.java:1545)
... 5 more
Exception in thread "main" java.lang.NullPointerException
at Twitter.TwitterAPI.getTweets(TwitterAPI.java:108)
at Twitter.TwitterAPI.main(TwitterAPI.java:235)
I also checked the max_connections variable in the my.ini file in MySQL. Here's it's value:
我还检查了 MySQL 中 my.ini 文件中的 max_connections 变量。这是它的价值：
The maximum amount of concurrent sessions the MySQL server will allow. One of these connections will be reserved for a user with SUPER privileges to allow the administrator to login even if the connection limit has been reached.
max_connections=100
MySQL 服务器将允许的最大并发会话数。这些连接之一将保留给具有 SUPER 权限的用户，以允许管理员登录，即使已达到连接限制。
max_connections=100
The show processlistcommand on the MySQL terminal shows 101 processes in sleep.
在SHOW PROCESSLIST上的MySQL终端显示101个处理在睡眠命令。
Any kind of help/comments will be appreciated
任何形式的帮助/评论将不胜感激
Update 2 -- Solution::So, I figured out the solution. I hadn't mentioned the port name in the url connection to the database.
更新 2 -- 解决方案::所以，我想出了解决方案。我没有在与数据库的 url 连接中提到端口名称。
String url = "jdbc:mysql://" + server_name + "/" + database;
Probably, hence it led to many leakingconnections. Once done, I tried with the example given here. It now doesn't throw any error. Thanks to BalusC, as I figured this out only due to his comment on changing the port number on MySQL. To add, the way to change the MySQL port number is NOTby changing the my.inifile but by running the MySQL instance config wizardunder Start -> Programs -> MySQL Server 5.1 -> MySQL Server Instance Config Wizard. It was also interesting to note the code didn't throw any error when the port number wasn't specified and the program ran smoothly. Probably, JDBC connects to 3306 by default. If anyone has any particular idea about the same, please share.
可能，因此它导致了许多泄漏的连接。完成后，我尝试使用此处给出的示例。它现在不会抛出任何错误。感谢 BalusC，因为我只是因为他对更改 MySQL 端口号的评论才知道这一点。要添加的方式来改变MySQL的端口号是不是通过改变my.ini的文件，但通过运行MySQL实例配置向导开始下- >程序- > MySQL服务器5.1 - > MySQL服务器实例配置向导。有趣的是，当未指定端口号并且程序运行顺利时，代码没有抛出任何错误。可能，JDBC 默认连接到 3306。如果有人对此有任何特别的想法，请分享。
For my complete source code see my answer below
对于我的完整源代码，请参阅下面的答案
采纳答案by Dexter
So, I figured out the solution. I hadn't mentioned the port name in the url connection to the database.
所以，我想出了解决办法。我没有在与数据库的 url 连接中提到端口名称。
String url = "jdbc:mysql://" + server_name + "/" + database;
Probably, hence it led to many leakingconnections. Once done, I tried with the example given here. It now doesn't throw any error. Thanks to BalusC, as I figured this out only due to his comment on changing the port number on MySQL. To add, the way to change the MySQL port number is NOTby changing the my.ini file but by running the MySQL instance config wizard under Start -> Programs -> MySQL Server 5.1 -> MySQL Server Instance Config Wizard. It was also interesting to note the code didn't throw any error when the port number wasn't specified and the program ran smoothly. Probably, JDBC connects to 3306 by default. If anyone has any particular idea about the same, please share.
可能，因此它导致了许多泄漏的连接。完成后，我尝试使用此处给出的示例。它现在不会抛出任何错误。感谢 BalusC，因为我只是因为他对更改 MySQL 端口号的评论才知道这一点。另外，更改 MySQL 端口号的方法不是通过更改 my.ini 文件，而是通过在开始 -> 程序 -> MySQL Server 5.1 -> MySQL 服务器实例配置向导下运行 MySQL 实例配置向导。有趣的是，当未指定端口号并且程序运行顺利时，代码没有抛出任何错误。可能，JDBC 默认连接到 3306。如果有人对此有任何特别的想法，请分享。
For benefit of folks, here's the source code:
为了大家的利益，这里是源代码：
DBUtils.java
数据库实用程序
package DB;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import javax.sql.DataSource;
import org.apache.commons.dbcp.BasicDataSource;
import com.mysql.jdbc.Driver;
/**
* @author decorrea
*
*/
public class DBUtils {
public static String jdbc_driver_name = "com.mysql.jdbc.Driver";
private static String server_name ;
private static String database;
private static String username;
private static String password;
private static int maxActive = 20;
private static int maxIdle = 2 ;
public String getServer_name() {
return server_name;
}
public void setServer_name(String serverName) {
server_name = serverName;
}
public String getDatabase() {
return database;
}
public void setDatabase(String database) {
this.database = database;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public static DataSource getDataSource(String server_name, String database, String username, String password){
BasicDataSource datasource = new BasicDataSource();
datasource.setDriverClassName(jdbc_driver_name);
String url = "jdbc:mysql://" + server_name + "/" + database;
System.out.println(url);
datasource.setUsername(username);
datasource.setPassword(password);
datasource.setUrl(url);
datasource.setMaxActive(maxActive);
datasource.setMaxIdle(maxIdle);
return datasource;
}
}
TwitterAPI.java
推特API.java
public class TwitterAPI {
private static String server_name = "localhost:7777";
private static String twitter_databse = "twitter";
private static String username = "root";
private static String password = "password";
public static String twitter_unique_usernames_file = "twitter_unique_usernames_file.txt";
public static String language_model_file = "C:\de\JARS\lingpipe-4.0.0\demos\models\langid-leipzig.classifier";
public static DataSource dataSource = DBUtils.getDataSource(server_name, twitter_databse, username, password);
public static Connection startDBConnection(String server_name, String database, String username, String password) {
//Set DB parameters
//DBUtils mysql_obj = setDBParams(server_name, database, username, password);
Connection connection = null;
//connection = mysql_obj.createConnection();
try {
connection = dataSource.getConnection();
} catch (SQLException e) {
e.printStackTrace();
}
return connection;
}
public static DBUtils setDBParams(String server_name, String database, String username, String password){
DBUtils mysql_obj = new DBUtils();
mysql_obj.setServer_name(server_name);
mysql_obj.setDatabase(database);
mysql_obj.setUsername(username);
mysql_obj.setPassword(password);
return mysql_obj;
}
public static String getTweets(BigInteger id){
Connection connection = startDBConnection(server_name,twitter_databse,username,password);
ResultSet resultSet = null;
String tweet = new String();
try {
Statement statement = connection.createStatement();
String query = SQL_queries.get_tweets_on_id + id.toString();
//Execute the query
resultSet = statement.executeQuery(query);
while(resultSet.next()){
tweet = resultSet.getString("content");
}
resultSet.close();
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
finally{
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return tweet;
}
Hope this helps.
希望这可以帮助。
回答by BalusC
org.apache.commons.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory (Data source rejected establishment of connection, message from server: "Too many connections")
org.apache.commons.dbcp.SQLNestedException：无法创建 PoolableConnectionFactory（数据源拒绝建立连接，来自服务器的消息：“连接太多”）
This indicates that somethingis leaking connections. I.e. somethingkeeps acquiring (opening) connections without ever closing them and/or returning to connection pool. Restarting the DB so that it can hard-close all opened connections should temporarily fix the issue. Fixing somethingso that it is properly closing the connection after use should permanently fix the issue.
这表明某些东西正在泄漏连接。即某些东西不断获取（打开）连接，而不会关闭它们和/或返回连接池。重新启动数据库以便它可以硬关闭所有打开的连接应该可以暂时解决这个问题。修复某些东西以便在使用后正确关闭连接应该可以永久解决问题。
Although the posted JDBC code is not following the optimal idiom, it doesn't look like to be the cause of leaking connections. Probably the DB is already running for hours/days and you've during earlier tests acquired too many connections without closing them so that the DB is running out of them.
尽管发布的 JDBC 代码没有遵循最佳习惯用法，但它看起来并不是导致连接泄漏的原因。可能数据库已经运行了几个小时/几天，并且您在早期的测试中获得了太多连接而没有关闭它们，以至于数据库用完了它们。
