Connection not release when using stmt.getConnection()

[bswingle]

All,
So I discovered an issue when attempting to implement BoneCP in our database class. The way we have our class architected we have a method called getPreparedStatement that gets a conenction from the pool and returns the preparedstatment, we then call another method executePreparedStatement which returns a resultset. After the resultset is used we call a method called releaseResources which accepts a results set and gets the connection from the resultset using statment and then attempts to close it (see Below). The issue is that the connection thats returned from the resultset isn't the actual connection but a shadow object of the original connection. So therefore the connections are never released from the pool. DBCP had this same issue and I believe proxool still has it. I REALLY REALLY REALLY do not want to use DBCP!!!! Has anyone encountered this before and can anyone provide anyway to resolve this short of rearchitecting this class or using a result caching method as at least for this release this will not be an option. Don't hate on the architecture its not mine i'm just charged with fixing it!

------------------------ Example ------------------------

public void releaseResources(ResultSet rs)
{
Statement stmt = null;
Connection conn = null;
try
{
stmt = rs.getStatement();
conn = stmt.getConnection();
}
catch(SQLException e)
{
RALogger.logMessage("Error releasing resources.","ERROR");
e.printStackTrace();
}
finally
{
if(rs != null) rs.close();
if(stmt != null) stmt.close();
if(conn != null && !conn.isClosed()) conn.close();
}


}
--------------------------- End ---------------------------

DBCP bug report and fix.
https://issues.apache.org/jira/browse/DBCP-11

Thanks
Brian Swingle

[wwadge]

Not sure you want the original connection rather than the wrapped connection, but if you really need it, the internal connection can be obtained via connection.getInternalConnection().

[bswingle]

Wadge,
So if you refer to my example bellow they would be basically the same? As the issue is that the first example without the thrid line you suggested doesn't release the connection back to the pool where the second example obviously does. Please let me know your thoughts and I'll run a quick test.

stmt = rs.getStatement();
conn = stmt.getConnection();
conn = conn.getInternalConnection();
conn.close();

is the same as

conn = connectionPool.getConnection();
conn.close();

[wwadge]

you should never be closing off your connection manually, your pool is likely to break in the way you mentioned. Just say connection close (on the wrapped connection) and let the connection return to the pool, it will be closed off for you eventually.

[bswingle]

OK so thats were the confusion is if your architecture required you to get the connection from the statement object like mine does and you use typical conn.close() the conneciton is never returned to the pool. I will create a quick batch example and post it in about an hour, that you can run some tests on to see what I'm seeing. I really appreaciate the quick responses on this as we are really really hopeful to get this resolved so we can use BoneCP in our product.

stmt = rs.getStatement();
conn = stmt.getConnection();
conn.close();


Thanks
Brian Swingle

[wwadge]

"conn.close() the conneciton is never returned to the pool"

this is the bit that I'm lost.

Incidentally there's a connection tracking feature that will still close off a connection if you forget to do so yourself (but this is bad practice)

Wallace

[bswingle]

Code: Select all

import java.io.Console;
import java.io.PrintWriter;
import java.util.Scanner;
import java.sql.*;
import java.sql.DriverManager;
import java.util.*;
import java.io.*;
import javax.naming.*;
import com.jolbox.bonecp.*;

public class PoolTest
{

   static BoneCP connectionPool;
   static BoneCPConfig config;

