Spring batch instance id duplicate key error, trying to start from #1

Question

I am copying java code(using springboot spring batch) and database from dev server to local(desktop) and run it. Getting an error.

It works fine in Dev server. In local , spring-batch is resetting Job instance to 1 and causing primary key error.Is there any option in spring batch so that it starts with next instance id instead of 1? Please let me know

Referred to stackoverflow link below , seems related but posted few years back and reference links does not work anymore. Duplicate Spring Batch Job Instance

@Configuration
@EnableBatchProcessing
public class Jobclass {
   #Rest of the code with Job Bean and steps which works fine in Dev server
}

Error: com.microsoft.sqlserver.jdbc.SQLServerException: Violation of PRIMARY KEY constraint 'PK__BATCH_JO__4848154AFB5435C7'. Cannot insert duplicate key in object 'dbo.BATCH_JOB_INSTANCE'. The duplicate key value is (5).

Answer 1

Batch table instance and execution not updated with proper key , so manually what I have done is created table and added first row, this will not throw any duplicate and primary key issue.

drop table batch_job_seq;
drop table batch_step_execution_context;
drop table batch_step_execution_seq;
drop table batch_step_execution;
drop table batch_job_execution_seq;
drop table batch_job_execution_params;
drop table batch_job_execution_context;
drop table batch_job_execution;
drop table batch_job_instance;


CREATE TABLE IF NOT EXISTS `batch_job_instance` (
  `JOB_INSTANCE_ID` BIGINT NOT NULL AUTO_INCREMENT,
  `VERSION` BIGINT NULL DEFAULT NULL,
  `JOB_NAME` VARCHAR(100) NOT NULL,
  `JOB_KEY` VARCHAR(32) NOT NULL,
  PRIMARY KEY (`JOB_INSTANCE_ID`),
  UNIQUE INDEX `JOB_INST_UN` (`JOB_NAME` ASC, `JOB_KEY` ASC) VISIBLE);


CREATE TABLE IF NOT EXISTS `batch_job_seq` (
  `ID` BIGINT NOT NULL,
  `UNIQUE_KEY` CHAR(1) NOT NULL,
  UNIQUE INDEX `UNIQUE_KEY_UN` (`UNIQUE_KEY` ASC));




CREATE TABLE IF NOT EXISTS `batch_job_execution` (
  `JOB_EXECUTION_ID` BIGINT NOT NULL ,
  `VERSION` BIGINT NULL DEFAULT NULL,
  `JOB_INSTANCE_ID` BIGINT NOT NULL AUTO_INCREMENT,
  `CREATE_TIME` DATETIME(6) NOT NULL,
  `START_TIME` DATETIME(6) NULL DEFAULT NULL,
  `END_TIME` DATETIME(6) NULL DEFAULT NULL,
  `STATUS` VARCHAR(10) NULL DEFAULT NULL,
  `EXIT_CODE` VARCHAR(2500) NULL DEFAULT NULL,
  `EXIT_MESSAGE` VARCHAR(2500) NULL DEFAULT NULL,
  `LAST_UPDATED` DATETIME(6) NULL DEFAULT NULL,
  `JOB_CONFIGURATION_LOCATION` VARCHAR(2500) NULL DEFAULT NULL,
  INDEX `JOB_INST_EXEC_FK` (`JOB_INSTANCE_ID` ASC) VISIBLE);


CREATE TABLE IF NOT EXISTS `batch_job_execution_context` (
  `JOB_EXECUTION_ID` BIGINT NOT NULL,
  `SHORT_CONTEXT` VARCHAR(2500) NOT NULL,
  `SERIALIZED_CONTEXT` TEXT NULL DEFAULT NULL);



CREATE TABLE IF NOT EXISTS `batch_job_execution_params` (
  `JOB_EXECUTION_ID` BIGINT NOT NULL AUTO_INCREMENT,
  `TYPE_CD` VARCHAR(6) NOT NULL,
  `KEY_NAME` VARCHAR(100) NOT NULL,
  `STRING_VAL` VARCHAR(250) NULL DEFAULT NULL,
  `DATE_VAL` DATETIME(6) NULL DEFAULT NULL,
  `LONG_VAL` BIGINT NULL DEFAULT NULL,
  `DOUBLE_VAL` DOUBLE NULL DEFAULT NULL,
  `IDENTIFYING` CHAR(1) NOT NULL,
  INDEX `JOB_EXEC_PARAMS_FK` (`JOB_EXECUTION_ID` ASC) VISIBLE );


CREATE TABLE IF NOT EXISTS `batch_job_execution_seq` (
  `ID` BIGINT NOT NULL,
  `UNIQUE_KEY` CHAR(1) NOT NULL,
  UNIQUE INDEX `UNIQUE_KEY_UN` (`UNIQUE_KEY` ASC));

CREATE TABLE IF NOT EXISTS `batch_step_execution` (
  `STEP_EXECUTION_ID` BIGINT NOT NULL,
  `VERSION` BIGINT NOT NULL,
  `STEP_NAME` VARCHAR(100) NOT NULL,
  `JOB_EXECUTION_ID` BIGINT NOT NULL,
  `START_TIME` DATETIME(6) NOT NULL,
  `END_TIME` DATETIME(6) NULL DEFAULT NULL,
  `STATUS` VARCHAR(10) NULL DEFAULT NULL,
  `COMMIT_COUNT` BIGINT NULL DEFAULT NULL,
  `READ_COUNT` BIGINT NULL DEFAULT NULL,
  `FILTER_COUNT` BIGINT NULL DEFAULT NULL,
  `WRITE_COUNT` BIGINT NULL DEFAULT NULL,
  `READ_SKIP_COUNT` BIGINT NULL DEFAULT NULL,
  `WRITE_SKIP_COUNT` BIGINT NULL DEFAULT NULL,
  `PROCESS_SKIP_COUNT` BIGINT NULL DEFAULT NULL,
  `ROLLBACK_COUNT` BIGINT NULL DEFAULT NULL,
  `EXIT_CODE` VARCHAR(2500) NULL DEFAULT NULL,
  `EXIT_MESSAGE` VARCHAR(2500) NULL DEFAULT NULL,
  `LAST_UPDATED` DATETIME(6) NULL DEFAULT NULL,
  INDEX `JOB_EXEC_STEP_FK` (`JOB_EXECUTION_ID` ASC) VISIBLE );




CREATE TABLE IF NOT EXISTS `batch_step_execution_context` (
  `STEP_EXECUTION_ID` BIGINT NOT NULL,
  `SHORT_CONTEXT` VARCHAR(2500) NOT NULL,
  `SERIALIZED_CONTEXT` TEXT NULL DEFAULT NULL );

CREATE TABLE IF NOT EXISTS `batch_step_execution_seq` (
  `ID` BIGINT NOT NULL,
  `UNIQUE_KEY` CHAR(1) NOT NULL,
  UNIQUE INDEX `UNIQUE_KEY_UN` (`UNIQUE_KEY` ASC));
  
INSERT INTO BATCH_STEP_EXECUTION_SEQ (ID, UNIQUE_KEY) select * from (select 10000 as ID, '0' as UNIQUE_KEY) as tmp where not exists(select * from BATCH_STEP_EXECUTION_SEQ);

INSERT INTO BATCH_JOB_SEQ (ID, UNIQUE_KEY) select * from (select 10000 as ID, '0' as UNIQUE_KEY) as tmp where not exists(select * from BATCH_JOB_SEQ);

Hope this help!!