4

We are in the process of moving to Azure SQL Server from Oracle DB for our Spring Batch application that runs for few hours with 50 Threads.

Below is my JDBC Configuration

MSSQL_DRIVERCLASSNAME: "com.microsoft.sqlserver.jdbc.SQLServerDriver"
MSSQL_DATASOURCE_URL: "jdbc:sqlserver://databaseFQDN:1433;databaseName=databaseName;trustServerCertificate=true"

I am getting the following error intermittently

[144] ERROR : 03.10.2022:0454 (54.161) [[]main] AbstractJob: Encountered fatal error executing job org.springframework.batch.core.JobExecutionException: Flow execution ended unexpectedly
    at org.springframework.batch.core.job.flow.FlowJob.doExecute(FlowJob.java:141) ~[irol-bat-applybatch-jobs-2.2.12-SNAPSHOT.jar:?]
    at org.springframework.batch.core.job.AbstractJob.execute(AbstractJob.java:281) [irol-bat-applybatch-jobs-2.2.12-SNAPSHOT.jar:?]
    at org.springframework.batch.core.launch.support.SimpleJobLauncher$1.run(SimpleJobLauncher.java:120) [irol-bat-applybatch-jobs-2.2.12-SNAPSHOT.jar:?]
    at org.springframework.core.task.SyncTaskExecutor.execute(SyncTaskExecutor.java:49) [irol-bat-applybatch-jobs-2.2.12-SNAPSHOT.jar:?]
    at org.springframework.batch.core.launch.support.SimpleJobLauncher.run(SimpleJobLauncher.java:114) [irol-bat-applybatch-jobs-2.2.12-SNAPSHOT.jar:?]
    at org.springframework.batch.core.launch.support.CommandLineJobRunner.start(CommandLineJobRunner.java:349) [irol-bat-applybatch-jobs-2.2.12-SNAPSHOT.jar:?]
    at org.springframework.batch.core.launch.support.CommandLineJobRunner.main(CommandLineJobRunner.java:574) [irol-bat-applybatch-jobs-2.2.12-SNAPSHOT.jar:?] Caused by: org.springframework.batch.core.job.flow.FlowExecutionException: Ended flow=multithreadedItemSkuProcessing.7 at state=multithreadedItemSkuProcessing.7.applyItem8 with exception
    at org.springframework.batch.core.job.flow.support.SimpleFlow.resume(SimpleFlow.java:152) ~[irol-bat-applybatch-jobs-2.2.12-SNAPSHOT.jar:?]
    at org.springframework.batch.core.job.flow.support.SimpleFlow.start(SimpleFlow.java:124) ~[irol-bat-applybatch-jobs-2.2.12-SNAPSHOT.jar:?]
    at org.springframework.batch.core.job.flow.support.state.SplitState$1.call(SplitState.java:91) ~[irol-bat-applybatch-jobs-2.2.12-SNAPSHOT.jar:?]
    at org.springframework.batch.core.job.flow.support.state.SplitState$1.call(SplitState.java:90) ~[irol-bat-applybatch-jobs-2.2.12-SNAPSHOT.jar:?]
    at java.util.concurrent.FutureTask.run(FutureTask.java:266) ~[?:1.8.0_292]
    at java.lang.Thread.run(Thread.java:748) ~[?:1.8.0_292] Caused by: org.springframework.transaction.TransactionSystemException: Could not roll back JDBC transaction; nested exception is com.microsoft.sqlserver.jdbc.SQLServerException: The connection is closed.
    at org.springframework.jdbc.datasource.DataSourceTransactionManager.doRollback(DataSourceTransactionManager.java:288) ~[irol-bat-applybatch-jobs-2.2.12-SNAPSHOT.jar:?]
    at org.springframework.transaction.support.AbstractPlatformTransactionManager.processRollback(AbstractPlatformTransactionManager.java:846) ~[irol-bat-applybatch-jobs-2.2.12-SNAPSHOT.jar:?]
    at org.springframework.transaction.support.AbstractPlatformTransactionManager.rollback(AbstractPlatformTransactionManager.java:823) ~[irol-bat-applybatch-jobs-2.2.12-SNAPSHOT.jar:?]
    at org.springframework.transaction.interceptor.TransactionAspectSupport.completeTransactionAfterThrowing(TransactionAspectSupport.java:493) ~[irol-bat-applybatch-jobs-2.2.12-SNAPSHOT.jar:?]
    at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:264) ~[irol-bat-applybatch-jobs-2.2.12-SNAPSHOT.jar:?]
    at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:94) ~[irol-bat-applybatch-jobs-2.2.12-SNAPSHOT.jar:?]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172) ~[irol-bat-applybatch-jobs-2.2.12-SNAPSHOT.jar:?]
    at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204) ~[irol-bat-applybatch-jobs-2.2.12-SNAPSHOT.jar:?]
    at com.sun.proxy.$Proxy18.getLastStepExecution(Unknown Source) ~[?:?]
    at org.springframework.batch.core.job.SimpleStepHandler.handleStep(SimpleStepHandler.java:106) ~[irol-bat-applybatch-jobs-2.2.12-SNAPSHOT.jar:?]
    at org.springframework.batch.core.job.flow.JobFlowExecutor.executeStep(JobFlowExecutor.java:61) ~[irol-bat-applybatch-jobs-2.2.12-SNAPSHOT.jar:?]
    at org.springframework.batch.core.job.flow.support.state.StepState.handle(StepState.java:60) ~[irol-bat-applybatch-jobs-2.2.12-SNAPSHOT.jar:?]
    at org.springframework.batch.core.job.flow.support.SimpleFlow.resume(SimpleFlow.java:144) ~[irol-bat-applybatch-jobs-2.2.12-SNAPSHOT.jar:?]
    at org.springframework.batch.core.job.flow.support.SimpleFlow.start(SimpleFlow.java:124) ~[irol-bat-applybatch-jobs-2.2.12-SNAPSHOT.jar:?]
    at org.springframework.batch.core.job.flow.support.state.SplitState$1.call(SplitState.java:91) ~[irol-bat-applybatch-jobs-2.2.12-SNAPSHOT.jar:?]
    at org.springframework.batch.core.job.flow.support.state.SplitState$1.call(SplitState.java:90) ~[irol-bat-applybatch-jobs-2.2.12-SNAPSHOT.jar:?]
    at java.util.concurrent.FutureTask.run(FutureTask.java:266) ~[?:1.8.0_292]
    at java.lang.Thread.run(Thread.java:748) ~[?:1.8.0_292] Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The connection is closed.
    at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:234) ~[irol-bat-applybatch-jobs-2.2.12-SNAPSHOT.jar:?]
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.checkClosed(SQLServerConnection.java:1090) ~[irol-bat-applybatch-jobs-2.2.12-SNAPSHOT.jar:?]
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.rollback(SQLServerConnection.java:3195) ~[irol-bat-applybatch-jobs-2.2.12-SNAPSHOT.jar:?]
    at org.apache.commons.dbcp.DelegatingConnection.rollback(DelegatingConnection.java:368) ~[irol-bat-applybatch-jobs-2.2.12-SNAPSHOT.jar:?]
    at org.apache.commons.dbcp.DelegatingConnection.rollback(DelegatingConnection.java:368) ~[irol-bat-applybatch-jobs-2.2.12-SNAPSHOT.jar:?]
    at org.apache.commons.dbcp.PoolingDataSource$PoolGuardConnectionWrapper.rollback(PoolingDataSource.java:323) ~[irol-bat-applybatch-jobs-2.2.12-SNAPSHOT.jar:?]
    at org.springframework.jdbc.datasource.DataSourceTransactionManager.doRollback(DataSourceTransactionManager.java:285) ~[irol-bat-applybatch-jobs-2.2.12-SNAPSHOT.jar:?]
    at org.springframework.transaction.support.AbstractPlatformTransactionManager.processRollback(AbstractPlatformTransactionManager.java:846) ~[irol-bat-applybatch-jobs-2.2.12-SNAPSHOT.jar:?]
    at org.springframework.transaction.support.AbstractPlatformTransactionManager.rollback(AbstractPlatformTransactionManager.java:823) ~[irol-bat-applybatch-jobs-2.2.12-SNAPSHOT.jar:?]
    at org.springframework.transaction.interceptor.TransactionAspectSupport.completeTransactionAfterThrowing(TransactionAspectSupport.java:493) ~[irol-bat-applybatch-jobs-2.2.12-SNAPSHOT.jar:?]
    at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:264) ~[irol-bat-applybatch-jobs-2.2.12-SNAPSHOT.jar:?]
    at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:94) ~[irol-bat-applybatch-jobs-2.2.12-SNAPSHOT.jar:?]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172) ~[irol-bat-applybatch-jobs-2.2.12-SNAPSHOT.jar:?]
    at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204) ~[irol-bat-applybatch-jobs-2.2.12-SNAPSHOT.jar:?]
    at com.sun.proxy.$Proxy18.getLastStepExecution(Unknown Source) ~[?:?]
    at org.springframework.batch.core.job.SimpleStepHandler.handleStep(SimpleStepHandler.java:106) ~[irol-bat-applybatch-jobs-2.2.12-SNAPSHOT.jar:?]
    at org.springframework.batch.core.job.flow.JobFlowExecutor.executeStep(JobFlowExecutor.java:61) ~[irol-bat-applybatch-jobs-2.2.12-SNAPSHOT.jar:?]
    at org.springframework.batch.core.job.flow.support.state.StepState.handle(StepState.java:60) ~[irol-bat-applybatch-jobs-2.2.12-SNAPSHOT.jar:?]
    at org.springframework.batch.core.job.flow.support.SimpleFlow.resume(SimpleFlow.java:144) ~[irol-bat-applybatch-jobs-2.2.12-SNAPSHOT.jar:?]
    at org.springframework.batch.core.job.flow.support.SimpleFlow.start(SimpleFlow.java:124) ~[irol-bat-applybatch-jobs-2.2.12-SNAPSHOT.jar:?]
    at org.springframework.batch.core.job.flow.support.state.SplitState$1.call(SplitState.java:91) ~[irol-bat-applybatch-jobs-2.2.12-SNAPSHOT.jar:?]
    at org.springframework.batch.core.job.flow.support.state.SplitState$1.call(SplitState.java:90) ~[irol-bat-applybatch-jobs-2.2.12-SNAPSHOT.jar:?]
    at java.util.concurrent.FutureTask.run(FutureTask.java:266) ~[?:1.8.0_292]
    at java.lang.Thread.run(Thread.java:748) ~[?:1.8.0_292]

