[Spring Batch 에러] Caused by:org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [SELECT JOB_INSTANCE_ID, JOB_NAMEFROM BATCH_JOB_INSTANCEWHERE JOB_NAME = ? and JOB_KEY = ?]

Spring Batch 시작 후 어플리케이션 기동 하니 바로 에러 발생했습니다.

 

 

java.lang.IllegalStateException: Failed to execute CommandLineRunner
at org.springframework.boot.SpringApplication.callRunner(SpringApplication.java:777) ~[spring-boot-3.1.4.jar:3.1.4]
at org.springframework.boot.SpringApplication.callRunners(SpringApplication.java:758) ~[spring-boot-3.1.4.jar:3.1.4]
at org.springframework.boot.SpringApplication.run(SpringApplication.java:322) ~[spring-boot-3.1.4.jar:3.1.4]
at org.springframework.boot.SpringApplication.run(SpringApplication.java:1309) ~[spring-boot-3.1.4.jar:3.1.4]
at org.springframework.boot.SpringApplication.run(SpringApplication.java:1298) ~[spring-boot-3.1.4.jar:3.1.4]
at co m.rsn.dic.RsnDictionaryBatch.RsnDictionaryBatchApplication.main(RsnDictionaryBatchApplication.java:12) ~[main/:na]
Caused by: org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [SELECT JOB_INSTANCE_ID, JOB_NAME
FROM BATCH_JOB_INSTANCE
WHERE JOB_NAME = ?
 and JOB_KEY = ?]

 

 

BATCH_JOB_INSTANCE TABLE 이 뭔지 찾아봤습니다.

 

아래와 같이 구성되어있는 한 조각의 테이블이었습니다.

이 정보는 Spring Batch의 데이터베이스 테이블와 관련된 DDL(Domain Definition Language) 및 사용 전략에 대한 개요를 제공합니다. 

데이터베이스 스키마는 Spring Batch 작업의 상태 및 메타데이터를 저장하고, 작업이 실행되는 동안 어떤 단계와 상태에 있는지를 트래킹하기 위해 사용됩니다. 

이를 통해, 작업이 중단되었을 때 이어서 진행할 수 있게 해주고, 어떤 작업이 어떻게 실행되었는지에 대한 이력(audit trail)을 제공합니다.

여기에 제공된 DDL 예제와 테이블 정보는 데이터베이스의 생성과 관리를 도와주며, 작업이 어떻게 동작하고 어떻게 관리되는지에 대한 상세한 컨텍스트를 제공합니다.

 

 

 

 

Spring Batch의 DDL 스크립트와 관련된 데이터베이스 테이블은, 작업의 실행 상태, 파라미터, 결과 및 컨텍스트 데이터를 저장하고 관리하는 데 필수적입니다. 

이는 작업의 안정적인 실행과 실패 시 재시작 기능, 그리고 작업 실행에 대한 트래킹 및 보고를 가능하게 합니다. 그

러나 이러한 테이블과 데이터는 적절한 관리 및 최적화 전략 없이는 시간이 지남에 따라 성능 문제를 초래할 수 있습니다. 

따라서, 효율적인 데이터 관리 및 성능 최적화 전략은 Spring Batch 솔루션의 핵심 부분이며, 비즈니스 요구와 데이터 전략에 맞게 적절히 조절되어야 합니다.

 

하지만 데이터베이스에 해당 테이블들이 초기 생성 되어있지 않다.

 

spring.batch.jdbc.initialize-schema 설정은 개발 과정에서 데이터베이스 스키마를 자동으로 관리하고 싶을 때 유용하게 사용할 수 있습니다. 개발 단계에서는 데이터베이스를 자주 재생성할 필요가 있을 수 있으며, 이러한 경우 always 옵션을 사용하면 개발자가 수동으로 데이터베이스를 초기화하거나 관리할 필요가 없어집니다. 그러나 프로덕션 환경에서는 데이터를 보존해야 하므로, 스키마 초기화 옵션을 신중하게 선택해야 합니다.

 

아래와 같이 spring.batch.jdbc.initialize-schema 설정을 추가해줍니다.

 

 

spring.batch.jdbc.initialize-schema=always

 

 

근데 또 아래와 같은 에러가 발생했습니다.

 

 

