search

HabitPay / backend

https://habitpay.github.io/backend/
0 stars 0 forks source link

remove: absenceTotalFee 컬럼 생성 sql 파일 삭제 #278 #280

Closed Han-Joon-Hyeok closed 1 month ago

Han-Joon-Hyeok commented 1 month ago

개요

운영 서버에는 absenceTotalFee 가 삭제되지 않아서 Flyway DB 마이그레이션이 작동하지 않았습니다.

즉, absenceTotalFee 컬럼을 생성하려는 SQL 은 이미 존재하는 컬럼에 대해서 실행되었기 때문에 오류가 발생했습니다.

오류 전문

Caused by: org.flywaydb.core.internal.sqlscript.FlywaySqlScriptException: Migration V202410162038__Add_totalAbsenceFee_in_challenge.sql failed
--------------------------------------------------------------------
SQL State  : 42701
Error Code : 0
Message    : ERROR: column "total_absence_fee" of relation "challenge" already exists
Location   : db/migration/V202410162038__Add_totalAbsenceFee_in_challenge.sql (/usr/app/nested:/usr/app/app.jar/!BOOT-INF/classes/!/db/migration/V202410162038__Add_totalAbsenceFee_in_challenge.sql)
Line       : 1
Statement  : ALTER TABLE challenge
    ADD total_absence_fee integer

    at org.flywaydb.core.internal.sqlscript.DefaultSqlScriptExecutor.handleException(DefaultSqlScriptExecutor.java:267) ~[flyway-core-9.22.3.jar!/:na]
    at org.flywaydb.core.internal.sqlscript.DefaultSqlScriptExecutor.executeStatement(DefaultSqlScriptExecutor.java:222) ~[flyway-core-9.22.3.jar!/:na]
    at org.flywaydb.core.internal.sqlscript.DefaultSqlScriptExecutor.execute(DefaultSqlScriptExecutor.java:126) ~[flyway-core-9.22.3.jar!/:na]
    at org.flywaydb.core.internal.resolver.sql.SqlMigrationExecutor.executeOnce(SqlMigrationExecutor.java:68) ~[flyway-core-9.22.3.jar!/:na]
    at org.flywaydb.core.internal.resolver.sql.SqlMigrationExecutor.lambda$execute$0(SqlMigrationExecutor.java:57) ~[flyway-core-9.22.3.jar!/:na]
    at org.flywaydb.core.internal.database.DefaultExecutionStrategy.execute(DefaultExecutionStrategy.java:27) ~[flyway-core-9.22.3.jar!/:na]
    at org.flywaydb.core.internal.resolver.sql.SqlMigrationExecutor.execute(SqlMigrationExecutor.java:56) ~[flyway-core-9.22.3.jar!/:na]
    at org.flywaydb.core.internal.command.DbMigrate.doMigrateGroup(DbMigrate.java:374) ~[flyway-core-9.22.3.jar!/:na]
    ... 143 common frames omitted
Caused by: org.postgresql.util.PSQLException: ERROR: column "total_absence_fee" of relation "challenge" already exists
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2713) ~[postgresql-42.6.1.jar!/:42.6.1]
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2401) ~[postgresql-42.6.1.jar!/:42.6.1]
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:368) ~[postgresql-42.6.1.jar!/:42.6.1]
    at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:498) ~[postgresql-42.6.1.jar!/:42.6.1]
    at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:415) ~[postgresql-42.6.1.jar!/:42.6.1]
    at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:335) ~[postgresql-42.6.1.jar!/:42.6.1]
    at org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:321) ~[postgresql-42.6.1.jar!/:42.6.1]
    at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:297) ~[postgresql-42.6.1.jar!/:42.6.1]
    at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:292) ~[postgresql-42.6.1.jar!/:42.6.1]
    at com.zaxxer.hikari.pool.ProxyStatement.execute(ProxyStatement.java:94) ~[HikariCP-5.0.1.jar!/:na]
    at com.zaxxer.hikari.pool.HikariProxyStatement.execute(HikariProxyStatement.java) ~[HikariCP-5.0.1.jar!/:na]
    at org.flywaydb.core.internal.jdbc.JdbcTemplate.executeStatement(JdbcTemplate.java:201) ~[flyway-core-9.22.3.jar!/:na]
    at org.flywaydb.core.internal.sqlscript.ParsedSqlStatement.execute(ParsedSqlStatement.java:95) ~[flyway-core-9.22.3.jar!/:na]
    at org.flywaydb.core.internal.sqlscript.DefaultSqlScriptExecutor.executeStatement(DefaultSqlScriptExecutor.java:210) ~[flyway-core-9.22.3.jar!/:na]
    ... 149 common frames omitted

조치

  1. 운영 DB 에서 flyway_schema_history 테이블을 삭제했습니다.
    • SQL 이 실행된 이력이 있어서 SQL 파일을 삭제하면 충돌이 날 수 있기에 Flyway 도입 이전의 상태로 되돌렸습니다.
    • 권장되는 조치는 아닙니다.
  2. V202410162038__Add_totalAbsenceFee_in_challenge.sql 파일을 삭제했습니다.
    • 해당 파일이 있어도 오류가 발생하기 때문에 삭제했습니다.