Flyway repair with Spring Boot

38
votes

I don't quite understand what I am supposed to do when a migration fails using Flyway in a Spring Boot project.

I activated Flyway by simply adding the Flyway dependency in my pom.xml. And everything works fine. My database scripts are migrated when I launch the Spring Boot app.

But I had an error in one of my scripts and my last migration failed. Now when I try to migrate, there is a "Migration checksum mismatch". Normally, I would run mvn flyway:repair, but since I am using Spring Boot, I am not supposed to use the Flyway Maven plug-in. So what am I supposed to do?

5
javaspringmavenspring-bootflyway
I would say run manually flyway repair as changing previous sql files should be very exceptional. But it looks that checksums doesn't match between sql files in classpath and local... – Thomas Duchatelle
But, like I said, when using Spring Boot, you are not expected to use the Flyway plug-in. – Jean-François Beauchef
No, but by using flyway, you're not expected to change existing sql files! Especially if they have been already executed on prod db. You should only add new SQL files with your changes everytime. I can understand why spring-boot doesn't support this: it should stay a manual operation and should certainly not be per default... (ignoring any change make on SQL already ran) – Thomas Duchatelle
Ok, but how do you test your SQL script then? Manually? Or with the Flyway plug-in? – Jean-François Beauchef
Both. I basically have a "test database" on my dev environment which I can flush at anytime: mvn flyway:clean flyway:migrate. You can also use flyway command line. – Thomas Duchatelle

5 Answers

32
votes

there are several ways to perform a repair on the database. I personally prefer the simple SQL statement.

SQL Statement:

Just delete the row with the failed migration. After that you can run the migration again.

Run flyway directly

You can install Flyway local and run flyway repair in the console

Use the Flyway Maven Plugin

Add the Flyway Maven Plugin to your pom and run mvn flyway:repair. I don't think this contradict with the Spring Boot concept.

Extend Spring Boot

Spring Boot will call Flyway.migrate() to perform the database migration. If you would like more control, provide a @Bean that implements FlywayMigrationStrategy.

In the FlywayMigrationStrategy you can call the migrate or repair method from flyway. More Information is available in the Spring Boot Reference Guide.

I don't think the FlywayMigrationStrategy in the application is the right place to repair the database. A failed migration is a exception and should be handle outside the application.

7
votes

You can do it through code by declaring the following bean.

@Bean
public FlywayMigrationStrategy cleanMigrateStrategy() {
    return flyway -> {
        flyway.repair();
        flyway.migrate();
    };
}
5
votes

Flyway Maven Plugin

Just to add this info to @Daniel's answer

1.

      ...
        <plugin>
            <groupId>org.flywaydb</groupId>
            <artifactId>flyway-maven-plugin</artifactId>
            <version>4.1.0</version>
            <configuration>
                <url>jdbc:mysql://localhost:3306</url>
                <user>root</user>
                <password>root</password>
                <schemas>
                    <schema>[your_schema]</schema>
                </schemas>
            </configuration>
        </plugin>
      ...

2.

mvn flyway:clean

3.

mvn flyway:repair

PS.: if the step 2 and 3 don't work change the order.

More info on maven goals: https://flywaydb.org/documentation/maven/

3
votes

When database migration fails, the migration is marked as failed in the schema history table (i.e flyway_schema_history) indicating manual database cleanup may be required. But if database supports DDL transactions, the migration is rolled back automatically and nothing is recorded in the schema history table. PostgreSQL, Amazon Redshift, MS SQL are few of the databases which support DDL transactions whereas Oracle Database, MySQL, MariaDB, Amazon Aurora does not support DDL transactions.

In case of failed migration entries, there are several options to repair it (only applicable for databases that do NOT support DDL transactions) as described by @daniel-käfer. I want to add another (may be easier way) to deal with failed migrations.

There are several callbacks supported by flyway, afterMigrateError is one of them. If we add a sql file with name afterMigrateError.sql then, it will be executed after each failed migrate runs. Therefore, we can simply create a file afterMigrateError.sql on default location of database migration folder (resources/db/migration) with sql command to remove failed migrations from flyway_schema_history table.

The sql command afterMigrateError.sql can be as mentioned below:

DELETE IGNORE FROM flyway_schema_history WHERE success=0;

This command looks for the table flyway_schema_history if it exists otherwise it will do no changes. Then it simply looks for the rows which has success column with 0 entry (actually this happen if migration fails , all successful migration will have value 1 in success column), then delete such entries. Now, we can simply change our latest migration file and correct it and run again.

1
votes

Install flyway locally as said above, change directory into the installation then run (example for H2):

./flyway -url=jdbc:h2:/Users/mugo/dev/h2/das-boot -user=sa -password= repair