[spring boot] JdbcSQLSyntaxErrorException: Syntax error in SQL statement ... expected "identifier" 해결 방안

안녕하세요 😉

유유자적한 개발자 유로띠 입니다 😀

👏👏👏👏

 

 

 

 

이번 포스팅에서는

JdbcSQLSyntaxErrorException: Syntax error in SQL statement ... expected "identifier"

에 대해 삽질기와 해결 방법에 대해 알아보겠습니다.

 

 

🎉 spring boot + jpa + h2 사용시 에러 발생

---

 

test code를 작업하기 위해 h2의 in-memory db를 이용하기로 하고 작업을 진행하였습니다.

 

그러던 중 jpa와 H2 DB를 사용하다 아래와 같은

JdbcSQLSyntaxErrorException: Syntax error in SQL statement ... expected "identifier" 

에러가 발생하였습니다. 🥲

 

 

 

이 error는 저를 2일동안 고통스럽게 했지요..

 

✅ Error message

Error executing DDL "create table organization (id bigint not null auto_increment, name varchar(255), primary key (id)) engine=InnoDB" via JDBC Statement
...
org.h2.jdbc.JdbcSQLSyntaxErrorException: Syntax error in SQL statement "CREATE TABLE ORGANIZATION (ID BIGINT NOT NULL AUTO_INCREMENT, NAME VARCHAR(255), PRIMARY KEY (ID)) ENGINE=[*]INNODB"; expected "identifier"; SQL statement:
create table organization (id bigint not null auto_increment, name varchar(255), primary key (id)) engine=InnoDB [42001-202]

 

 

우선 저의 프로젝트 환경 정보입니다.

 

 

 

✅ build.gradle

plugins {
	id 'org.springframework.boot' version '2.6.2'
	id 'java'
}

sourceCompatibility = '11'

dependencies {
  implementation 'org.springframework.boot:spring-boot-starter-data-jpa'
  testImplementation 'com.h2database:h2:2.0.202'
}

 

✅ application.yml

 

profile이 test인 경우 해당 h2가 실행하도록 active를 설정하였습니다.

spring:
  config:
    activate:
      on-profile: test
  h2:
    console:
      enabled: true
  jpa:
    show-sql: true
    properties:
      hibernate:
        dialect: org.hibernate.dialect.MySQL5InnoDBDialect

 

✅ domain(entity) - organization.java

 

기업정보를 위한 도메인입니다.
기본적인 id, name만 적용하였습니다.

@Getter
@Entity
public class Organization {

  @Id
  @GeneratedValue(strategy = GenerationType.IDENTITY)
  private Long id;

  @Column
  private String name;
}

 

 

봐도 뭐가 문제인지 모르겠습니다. 🤣
다음으로는 구글링을 통해 에러를 해결하기 위한 삽질의 여정입니다.🚀

 

 

 

에러를 해결하기 위한 다양한 방법 테스트 👀

---

✅ 예약어 ?

domain안에 order, group과 같은 예약어가 있으면 테이블이 생성할 수 없어 에러가 발생된다고 합니다.
그래서 아래와 같이 하이버네이트의 globally_quoted_identifiers를 활성화하였습니다.

spring:
 jpa:
    show-sql: true
    properties:
      hibernate:
        dialect: org.hibernate.dialect.MySQL5InnoDBDialect
        globally_quoted_identifiers: true

 

사실 제가 생성한 organization은 예약어도 아니고 작은 따옴표가 생겼지만 그래도 실패하였습니다. 🥲

 

Error executing DDL "create table `organization` (`id` bigint not null auto_increment, `name` varchar(255), primary key (`id`)) engine=InnoDB" via JDBC Statement
...
org.h2.jdbc.JdbcSQLSyntaxErrorException: Syntax error in SQL statement "CREATE TABLE `ORGANIZATION` (`ID` BIGINT NOT NULL AUTO_INCREMENT, `NAME` VARCHAR(255), PRIMARY KEY (`ID`)) ENGINE=[*]INNODB"; expected "identifier"; SQL statement:
create table `organization` (`id` bigint not null auto_increment, `name` varchar(255), primary key (`id`)) engine=InnoDB [42001-202]

 

✅ Dialect 변경 ?

JPA가 SQL을 작성하는데 DBMS 종류마다 SQL 작성법이 다르기 때문에 dialect(방언)를 설정합니다.
다양한 dialect로 적용해 보았지만 전부 실패... 🥲

 org.hibernate.dialect.MySQLMyISAMDialect
 org.hibernate.dialect.MySQL5Dialect
 org.hibernate.dialect.MySQL8Dialect

 

