attempt to reconnect jdbc pool datasource after database restarts

Question

I have a web-app with a Java back-end that uses Tomcat jdbc-pool for database connections. This works fine.

However I am trying to foolproof it before exporting it to other locations, and recently a scenario occurred where someone restarted the SQL Server database service but did not restart the Tomcat service. This caused a SQLException: java.sql.SQLException: I/O Error: Connection reset by peer: socket write error until I restarted Tomcat, forcing the jdbc-pool datasource to reconnect.

I looked for some kind of a configuration in the Tomcat jdbc-pool docs to tell the datasource to attempt to reconnect but I couldn't find anything.

Does anyone know if there is some kind of configuration for this or should I check this condition before each request?

score 21 · Accepted Answer · edited Mar 11 '15 at 10:01

21

Not 100% sure if this is your problem but on http://www.tomcatexpert.com/blog/2010/04/01/configuring-jdbc-pool-high-concurrency it says you can use testOnBorrow with a validationQuery.

<Resource type="javax.sql.DataSource"
            ...
            testOnBorrow="true"
            validationQuery="SELECT 1"
            removeAbandoned="true"
            />

edited Mar 11 '15 at 10:01

Garima Gangwar

3
3

answered Jul 02 '12 at 22:48

Natan Cox

1,495
2
15
28

5

I was trying to avoid the validation query overhead, and found another method in that same link using `timeBetweenEvictionRunsMillis="5000"` and `minEvictableIdleTimeMillis="5000"` and `minIdle="0"` that seems to handle the problem, thanks. – egerardus Jul 27 '12 at 22:44
@Geronimo: well I upvoted your comment yesterday but today I realized that using `minIdle="0"` may actually be worse: it's actually closing the connection after 5s of inactivity, whether it's fine or not. So I'm not sure what's worse: executing `SELECT 1` before each query or having to reconnect every 5s of inactivity... – xav Nov 08 '18 at 13:24
the `testOnBorrow="true"` will auto start a new connection if found the old connection is closed? – Panadol Chong Feb 14 '19 at 02:57
That original link is gone, but it was recorded by the wayback machine: https://web.archive.org/web/20180521112213/http://www.tomcatexpert.com/blog/2010/04/01/configuring-jdbc-pool-high-concurrency – hooknc Aug 04 '20 at 21:20

score 1 · Answer 2 · answered May 20 '16 at 23:17

While checking for the same issue I came across this post which has the auto connect configurations for all app servers.

Below are the configuration which I used for auto connect in tomcat for reference.

    <Resource auth="Container"
driverClassName="oracle.jdbc.OracleDriver"
initialSize="5"
maxActive="120"
maxIdle="5"
maxWait="5000"
name="jdbc/oracle/myds"
password="secret"
poolPreparedStatements="true"
type="javax.sql.DataSource"
url="jdbc:oracle:thin:@DBHOSTNAME:1521/ServiceName"
username="testuser"
validationQuery="select 1 from tab"
testOnBorrow="true"/>

Complete auto connect configurations for all app servers can be found here in Datasource autoreconnect in Java Application Servers.

score 1 · Answer 3 · answered Nov 30 '17 at 00:05

Just to add on to Natan Cox's answer

Reference - http://tomcat.apache.org/tomcat-8.0-doc/jdbc-pool.html#Common_Attributes

<Resource type="javax.sql.DataSource"
        ...
        testOnBorrow="true"
        validationQuery="SELECT 1"
        removeAbandoned="true"
        />

As against Geronimo, I would still like to use validationQuery

Database validationQuery notes

hsqldb - select 1 from INFORMATION_SCHEMA.SYSTEM_USERS

Oracle - select 1 from dual

DB2 - select 1 from sysibm.sysdummy1

mysql - select 1

microsoft SQL Server - select 1

postgresql - select 1

ingres - select 1

derby - values 1

H2 - select 1

Firebird - select 1 from rdb$database

Reference - DBCP - validationQuery for different Databases

the testOnBorrow="true" will auto start a new connection if found the old connection is closed? — Panadol Chong, Feb 14 '19 at 02:58

attempt to reconnect jdbc pool datasource after database restarts

3 Answers3

Linked