Spring Boot Gradle Test 실패?

프로젝트에 작성된 통합 테스트들이 개별적으로 동작할 때는 아무 문제가 발생하지 않았었는데, Gradle Test를 통해 전체적으로 진행하니 몇몇 문제가 발생하게 되었다.

 

BUG!

JdbcSQLSyntaxErrorException: Table "POST" already exists;... [42101-200]

Caused by: org.springframework.beans.factory.UnsatisfiedDependencyException: Error creating bean with name 'postController' defined in file [C:\Users\serrl\Desktop\Mentoring\Somaeja\build\classes\java\main\com\somaeja\post\controller\PostController.class]: Unsatisfied dependency expressed through constructor parameter 0; nested exception is org.springframework.beans.factory.UnsatisfiedDependencyException: Error creating bean with name 'postService' defined in file [C:\Users\serrl\Desktop\Mentoring\Somaeja\build\classes\java\main\com\somaeja\post\service\PostService.class]: Unsatisfied dependency expressed through constructor parameter 0; nested exception is org.springframework.beans.factory.UnsatisfiedDependencyException: Error creating bean with name 'postMapper' defined in file [C:\Users\serrl\Desktop\Mentoring\Somaeja\build\classes\java\main\com\somaeja\post\mapper\PostMapper.class]: Unsatisfied dependency expressed through bean property 'sqlSessionFactory'; nested exception is org.springframework.beans.factory.UnsatisfiedDependencyException: Error creating bean with name 'sqlSessionFactory' defined in class path resource [com/somaeja/common/config/PersistenceConfig.class]: Unsatisfied dependency expressed through method 'sqlSessionFactory' parameter 0; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'dataSource' defined in class path resource [org/springframework/boot/autoconfigure/jdbc/DataSourceConfiguration$Hikari.class]: Initialization of bean failed; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'org.springframework.boot.autoconfigure.jdbc.DataSourceInitializerInvoker': Invocation of init method failed; nested exception is org.springframework.jdbc.datasource.init.ScriptStatementFailedException: Failed to execute SQL script statement #1 of URL [file:/C:/Users/serrl/Desktop/Mentoring/Somaeja/build/resources/main/schema.sql]: CREATE TABLE POST ( POST_ID INTEGER PRIMARY KEY auto_increment, USER_ID INTEGER NOT NULL, LOCATION_ID INTEGER NOT NULL, IMAGE_ID INTEGER NOT NULL, TITLE VARCHAR(255) NOT NULL, CONTENT VARCHAR(255) NOT NULL, PRICE VARCHAR(255) NOT NULL, IS_NEGOTIABLE TINYINT(1) NOT NULL, IS_DIRECTTRADE TINYINT(1) NOT NULL, CREATEDATE VARCHAR(255) NOT NULL, MODIFYDATE VARCHAR(255) NOT NULL ); nested exception is org.h2.jdbc.JdbcSQLSyntaxErrorException: Table "POST" already exists; SQL statement:
CREATE TABLE POST ( POST_ID INTEGER PRIMARY KEY auto_increment, USER_ID INTEGER NOT NULL, LOCATION_ID INTEGER NOT NULL, IMAGE_ID INTEGER NOT NULL, TITLE VARCHAR(255) NOT NULL, CONTENT VARCHAR(255) NOT NULL, PRICE VARCHAR(255) NOT NULL, IS_NEGOTIABLE TINYINT(1) NOT NULL, IS_DIRECTTRADE TINYINT(1) NOT NULL, CREATEDATE VARCHAR(255) NOT NULL, MODIFYDATE VARCHAR(255) NOT NULL ) [42101-200]
    at org.springframework.beans.factory.support.ConstructorResolver.createArgumentArray(ConstructorResolver.java:797)
    at org.springframework.beans.factory.support.ConstructorResolver.autowireConstructor(ConstructorResolver.java:227)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.autowireConstructor(AbstractAutowireCapableBeanFactory.java:1356)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBeanInstance(AbstractAutowireCapableBeanFactory.java:1203)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:556)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:516)
    at org.springframework.beans.factory.support.AbstractBeanFactory.lambda$doGetBean$0(AbstractBeanFactory.java:324)
    at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:234)
    at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:322)
    at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:202)
    at org.springframework.beans.factory.support.DefaultListableBeanFactory.preInstantiateSingletons(DefaultListableBeanFactory.java:897)
    at org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:879)
    at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:551)
    at org.springframework.boot.SpringApplication.refresh(SpringApplication.java:758)
    at org.springframework.boot.SpringApplication.refresh(SpringApplication.java:750)
    at org.springframework.boot.SpringApplication.refreshContext(SpringApplication.java:405)
    at org.springframework.boot.SpringApplication.run(SpringApplication.java:315)
    at org.springframework.boot.test.context.SpringBootContextLoader.loadContext(SpringBootContextLoader.java:120)
    at org.springframework.test.context.cache.DefaultCacheAwareContextLoaderDelegate.loadContextInternal(DefaultCacheAwareContextLoaderDelegate.java:99)
    at org.springframework.test.context.cache.DefaultCacheAwareContextLoaderDelegate.loadContext(DefaultCacheAwareContextLoaderDelegate.java:124)
    ... 88 more

 

Table "POST" already exists; [42101-200] 에러는 무엇인가?

해당 H2 버전부터 테이블 삭제 명령의 동작 이 SQL 표준을 준수하도록 변경되었다고 한다. 즉 이전 버전에서는 테이블을 삭제할 때 제약 조건을 무시하였지만, 현재 버전부터 제약 조건에 의해 삭제가 실패하게 된다는 것이다.

 

짐작해보자면? 현재 Post Table에는 FK가 연결되어 있으며, 해당 테이블 삭제시 참조 무결성으로 인해 삭제가 되지 않고 중복되는 일이 발생하는 것 같다.

 

 

Gradle Test 시 예상 실행 흐름

해결 방법?

1. 삭제를 시도했으나 실패하고 SQL 파일 실행 시 데이터가 존재하는 것이라면 강제 삭제를 할 수 있게끔 CASCADE가 명시된 삭제를 진행
2. Test Class에 롤백 적용하기??

 

1번을 통해 문제를 해결하였다.

 

 

Schema.sql 최상단에 추가!

이를 통해 제대로 초기화되지 않았을 데이터들을 다음 테스트를 위한 데이터 생성 이전에 한번 더 정리한다.

DROP TABLE IF EXISTS POST CASCADE;
DROP TABLE IF EXISTS LOCATION CASCADE;