flyway 실행시 Table already exists 에러 처리

옛날에 flyway 써보고 그뒤로 안쓰다보니... 이번에 하는데 몇시간 정도 삽질을 했다.
공식 문서에서도 적용하는건 일도 아닌걸로 나오는데 이상하게도 아래와 같은 에러가 발생
우선 V1__init.sql 파일은 이렇게 되어있다.

CREATE TABLE `demo`.`school` (
                                 `id` int NOT NULL AUTO_INCREMENT,
                                 `isActive` tinyint NOT NULL DEFAULT '1',
                                 `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
                                 `location` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
                                 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'flyway' defined in class path resource [com/joonseolee/springmvcquerydslboilerplate/config/FlywayConfiguration.class]: Bean instantiation via factory method failed; nested exception is org.springframework.beans.BeanInstantiationException: Failed to instantiate [org.flywaydb.core.Flyway]: Factory method 'flyway' threw exception; nested exception is org.flywaydb.core.internal.command.DbMigrate$FlywayMigrateException: 
Migration V1__init.sql failed
-----------------------------
SQL State  : 42S01
Error Code : 1050
Message    : Table 'school' already exists
Location   : db/migration/V1__init.sql (/Users/nhn/Documents/github-repostories/springmvc-querydsl-boilerplate/build/resources/main/db/migration/V1__init.sql)
Line       : 1
Statement  : CREATE TABLE `demo`.`school` (
                                 `id` int NOT NULL AUTO_INCREMENT,
                                 `isActive` tinyint NOT NULL DEFAULT '1',
                                 `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
                                 `location` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
                                 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

대충 보면 school 이 이미 존재해있기때문에 에러가 나는건데
실제 서비스할때 dba분께서 테이블을 추가해주시지 제쪽에서 추가하진않기때문에 다른방법을 찾아보기로함.
근데 이상하게도 flyway에서 자동으로 추가하는 테이블 flyway_schema_history 이 없으면 위와 같이 뜨고 있으면 아래와 같이 에러 발생

Caused by: org.springframework.beans.BeanInstantiationException: Failed to instantiate [org.flywaydb.core.Flyway]: Factory method 'flyway' threw exception; nested exception is org.flywaydb.core.api.exception.FlywayValidateException: Validate failed: Migrations have failed validation
Detected failed migration to version 1 (init). Please remove any half-completed changes then run repair to fix the schema history.
    at org.springframework.beans.factory.support.SimpleInstantiationStrategy.instantiate(SimpleInstantiationStrategy.java:185) ~[spring-beans-5.3.9.jar:5.3.9]
    at org.springframework.beans.factory.support.ConstructorResolver.instantiate(ConstructorResolver.java:653) ~[spring-beans-5.3.9.jar:5.3.9]
    ... 26 common frames omitted
Caused by: org.flywaydb.core.api.exception.FlywayValidateException: Validate failed: Migrations have failed validation
Detected failed migration to version 1 (init). Please remove any half-completed changes then run repair to fix the schema history.
    at org.flywaydb.core.Flyway$1.execute(Flyway.java:173) ~[flyway-core-7.7.3.jar:na]
    at org.flywaydb.core.Flyway$1.execute(Flyway.java:165) ~[flyway-core-7.7.3.jar:na]

그래서 기존 테이블이 있을경우 무시하는방법이 있지않을까? 생각해서 쿼리를 아래와 같이 수정해주니 정상적으로 작동이 되었다!

CREATE TABLE IF NOT EXISTS `demo`.`school` (
                                 `id` int NOT NULL AUTO_INCREMENT,
                                 `isActive` tinyint NOT NULL DEFAULT '1',
                                 `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
                                 `location` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
                                 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

2021-09-20 18:59:47.780  INFO 14924 --- [  restartedMain] o.f.core.internal.command.DbValidate     : Successfully validated 1 migration (execution time 00:00.019s)
2021-09-20 18:59:47.817  INFO 14924 --- [  restartedMain] o.f.c.i.s.JdbcTableSchemaHistory         : Creating Schema History table `demo`.`flyway_schema_history` with baseline ...
2021-09-20 18:59:47.866  INFO 14924 --- [  restartedMain] o.f.c.i.s.DefaultSqlScriptExecutor       : 1 rows affected
2021-09-20 18:59:47.894  INFO 14924 --- [  restartedMain] o.f.c.i.s.DefaultSqlScriptExecutor       : 0 rows affected
2021-09-20 18:59:47.908  INFO 14924 --- [  restartedMain] o.f.core.internal.command.DbBaseline     : Successfully baselined schema with version: 0
2021-09-20 18:59:47.929  INFO 14924 --- [  restartedMain] o.f.core.internal.command.DbMigrate      : Current version of schema `demo`: 0
2021-09-20 18:59:47.942  INFO 14924 --- [  restartedMain] o.f.core.internal.command.DbMigrate      : Migrating schema `demo` to version "1 - init"
2021-09-20 18:59:47.962  WARN 14924 --- [  restartedMain] o.f.c.i.s.DefaultSqlScriptExecutor       : DB: Table 'school' already exists (SQL State: 42S01 - Error Code: 1050)
2021-09-20 18:59:47.962  INFO 14924 --- [  restartedMain] o.f.c.i.s.DefaultSqlScriptExecutor       : 0 rows affected
2021-09-20 18:59:48.014  INFO 14924 --- [  restartedMain] o.f.core.internal.command.DbMigrate      : Successfully applied 1 migration to schema `demo`, now at version v1 (execution time 00:00.103s)

---

출처

1. flyway usage api