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.

Spring JDBC and MySql using UUIDs in Java and VARCHAR(36) in database incorrect string value solution

Introduction

Using H2 as initial embedded database for a Spring Boot application worked fine. H2 is very forgiving in many situations and of course only tries to emulate the real target database, in this case MySql 8.0.
So after connecting my Spring Boot application to MySql, this error started to appear when inserting a row in a table with a java.util.UUID property as 'id' field:  

    java.sql.SQLException: Incorrect string value: '\xAC\xED\x00\x05sr...' for column 'id' at row 1

A quick internet search showed that potentially my character set and collate setting for the tables were using 3 bytes instead of 4 for UTF-8 storage.
But the database, tables and columns all had utf8mb4 specified as CHARACTER SET and COLLATE, since I'm using MySql 8.0. So that 3 vs 4 bytes for UTF-8 issue did not apply for me. 

Solution

Then I found this blog https://petrepopescu.tech/2021/01/how-to-use-string-uuid-in-hibernate-with-mysql/ that at least when using Hibernate, it doesn't know how to convert UUIDs to strings (varchars), and you need to specify a Hibernate-provided converter. But the Hibernate annotation of course did not work for Spring Data JDBC.

Luckily there is a way to write your own converters for Spring JDBC datatypes.

Implementing that fixed that initial error message. Note the example in the above post is missing the @Configuration annotation on the MyJdbcConfiguration class.

But then the error happened again during this custom JdbcTemplate select query:

    String query = "SELECT DISTINCT r.id, user_id FROM recipe r WHERE user_id = ?";
    List<Object> parameterValues = new ArrayList<>();
    parameterValues.add(userId);
    Object[] parameterValuesArray = parameterValues.toArray();
    jdbcTemplate.query(query, parameterValuesArray, new JdbcRecipeRowMapper());

This was the output of that query, including the parameters used in the query:

Executing prepared SQL statement [SELECT DISTINCT r.id, user_id, r.name, vegetarian, number_of_servings, instructions, r.created_at, r.updated_at FROM recipe r INNER JOIN ingredient i ON r.id = i.recipe_id WHERE user_id = ? AND vegetarian = ? AND number_of_servings = ? AND instructions LIKE ? AND  i.name IN (?) ]
2022-09-14 15:46:38.062 TRACE 16148 --- [nio-7000-exec-2] o.s.jdbc.core.StatementCreatorUtils      : Setting SQL statement parameter value: column index 1, parameter value [e26b2a0a-3d2c-442f-8fa1-f26336d5a9d3], value class [java.util.UUID], SQL type unknown
2022-09-14 15:46:38.068 TRACE 16148 --- [nio-7000-exec-2] o.s.jdbc.core.StatementCreatorUtils      : Setting SQL statement parameter value: column index 2, parameter value [true], value class [java.lang.Boolean], SQL type unknown
2022-09-14 15:46:38.068 TRACE 16148 --- [nio-7000-exec-2] o.s.jdbc.core.StatementCreatorUtils      : Setting SQL statement parameter value: column index 3, parameter value [3], value class [java.lang.String], SQL type unknown
2022-09-14 15:46:38.068 TRACE 16148 --- [nio-7000-exec-2] o.s.jdbc.core.StatementCreatorUtils      : Setting SQL statement parameter value: column index 4, parameter value [%Step%], value class [java.lang.String], SQL type unknown
2022-09-14 15:46:38.068 TRACE 16148 --- [nio-7000-exec-2] o.s.jdbc.core.StatementCreatorUtils      : Setting SQL statement parameter value: column index 5, parameter value [spinach], value class [java.lang.String], SQL type unknown
2022-09-14 16:06:37.952 DEBUG 19748 --- [nio-7000-exec-2] o.s.jdbc.core.JdbcTemplate               : SQLWarning ignored: SQL state 'HY000', error code '1366', message [Incorrect string value: '\xAC\xED\x00\x05sr...' for column 'user_id' at row 1]

So that only shows a warning message at DEBUG level, not even as an error, so at first I missed it completely! The query just returned 0 results.

    SQLWarning ignored: SQL state 'HY000', error code '1366', message [Incorrect string value: '\xAC\xED\x00\x05sr...' for column 'user_id' at row 1]

Makes sense though that this custom query has the same issue, since my string-based query of course does not use the converters that I configured earlier.
So I had to change the third line to explicitly convert the value to a string, so Spring JDBC will pass it on as a string:

        parameterValues.add(userId.toString());

Note: maybe implementing a placeholder interface for each repository like this would also have fixed it for the Spring generated methods/queries like save(), remove() etc, e.g: interface RecipeRepository extends Repository<Recipe, UUID>. Did not try that out.

Other related links used to get to the above solution:

• https://www.vertica.com/docs/11.1.x/HTML/Content/Authoring/ConnectingToVertica/ClientJDBC/UUIDValues.htm
  
• https://www.anycodings.com/1questions/1947192/how-do-i-use-a-uuid-in-a-jdbc-template
  
• https://stackoverflow.com/questions/10957238/incorrect-string-value-when-trying-to-insert-utf-8-into-mysql-via-jdbc