Spring Batch & Azure SQL Server: Could not get JDBC Connection

Question

We are in the process of moving to Azure SQL Server from Oracle DB for our Spring Batch application.

I am getting the following error intermittently

ERROR : 01.03.2022:1458 (40.269) [[]main] CommandLineJobRunner: Job Terminated in error: Error creating bean with name 'dateStoreList': Cannot resolve reference to bean 'jobRepository' while setting bean property 'jobRepository'; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'jobRepository': Invocation of init method failed; nested exception is org.springframework.jdbc.support.MetaDataAccessException: Could not get Connection for extracting meta data; nested exception is org.springframework.jdbc.CannotGetJdbcConnectionException: Could not get JDBC Connection; nested exception is org.apache.commons.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory (The TCP/IP connection to the host sqlsrv-01.database.windows.net, port 1433 has failed. Error: "sqlsrv-01.database.windows.net. Verify the connection properties. Make sure that an instance of SQL Server is running on the host and accepting TCP/IP connections at the port. Make sure that TCP connections to the port are not blocked by a firewall.".)

Data Source:

<!-- Connection Pooled DATA SOURCE -->
<beanid="dataSource"class="org.apache.commons.dbcp.BasicDataSource"destroy-method="close">
    <propertyname="driverClassName"value="${jdbc.driverClassName}"/>
    <propertyname="url"value="${jdbc.url}"/>
    <propertyname="connectionProperties"value="sendStringParametersAsUnicode=false;"/>
    <propertyname="username"value="${jdbc.username}"/>
    <propertyname="password"value="${jdbc.password}"/>
    <property name="initialSize" value="2" />
    <property name="maxActive" value="20" />
    <property name="maxIdle" value="1" />
    <property name="validationQuery" value="SELECT 1"/>
    <propertyname="testOnBorrow"value="false"/>
    <propertyname="testWhileIdle"value="true"/>
    <propertyname="timeBetweenEvictionRunsMillis"value="1200000"/>
    <propertyname="minEvictableIdleTimeMillis"value="3000000"/>
    <propertyname="numTestsPerEvictionRun"value="10"/>
    <propertyname="poolPreparedStatements"value="true"/>
    <propertyname="defaultAutoCommit"value="false"/>
</bean>

How do I enable retry at the spring batch application level to handle any database intermittent failure and recovery automatically?

score 1 · Answer 1 · answered Jan 07 '22 at 15:15

According to this StackOverflow answer, as a temporary solution, you can add ?autoReconnect=true to your JDBC URL. I wonder, though, if this is a bandaid solution, and if there is a worse issue present. Take a look at this Microsoft article about dropped connections. Are your connections getting stale and being dropped?

Spring Batch & Azure SQL Server: Could not get JDBC Connection

1 Answers1