How to set timeout for db connection

Question

We are running Tomcat 9 Java 11 on Kubernetes. When the applications loses the ability to get to the database (network issue, firewall etc), it is taking 4 and half minutes to finally timeout and give this error:

java.sql.SQLRecoverableException: IO Error: The Network Adapter could not establish the connection

I have the login timeout set to 30 seconds on the actual database calls when it gets the datasource, but it is ignored. I guess since it can't get to the database over the network (we have it blocked in firewall to debug this issue so we can figure out where the timeout issue is)

We have the pool set as:

<Resource name="jdbc/appDS" auth="Container"
          type="javax.sql.DataSource"
          driverClassName="oracle.jdbc.OracleDriver"
          factory="org.apache.tomcat.jdbc.pool.DataSourceFactory"
          username="DB_USER"
          password="DB_PASS"
          url="jdbc:oracle:thin:@DB_URL"
          initialSize="0"
          maxIdle="10"
          minIdle="10"
          maxTotal="100"
          removeAbandonedOnBorrow="true"
          validationQuery="select 1 from dual"
          validationQueryTimeout="20"
          maxWait="30000"
          logAbandoned="true"
          testOnConnect="true"
          testWhileIdle="true"
          testOnBorrow="true"
/>

The JDBC standard (2.0/3.0) does not support setting of the connection timeout, but per oracle doc, The default is 60 seconds. A value of zero forces caller to wait indefinitely, https://docs.oracle.com/cd/E19159-01/819-3681/abehq/index.html Hope this somewhat helps, (may have to code re-try logic in application). — mzm, Dec 08 '22 at 14:11
The maxWait is that setting. Seems to ignore it. Thinking it some sort of JVM or Tomcat setting on the network connection going out?? — dluft, Dec 08 '22 at 15:04

score 0 · Answer 1 · answered Dec 11 '22 at 10:08

As per this SO the error IO Error: The Network Adapter could not establish the connection got fixed by changing the port. Which also resulted in the db timeout. Can you check whether the correct port is used for connection or not.

And also you are getting the other issue with db connection timeout then refer to this Tomcat 9 JDBC Connection Pool and validate each attribute.

At the set url you are using Thin-style Service Name Syntax only the JDBC Thin driver supports this Thin-style service name. Kindly check if the JDBC Thin driver is installed and also check the correct syntax while using it.

The syntax is: @//host_name:port_number/service_name
For example: jdbc:oracle:thin:scott/tiger@//myhost:1521/myservicename

score 0 · Answer 2 · answered Dec 13 '22 at 13:56

I was able to set timeouts via Java options that are used when starting tomcat in the kube container..

 env:
          - name: JAVA_OPTS
            value: "-Doracle.jdbc.ReadTimeout=30000 -Dsun.net.client.defaultConnectTimeout=30000 -Dsun.net.client.defaultConnectTimeout=30000"

How to set timeout for db connection

2 Answers2