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

Question

I am trying to make connections to my database using connection pooling library: DBPool. Here's my source code.

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

/**
 * @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 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)

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

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

The show processlist command on the MySQL terminal shows 101 processes in sleep.

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.

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 my complete source code see my answer below

Answer 1

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.

Answer 2

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 ; 

    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;
    }

Hope this helps.