How to set timeout for db connection-CodePudding

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"
/>

CodePudding user response：

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

CodePudding user response：

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"