or

ERROR : 02.11.2022:1221 (34.455) [[]SimpleAsyncTaskExecutor-5] AbstractStep: Encountered an error executing the step
org.springframework.batch.item.ItemStreamException: Failed to initialize the reader
        .....
        at org.springframework.batch.item.support.CompositeItemStream.open(CompositeItemStream.java:93) ~[spring-batch-jobs-2.2.12-SNAPSHOT.jar:?]
        at org.springframework.batch.core.step.item.ChunkMonitor.open(ChunkMonitor.java:105) ~[spring-batch-jobs-2.2.12-SNAPSHOT.jar:?]
        at org.springframework.batch.item.support.CompositeItemStream.open(CompositeItemStream.java:93) ~[spring-batch-jobs-2.2.12-SNAPSHOT.jar:?]
        at org.springframework.batch.core.step.tasklet.TaskletStep.open(TaskletStep.java:301) ~[spring-batch-jobs-2.2.12-SNAPSHOT.jar:?]
        at org.springframework.batch.core.step.AbstractStep.execute(AbstractStep.java:192) [spring-batch-jobs-2.2.12-SNAPSHOT.jar:?]
        at org.springframework.batch.core.job.SimpleStepHandler.handleStep(SimpleStepHandler.java:135) [spring-batch-jobs-2.2.12-SNAPSHOT.jar:?]
        at org.springframework.batch.core.job.flow.JobFlowExecutor.executeStep(JobFlowExecutor.java:61) [spring-batch-jobs-2.2.12-SNAPSHOT.jar:?]
        at org.springframework.batch.core.job.flow.support.state.StepState.handle(StepState.java:60) [spring-batch-jobs-2.2.12-SNAPSHOT.jar:?]
        at org.springframework.batch.core.job.flow.support.SimpleFlow.resume(SimpleFlow.java:144) [spring-batch-jobs-2.2.12-SNAPSHOT.jar:?]
        at org.springframework.batch.core.job.flow.support.SimpleFlow.start(SimpleFlow.java:124) [spring-batch-jobs-2.2.12-SNAPSHOT.jar:?]
        at org.springframework.batch.core.job.flow.support.state.SplitState$1.call(SplitState.java:91) [spring-batch-jobs-2.2.12-SNAPSHOT.jar:?]
        at org.springframework.batch.core.job.flow.support.state.SplitState$1.call(SplitState.java:90) [spring-batch-jobs-2.2.12-SNAPSHOT.jar:?]
        at java.util.concurrent.FutureTask.run(FutureTask.java:266) [?:1.8.0_265]
        at java.lang.Thread.run(Thread.java:748) [?:1.8.0_265]