   public static void main(String[] args)
   {
      
      try
      {

            System.out.println("Creating Connection Pool");
            Class.forName("com.mysql.jdbc.Driver");    
            config = new BoneCPConfig();   
            config.setJdbcUrl("jdbc:mysql://localhost:3306/raukp");   
            config.setUsername("root");         
            config.setPassword("Password123");         
            config.setMinConnectionsPerPartition(100); 
            config.setMaxConnectionsPerPartition(100);
            config.setPartitionCount(2);
            config.setIdleMaxAgeInSeconds(20);
            //config.setServiceOrder("LIFO");
            //config.setReleaseHelperThreads(1000);
            //config.setDisableConnectionTracking(false);
            connectionPool = new BoneCP(config);    

         int i = 0;
         while(i < 350)
         {
            Statistics stats = new Statistics(connectionPool);
            System.out.println("--------  Connection:"+i+" --------");
            System.out.println("Tot Conn Created:   " + stats.getTotalCreatedConnections());
            System.out.println("Tot Free Conn:      " + stats.getTotalFree());
            System.out.println("Tot Leased Conn:    " + stats.getTotalLeased());

            Connection conn = connectionPool.getConnection();
            PreparedStatement pstmt = conn.prepareStatement("select * from groups");
            ResultSet rs = pstmt.executeQuery();
            
            while (rs.next())
            {
               int id = rs.getInt("groupid");
               System.out.println(id);
            }
                                                               
                                                                /*Imagine this bellow was in another method that had no access to the original connection object and recreated that connection from the statement object like so which follows JDBC spec. When we call close on the connection object it doesn't release to the pool like it should.*/

            Statement stmt = rs.getStatement();
            Connection conn2 = stmt.getConnection();

            if(rs != null) rs.close();
            if(pstmt != null) pstmt.close();
            if(conn2 != null && !conn2.isClosed())
               conn2.close();
            rs = null;
            pstmt = null;
            conn2 = null;            

            i++;
         }

      }
      catch(Exception ex)
      {
         System.out.println("MySQL Conn:"+ex);
         ex.printStackTrace();
      }
   }
}

[bswingle]

Wallace,
Please see my code example and a small snippet of what it prints the console is bellow. So to release a connection back to the pool you have to call conn.close() when you are done with the conneciton correct? If so then getting the connection from the statment object using stmt.getConnection() which according to JDBC spec should return the conneciton associated with that statment and then calling conn.close() on that gotten connection doesn't release it to the pool like it should. Other pooling packages have this same issue and DBCP is the only one thats patched it up to this point. Unfortunitely the architecture i'm stuck with for the time being forces me to get the connection from the statement object so either we find a way to patch this or I'm not going to be able to implement BoneCP in our product. Thanks agian for all your help on this I really appreacite it.

Sample output from console app.
-------- Connection:189 --------
Tot Conn Created: 200
Tot Free Conn: 11
Tot Leased Conn: 189
1
2
3
-------- Connection:190 --------
Tot Conn Created: 200
Tot Free Conn: 10
Tot Leased Conn: 190
1
2
3
-------- Connection:191 --------
Tot Conn Created: 200
Tot Free Conn: 9
Tot Leased Conn: 191
1
2
3
-------- Connection:192 --------
Tot Conn Created: 200
Tot Free Conn: 8
Tot Leased Conn: 192
1
2
3
-------- Connection:193 --------
Tot Conn Created: 200
Tot Free Conn: 7
Tot Leased Conn: 193
1
2
3
-------- Connection:194 --------
Tot Conn Created: 200
Tot Free Conn: 6
Tot Leased Conn: 194
1
2
3
-------- Connection:195 --------
Tot Conn Created: 200
Tot Free Conn: 5
Tot Leased Conn: 195
1
2
3
-------- Connection:196 --------
Tot Conn Created: 200
Tot Free Conn: 4
Tot Leased Conn: 196
1
2
3
-------- Connection:197 --------
Tot Conn Created: 200
Tot Free Conn: 3
Tot Leased Conn: 197
1
2
3
-------- Connection:198 --------
Tot Conn Created: 200
Tot Free Conn: 2
Tot Leased Conn: 198
1
2
3
-------- Connection:199 --------
Tot Conn Created: 200
Tot Free Conn: 1
Tot Leased Conn: 199
1
2
3
-------- Connection:200 --------
Tot Conn Created: 200
Tot Free Conn: 0
Tot Leased Conn: 200

[bswingle]

Wallace,
Please let me know what our next steps are to resolving this if you are willing to patch the issue? Like I previously mentioned we can certainly donate to your cause if this will help to expedite the process?

Best Regards,
Brian Swingle

[bswingle]

Wallace,
Come back to me here brother! If anything give me an up or down on whether you plan on looking into this?

Thanks
Brian Swingle