DEVHIDE
Login Or Sign up

SQL Server returned an incomplete response. The connection has been closed.; nested exception is com.microsoft.sqlserver.jdbc.SQLServerException:

1.4k Views Asked by At

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>
sql-server spring-batch jdbctemplate spring-retry dbcp
Original Q&A
0

There are 0 best solutions below

Related Questions in SQL-SERVER

Related Questions in SPRING-BATCH

Related Questions in JDBCTEMPLATE

Related Questions in SPRING-RETRY

Related Questions in DBCP

Trending Questions

Popular # Hahtags

javascript python java c# php android html jquery c++ css ios sql mysql r reactjs node.js arrays c asp.net json python-3.x ruby-on-rails .net sql-server swift django angular objective-c pandas excel

Popular Questions

Copyright © 2021 Jogjafile Inc.