空version_rank阻止了Flyway迁移

Question

我使用的是PostgreSQL 9.5和flyway 5.0.7

之前的6个迁移都运行良好，但现在最新的迁移受阻

我有以下错误：

22:27:45.230 [INFO ] o.f.c.i.u.l.slf4j.Slf4jLog - Flyway Community Edition 5.0.7 by Boxfuse
22:27:45.408 [INFO ] o.f.c.i.u.l.slf4j.Slf4jLog - Database: jdbc:postgresql://localhost:32767/my_db (PostgreSQL 9.5)
22:27:45.566 [INFO ] o.f.c.i.u.l.slf4j.Slf4jLog - Successfully validated 7 migrations (execution time 00:00.061s)
22:27:45.658 [INFO ] o.f.c.i.u.l.slf4j.Slf4jLog - Current version of schema "public": 6
22:27:45.733 [INFO ] o.f.c.i.u.l.slf4j.Slf4jLog - Migrating schema "public" to version 7 - update
Exception in thread "main" org.flywaydb.core.internal.exception.FlywaySqlException: 
Unable to insert row for version '7' in Schema History table "public"."flyway_schema_history"
---------------------------------------------------------------------------------------------
SQL State  : 23502
Error Code : 0
Message    : ERROR: null value in column "version_rank" violates not-null constraint
  Détail : Failing row contains (null, 7, 7, update, SQL, V7__update.sql, -1303600795, postgres, 2018-02-25 22:28:00.536556, 158, t).

    at org.flywaydb.core.internal.schemahistory.JdbcTableSchemaHistory.doAddAppliedMigration(JdbcTableSchemaHistory.java:171)
    at org.flywaydb.core.internal.schemahistory.SchemaHistory.addAppliedMigration(SchemaHistory.java:146)
    at org.flywaydb.core.internal.command.DbMigrate.doMigrateGroup(DbMigrate.java:378)
    at org.flywaydb.core.internal.command.DbMigrate.access$400(DbMigrate.java:52)
    at org.flywaydb.core.internal.command.DbMigrate$5.call(DbMigrate.java:297)
    at org.flywaydb.core.internal.util.jdbc.TransactionTemplate.execute(TransactionTemplate.java:75)
    at org.flywaydb.core.internal.command.DbMigrate.applyMigrations(DbMigrate.java:294)
    at org.flywaydb.core.internal.command.DbMigrate.migrateGroup(DbMigrate.java:259)
    at org.flywaydb.core.internal.command.DbMigrate.access$300(DbMigrate.java:52)
    at org.flywaydb.core.internal.command.DbMigrate$4.call(DbMigrate.java:179)
    at org.flywaydb.core.internal.command.DbMigrate$4.call(DbMigrate.java:176)
    at org.flywaydb.core.internal.database.postgresql.PostgreSQLAdvisoryLockTemplate.execute(PostgreSQLAdvisoryLockTemplate.java:71)
    at org.flywaydb.core.internal.database.postgresql.PostgreSQLConnection.lock(PostgreSQLConnection.java:110)
    at org.flywaydb.core.internal.schemahistory.JdbcTableSchemaHistory.lock(JdbcTableSchemaHistory.java:148)
    at org.flywaydb.core.internal.command.DbMigrate.migrateAll(DbMigrate.java:176)
    at org.flywaydb.core.internal.command.DbMigrate.migrate(DbMigrate.java:145)
    at org.flywaydb.core.Flyway$1.execute(Flyway.java:1206)
    at org.flywaydb.core.Flyway$1.execute(Flyway.java:1168)
    at org.flywaydb.core.Flyway.execute(Flyway.java:1655)
    at org.flywaydb.core.Flyway.migrate(Flyway.java:1168)
    at com.test.MyApplication.main(MainApplication.java:47)
Caused by: org.postgresql.util.PSQLException: ERROR: null value in column "version_rank" violates not-null constraint
  Détail : Failing row contains (null, 7, 7, update, SQL, V7__update.sql, -1303600795, postgres, 2018-02-25 22:28:00.536556, 158, t).
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2422)
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2167)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:306)
    at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:441)
    at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:365)
    at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:155)
    at org.postgresql.jdbc.PgPreparedStatement.executeUpdate(PgPreparedStatement.java:132)
    at org.flywaydb.core.internal.util.jdbc.JdbcTemplate.update(JdbcTemplate.java:334)
    at org.flywaydb.core.internal.schemahistory.JdbcTableSchemaHistory.doAddAppliedMigration(JdbcTableSchemaHistory.java:165)
    ... 20 more

您知道为什么没有生成或初始化列"version_rank“吗？

提前感谢您的帮助

Answer 1

您已从Flyway 3.x升级到5.x，跳过了4.x。如发行说明中所述，这是不可能的：https://flywaydb.org/documentation/releaseNotes#5.0.0

先升级到4.2.0，然后再升级到5.x，一切都会正常工作。

另外，下次升级主要版本时，请花一分钟时间查看发行说明。

Answer 2

如果您需要手动应用更改，可以使用the commit with the metadata table changes。我已经链接到postgres的版本-在文件夹中搜索与所需方言匹配的upgradeMetaDataTable.sql版本。

幸运的是，您可以将这些更改作为标准的flyway迁移来应用，因为元数据表直到每个脚本结束时才会使用。

例如，创建一个迁移V999.00__FlywayFix.sql来更正一个名为flyway_table的flyway版本表，如下所示：

DROP INDEX "flyway_table_vr_idx";
DROP INDEX "flyway_table_ir_idx";
ALTER TABLE "flyway_table" DROP COLUMN "version_rank";
ALTER TABLE "flyway_table" DROP CONSTRAINT "flyway_table_pk";
ALTER TABLE "flyway_table" ALTER COLUMN "version" DROP NOT NULL;
ALTER TABLE "flyway_table" ADD CONSTRAINT "flyway_table_pk" PRIMARY KEY ("installed_rank");
UPDATE "flyway_table" SET "type"='BASELINE' WHERE "type"='INIT';

Answer 3

这对我在Postgres工作很有效

CREATE TABLE flyway_schema_history (
  installed_rank INTEGER NOT NULL,
  version varchar(50) DEFAULT NULL,
  description varchar(200) NOT NULL,
  type varchar(20) NOT NULL,
  script varchar(1000) NOT NULL,
  checksum INTEGER DEFAULT NULL,
  installed_by varchar(100) NOT NULL,
  installed_on timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  execution_time INTEGER NOT NULL,
  success BOOLEAN NOT NULL,
  PRIMARY KEY (installed_rank)
);

INSERT INTO flyway_schema_history (installed_rank, version, description,     type, script, checksum, installed_by, installed_on, execution_time, success)
SELECT installed_rank, version, description, type, script, checksum,     installed_by, installed_on, execution_time, success
FROM schema_version;

ALTER TABLE schema_version RENAME TO bak_schema_version;