컴퓨터 관련/Java 정보

flyway 실행시 Table already exists 에러 처리

보뇨 2021. 9. 20. 19:00
반응형

옛날에 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
반응형