java.lang.IllegalStateException: Failed to execute CommandLineRunner
at org.springframework.boot.SpringApplication.callRunner(SpringApplication.java:777) ~[spring-boot-3.1.4.jar:3.1.4]
at org.springframework.boot.SpringApplication.callRunners(SpringApplication.java:758) ~[spring-boot-3.1.4.jar:3.1.4]
at org.springframework.boot.SpringApplication.run(SpringApplication.java:322) ~[spring-boot-3.1.4.jar:3.1.4]
at org.springframework.boot.SpringApplication.run(SpringApplication.java:1309) ~[spring-boot-3.1.4.jar:3.1.4]
at org.springframework.boot.SpringApplication.run(SpringApplication.java:1298) ~[spring-boot-3.1.4.jar:3.1.4]
at co m.rsn.dic.RsnDictionaryBatch.RsnDictionaryBatchApplication.main(RsnDictionaryBatchApplication.java:12) ~[main/:na]
Caused by: org.springframework.dao.DataAccessResourceFailureException: Could not increment ID for BATCH_JOB_SEQ sequence table
at org.springframework.jdbc.support.incrementer.MySQLMaxValueIncrementer.getNextKey(MySQLMaxValueIncrementer.java:153) ~[spring-jdbc-6.0.12.jar:6.0.12]
at org.springframework.jdbc.support.incrementer.AbstractDataFieldMaxValueIncrementer.nextLongValue(AbstractDataFieldMaxValueIncrementer.java:128) ~[spring-jdbc-6.0.12.jar:6.0.12]
at org.springframework.batch.core.repository.dao.JdbcJobInstanceDao.createJobInstance(JdbcJobInstanceDao.java:150) ~[spring-batch-core-5.0.3.jar:5.0.3]
at org.springframework.batch.core.repository.support.SimpleJobRepository.createJobExecution(SimpleJobRepository.java:169) ~[spring-batch-core-5.0.3.jar:5.0.3]
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:na]
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77) ~[na:na]
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:na]
at java.base/java.lang.reflect.Method.invoke(Method.java:568) ~[na:na]
at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:343) ~[spring-aop-6.0.12.jar:6.0.12]
at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:196) ~[spring-aop-6.0.12.jar:6.0.12]
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163) ~[spring-aop-6.0.12.jar:6.0.12]
at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:123) ~[spring-tx-6.0.12.jar:6.0.12]
at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:391) ~[spring-tx-6.0.12.jar:6.0.12]
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:119) ~[spring-tx-6.0.12.jar:6.0.12]
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184) ~[spring-aop-6.0.12.jar:6.0.12]
at org.springframework.batch.core.repository.support.AbstractJobRepositoryFactoryBean.lambda$getObject$0(AbstractJobRepositoryFactoryBean.java:204) ~[spring-batch-core-5.0.3.jar:5.0.3]
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184) ~[spring-aop-6.0.12.jar:6.0.12]
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:244) ~[spring-aop-6.0.12.jar:6.0.12]
at jdk.proxy2/jdk.proxy2.$Proxy67.createJobExecution(Unknown Source) ~[na:na]
at org.springframework.batch.core.launch.support.SimpleJobLauncher.run(SimpleJobLauncher.java:145) ~[spring-batch-core-5.0.3.jar:5.0.3]
at org.springframework.batch.core.launch.support.TaskExecutorJobLauncher.run(TaskExecutorJobLauncher.java:59) ~[spring-batch-core-5.0.3.jar:5.0.3]
at co m.rsn.dic.RsnDictionaryBatch.datamailsend.DataMailSendConfig$1.run(DataMailSendConfig.java:41) ~[main/:na]
at org.springframework.boot.SpringApplication.callRunner(SpringApplication.java:774) ~[spring-boot-3.1.4.jar:3.1.4]
... 5 common frames omitted
Caused by: java.sql.SQLSyntaxErrorException: Table 'SPAM_ANALYSIS.BATCH_JOB_SEQ' doesn't exist
at co m.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120) ~[mysql-connector-java-8.0.16.jar:8.0.16]
at co m.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97) ~[mysql-connector-java-8.0.16.jar:8.0.16]
at co m.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122) ~[mysql-connector-java-8.0.16.jar:8.0.16]
at co m.mysql.cj.jdbc.StatementImpl.executeUpdateInternal(StatementImpl.java:1355) ~[mysql-connector-java-8.0.16.jar:8.0.16]
at co m.mysql.cj.jdbc.StatementImpl.executeLargeUpdate(StatementImpl.java:2128) ~[mysql-connector-java-8.0.16.jar:8.0.16]
at co m.mysql.cj.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1264) ~[mysql-connector-java-8.0.16.jar:8.0.16]
at co m.zaxxer.hikari.pool.ProxyStatement.executeUpdate(ProxyStatement.java:119) ~[HikariCP-5.0.1.jar:na]
at co m.zaxxer.hikari.pool.HikariProxyStatement.executeUpdate(HikariProxyStatement.java) ~[HikariCP-5.0.1.jar:na]
at org.springframework.jdbc.support.incrementer.MySQLMaxValueIncrementer.getNextKey(MySQLMaxValueIncrementer.java:148) ~[spring-jdbc-6.0.12.jar:6.0.12]
... 27 common frames omitted

 

자동으로 테이블 생성해주는 줄 알았습니다. 멍청한 생각일 수도 있지만 !! ?  ( 다른 사람들은 자동 생성 한다고 하드라구요 ;; )

 

 

 

근데 공식 문서 보면 CREATE 문이 다 나와있지만 오라클 DB 기반의 명령문입니다.

