数据源拒绝建立连接，来自服务器的消息:“连接过多". [英] Data source rejected establishment of connection, message from server: &quot;Too many connections&quot;

查看：218 发布时间：2020/8/11 4:28:38 java mysql jdbc

本文介绍了数据源拒绝建立连接，来自服务器的消息:“连接过多".的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我正在尝试使用连接池库建立与数据库的连接: 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

/**
 * @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;
    }
}

我对连接池业务不熟悉，因此决定这样做是因为我收到了没有连接池的"Communications Link failure"消息.

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)

我还检查了MySQL中my.ini文件中的max_connections变量.这就是它的价值:

I also checked the max_connections variable in the my.ini file in MySQL. Here's it's value:

MySQL服务器将执行的最大并发会话数允许.这些连接之一将保留给具有以下功能的用户 SUPER权限允许管理员登录，即使已达到连接限制. max_connections=100

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终端上的 show processlist 命令显示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;

可能，因此导致了许多泄漏连接.完成后，我尝试使用给出的示例

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端口号的方法是否，方法是更改my.ini文件，但是通过在开始->程序-> MySQL Server 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屋！

查看全文

数据源拒绝建立连接，来自服务器的消息:“连接过多". [英] Data source rejected establishment of connection, message from server: &quot;Too many connections&quot;

问题描述

TwitterAPI.java

推荐答案

相关文章

Java开发最新文章

热门教程

热门工具

登录关闭

数据源拒绝建立连接，来自服务器的消息:“连接过多". [英] Data source rejected establishment of connection, message from server: &amp;quot;Too many connections&amp;quot;

问题描述

TwitterAPI.java

推荐答案

相关文章

Java开发最新文章

热门教程

热门工具

登录 关闭

数据源拒绝建立连接，来自服务器的消息:“连接过多". [英] Data source rejected establishment of connection, message from server: "Too many connections"

登录关闭