数据源拒绝建立连接，来自服务器的消息：“连接太多” [英] Data source rejected establishment of connection, message from server: "Too many connections"
问题描述
我正在尝试使用连接池库建立与数据库的连接： DBPool 。这是我的源代码。
I am trying to make connections to my database using connection pooling library: DBPool. Here's my source code.
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
TwitterAPI.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.
更新1：要添加我还试过Apache DBCP 并尝试了这个示例但仍然收到相同的错误。
Update 1: To add I also tried Apache DBCP and tried this example but still receive the same error.
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)
我还检查了max_connections变量MySQL中的my.ini文件。这是它的价值：
I also checked the max_connections variable in the my.ini file in MySQL. Here's it's value:
MySQL服务器将允许的最大并发会话数.b $ b允许。其中一个连接将保留给具有
SUPER权限的用户，即使已达到
连接限制，也允许管理员登录。
max_connections = 100
show processlist < MySQL终端上的/ em>命令显示处于睡眠状态的101个进程。
The show processlist command on the MySQL terminal shows 101 processes in sleep.
任何形式的帮助/评论都将受到赞赏
Any kind of help/comments will be appreciated
更新2 - 解决方案::
所以，我找到了解决方案。我没有在与数据库的url连接中提到端口名称。
Update 2 -- Solution:: So, I figured out the solution. I hadn't mentioned the port name in the url connection to the database.
String url = "jdbc:mysql://" + server_name + "/" + database;
可能因此导致许多泄漏的连接。完成后，我尝试了给出的示例这里。它现在不会抛出任何错误。感谢BalusC，我认为这只是因为他对更改MySQL端口号的评论。要添加，更改MySQL端口号的方法是 NOT ，方法是更改 my.ini 文件，但运行 MySQL实例配置向导开始 - >程序 - > MySQL服务器5.1 - > MySQL服务器实例配置向导。有趣的是，当没有指定端口号并且程序运行顺利时，代码没有抛出任何错误。也许，JDBC默认连接到3306。如果有人对此有任何特别的想法，请分享。
Probably, hence it led to many leaking connections. 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 NOT by 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.
我的完整源代码请参阅下面的答案
推荐答案
所以，我找到了解决方案。我没有在与数据库的url连接中提到端口名称。
So, I figured out the solution. I hadn't mentioned the port name in the url connection to the database.
String url = "jdbc:mysql://" + server_name + "/" + database;
可能因此导致许多泄漏的连接。完成后，我尝试了这里给出的例子。它现在不会抛出任何错误。感谢BalusC，我认为这只是因为他对更改MySQL端口号的评论。要添加，更改MySQL端口号的方法是 NOT ，方法是更改my.ini文件，但运行启动 - >程序 - > MySQL服务器5.1 - > MySQL服务器实例下的MySQL实例配置向导配置向导。有趣的是，当没有指定端口号并且程序运行顺利时，代码没有抛出任何错误。也许，JDBC默认连接到3306。如果有人对此有任何特别的想法，请分享。
Probably, hence it led to many leaking connections. 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 NOT by 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.
为了人们的利益，这里是源代码：
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
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;
}
希望这有帮助。
这篇关于数据源拒绝建立连接，来自服务器的消息：“连接太多”的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
