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:
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:
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'
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!
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);