Environment
Hikari CP Version : 3.4.1
JDK version: 1.8.0_251
Database: Azure SQL
SpringBoot Version: 2.2.2 RELEASE
MS-SQL JDBC Driver version:- 8.4.1-jre8
I am working on a Spring Boot app where I have a requirement for configuring the auto failover of database and we are leveraging Azure Failover Groups. The application is connected to the primary database and when the manual failover of the primary server is done, the application should connect to secondary server which is now the new primary.
Below is my JDBC connection string and Hikari properties:
logging.level.org.springframework.jdbc.core=DEBUG
spring.datasource.url=jdbc:sqlserver://<FailoverGroupname>.database.windows.net:1433;database=<DatabaseName>;encrypt=true;trustServerCertificate=false;hostNameInCertificate=*.database.windows.net;loginTimeout=30;
spring.datasource.username=demo
spring.datasource.password=***
spring.datasource.initialization-mode=always
hibernate.hikari.minimumIdle=0
hibernate.hikari.maxPoolSize=1
hibernate.hikari.autoCommit=false
hibernate.hikari.initializationFailTimeout=3000
hibernate.hikari.connectionTestQuery=SELECT 1
When the application is started, below is the analysis:
- Hikari has a valid connection in the pool
- Spring JPA Transaction pulls the connection from the pool
- Data is getting persisted in database successfully
- next, Manual Failover is done
- txn.begin(), throws exception
- Broken Pipe Write Failed Exception
- Connection is closed (SQLServerException: The connection is closed)
- Connection remains closed till the application is running
Expectation:
Since pool has come in bad state, the connections after getting closed should be recovered and connect to the new primary database that serves as backup
Does anyone knows how can I re-establish the closed connections to auto reconnect to backup database.