Caused by: org.springframework.dao.DataAccessResourceFailureException: Executing stored procedure; SQL [{call itemmaintenance(?, ?, ?, ?)}]; SQL Server returned an incomplete response. The connection has been closed.; nested exception is com.microsoft.sqlserver.jdbc.SQLServerException: SQL Server returned an incomplete response. The connection has been closed.
        at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:103) ~[spring-batch-jobs-2.2.12-SNAPSHOT.jar:?]
        at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72) ~[spring-batch-jobs-2.2.12-SNAPSHOT.jar:?]
        at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80) ~[spring-batch-jobs-2.2.12-SNAPSHOT.jar:?]
        at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80) ~[spring-batch-jobs-2.2.12-SNAPSHOT.jar:?]
        at org.springframework.batch.item.database.StoredProcedureItemReader.openCursor(StoredProcedureItemReader.java:221) ~[spring-batch-jobs-2.2.12-SNAPSHOT.jar:?]
        at org.springframework.batch.item.database.AbstractCursorItemReader.doOpen(AbstractCursorItemReader.java:401) ~[spring-batch-jobs-2.2.12-SNAPSHOT.jar:?]
        ... 25 more
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: SQL Server returned an incomplete response. The connection has been closed.
        at com.microsoft.sqlserver.jdbc.SQLServerConnection.terminate(SQLServerConnection.java:2924) ~[spring-batch-jobs-2.2.12-SNAPSHOT.jar:?]
        at com.microsoft.sqlserver.jdbc.SQLServerConnection.terminate(SQLServerConnection.java:2913) ~[spring-batch-jobs-2.2.12-SNAPSHOT.jar:?]
        at com.microsoft.sqlserver.jdbc.TDSReader.readPacket(IOBuffer.java:6425) ~[spring-batch-jobs-2.2.12-SNAPSHOT.jar:?]
        at com.microsoft.sqlserver.jdbc.TDSReader.nextPacket(IOBuffer.java:6368) ~[spring-batch-jobs-2.2.12-SNAPSHOT.jar:?]
        at com.microsoft.sqlserver.jdbc.TDSReader.ensurePayload(IOBuffer.java:6346) ~[spring-batch-jobs-2.2.12-SNAPSHOT.jar:?]
        at com.microsoft.sqlserver.jdbc.TDSReader.readBytes(IOBuffer.java:6647) ~[spring-batch-jobs-2.2.12-SNAPSHOT.jar:?]
        at com.microsoft.sqlserver.jdbc.TDSReader.readUnicodeString(IOBuffer.java:6601) ~[spring-batch-jobs-2.2.12-SNAPSHOT.jar:?]
        at com.microsoft.sqlserver.jdbc.StreamColumns.setFromTDS(StreamColumns.java:213) ~[spring-batch-jobs-2.2.12-SNAPSHOT.jar:?]
        at com.microsoft.sqlserver.jdbc.SQLServerResultSet$1CursorInitializer.onColMetaData(SQLServerResultSet.java:276) ~[spring-batch-jobs-2.2.12-SNAPSHOT.jar:?]
        at com.microsoft.sqlserver.jdbc.TDSParser.parse(tdsparser.java:109) ~[spring-batch-jobs-2.2.12-SNAPSHOT.jar:?]
        at com.microsoft.sqlserver.jdbc.TDSParser.parse(tdsparser.java:37) ~[spring-batch-jobs-2.2.12-SNAPSHOT.jar:?]
        at com.microsoft.sqlserver.jdbc.SQLServerResultSet.<init>(SQLServerResultSet.java:385) ~[spring-batch-jobs-2.2.12-SNAPSHOT.jar:?]
        at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1634) ~[spring-batch-jobs-2.2.12-SNAPSHOT.jar:?]
        at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:594) ~[spring-batch-jobs-2.2.12-SNAPSHOT.jar:?]
        at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:524) ~[spring-batch-jobs-2.2.12-SNAPSHOT.jar:?]
        at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7194) ~[spring-batch-jobs-2.2.12-SNAPSHOT.jar:?]
        at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:2979) ~[spring-batch-jobs-2.2.12-SNAPSHOT.jar:?]
        at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:248) ~[spring-batch-jobs-2.2.12-SNAPSHOT.jar:?]
        at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:223) ~[spring-batch-jobs-2.2.12-SNAPSHOT.jar:?]
        at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.execute(SQLServerPreparedStatement.java:505) ~[spring-batch-jobs-2.2.12-SNAPSHOT.jar:?]
        at org.apache.commons.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:172) ~[spring-batch-jobs-2.2.12-SNAPSHOT.jar:?]
        at org.apache.commons.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:172) ~[spring-batch-jobs-2.2.12-SNAPSHOT.jar:?]
        at org.apache.commons.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:172) ~[spring-batch-jobs-2.2.12-SNAPSHOT.jar:?]
        at org.springframework.batch.item.database.StoredProcedureItemReader.openCursor(StoredProcedureItemReader.java:205)

