Sequences not working when switching to MySQL for job repository database

[stevedperkins]

I have a Spring Batch deployment in production, which was originally developed using an H2 data source for the job repository. Everything is working fine with this initial effort, but we would like to replace H2 with a more traditional database as our use of Spring Batch expands. For better or for worse (it wasn't completely my decision), I am moving to MySQL instead.

I have switched the data source for the job repository from H2 to MySQL, and changed the <jdbc:initialize-database> element to use "classpath:/org/springframework/batch/core/schema-mysql.sql". When I run a batch job for the very first time, Spring Batch creates the repository tables and runs my job without issue.

However, subsequent executions fail... even though I am properly using a job incrementer, passing the "-next" parameter, and have seen it work fine with H2. When I set my logging to "debug" level, I see the following exception being through on subsequent executions:

org.springframework.dao.DuplicateKeyException: PreparedStatementCallback; SQL [INSERT into BATCH_JOB_INSTANCE(JOB_INSTANCE_ID, JOB_NAME, JOB_KEY, VERSION) values (?, ?, ?, ?)]; 
Duplicate entry '1' for key 'PRIMARY'; 
nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry '1' for key 'PRIMARY'

I've looked at the "schema-*.sql" scripts that Spring Batch uses to create its job repository tables. Other database types (e.g. H2, Oracle, Postgres, etc) create three sequences... BATCH_STEP_EXECUTION_SEQ, BATCH_JOB_EXECUTION_SEQ, and BATCH_JOB_SEQ. I am not a MySQL expert, but it looks like MySQL lacks support for sequences... because "schema-mysql.sql" includes these lines as an apparent workaround:

CREATE TABLE BATCH_STEP_EXECUTION_SEQ (ID BIGINT NOT NULL) ENGINE=MYISAM;
INSERT INTO BATCH_STEP_EXECUTION_SEQ values(0);
CREATE TABLE BATCH_JOB_EXECUTION_SEQ (ID BIGINT NOT NULL) ENGINE=MYISAM;
INSERT INTO BATCH_JOB_EXECUTION_SEQ values(0);
CREATE TABLE BATCH_JOB_SEQ (ID BIGINT NOT NULL) ENGINE=MYISAM;
INSERT INTO BATCH_JOB_SEQ values(0);

It seems obvious that MySQL's handling of sequences and autoincrement values differs from other databases, and so presumably Spring Batch has to do something different to increment ID's in its job repository tables. Are there some other MySQL-specific steps I need to take, of which I might not be aware? Thanks in advance!

[mminella]

There should be no additional setup for MySql. That being said, are you running the initialization every time the job runs?

[stevedperkins]

There should be no additional setup for MySql. That being said, are you running the initialization every time the job runs?

Thank you, that was it! With H2, there is apparently no harmful effect when leaving the <jdbc:initialize-database> element to be invoked every time (so long as it includes the ignore-failures="ALL" attribute). I had grown so used to this, I had completely forgotten about it.

However, MySQL apparently uses a workaround for sequences, and it DOES create a problem to execute that SQL repeatedly. So I've removed the <jdbc:initialize-database> element altogether, manually executed it once on an empty database, and my job now runs multiple invocations just fine.