그래서 아래와 같이 MYSQL 명령문으로 변경 후 생성해주었습니다.

 

drop table BATCH_JOB_INSTANCE;
drop table BATCH_JOB_EXECUTION_PARAMS; 
drop table BATCH_JOB_EXECUTION ; 
drop table BATCH_STEP_EXECUTION; 
drop table BATCH_JOB_EXECUTION_CONTEXT; 
drop table BATCH_STEP_EXECUTION_CONTEXT; 
drop table BATCH_STEP_EXECUTION_SEQ; 
drop table BATCH_JOB_EXECUTION_SEQ; 
drop table BATCH_JOB_SEQ;  

CREATE TABLE BATCH_JOB_INSTANCE  (
  JOB_INSTANCE_ID BIGINT  PRIMARY KEY ,
  VERSION BIGINT,
  JOB_NAME VARCHAR(100) NOT NULL ,
  JOB_KEY VARCHAR(32) NOT NULL
);

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)
);

CREATE TABLE BATCH_JOB_EXECUTION  (
  JOB_EXECUTION_ID BIGINT  PRIMARY KEY ,
  VERSION BIGINT,
  JOB_INSTANCE_ID BIGINT NOT NULL,
  CREATE_TIME TIMESTAMP NOT NULL,
  START_TIME TIMESTAMP DEFAULT NULL,
  END_TIME TIMESTAMP DEFAULT NULL,
  STATUS VARCHAR(10),
  EXIT_CODE VARCHAR(20),
  EXIT_MESSAGE VARCHAR(2500),
  LAST_UPDATED TIMESTAMP,
  constraint JOB_INSTANCE_EXECUTION_FK foreign key (JOB_INSTANCE_ID)
  references BATCH_JOB_INSTANCE(JOB_INSTANCE_ID)
) ;

CREATE TABLE BATCH_STEP_EXECUTION  (
  STEP_EXECUTION_ID BIGINT NOT NULL PRIMARY KEY ,
  VERSION BIGINT NOT NULL,
  STEP_NAME VARCHAR(100) NOT NULL,
  JOB_EXECUTION_ID BIGINT NOT NULL,
  CREATE_TIME TIMESTAMP NOT NULL,
  START_TIME TIMESTAMP DEFAULT NULL ,
  END_TIME TIMESTAMP DEFAULT NULL,
  STATUS VARCHAR(10),
  COMMIT_COUNT BIGINT ,
  READ_COUNT BIGINT ,
  FILTER_COUNT BIGINT ,
  WRITE_COUNT BIGINT ,
  READ_SKIP_COUNT BIGINT ,
  WRITE_SKIP_COUNT BIGINT ,
  PROCESS_SKIP_COUNT BIGINT ,
  ROLLBACK_COUNT BIGINT ,
  EXIT_CODE VARCHAR(20) ,
  EXIT_MESSAGE VARCHAR(2500) ,
  LAST_UPDATED TIMESTAMP,
  constraint JOB_EXECUTION_STEP_FK foreign key (JOB_EXECUTION_ID)
  references BATCH_JOB_EXECUTION(JOB_EXECUTION_ID)
) ;

CREATE TABLE BATCH_JOB_EXECUTION_CONTEXT  (
  JOB_EXECUTION_ID BIGINT PRIMARY KEY,
  SHORT_CONTEXT VARCHAR(2500) NOT NULL,
  SERIALIZED_CONTEXT LONGTEXT,
  constraint JOB_EXEC_CTX_FK foreign key (JOB_EXECUTION_ID)
  references BATCH_JOB_EXECUTION(JOB_EXECUTION_ID)
) ;

CREATE TABLE BATCH_STEP_EXECUTION_CONTEXT  (
  STEP_EXECUTION_ID BIGINT PRIMARY KEY,
  SHORT_CONTEXT VARCHAR(2500) NOT NULL,
  SERIALIZED_CONTEXT LONGTEXT,
  constraint STEP_EXEC_CTX_FK foreign key (STEP_EXECUTION_ID)
  references BATCH_STEP_EXECUTION(STEP_EXECUTION_ID)
) ;

CREATE TABLE BATCH_STEP_EXECUTION_SEQ (
  ID BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY
) ENGINE=InnoDB;

CREATE TABLE BATCH_JOB_EXECUTION_SEQ (
  ID BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY
) ENGINE=InnoDB;

CREATE TABLE BATCH_JOB_SEQ (
  ID BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY
) ENGINE=InnoDB;


INSERT INTO BATCH_STEP_EXECUTION_SEQ values(0);
INSERT INTO BATCH_JOB_EXECUTION_SEQ values(0);
INSERT INTO BATCH_JOB_SEQ values(0);

 

 

 

그러고 시작하니 일단 에러는 해결되었는데 이게 맞나요 .

피드백 정말 대환영입니다. . 

 

 

참고 자료

 

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

Meta-Data Schema

https://leeheefull.tistory.com/66

Spring batch BadSqlGrammarException