ansaurus

Question

Data source rejected establishment of connection, message from server: "Too many connections"

Answer 1

+1 A:

org.apache.commons.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory (Data source rejected establishment of connection, message from server: "Too many connections")

This indicates that something is leaking connections. I.e. something keeps 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 something so 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.

BalusC 2010-07-08 13:24:55

BalusC, I do close the connections in the getTweets() function in the finally block. It's the only place where I am "making" a connection. What else could be leaking ?

Denzil 2010-07-08 13:31:47

Restart DB and reobserve if the problem would occur again. I brought the last paragraph of my answer in during an edit which you might have missed.

BalusC 2010-07-08 13:32:35

BalusC, Yes I did miss it. 1. I have restarted my DB multiple times and it doesn't have any effect2. The code isn't optimal agreed but I'm more curious on the problem3. As told earlier, I have checked the connections via MySQL but none of them seem to be an issue.

Denzil 2010-07-08 13:44:47

Well, it much look like that *something else* is using the DB. Try changing the DB port number (so that something else cannot connect it without being reconfigured) and retry in Java.

BalusC 2010-07-08 13:52:08

BalusC, I tried changing the port number and tried using Java to no success. :-(

Denzil 2010-07-09 06:34:04

BalusC, I figured out the solution. Updated in the question itself. Thanks for the help. Just curious to know, if this is you : http://balusc.blogspot.com/ ?

Denzil 2010-07-09 13:03:37

Cheers. Yes, it's me. Also see my profile's homepage :)

BalusC 2010-07-09 13:24:24

Answer 2

+1 A:

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.

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 ; 

    /**
     * @return the server_name
     */
    public String getServer_name() {
        return server_name;
    }

    /**
     * @param serverName the server_name to set
     */
    public void setServer_name(String serverName) {
        server_name = serverName;
    }

    /**
     * @return the database
     */
    public String getDatabase() {
        return database;
    }

    /**
     * @param database the database to set
     */
    public void setDatabase(String database) {
        this.database = database;
    }

    /**
     * @return the username
     */
    public String getUsername() {
        return username;
    }

    /**
     * @param username the username to set
     */
    public void setUsername(String username) {
        this.username = username;
    }

    /**
     * @return the password
     */
    public String getPassword() {
        return password;
    }

    /**
     * @param password the password to set
     */
    public void setPassword(String password) {
        this.password = password;
    }




    /*
     * Get Data Source
     */
    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);

    /**
     * @return
     */
    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) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return connection;
    }

    /*
     * Set DB parameters
     */
    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) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        finally{
            try {
                connection.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }

        return tweet;
    }

Hope this helps.

Denzil 2010-07-09 13:17:43

Interesting, I didn't expect that omitting the port number would lead to this behaviour... Does it also happen when you don't use a connection pool?

BalusC 2010-07-09 21:58:11

BalusC, With port number + connection pool : no issues. Without port number + connection pool : can't connect. Without port number + Without connection pool : Can run for a limited time but soon exceeds max_number_of_connections. With port number + without connection pool: haven't tried as yet.

Denzil 2010-07-10 09:53:37

ansaurus

tags:

views:

answers:

Data source rejected establishment of connection, message from server: "Too many connections"

DBUtils.java

TwitterAPI.java

related questions