[Spring Batch] 조져보자 meta table

Spring Batch 에서는 meta table이라고 해서 Batch 의 작업을 도와주는 테이블이 있는데...

 

 

 

 

여하튼 서론은 막론하고 MySQL 8.0 버전을 올리면서 이후부터는 System Variable 로 sql_require_primary_key 가 true 가 걸려있게 되는 경우 application.yml 에서 설정을 해도 작동을 하지 않는다 이말이다

 

전통적으로는 spring.batch.jdbc.

initialize-schema: always

 

이렇게 때려박아 주고 있는데, 여하튼 이거랑 별도로 환경변수에서 저렇게 잡아놓으면 데이터베이스가 생성하고 싶어도 생성할 수가 없다.

 

 

그렇다면 Spring Batch 공식문서로 가보자

---

The BATCH_JOB_EXECUTION_PARAMS Table

The BATCH_JOB_EXECUTION_PARAMS table holds all information relevant to the JobParameters object. It contains 0 or more key/value pairs passed to a Job and serves as a record of the parameters with which a job was run. For each parameter that contributes to the generation of a job’s identity, the IDENTIFYING flag is set to true. Note that the table has been denormalized. Rather than creating a separate table for each type, there is one table with a column indicating the type, as the following listing shows:

CREATE TABLE BATCH_JOB_EXECUTION_PARAMS  (
	JOB_EXECUTION_ID BIGINT NOT NULL ,
	PARAMETER_NAME VARCHAR(100) NOT NULL ,
	PARAMETER_TYPE VARCHAR(100) NOT NULL ,
	PARAMETER_VALUE VARCHAR(2500) ,
	IDENTIFYING CHAR(1) NOT NULL ,
	constraint JOB_EXEC_PARAMS_FK foreign key (JOB_EXECUTION_ID)
	references BATCH_JOB_EXECUTION(JOB_EXECUTION_ID)
);

 

The following list describes each column:

• JOB_EXECUTION_ID: Foreign key from the BATCH_JOB_EXECUTION table that indicates the job execution to which the parameter entry belongs. Note that multiple rows (that is, key/value pairs) may exist for each execution.
• PARAMETER_NAME: The parameter name.
• PARAMETER_TYPE: The fully qualified name of the type of the parameter.
• PARAMETER_VALUE: Parameter value
• IDENTIFYING: Flag indicating whether the parameter contributed to the identity of the related JobInstance.

Note that there is no primary key for this table. This is because the framework has no use for one and, thus, does not require it. If need be, you can add a primary key with a database generated key without causing any issues to the framework itself.

---

 

간단하게 요약하자면 원래 이 테이블에는 Primary Key 따위는 없는 것인데, 네놈이 꼴린다면 generated Key 를 생성해서 그 키를 Primary Key로 쓸 수 있다 이말씀이다.

 

참고로 MySQL에서 SEQUENCE 를 생성해서 사용하려고 해도 PRIMARY KEY 때문에 작동하지 않을 것이므로 

 

여하튼 데이터베이스를 알게된 이래로 generated key 라는 놈은 처음 들어봣는데

 

 

내용을 잘 살펴보아하자니 프로세스가 진행될 때마다 증가하면서 자동으로 생성되는 놈이라하니 꼭 AI 를 박아놓은 친구처럼 생겼다.

 

그래서 만들어보았다 generated key

CREATE TABLE BATCH_JOB_EXECUTION_PARAMS (
    PARAM_ID BIGINT AUTO_INCREMENT PRIMARY KEY,
    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,
    CONSTRAINT JOB_EXEC_PARAMS_FK FOREIGN KEY (JOB_EXECUTION_ID)
        REFERENCES BATCH_JOB_EXECUTION(JOB_EXECUTION_ID)
) 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_STEP_EXECUTION_SEQ (
                                        SEQ_ID  BIGINT  AUTO_INCREMENT  PRIMARY KEY ,
                                          ID BIGINT NOT NULL,
                                          UNIQUE_KEY CHAR(1) NOT NULL,
                                          constraint UNIQUE_KEY_UN unique (UNIQUE_KEY)
) ENGINE=InnoDB;


CREATE TABLE BATCH_JOB_EXECUTION_SEQ (
                                         SEQ_ID  BIGINT  AUTO_INCREMENT  PRIMARY KEY ,
                                         ID BIGINT NOT NULL,
                                         UNIQUE_KEY CHAR(1) NOT NULL,
                                         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 (
                               SEQ_ID  BIGINT  AUTO_INCREMENT  PRIMARY KEY ,
                               ID BIGINT NOT NULL,
                               UNIQUE_KEY CHAR(1) NOT NULL,
                               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);

 

이러면 쌈뽕하게 작동한다