46

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?

Jean-François Beauchef
  • 1,291
  • 2
  • 11
  • 21
  • 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 Jun 03 '16 at 16:23
  • But, like I said, when using Spring Boot, you are not expected to use the Flyway plug-in. – Jean-François Beauchef Jun 04 '16 at 14:15
  • 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 Jun 07 '16 at 14:18
  • 1
    Ok, but how do you test your SQL script then? Manually? Or with the Flyway plug-in? – Jean-François Beauchef Jun 07 '16 at 17:40
  • 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 Jun 08 '16 at 11:00
  • Ok, I thought there would be a way to avoid using the Flyway plug-in. BTW, I was talking about running the script on my dev database. I never ran my script in prod. So, a mistake at this point can occur. I was looking for a way to repair my dev database. But thanks for your input. – Jean-François Beauchef Jun 09 '16 at 14:37
  • There's something ironic about flyway, whose sole job is to assist in version migrations, having its own version migration issues. – Magnus Mar 12 '19 at 03:05

6 Answers6

38

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.

Community
  • 1
  • 1
Daniel Käfer
  • 4,458
  • 3
  • 32
  • 41
  • 1
    Can flyway-maven-plugin just read the datasource properties from my Spring's Boot application.properties file? – ieXcept Mar 29 '17 at 11:55
  • 1
    @Daniel Käfer How about using FlywayCallback to clean up the data in schema_version (i.e delete the failed migration ) before the migration starts? – Dharmvir Tiwari Nov 01 '17 at 06:22
  • @DharmvirTiwari I don't think this is a good idea. If it fails the first time it will also fail the second time. You have to do something to make it work and you can manuel remove the row in the schema_version table. – Daniel Käfer Nov 06 '17 at 11:25
21

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

@Bean
public FlywayMigrationStrategy cleanMigrateStrategy() {
    return flyway -> {
        flyway.repair();
        flyway.migrate();
    };
}
Javadroider
  • 2,280
  • 1
  • 22
  • 45
  • 1
    This worked perfectly as I don't have access to the db itself. Cheers! – jason adams Mar 22 '20 at 01:29
  • 2
    Added this. thanks nice hint. @ConditionalOnProperty(value = "custom.spring.flyway.repair-on-migrate", havingValue = "true") @Primary – Tama Mar 24 '20 at 11:33
  • Note: This is not enough when upgrading from spring-boot 1.5 to 2.x, as Flyway 5 is not backward compatible with the 3.0 schema (oh the irony!). [This question](https://stackoverflow.com/questions/49945098/how-to-upgrade-from-flyway-3-directly-to-flyway-5/50390573) provides answers for spring-boot upgrades. – dube Dec 01 '20 at 17:01
  • "Just delete the row with the failed migration. After that you can run the migration again.": this does not work if the migration contained DDL statements and the DBMS does not support DDL transactions. The migration may have failed in the middle and re-running it from scratch may cause other errors. – Mauro Molinari Oct 04 '21 at 13:09
5

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/

Felipe Pereira
  • 1,368
  • 16
  • 26
  • Can flyway-maven-plugin just read the datasource properties from my Spring's Boot application.properties file? – ieXcept Mar 29 '17 at 11:54
  • @ieXcept What kind of information would you like to add to your properties file ? – Felipe Pereira Mar 29 '17 at 13:36
  • 1
    I've already add `spring.datasource.url`, `spring.datasource.username`, `spring.datasource.password`. That's how my app connects to db. Can I reuse this properties for the plugin? – ieXcept Apr 01 '17 at 16:06
  • @ieXcept yes you can !! – Felipe Pereira Apr 01 '17 at 16:31
  • 1
    @ieXcept could I suggest this plugin I'm working on? Includes a command to generate migrations https://github.com/tom-power/spring-boot-flyway-migrate – Tom Power Apr 06 '17 at 07:25
5

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.

BishalG
  • 1,414
  • 13
  • 24
  • 1
    This method is great! Much more convenient than manually typing maven commands, thank you so much! – mzoz Sep 25 '19 at 12:59
2

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
xilef
  • 2,199
  • 22
  • 16
0

I followed this procedure:

delete from flyway_schema_history where installed_rank = 2

Worked fine, very fast and easy to apply, solved the problem, the migration was executed successfully!

Rowi
  • 545
  • 3
  • 9
Alexandre Ferreira
  • 1
  • 2