Hikari Pool + mssql jdbc driver database failover recovery issue

[anilkbachola]

Driver version

8.2.2.jre11

SQL Server version

Microsoft SQL Server 2016 (SP2-CU11) (KB4527378) - 13.0.5598.27 (X64)
Nov 27 2019 18:09:22
Copyright (c) Microsoft Corporation
Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2012 R2 Standard 6.3 (Build 9600: ) (Hypervisor)

Client Operating System

Operating System: Red Hat Enterprise Linux Server 7.7 (Maipo)
CPE OS Name: cpe:/o:redhat:enterprise_linux:7.7:GA:server
Kernel: Linux 3.10.0-957.10.1.el7.x86_64
Architecture: x86-64

JAVA/JVM version

openjdk version "11.0.2" 2019-01-15
OpenJDK Runtime Environment 18.9 (build 11.0.2+9)
OpenJDK 64-Bit Server VM 18.9 (build 11.0.2+9, mixed mode)

Table schema

Problem description

spring boot application which we trying to switch to mssql-jdbc driver from JSQLConnect.
We have been testing our application with the driver, as part of the testing, we also did a failover test. We put the application under load and while the load is progressing we failed over the database. the database server has setup with always-on(failover method)

1. Expected behaviour:
   The expected behavior is that the application should recover within 2-3 minutes (our baseline with JSQLConnect driver), but the application is taking ~30 minutes to fully recover.

2. Actual behaviour:
   Fully application recovery is taking ~30 minutes. Application keep using the closed connections from Hikari and calls using the connection were failing with 'connection closed' errors.

We investigated and found that it has to do with sql server driver not returning the correct sql state when it detects a connection close while executing a statement. The driver seems to be behaving correctly when pool calls isValid() on the connection, but not when it encounters a connection closed error during statement execution.

3. Error message/stack trace:
   uncategorized SQLException for SQL []; SQL state [null]; error code [0]; The connection is closed.; nested exception is com.microsoft.sqlserver.jdbc.SQLServerException: The connection is closed.

4. Any other details that can be helpful:
   Hikari version: 3.4.2
   Max pool size: 150, minimumIdle: 100

Would like to mention the issue that is discussed in how hikaricp vs dbcp behaving to db failover with mssql-jdbc driver: here
Hikari seems to be relying on SQL State to decide whether the connection is actually closed here

JDBC trace logs

Reproduction code

[ulvii]

Hi @anilkbachola ,

Thanks for creating the issue and the PR. We will investigate and get back you.