[DBCP-442] All connections to MS SQL Server fail when SharedPoolDataSource has testOnBorrow set

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 2.0.1, 2.1
• Fix Version/s: 2.1.1

Environment

Windows 7, fully updated as of 2015-07-24, Cygwin, Oracle Java 1.8.0_40, DBCP 2.1, MS SQL Server driver sqljdbc42.jar

• Assignee: Unassigned
• Reporter: Jeff Greif (work)
• Created: 25/Jul/15 01:13
• Updated: 07/Aug/15 03:42
• Resolved: 26/Jul/15 01:24

Description

When testOnBorrow is set on a SharedPoolDataSource with connections on MS SQL Server, the first (and all subsequent) connections retrieved from the pool get the exception in this stack trace as a result of executing Connection.isValid() in the validation on borrow.

java -cp ".;$CLASSPATH" Dbcp2TestOnBorrowFailure 'jdbc:sqlserver://myserver.example.com;databasename=mydb' myuser mypassword
Iteration: 0
PooledConnection was reused, withoutits previous Connection being closed.
java.sql.SQLException: PooledConnection was reused, withoutits previous Connection being closed.
at org.apache.commons.dbcp2.cpdsadapter.PooledConnectionImpl.getConnection(PooledConnectionImpl.java:183)
at org.apache.commons.dbcp2.datasources.InstanceKeyDataSource.getConnection(InstanceKeyDataSource.java:951)
at Dbcp2TestOnBorrowFailure.getConnection(Dbcp2TestOnBorrowFailure.java:30)
at Dbcp2TestOnBorrowFailure.main(Dbcp2TestOnBorrowFailure.java:42)

The code for this example is pasted in here (the preview shows that the code indentation and formatting is lost – sorry):
----------------------------------------------------------------------
import java.sql.Connection;
import java.sql.Driver;
import java.sql.SQLException;
import java.util.Properties;

import org.apache.commons.dbcp2.cpdsadapter.DriverAdapterCPDS;
import org.apache.commons.dbcp2.datasources.SharedPoolDataSource;

public final class Dbcp2TestOnBorrowFailure {

private static final int MAX_CONNECTIONS = 3;

private final SharedPoolDataSource poolDataSource
= new SharedPoolDataSource();

public Dbcp2TestOnBorrowFailure(String jdbcUrl)

{ DriverAdapterCPDS cpds = new DriverAdapterCPDS(); cpds.setUrl(jdbcUrl); poolDataSource.setConnectionPoolDataSource(cpds); poolDataSource.setMaxTotal(MAX_CONNECTIONS); poolDataSource.setMaxConnLifetimeMillis(300000); poolDataSource.setDefaultMaxWaitMillis(1000); poolDataSource.setDefaultAutoCommit(Boolean.TRUE); poolDataSource.setDefaultTestOnBorrow(true); poolDataSource.setValidationQueryTimeout(3000); }

public Connection getConnection(String user, String pwd) throws Exception

{ return poolDataSource.getConnection(user, pwd); }

public static void main(String[] args) {
if (args.length != 3)

{ usage(); }

Dbcp2TestOnBorrowFailure poolFailure = new Dbcp2TestOnBorrowFailure(args[0]);
for (int i = 0; i < 10; ++i) {
System.err.println("Iteration: " + i);
Connection conn = null;
try

{ conn = poolFailure.getConnection(args[1], args[2]); conn.close(); conn = null; }

catch (Exception e)

{ System.err.println(e.getMessage()); e.printStackTrace(System.err); System.exit(88); }

finally {
try {
if (conn != null)

{ conn.close(); }

} catch (SQLException e) {

}
}
}
}

private static void usage()

{ System.err.println("Usage: java -cp ... Dbcp2TestOnBorrowFailure jdbcUrl user password"); System.exit(77); }

}