Flyway migration blocked by null version_rank

Question

I'm using PostgreSQL 9.5 and flyway 5.0.7

Everything worked fine for the previous 6 migrations but now it blocks for the latest

I have the following error :

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

Any idea why this column "version_rank" is not generated or not initialized ?

Thanks in advance for your help

Answer 1

Here's the commit with the metadata table changes if you need to apply the changes manually. I've linked to the version for postgres - search for the version of upgradeMetaDataTable.sql in the folder matching the required dialect.

Fortunately you can apply the changes as a standard flyway migration, as the metadata tables are not used until the end of each script.

E.G. create a migration V999.00__FlywayFix.sql to correct a flyway version table called flyway_table as follows:

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 2

You upgraded from Flyway 3.x to 5.x, skipping 4.x. This is not possible as written in the release notes: https://flywaydb.org/documentation/releaseNotes#5.0.0

Upgrade to 4.2.0 first before upgrading to 5.x and everything will work as expected.

Also please take a minute to check the release notes next time you upgrade a major version.

Answer 3

it's worked for me for 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;