I am using the below MSSQL dependency

<!-- https://mvnrepository.com/artifact/com.microsoft.sqlserver/mssql-jdbc -->
<dependency>
    <groupId>com.microsoft.sqlserver</groupId>
    <artifactId>mssql-jdbc</artifactId>
    <version>7.4.1.jre8</version>
</dependency>

Note: I am running my Spring batch on AZURE KUBERNETES and it's in the same VNET as the Azure Sql database. Technically both are in the same network and network should not be the cause

Should I try: https://github.com/Microsoft/mssql-jdbc/issues/879#:~:text=We%20fixed%20this,up%20for%20free?

Update (03-11-2022): Tried the following retry setting but it didn't work. what am I missing?

<chunk reader="cvsFileItemReader" writer="itemWriter" commit-interval="10" retry-limit="10" >
    <retryable-exception-classes>
        <include class="org.springframework.dao.DataAccessResourceFailureException"/>
        <include class="org.springframework.jdbc.UncategorizedSQLException"/>
        <include class="com.microsoft.sqlserver.jdbc.SQLServerException"/>
        <include class="org.springframework.jdbc.support.MetaDataAccessException"/>
        <include class="org.springframework.batch.core.step.FatalStepExecutionException"/>
        <include class="org.springframework.transaction.TransactionSystemException"/>
        <include class="org.springframework.batch.core.JobInterruptedException"/>
        <include class="org.springframework.batch.item.ItemStreamException"/>
    </retryable-exception-classes>
