스프링] data.sql 에러

개발 환경

OS : mac Ventura 13.5

스프링부트 : 2.7.8

JAVA : 11

H2 Database :  2.1.214

Intellij IDEA : 2022.3.2 (Ultimate Ed)

에러

Error starting ApplicationContext. To display the conditions report re-run your application with 'debug' enabled.
2023-08-09 17:29:34.711 ERROR 29606 --- [           main] o.s.boot.SpringApplication               : Application run failed

org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'dataSourceScriptDatabaseInitializer' defined in class path resource [org/springframework/boot/autoconfigure/sql/init/DataSourceInitializationConfiguration.class]: Invocation of init method failed; nested exception is org.springframework.jdbc.datasource.init.ScriptStatementFailedException: Failed to execute SQL script statement #1 of URL [file:/Users/june/Repository/juneHouse/out/production/resources/data.sql]: INSERT INTO `user` (name, email, password, createAt) VALUES ('전광렬', 'abc@naver.com', '1234', '2023-08-09 17:15:00'); nested exception is org.h2.jdbc.JdbcSQLSyntaxErrorException: Table "USER" not found; SQL statement:
INSERT INTO `user` (name, email, password, createAt) VALUES ('전광렬', 'abc@naver.com', '1234', '2023-08-09 17:15:00') [42102-214]
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1804) ~[spring-beans-5.3.25.jar:5.3.25]
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:620) ~[spring-beans-5.3.25.jar:5.3.25]
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:542) ~[spring-beans-5.3.25.jar:5.3.25]
at org.springframework.beans.factory.support.AbstractBeanFactory.lambda$doGetBean$0(AbstractBeanFactory.java:335) ~[spring-beans-5.3.25.jar:5.3.25]
at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:234) ~[spring-beans-5.3.25.jar:5.3.25]
at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:333) ~[spring-beans-5.3.25.jar:5.3.25]
at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:208) ~[spring-beans-5.3.25.jar:5.3.25]
at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:322) ~[spring-beans-5.3.25.jar:5.3.25]
at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:208) ~[spring-beans-5.3.25.jar:5.3.25]
at org.springframework.beans.factory.support.DefaultListableBeanFactory.preInstantiateSingletons(DefaultListableBeanFactory.java:955) ~[spring-beans-5.3.25.jar:5.3.25]
at org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:918) ~[spring-context-5.3.25.jar:5.3.25]
at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:583) ~[spring-context-5.3.25.jar:5.3.25]
at org.springframework.boot.web.servlet.context.ServletWebServerApplicationContext.refresh(ServletWebServerApplicationContext.java:147) ~[spring-boot-2.7.8.jar:2.7.8]
at org.springframework.boot.SpringApplication.refresh(SpringApplication.java:731) ~[spring-boot-2.7.8.jar:2.7.8]
at org.springframework.boot.SpringApplication.refreshContext(SpringApplication.java:408) ~[spring-boot-2.7.8.jar:2.7.8]
at org.springframework.boot.SpringApplication.run(SpringApplication.java:307) ~[spring-boot-2.7.8.jar:2.7.8]
at org.springframework.boot.SpringApplication.run(SpringApplication.java:1303) ~[spring-boot-2.7.8.jar:2.7.8]
at org.springframework.boot.SpringApplication.run(SpringApplication.java:1292) ~[spring-boot-2.7.8.jar:2.7.8]
at co m.junehouse.JuneHouseApplication.main(JuneHouseApplication.java:10) ~[classes/:na]
Caused by: org.springframework.jdbc.datasource.init.ScriptStatementFailedException: Failed to execute SQL script statement #1 of URL [file:/Users/june/Repository/juneHouse/out/production/resources/data.sql]: INSERT INTO `user` (name, email, password, createAt) VALUES ('전광렬', 'abc@naver.com', '1234', '2023-08-09 17:15:00'); nested exception is org.h2.jdbc.JdbcSQLSyntaxErrorException: Table "USER" not found; SQL statement:
INSERT INTO `user` (name, email, password, createAt) VALUES ('전광렬', 'abc@naver.com', '1234', '2023-08-09 17:15:00') [42102-214]
at org.springframework.jdbc.datasource.init.ScriptUtils.executeSqlScript(ScriptUtils.java:282) ~[spring-jdbc-5.3.25.jar:5.3.25]
at org.springframework.jdbc.datasource.init.ResourceDatabasePopulator.populate(ResourceDatabasePopulator.java:254) ~[spring-jdbc-5.3.25.jar:5.3.25]
at org.springframework.jdbc.datasource.init.DatabasePopulatorUtils.execute(DatabasePopulatorUtils.java:54) ~[spring-jdbc-5.3.25.jar:5.3.25]
at org.springframework.boot.jdbc.init.DataSourceScriptDatabaseInitializer.runScripts(DataSourceScriptDatabaseInitializer.java:90) ~[spring-boot-2.7.8.jar:2.7.8]
at org.springframework.boot.sql.init.AbstractScriptDatabaseInitializer.runScripts(AbstractScriptDatabaseInitializer.java:145) ~[spring-boot-2.7.8.jar:2.7.8]
at org.springframework.boot.sql.init.AbstractScriptDatabaseInitializer.applyScripts(AbstractScriptDatabaseInitializer.java:107) ~[spring-boot-2.7.8.jar:2.7.8]
at org.springframework.boot.sql.init.AbstractScriptDatabaseInitializer.applyDataScripts(AbstractScriptDatabaseInitializer.java:101) ~[spring-boot-2.7.8.jar:2.7.8]
at org.springframework.boot.sql.init.AbstractScriptDatabaseInitializer.initializeDatabase(AbstractScriptDatabaseInitializer.java:76) ~[spring-boot-2.7.8.jar:2.7.8]
at org.springframework.boot.sql.init.AbstractScriptDatabaseInitializer.afterPropertiesSet(AbstractScriptDatabaseInitializer.java:65) ~[spring-boot-2.7.8.jar:2.7.8]
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1863) ~[spring-beans-5.3.25.jar:5.3.25]
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1800) ~[spring-beans-5.3.25.jar:5.3.25]
... 18 common frames omitted
Caused by: org.h2.jdbc.JdbcSQLSyntaxErrorException: Table "USER" not found; SQL statement:
INSERT INTO `user` (name, email, password, createAt) VALUES ('전광렬', 'abc@naver.com', '1234', '2023-08-09 17:15:00') [42102-214]
at org.h2.message.DbException.getJdbcSQLException(DbException.java:502) ~[h2-2.1.214.jar:2.1.214]
at org.h2.message.DbException.getJdbcSQLException(DbException.java:477) ~[h2-2.1.214.jar:2.1.214]
at org.h2.message.DbException.get(DbException.java:223) ~[h2-2.1.214.jar:2.1.214]
at org.h2.message.DbException.get(DbException.java:199) ~[h2-2.1.214.jar:2.1.214]
at org.h2.command.Parser.getTableOrViewNotFoundDbException(Parser.java:8398) ~[h2-2.1.214.jar:2.1.214]
at org.h2.command.Parser.getTableOrViewNotFoundDbException(Parser.java:8369) ~[h2-2.1.214.jar:2.1.214]
at org.h2.command.Parser.readTableOrView(Parser.java:8358) ~[h2-2.1.214.jar:2.1.214]
at org.h2.command.Parser.readTableOrView(Parser.java:8328) ~[h2-2.1.214.jar:2.1.214]
at org.h2.command.Parser.parseInsert(Parser.java:1632) ~[h2-2.1.214.jar:2.1.214]
at org.h2.command.Parser.parsePrepared(Parser.java:814) ~[h2-2.1.214.jar:2.1.214]
at org.h2.command.Parser.parse(Parser.java:689) ~[h2-2.1.214.jar:2.1.214]
at org.h2.command.Parser.parse(Parser.java:661) ~[h2-2.1.214.jar:2.1.214]
at org.h2.command.Parser.prepareCommand(Parser.java:569) ~[h2-2.1.214.jar:2.1.214]
at org.h2.engine.SessionLocal.prepareLocal(SessionLocal.java:631) ~[h2-2.1.214.jar:2.1.214]
at org.h2.engine.SessionLocal.prepareCommand(SessionLocal.java:554) ~[h2-2.1.214.jar:2.1.214]
at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:1116) ~[h2-2.1.214.jar:2.1.214]
at org.h2.jdbc.JdbcStatement.executeInternal(JdbcStatement.java:237) ~[h2-2.1.214.jar:2.1.214]
at org.h2.jdbc.JdbcStatement.execute(JdbcStatement.java:223) ~[h2-2.1.214.jar:2.1.214]
at co m.zaxxer.hikari.pool.ProxyStatement.execute(ProxyStatement.java:94) ~[HikariCP-4.0.3.jar:na]
at co m.zaxxer.hikari.pool.HikariProxyStatement.execute(HikariProxyStatement.java) ~[HikariCP-4.0.3.jar:na]
at org.springframework.jdbc.datasource.init.ScriptUtils.executeSqlScript(ScriptUtils.java:261) ~[spring-jdbc-5.3.25.jar:5.3.25]

 