type만 변경되었을 뿐 여전히 error....

사실 type=MyISAM이 성공되더라도 사용하기에는 무리가 있습니다.

두 엔진(MyISAM, innoDB)의 가장 큰 차이는 트랜잭션 기능 유무입니다.

 

Error executing DDL "create table organization (id bigint not null auto_increment, name varchar(255), primary key (id)) type=MyISAM" via JDBC Statement

 

 

✅ DDL 옵션 ?

테이블을 생성하고 옵션에 따라 상세하게 처리할 수 있는 DDL 옵션을 적용하였습니다.

spring:
  jpa:
    show-sql: true
    properties:
      hibernate:
        dialect: org.hibernate.dialect.MySQL5InnoDBDialect
    generate-ddl: true
    hibernate:
      ddl-auto: create-drop

ℹ️ ddl-auto 옵션의 종류

🟡  none
: 사용하지 않음

🟡  validate
: entity와 테이블이 정상 반영되었는지 확인

🟡  update
: 변경 정보만 반영

🟡  create
: 기존 테이블 drop 후 생성

🟡  create-drop
: 기존 테이블 drop 후 생성 그리고 종료 시점에 drop

 

 

 

하지만 옵션을 설정하여도 에러가 발생되었습니다.

왜냐면 embedded database를 사용하면 기본적으로 create-drop을 사용합니다.

 

 

 

✅ 테스트코드의 어노테이션 ?

@DataJpaTest로 되어있는경우 안되는 케이스도 존재합니다.
하지만 저는 이것 또한 @SpringBootTest로 설정되어있습니다.

@ActiveProfiles("test")
@SpringBootTest(webEnvironment = SpringBootTest.WebEnvironment.RANDOM_PORT)
public class organizationControllerTest {
...
}

 

✅ spring boot 버전 차이 ?

 

검색 중에 spring boot version 2.5 부터 다음과 같은 이슈가 있습니다.

Hibernate and data.sql
By default, data.sql scripts are now run before Hibernate is initialized. This aligns the behavior of basic script-based initialization with that of Flyway and Liquibase. If you want to use data.sql to populate a schema created by Hibernate, set spring.jpa.defer-datasource-initialization to true. While mixing database initialization technologies is not recommended, this will also allow you to use a schema.sql script to build upon a Hibernate-created schema before it’s populated via data.sql.

 

Hibernate가 초기화 되기 전에 data.sql 스크립트가 실행되어

data.sql을 실행하려면 해당 옵션(defer-datasource-initialization)을 true로 설정해야된다고 합니다.

 

spring:
    jpa:
        defer-datasource-initialization: true

 

물론 저는 data.sql을 통해 table을 생성하는것이 아니기에 해당 이슈와는 무관하였습니다. 🤣

이렇게 삽질을 계속되었습니다.

 

🎉 드디어 해결

---

 

 

드디어 모든 삽질을 끝내고 문제를 해결하였습니다.

spring boot 2.1.10 이후로 약간의 추가적인 설정이 필요합니다.

 

✅ application.yml

 

spring boot 2.1.10 이후에는 직접적으로 jdbc-url을 선언하여 h2주소 뒤에 MODE=MYSQL를 붙어야 mysql 테이블 쿼리가 정상 작동됨을 확인하였습니다.

 

spring:
  config:
    activate:
      on-profile: test
  h2:
    console:
      enabled: true
    datasource:
    hikari:
      jdbc-url: jdbc:h2:mem:testdb;MODE=MYSQL
  jpa:
    show-sql: true
    database-platform: org.hibernate.dialect.MySQL57Dialect

 

해당 이슈 해결과 관련이 없지만

jpa.database-platform으로 변경하였습니다.

또한 MySQL5InnoDBDialect는 deprecated되었고

따라서 MySQL57Dialect을 사용하였습니다.

MySQL8Dialect이 있지만 아직 공식적으로 사용한다는 문서를 찾지 못했습니다.

 

 

 

 

저처럼 고생할 사람이 없길 바라며...

 

참고
https://docs.spring.io/spring-boot/docs/2.6.1/reference/html/howto.html#howto.data-access.jpa-properties

“How-to” Guides

https://github.com/jojoldu/freelec-springboot2-webservice/issues/67

P.100 properties 추가 시 작동 오류 · Issue #67 · jojoldu/freelec-springboot2-webservice