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.

Configuring MySQL test-containers in your Spring Boot Java Integration Tests

Introduction

In your Integration Tests (IT) you often try to use the H2 in-memory database, to improve the speed of your integration tests. But on the other hand you want to mimic the production database as much as possible in your integration-tests.

Setting H2 in MySQL database compatibility mode tries to emulate MySQL as much as possible, but only a small subset of the differences are implemented. What for example not works correctly in H2 for JSON fields is that it escapes strings with "". For that reason you usually want to switch to for example starting a Docker database testcontainer in your IT tests, which uses a real MySQL database. With the Java-specific version in https://github.com/testcontainers/testcontainers-java.

Configuration

There are several good-to-know tips when configuring the testcontainers in your ITs.

1. The simplest configuration is using a datasource URL in the Spring Boot properties file. This has the disadvantage that whatever database name you specify, the testcontainers library still creates a DB named 'test'. So below it will be named 'integration_test_db' you'd think, but it is still named 'test' when the IT runs:
   
   spring.datasource.url=jdbc:tc:mysql:5.7.32:///integration_test_db?sessionVariables=sql_mode='STRICT_TRANS_TABLES'&TC_MY_CNF=mysql&TC_INITSCRIPT=mysql/init_mysql_integration_tests.sql
   
   To be able to do everything on the started database, including giving it the name you want, use this URL (or see below the Java version). Notice the user 'root' in the URL:
   spring.datasource.url=jdbc:tc:mysql:5.7.32:///integration_test_db?user=root&password=&sessionVariables=sql_mode='STRICT_TRANS_TABLES'&TC_MY_CNF=mysql&TC_INITSCRIPT=mysql/init_mysql_integration_tests.sql
   
   Via: https://github.com/testcontainers/testcontainers-java/issues/932
   
   
2. To initialize your database, specify the script via this extra datasource URL variable:
   
   TC_INITSCRIPT=mysql/init_mysql_integration_tests.sql
   
   Note the default directory it looks in is ..../resources for the scripts. So the full path is ..../resources/mysql/
   
   
3. An example to prevent GROUP BY error from strict mode, add this in your TC_INITSCRIPT:
   
   SET GLOBAL sql_mode = 'STRICT_TRANS_TABLES';
   SET SESSION sql_mode = 'STRICT_TRANS_TABLES';
   
   
4. To configure it in the IT Java class itself:
   
   @RunWith(SpringRunner.class)
   @SpringBootTest(classes = { SomeClassA.class, SomeClassB.class, ApplicationConfiguration.class}, webEnvironment = SpringBootTest.WebEnvironment.RANDOM_PORT)
   @TestPropertySource(locations = {
           "classpath:/application-test-mysql.properties" })
   @ContextConfiguration(initializers = {ThisITClass.Initializer.class})
   
   static class Initializer implements ApplicationContextInitializer<ConfigurableApplicationContext> {
           public void initialize(ConfigurableApplicationContext configurableApplicationContext) {
               TestPropertyValues.of(
                       "spring.datasource.url=" + mySQLContainer.getJdbcUrl(),
                       "spring.datasource.username=" + mySQLContainer.getUsername(),
                       "spring.datasource.password=" + mySQLContainer.getPassword()
               ).applyTo(configurableApplicationContext.getEnvironment());
           }
       }
   
   @ClassRule
   public static MySQLContainer mySQLContainer = new MySQLContainer<>("mysql:5.7.31")
               .withUsername("root") // So now you can do a GRANT too for example
               .withPassword("") // Only possible for user 'root'
               .withEnv("MYSQL_ROOT_HOST", "%")
               .withDatabaseName("integration_test_db") // So this name will now be used, not 'test'
               .withInitScript("mysql/init_mysql_integration_tests.sql")
               ;
   
   
5. The MySQL docker image used in the tests is retrieved from DockerHub https://hub.docker.com/_/mysql
   
   

Logback DBAppender sometimes gives error on AWS Aurora: IllegalStateException: DBAppender cannot function if the JDBC driver does not support getGeneratedKeys method *and* without a specific SQL dialect

LOGBack DBAppender IllegalStateException

Sometimes when starting a Spring Boot application with Logback DBAppender configured for PostgreSQL or AWS Aurora in logback-spring.xml, it gives this error:

java.lang.IllegalStateException: Logback configuration error detected: ERROR in ch.qos.logback.core.joran.spi.Interpreter@22:16 - RuntimeException in Action for tag [appender] java.lang.IllegalStateException: DBAppender cannot function if the JDBC driver does not support getGeneratedKeys method *and* without a specific SQL dialect

The error can be quite confusing. From the documentation it says that Logback should be able to detect the dialect from the driver class.

But apparently it doesn't. Sometimes. After investigating, it turns out that this error is also given when the driver can't connect correctly to the database. Because it will then not be able to find the metadata either, which it uses to detect the dialect. And thus you get this error too in that case!
A confusing error message indeed.

A suggestion in some post was to specify the <sqlDialect> tag, but that is not needed anymore in recent Logback versions. Indeed, it now gives these errors when putting it in logback-spring.xml file either below <password> or below <connectionSource>:

ERROR in ch.qos.logback.core.joran.spi.Interpreter@25:87 - no applicable action for [sqlDialect], current ElementPath  is [[configuration][appender][connectionSource][dataSource][sqlDialect]]
or
ERROR in ch.qos.logback.core.joran.spi.Interpreter@27:79 - no applicable action for [sqlDialect], current ElementPath  is [[configuration][appender][sqlDialect]]
To get a better error message it's better to implement the setup of the LogBack DBAppender in code, instead of in the logback-spring.xml. See for examples here and here.

Best of this Week Summary 15 March - 21 March 2010

• Twitter and Digg are moving from MySQL to Cassandra (a highly scalable second-generation distributed database, bringing together Dynamo's fully distributed design and Bigtable's ColumnFamily-based data model; a Facebook opensourced project). The reason for Digg for the move "is the increasing difficulty of building a high-performance, write-intensive application on a data set that is growing quickly, with no end in sight. This growth has forced them into horizontal and vertical partitioning strategies that have eliminated most of the value of a relational database, while still incurring all the overhead".
  Twitter has about the same reason: "No single points of failure", "Highly scalable writes (we have highly variable write traffic)", and "A healthy and productive open source community".
  Twitter tried HBase, Voldemort, MongoDB, MemcacheDB, Redis, Cassandra, and HyperTable amongst others before deciding to go with Cassandra. Interesting to read is how they slowly rollout Cassandra to limited sets of users.
  An introduction to Cassandra to get it up and running can be found here.
  
  


• A short post on how to implement Automatic testing Oracle Service Bus using Hudson, Maven and SoapUI.
  


• What to expect from HTML5 for webdevelopers.
  

Best of this Week Summary 14 September - 20 September 2009

• Scala is slowly getting some more and more traction lately. Is it a potential long term replacement for Java?
  Interview with Scala creator Martin Odersky where he tells its history, future and why it's so interesting (like combining OO- and functional techniques).
  


• Six valid "takeaways on what most REST adopters can and should do to get the most from their use of this increasingly popular architectural style" summarised by Dion Hinchcliffe.
  


• Article that describes using Benerator, which is a data generator tool that can be used to feed database with pseudo-random test data.
  


• Tip that shows you how to implement composite keys with JPA and Hibernate. Check also the comments here.