[Spring Batch] multiple nodes with MySQL 8.0.33

Improve replication friendliness of the MySQL schema

 

Status quo

The table BATCH_JOB_EXECUTION_PARAMS currently has no primary key.

For a single node MySQL deployment, this does not cause any issue. For a setup of multiple nodes with replication, this is however not ideal as it causes performance problems with row-based replication and leads to ambiguity when calculating check sums over tables to validate the integrity of the replication.

To prevent such issues, MySQL 8 offers the system variable sql_require_primary_key. If set to ON, it is currently not possible to deploy the Spring Batch schema.

Suggestion

As job parameters are handled by Spring Batch internally as maps, it already works with the assumption that job parameter keys are unique for each job execution. This allows to declare a primary key on BATCH_JOB_EXECUTION_PARAMS consisting of JOB_EXECUTION_ID and KEY_NAME.

It would be nice if this (or any other) primary key were defined on BATCH_JOB_EXECUTION_PARAMS in the MySQL schema.

The tables BATCH_JOB_SEQ, BATCH_JOB_EXECUTION_SEQ, BATCH_STEP_EXECUTION_SEQ also do not have a primary key. As they contain only a single row, respectively, this is not a problem in practice. But if the column UNIQUE_KEY were to be declared as primary key, this would allow a seamless deployment to a MySQL instance with sql_require_primary_key=ON.

 

 

---

BATCH_JOB_EXECUTION_PARAMS

CREATE TABLE BATCH_JOB_EXECUTION_PARAMS  (
	JOB_EXECUTION_ID BIGINT NOT NULL ,
	TYPE_CD VARCHAR(6) NOT NULL ,
	KEY_NAME VARCHAR(100) NOT NULL ,
	STRING_VAL VARCHAR(250) ,
	DATE_VAL DATETIME(6) DEFAULT NULL ,
	LONG_VAL BIGINT ,
	DOUBLE_VAL DOUBLE PRECISION ,
	IDENTIFYING CHAR(1) NOT NULL ,
    PRIMARY KEY (JOB_EXECUTION_ID, KEY_NAME)
	constraint JOB_EXEC_PARAMS_FK foreign key (JOB_EXECUTION_ID)
	references BATCH_JOB_EXECUTION(JOB_EXECUTION_ID)
) ENGINE=InnoDB;

 

Other meta tables

CREATE TABLE BATCH_STEP_EXECUTION_SEQ (
	ID BIGINT NOT NULL,
	UNIQUE_KEY CHAR(1) NOT NULL,
    PRIMARY KEY(UNIQUE_KEY),
	constraint UNIQUE_KEY_UN unique (UNIQUE_KEY)
) ENGINE=InnoDB;

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

CREATE TABLE BATCH_JOB_EXECUTION_SEQ (
	ID BIGINT NOT NULL,
	UNIQUE_KEY CHAR(1) NOT NULL,
    PRIMARY KEY(UNIQUE_KEY),
	constraint UNIQUE_KEY_UN unique (UNIQUE_KEY)
) ENGINE=InnoDB;

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

CREATE TABLE BATCH_JOB_SEQ (
	ID BIGINT NOT NULL,
	UNIQUE_KEY CHAR(1) NOT NULL,
    PRIMARY KEY(UNIQUE_KEY),
	constraint UNIQUE_KEY_UN unique (UNIQUE_KEY)
) ENGINE=InnoDB;

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

 

[important]

Do not create generated key in PARAMS table like below;

https://docs.spring.io/spring-batch/reference/schema-appendix.html

Meta-Data Schema :: Spring Batch