Flyway FlywayValidateException Validate failed: Migrations have failed validation. Detected failed migration to version 1 solution, please remove any half-completed changes then run repair to fix the schema history

Introduction

Setup:

• Spring boot 2.7.4
• Kotlin 1.7.20

Flyway dependencies:

plugins {

    id "org.flywaydb.flyway" version "9.6.0"

}

project(":application") {

  dependencies {

      dependencies {

          implementation "org.flywaydb:flyway-core:9.6.0"

          implementation "org.flywaydb:flyway-mysql:9.6.0"

  }

}

With only this Flyway script V1__initial_script present in the Spring Boot application:

create table order

(

    id                 varchar(255) primary key,

    orderId        bigint(20)    not null

);

create index order_id_idx on order(orderId);

create table order_entry

(

    id              varchar(255) primary key,

    orderid     bigint(20) not null,

    desc              varchar(255) not null,

    UNIQUE (oderId),

    constraint fk_orderId foreign key (orderId) references order (id)

);

gave this error:

Invocation of init method failed; nested exception is org.flywaydb.core.api.exception.FlywayValidateException: Validate failed: Migrations have failed validation

Detected failed migration to version 1 (initialize schema).

Please remove any half-completed changes then run repair to fix the schema history.

Need more flexibility with validation rules? Learn more: https://rd.gt/3AbJUZE

See also the below screenshot:

So the error is very vague. I also knew for sure that the schema script was the very first Flyway script to be run for that database, so it could not be the case that I edited the script after it already had run before succesfully.

Solution

The error code documentation doesn't tell much either. No details at all. 

But when I ran the contents of the above script on the SQL command line in DBeaver you get the exact details of the problem:

org.jkiss.dbeaver.model.sql.DBSQLException: SQL Error [3780] [HY000]: Referencing column 'orderId' and referenced column 'id' in foreign key constraint 'fk_orderId' are incompatible.

at org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCStatementImpl.executeStatement(JDBCStatementImpl.java:133)

...

So Flyway just doesn't log the details of the error at all. I also tried the Flyway validate in a bean, to see if I could inspect the exception hopefully returned in the validateWithResult variable:

  @Bean

  fun myFlyway(dataSource: DataSource): String {

    val flyway = Flyway.configure().dataSource(dataSource).load()

    val validateWithResult = flyway.validateWithResult()

    logger.info("Flyway validate = ${validateWithResult}");

  }

But that only showed again the high level error:

Migrations have failed validation

Error code: FAILED_VERSIONED_MIGRATION

Error message: Detected failed migration to version 1 (initialize schema). Please remove any half-completed changes then run repair to fix the schema history.

I found out that older Flyway versions used to have a bug not showing the whole exception,

But even in this most version 9.6.0 the full exception is not logged.

I also tried configuring Flyway with outputting JSON as mentioned here and here:

flyway {

    outputType="json"

}

But outputType can't be used here. Didn't further investigate how to specify that parameter, the @Bean has no option for it. Maybe it is possible via application.yml...

Update: it actually seems only to be available for the command-line option: https://documentation.red-gate.com/fd/command-line-flyway-validate-167936471.html and https://flywaydb.org/documentation/usage/gradle/validate

Though it seems it should be possible via configuration files and environment variables.

Thus in the end the solution for me was to run the script manually against the database to see what the exact error is. Running repair is also not the correct suggestion, because the script just contained some syntax/semantic errors. It looks like a Flyway bug that the exact error details aren't logged by default, and I couldn't get it to do that either.