[IBATIS-449] Is there a way to specify a timeout for Pool.PingQuery when using a jdbc SimpleDataSource?

• Type: New Feature
• Status: Closed
• Priority: Critical
• Resolution: Invalid
• Environment: Red Hat Enterprise Linux 4 Update 5 x86-64, JBOSS 4.0.5
• Assignee: Unassigned
• Reporter: Anton Rothenbacher
• Created: 12/Aug/07 16:14
• Updated: 15/Aug/09 13:33
• Resolved: 15/Aug/09 13:18

Description

It seems there is no way to specify a timeout when implementing the Pool.PingQuery to test pooled connections before use. We are in an environment that is closing connections on us and when the app is inactive for a long time, the first connection to be tested out of the pool is bad, and it takes 15 minutes before the connection is deemed bad and the connection recycled.

2007-08-11 06:38:14,032 DEBUG [com.ibatis.common.jdbc.SimpleDataSource] Checked out connection 2865240 from pool.
2007-08-11 06:38:14,032 DEBUG [com.ibatis.common.jdbc.SimpleDataSource] Testing connection 2865240 ...
.....
2007-08-11 06:53:40,695 WARN [com.ibatis.common.jdbc.SimpleDataSource] Execution of ping query 'select 1 from DUAL' failed: Io exception: Connection timed out
2007-08-11 06:53:40,695 DEBUG [com.ibatis.common.jdbc.SimpleDataSource] Connection 2865240 is BAD: Io exception: Connection timed out
2007-08-11 06:53:40,696 DEBUG [com.ibatis.common.jdbc.SimpleDataSource] A bad connection (2865240) was returned from the pool, getting another connection.
2007-08-11 06:53:40,750 DEBUG [com.ibatis.common.jdbc.SimpleDataSource] Created connection 10545003.

This is a problem since the app hangs for the time when it is waiting for the connection. We are looking for a way to speed up the PingQuery test so that if it does not respond in x seconds, (it is a very fast query) then recycle the connection right away, not 15min later when the connection times out.

Here is the relevent section from our SqlMapConfig.xml

<!-- Configure a datasource to use with this SQL Map using SimpleDataSource.
Notice the use of the properties from the above resource -->
<transactionManager type="JDBC" commitRequired="true">
<dataSource type="SIMPLE">
<property name="JDBC.Driver" value="${driver}"/>
<property name="JDBC.ConnectionURL" value="${url}"/>
<property name="JDBC.Username" value="${username}"/>
<property name="JDBC.Password" value="${password}"/>
<property name="JDBC.DefaultAutoCommit" value="${jdbc.default.auto.commit}" />
<property name="Pool.MaximumActiveConnections" value="${pool.maximum.active.connections}"/>
<property name="Pool.MaximumIdleConnections" value="${pool.maximum.idle.connections}"/>
<property name="Pool.MaximumCheckoutTime" value="${pool.maximum.checkout.time}"/>
<property name="Pool.TimeToWait" value="${pool.time.to.wait}"/>
<property name="Pool.PingQuery" value="select 1 from DUAL"/>
<property name="Pool.PingEnabled" value="true"/>
<property name="Pool.PingConnectionsOlderThan" value="1"/>
<property name="Pool.PingConnectionsNotUsedFor" value="1"/>
</dataSource>
</transactionManager>

Is DBCP type connection better for what we wish to accomplish? It seems it can test connections idle connections out of the pool on a regular basis but as far as I can tell SimpleDataSource can only test connections as they are requested out of the pool. Am I correct, does anyone have any suggestions?

Thanks much for your time!