Connection timeout is not matching what I set

[Matt D]

Hi,

I haven't been able to pinpoint why the discrepancy is happening, but in the code I'm setting:

config.setConnectionTimeout(30000);

Randomly, sometimes if I have network issues etc., I get back a timeout error.

java.sql.SQLTransientConnectionException: Connection is not available, request timed out after 56765ms.

at com.zaxxer.hikari.pool.HikariPool.getConnection(HikariPool.java:213)

at com.zaxxer.hikari.pool.HikariPool.getConnection(HikariPool.java:163)

at com.zaxxer.hikari.HikariDataSource.getConnection(HikariDataSource.java:85)

I happened to notice that it always seems to be in the 56 second time range instead of 30 seconds.

Any ideas why that might be?

I even set a breakpoint in HikariPool at line 213 and confirmed that the hardTimeout being passed in to the method is correct.

Thanks,

Matt

[Brett Wooldridge]

Can you tell me what database and driver you are using?

There are two code paths for checking the aliveness of the connection.  If the driver supports the JDBC 4.0 Connection.isValid(timeout) method, we're going to use that.  However, many drivers do not implement this with a socket level timeout, which can cause the issue that you are seeing.

If you look in the code linked above, you'll see the second connection test path, which is run if a connectionTestQuery is configured (isUseJdbcValidation will be false in that case).  In this case, we try to bracket the test query execution by setting the network timeout on the connection.  That is, if the driver supports the Connection.setNetworkTimeout() API, it will be set.

If the driver does not support setting the network timeout, there won't be a lot that we can do to return back from a validation within the timeout when there is a network disruption.

So, I would first suggest configuring the pool to use a connectionTestQuery, and see if that clears things up.  If you turn on debug logging you can see messages during startup like:

Connection.setNetworkTimeout() is not supported

If you do see that message, there is nothing HikariCP can do to return within the connectionTimeout in the case of a network disruption.  If you don't see that message, it implies that it is supported, and should work.

[Matt D]

Thanks for the info!

I'm using the Microsoft JDBC Driver for SQL Server.

I just turned on debugging and it looks like it doesn't support Connection.setNetworkTimeout() unfortunately:

Connection.setNetworkTimeout() is not supported (This operation is not supported.)

It sounds like there isn't much I can do then, but at least I'm more aware of why so thank you!

[Nitin Chauhan]

SQL Server 2012 supports isValid() ! https://msdn.microsoft.com/en-us/library/dd571242(v=sql.110).aspx

may be try without setting connectionTestQuery and see if it returns within time.

[Matt D]

Hi Nitin,

Originally, I didn't set connectionTestQuery and this is the behaviour I observed.

Matt

[Nitin Chauhan]

means the db or driver you are using is not honoring time out value passed to isValid(timeoutsec)  bad :(