</chunk>
Paulo
  • 8,690
  • 5
  • 20
  • 34
One Developer
  • 99
  • 5
  • 43
  • 103
  • Transient errors should be expected when using Azure, have you implemented spring retry logic? Also take a look here https://devblogs.microsoft.com/azure-sql/improve-jdbc-application-reliability-with-azure-sql-and-connection-pooling/ – Steve Ford Feb 14 '22 at 19:53
  • We are using DBCP1.x for connection pooling and Spring batch 2.1.x- can I still implement - spring retry logic? please suggest. – One Developer Feb 15 '22 at 07:46
  • Is there a way to configure IdleTimeout in DBCP1.4? – One Developer Feb 17 '22 at 07:35
  • 1
    You should add the tag `Oracle` and 'JDBC '. – Ronen Ariely Feb 20 '22 at 03:54
  • I see `Executing stored procedure; SQL [{call itemmaintenance(?, ?, ?, ?)}]; SQL Server returned an incomplete response` Doesnot look like t-sql syntax but oracle pl/sql – jjdesign Mar 17 '22 at 19:55
  • It is an Azure SQL server query – One Developer Mar 18 '22 at 00:10
  • this one is not obviously. Oracle pl/sql can be compatible with t-sql if you don't use special functions calls, but this one is not compatible in any sql server. have you parsed all your pl/sql scripts to make sure they are t-sql compatible? – jjdesign Mar 18 '22 at 08:24

0 Answers0