title: Data source rejected establishment of connection, message from server: "Too many connections"
body: <p>I am trying to make connections to my database using connection pooling library: <a href="http://www.snaq.net/java/DBPool/" rel="noreferrer">DBPool</a>. Here's my source code. <br></p>
  
  <h3>DBUtils.java</h3>
  
  <pre><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;
      }
  }
  </code></pre>
  
  <h3>TwitterAPI.java</h3>
  
  <pre><code>/**
   * @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;
      }
  }
  </code></pre>
  
  <p>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. </p>
  
  <p><strong>Update 1:</strong> To add I also tried Apache <a href="http://commons.apache.org/dbcp/" rel="noreferrer"> DBCP </a> and tried this <a href="http://ezinearticles.com/?How-To-Create-A-Standalone-Connection-Pool-In-Java?&amp;id=924710" rel="noreferrer">example</a> but still receive the same error.</p>
  
  <pre><code>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.&lt;init&gt;(ConnectionImpl.java:787)
      at com.mysql.jdbc.JDBC4Connection.&lt;init&gt;(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)
  </code></pre>
  
  <p>I also checked the max_connections variable in the my.ini file in MySQL. Here's it's value:</p>
  
  <blockquote>
    <p>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.
    <code>max_connections=100</code></p>
  </blockquote>
  
  <p>The <em>show processlist</em> command on the MySQL terminal shows 101 processes in sleep. </p>
  
  <p>Any kind of help/comments will be appreciated</p>
  
  <p><strong>Update 2 -- Solution::</strong>
  So, I figured out the solution. I hadn't mentioned the port name in the url connection to the database.</p>
  
  <pre><code>String url = "jdbc:mysql://" + server_name +  "/" + database;
  </code></pre>
  
  <p>Probably, hence it led to many <em>leaking</em> connections. Once done, I tried with the example given <a href="http://ezinearticles.com/?How-To-Create-A-Standalone-Connection-Pool-In-Java?&amp;id=924710" rel="noreferrer">here</a>. 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 <strong>NOT</strong> by changing the <em>my.ini</em> file but by running the <em>MySQL instance config wizard</em> 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.</p>
  
  <p>For my complete source code <a href="https://stackoverflow.com/a/3212907/281545">see my answer below</a></p>