6

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 while trying to execute the job post migration to SQL Server

Could not increment identity; nested exception is com.microsoft.sqlserver.jdbc.SQLServerException: Invalid object name 'AppName.BATCH_JOB_SEQ'.

I can that SQL Server has the required sequence

enter image description here

Below the job repository configuration

<batch:job-repository id="jobRepository"
 isolation-level-for-create="READ_COMMITTED"
 table-prefix="MyApp.BATCH_"/>

Below are tables and sequences are available in Oracle

enter image description here enter image description here

Below are tables and sequences are available in Azure SQL

enter image description here enter image description here

I am using the following version enter image description here

should I upgrade to

enter image description here

or should I recreate the tables as per https://github.com/spring-projects/spring-batch/blob/main/spring-batch-core/src/main/resources/org/springframework/batch/core/schema-sqlserver.sql

One Developer
  • 99
  • 5
  • 43
  • 103
  • Check out https://stackoverflow.com/questions/64094079/spring-batch-invalid-object-name-batch-job-instance . Few options in there and different approaches. – mkane Aug 24 '21 at 12:26
  • @MugilKarthikeyan Are you connecting to the right database? Please, try providing [`databaseName`](https://learn.microsoft.com/en-us/sql/connect/jdbc/building-the-connection-url?view=sql-server-ver15) to the one in which your tables are created in the database URL when configuring the Spring Batch datasource. – jccampanero Aug 31 '21 at 14:13
  • @jccampanero - I can confirm that I am connecting to the right database however it is an Azure SQL server migrated from on-premise Oracle – One Developer Aug 31 '21 at 16:01
  • Hi @MugilKarthikeyan. Thank you for the feedback. I am sure about that ;) The comment was more in the sense of using the databaseName parameter in the connection URL to avoid any possible problem related with the existence of different databases in the same server. I posted an answer describing the problem as a consequence of the migration process from Oracle. I hope it helps. – jccampanero Aug 31 '21 at 21:26

2 Answers2

6

Probably the error is related to the migration from Oracle to Azure SQL Server.

As you can see in the source code of the library under the hood Spring Batch uses different strategies when generating the ids for jobs, job executions, and step executions.

In the Oracle case, they use sequences; with SQL Server, they implemented id generation using tables with an identity column.

The migration process also replicated the different Oracle sequences required by Spring Batch and very likely it is causing the issue when the aforementioned SQL Server id generation strategy tries obtaining the next value.

Please, drop the migrated sequences and create the three tables required for SQL Server with the appropriate values:

CREATE TABLE BATCH_STEP_EXECUTION_SEQ (
  ID BIGINT IDENTITY(<last analogous Oracle sequence value>, 1)
);

CREATE TABLE BATCH_JOB_EXECUTION_SEQ (
  ID BIGINT IDENTITY(<last analogous Oracle sequence value>, 1)
);

CREATE TABLE BATCH_JOB_SEQ (
  ID BIGINT IDENTITY(<last analogous Oracle sequence value>, 1)
);
jccampanero
  • 50,989
  • 3
  • 20
  • 49
0

There is more than one post/question in Stack Overflow related to this issue. I provided a cleaner explanation and fix for this issue in here.

Keeping tables as sequences in spring-batch leads to deadlock issues reported by many users already, so better replace them with real sequences if you are working with SQL Server >= 2012

Happy coding!

momonari8
  • 51
  • 4