과정

경로 : src/main/resources/data.sql

data.sql 파일에 쿼리는 아래와 같이 작성해줬다.

INSERT INTO `user` (name, email, password, createAt) VALUES ('전광렬', 'abc@naver.com', '1234', '2023-08-09 17:15:00');

 

yml 설정은 다음과 같다.

spring:
  h2:
    console:
      enabled: true
      path: /h2-console
  sql:
    init:
      mode: always

  datasource:
    url: jdbc:h2:mem:june
    username: sa
    password:
    driver-class-name: org.h2.Driver

  data:
    web:
      pageable:
        one-indexed-parameters: true
        default-page-size: 5

  jpa:
    properties:
      hibernate:
        show_sql: true
        format_sql: true
        use_sql_comments: true
    defer-datasource-initialization: true

defer-datasource-initialization : true 옵션을 설정하면 된다고 했는데 아무리해도 안되서 찾고 찾다가 아래와 같은 해결 방법을 알아냈다. 

해결

파일 이름을 변경한다

data.sql 파일의 이름을 data_sql.sql로 변경했더니 실행이 아주 잘된다......

파일의 data라는 것이 뭔가 충돌이 나는 것 같은데 정확한 원인은 파악하지 못 했다.

 

쿼리문 혹은 yml 스팰링 등을 잘못 썼는지 확인해 보고

defer-datasource-initialization true 옵션도 제대로 입력했는데 정상적으로 작동되지 않으면

.sql 파일명을 변경해보길 바랍니다.

* 테스트를 위해서 data_sql.sql로 변경했으나 보통은 import.sql로 사용하는 것 같다

---

추가

에러

org.hibernate.tool.schema.spi.CommandAcceptanceException: Error executing DDL "
    drop table if exists user CASCADE " via JDBC Statement ....

 

원인

H2 Database을 이용할 때 나오는 에러인데 H2 Database의 버전이 상향되면서 USER가 예약어가 됐다.

그래서 해당 USER 라는 이름의 테이블을 사용할 수 없다는 경고이다.

 

참고로 사용하면 안되는 단어들이 있다.

> order, user, createat 등등

 

해결

1. 해당 도메인의 이름을 아래와 같이 @Table annotation을 이용해 변경한다.

@Table(name = "users")

 

2. yml에서 NON_KEYWORDS=USER 설정을 추가한다.

  datasource:
    url: jdbc:h2:mem:june;NON_KEYWORDS=USER
    username: sa
    password:
    driver-class-name: org.h2.Driver