Lesson Learned #499: HikariCP Retry Policy - The connection is closed

[Guest Jose_Manuel_Jurado]

Today, I worked on a service request that our customer got the following error message:

 

 

 

• 19:28:43.232 [main] WARN com.zaxxer.hikari.pool.PoolBase - AppExample-ConnectionPooling - Failed to validate connection ConnectionID:3 ClientConnectionId: 8351bddc-acbb-4669-xxxx-xxxxxxxxxxxx (The connection is closed.). Possibly consider using a shorter maxLifetime value.
  
• 19:28:43.234 [AppExample-ConnectionPooling connection closer] DEBUG com.zaxxer.hikari.pool.PoolBase - DotNetExample-ConnectionPooling - Closing connection ConnectionID:3 ClientConnectionId: 8351bddc-acbb--xxxx-xxxxxxxxxxxx: (connection is dead). Following I would like to share my experience with this error.
  

 

 

 

This error indicates that the connection was closed unexpectedly and cannot be reused. In this situation, HikariCP will evict the connection from the pool and open a new one according to its Retry Logic policy.

 

Let me explain the Retry Logic with an example. Before providing a valid connection to the application or returning it to the HikariCP pool, the software validates the connection by checking the port and running a validation query, such as [iCODE]SELECT 1[/iCODE].

 

As we can see below, when a dead connection is detected, a new connection is immediately opened or reused to provide a valid connection to the application. In my case, working from home and connecting to the West Europe region, establishing a connection took around 46 ms. Running the client machine from Azure would reduce this time.

 

 

 

 

 

19:41:34.359 [singleThread] Connecting to Database
19:41:34.362 [main] WARN com.zaxxer.hikari.pool.PoolBase - AppExample-ConnectionPooling - Failed to validate connection ConnectionID:1 ClientConnectionId: d0f3613f-47d6 (The connection is closed.). Possibly consider using a shorter maxLifetime value.
19:41:34.365 [AppName-ConnectionPooling connection closer] DEBUG com.zaxxer.hikari.pool.PoolBase - AppNameExample-ConnectionPooling - Closing connection ConnectionID:1 ClientConnectionId: d0f3613f-47d6: (connection is dead)
19:41:34.405 [singleThread] Connected to Database
19:41:34.412 [singleThread] Connection Time (ms) 46
19:41:34.450 [singleThread] Reading Data exec testconn

 

 

 

 

 

Other lesson learned is what happens if the connection is established but the validation query (SELECT 1) gives an error or takes more time than the validation timeout is specified, we are going to have another error like this one: [main] ERROR com.zaxxer.hikari.pool.PoolBase - AppExample-ConnectionPooling - Failed to execute connection test query (Read timed out).

 

Continue reading...