Flyway迁移报错：表不存在问题排查（Scala项目）

问题 #

在Scala项目中使用Flyway做数据库迁移时无法执行脚本，具体信息如下：

项目配置（application.conf） #

my-platform.jdbc {
  driver = "org.h2.Driver"
  dbName = "my-platform-h2-test.db;DB_CLOSE_ON_EXIT=FALSE;MODE=MYSQL"
  url = "jdbc:h2:~/my-platform/database/"${my-platform.jdbc.dbName}
  user = "sa"
  migrations-table = "MyPlatformDBSchema"
  migrations-locations = [
    "classpath:h2/"
  ]
}

运行时错误日志 #

INFO: Current version of schema "PUBLIC": << Empty Schema >>
Aug 28, 2023 9:33:23 PM org.flywaydb.core.internal.command.DbMigrate doMigrateGroup
INFO: Migrating schema "PUBLIC" to version "001 - baseline create initial tables"
org.flywaydb.core.internal.exception.FlywaySqlException: Unable to insert row for version '001' in Schema History table "PUBLIC"."MyPlatformDBSchema"
---------------------------------------------------------------------------------------------
SQL State  : 42S02
Error Code : 42102
Message    : Table "MyPlatformDBSchema" not found; SQL statement:
INSERT INTO "PUBLIC"."MyPlatformDBSchema" ("installed_rank", "version", "description", "type", "script", "checksum", "installed_by", "execution_time", "success") VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?) [42102-214]

    at org.flywaydb.core.internal.schemahistory.JdbcTableSchemaHistory.doAddAppliedMigration(JdbcTableSchemaHistory.java:177)
    at org.flywaydb.core.internal.schemahistory.SchemaHistory.addAppliedMigration(SchemaHistory.java:192)
    at org.flywaydb.core.internal.command.DbMigrate.doMigrateGroup(DbMigrate.java:402)
    at org.flywaydb.core.internal.command.DbMigrate.lambda$applyMigrations$1(DbMigrate.java:274)

迁移脚本（src/main/resources/h2/V001__baseline_create_initial_tables.sql） #

--
-- Drop everything if exists
--

DROP ALL OBJECTS DELETE FILES;

--
-- Schema for my-platform
--
CREATE SCHEMA my_platform_schema AUTHORIZATION SA;
SET SCHEMA my_platform_schema;
....
.... 
-- The remaining create tables
....
....

问题分析与解决方案 #

核心原因 #

1. Flyway启动后会先在默认连接的PUBLIC schema下创建迁移历史表MyPlatformDBSchema，但你的迁移脚本第一行执行DROP ALL OBJECTS DELETE FILES;，直接删除了刚创建的历史表。
2. 脚本后续切换到自定义schema my_platform_schema，但Flyway的历史表配置仍指向PUBLIC.MyPlatformDBSchema，导致迁移完成后无法找到历史表记录状态。

修复步骤 #

• 调整脚本的清理逻辑：替换全局删除语句，只清理自定义schema下的对象，避免误删Flyway历史表：

  -- 仅删除目标schema及下属对象，保留PUBLIC中的Flyway历史表
  DROP SCHEMA IF EXISTS my_platform_schema CASCADE;
  CREATE SCHEMA my_platform_schema AUTHORIZATION SA;
  SET SCHEMA my_platform_schema;
  -- 后续创建表逻辑
  

• 可选：让Flyway在自定义schema存储历史表：如果希望历史表也放在自定义schema中，修改application.conf添加默认schema配置：

  my-platform.jdbc {
    driver = "org.h2.Driver"
    dbName = "my-platform-h2-test.db;DB_CLOSE_ON_EXIT=FALSE;MODE=MYSQL"
    url = "jdbc:h2:~/my-platform/database/"${my-platform.jdbc.dbName}
    user = "sa"
    default-schema = "my_platform_schema"
    migrations-table = "MyPlatformDBSchema"
    migrations-locations = [
      "classpath:h2/"
    ]
  }
  

  此时需确保Flyway初始化前my_platform_schema已存在，可提前执行schema创建脚本，或配置Flyway的baseline策略自动处理。

内容的提问来源于stack exchange，提问作者joesan