[ https://issues.apache.org/jira/browse/IBATIS-449?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12587977#action_12587977 ]
Gerhard Kersten commented on IBATIS-449: ---------------------------------------- For us it's the same, and it's really mission-critical. Background: A firewall cuts a TCP connection after some time, but does not close it. With "Ping enabled" iBatis waits forever (actually 943 s) for the ping. Wihout "Ping enabled" a SQLException is thrown (Time out in rollback), also not earlier than after some 900 s. Setting "sun.net.client.defaultReadTimeout=60000" does not help. Here an example with a simulated TCP delay of 600s (against sun.net.client.defaultReadTimeout=60s): [EMAIL PROTECTED]:42:03 DEBUG - Testing connection 27114948... [EMAIL PROTECTED]:52:03 DEBUG - Connection 27114948 is GOOD! Setting maximumIdleConnections=0 as last hope is no option either, as it slows down everything significantly. For Bea Datasources I have observed the same firewall problem, but there the ping request returns almost immerdiatly, even for a corrupted TCP connection. Maybe some solution with a separate watchguard thread? > Is there a way to specify a timeout for Pool.PingQuery when using a jdbc > SimpleDataSource? > ------------------------------------------------------------------------------------------ > > Key: IBATIS-449 > URL: https://issues.apache.org/jira/browse/IBATIS-449 > Project: iBatis for Java > Issue Type: Improvement > Environment: Red Hat Enterprise Linux 4 Update 5 x86-64, JBOSS 4.0.5 > Reporter: Anton Rothenbacher > Priority: Critical > > 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! -- This message is automatically generated by JIRA. - You can reply to this email to add a comment to the issue online.