Uderc programming article aggregator sites

Too many connections - even after closing ResultSets and PreparedStatements

Tags:
Answers: Have 4 answers
Advertisement
I'm getting a "Too many connections" error with MySQL when I run my Java program.
2007-08-06 15:07:26,650 main/CLIRuntime [FATAL]: Too many connections
com.mysql.jdbc.exceptions.MySQLNonTransientConnectionException: Too many connections
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:921)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2870)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:812)
        at com.mysql.jdbc.MysqlIO.secureAuth411(MysqlIO.java:3269)
        at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1182)
        at com.mysql.jdbc.Connection.createNewIO(Connection.java:2670)I researched on this and found out that I wasn't closing the ResultSet and the PreparedStatement.
The JDBC connection is closed by a central program that handles connections (custom connection pooling).
I added the code to close all ResultSets and PreparedStatements, and re-started MySQL as per the instructions here
but still get "Too many connections" error.
A few other things come to mind, as to what I may be doing wrong, so I have a few questions:
1) A few PreparedStatements are created in one method, and they are used in a 2nd method and closed in the 2nd method
does this cause "Too many connections" error?
2) I have 2 different ResultSets, in nested while loops where the outer loop iterates over the first ResultSet and
the inner loop iterates over the second ResultSet.
I have a try-finally block that wraps the inner while loop, and I'm closing the second ResultSet and PreparedStement
in the inner while loop.
I also have a try-finally block that wraps the outer while loop, and I'm closing the first ResulSet and PreparedStatement
in the outer while loop as soon as the inner while loop completes.
So, in the above case the outer while loop's ResultSet and PreparedStatements remain open until the inner while loop completes.
Does the above cause "Too many connections" error?
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
The following is relevant sections of my code ( it is partially pseudo-code ) that shows the above 2 cases:
init( Connection jdbcConnection ){
   String firstSQLStatement = "....";
   PreparedStatement ps1 = jdbcConnection.prepareStatement( firstSQLStatement );
   String secondSQLStatement = "....";
   PreparedStatement ps2 = jdbcConnection.prepareStatement( secondSQLStatement );
   String thirdSQLStatement = "....";
   PreparedStatement ps3 = null;
   ResultSet rsA = null;
   try{
        ps3 = jdbcConnection.prepareStatement( thirdSQLStatement );
        rsA = ps3.executeQuery();
        if( rsA.next() ){
               rsA.getString( 1 );
   }finally{
        if( rsA != null )
               rsA.close();
        if( ps3 != null )
          ps3.close();
   //Notice, how ps1 and ps2 are created here but not used immediately, but only ps3 is
   //used immediately.
   //ps1 and ps2 are used in another method.
run( Connection jdbcConnection ){
     ResultSet rs1 = ps1.executeQuery();
        try{
           while(rs1.next()){
                String s = rs1.getString();
                ps2.setString(1, s);
          ResultSet rs2 = ps2.executeQuery();
                try{
               while(rs2.next()){
                    String s2 = rs2.getString();
                }finally{
               if( rs2 != null )
                 rs2.close();
               if( ps2 != null )
                 ps2.close();
     }catch( Exception e ){
          e.printStackTrace();
     }finally{
        if( rs1 != null )
              rs1.close();
           if( ps1 != null )
              ps1.close();
//Notice in the above case rs1 and ps1 are closed only after the inner
//while loop completes.
}I appreciate any help.
Advertisement
The best answer: Thanks for your reply.
I will look at the central connection pooling mechanism ( which was written by someone else) , but that is being used by many other Java programs others have written.
They are not getting this error.
An addendum to my previous note, I followed the instructions here.
http://dev.mysql.com/doc/refman/5.0/en/too-many-connections.html
There's probably something else in my code that is not closing the connection.
But I just wanted to rule out the fact that opening a PreparedStatement in one method and closing it in another is not a problem.
Or, if nested ResultSet loops don't cause the problem.
I've read in a few threads taht "Too many connections" can occur for unclosed RS and PS , and not just JDBC connections.