How to upgrade from flyway 3 directly to flyway 5

Question

Working on a product that is deployed by many clients in many production environments. It includes at least one Spring Boot app.

We've used flyway for db schema migrations. Upgrading from Spring Boot 1.5.x to 2.0.x has bumped our flyway version from 3.x to 5.x.

The Spring Boot migration guide simply says to upgrade to flyway 4 before the boot upgrade. However, this would require all of our customers to do an intermediate upgrade before being able to upgrade to the latest.

So, the question is: How would you upgrade from flyway 3 directly to flyway 5?

Answer 1

Step 0.

Upgrade to spring boot v2.1 (and therby implicitly to flyway 5).

Step 1.

Since schema_version was used in flyway 3.x let new flyway versions know that they should keep using this table.:

# application.yml
spring.flyway.table: schema_version # prior flyway version used this table and we keep it

Step 2.

Create file src/main/ressources/db/migration/flyway_upgradeMetaDataTable_V3_to_V4.sql for upgrading the meta table based on the dialect you use.

See https://github.com/flyway/flyway/commit/cea8526d7d0a9b0ec35bffa5cb43ae08ea5849e4#diff-b9cb194749ffef15acc9969b90488d98 for the update scripts of several dialects.

Here is the one for postgres and assuming the flyway table name is schema_version:

-- src/main/ressources/db/migration/flyway_upgradeMetaDataTable_V3_to_V4.sql
DROP INDEX "schema_version_vr_idx";
DROP INDEX "schema_version_ir_idx";
ALTER TABLE "schema_version" DROP COLUMN "version_rank";
ALTER TABLE "schema_version" DROP CONSTRAINT "schema_version_pk";
ALTER TABLE "schema_version" ALTER COLUMN "version" DROP NOT NULL;
ALTER TABLE "schema_version" ADD CONSTRAINT "schema_version_pk" PRIMARY KEY ("installed_rank");
UPDATE "schema_version" SET "type"='BASELINE' WHERE "type"='INIT';

Step 3.

Create Java file your.package/FlywayUpdate3To4Callback.java

Please note that this does the following:

• Run the sql script from Step 2
• call Flyway.repair()

// FlywayUpdate3To4Callback.java
package your.package;

import static org.springframework.core.Ordered.HIGHEST_PRECEDENCE;

import org.flywaydb.core.Flyway;
import org.flywaydb.core.api.callback.Callback;
import org.flywaydb.core.api.callback.Context;
import org.flywaydb.core.api.callback.Event;
import org.flywaydb.core.api.configuration.Configuration;
import org.springframework.context.annotation.Lazy;
import org.springframework.core.annotation.Order;
import org.springframework.core.io.ClassPathResource;
import org.springframework.core.io.Resource;
import org.springframework.jdbc.datasource.init.ScriptUtils;
import org.springframework.jdbc.support.JdbcUtils;
import org.springframework.jdbc.support.MetaDataAccessException;
import org.springframework.stereotype.Component;

import lombok.extern.slf4j.Slf4j;

@Component
@Order(HIGHEST_PRECEDENCE)
@Slf4j
public class FlywayUpdate3To4Callback implements Callback {
    private final Flyway flyway;

    public FlywayUpdate3To4Callback(@Lazy Flyway flyway) {
        this.flyway = flyway;
    }

    private boolean checkColumnExists(Configuration flywayConfiguration) throws MetaDataAccessException {
        return (boolean) JdbcUtils.extractDatabaseMetaData(flywayConfiguration.getDataSource(),
                callback -> callback
                        .getColumns(null, null, flywayConfiguration.getTable(), "version_rank")
                        .next());
    }

    @Override
    public boolean supports(Event event, Context context) {
        return event == Event.BEFORE_VALIDATE;
    }

    @Override
    public boolean canHandleInTransaction(Event event, Context context) {
        return false;
    }

    @Override
    public void handle(Event event, Context context) {
        boolean versionRankColumnExists = false;
        try {
            versionRankColumnExists = checkColumnExists(context.getConfiguration());
        } catch (MetaDataAccessException e) {
            log.error("Cannot obtain flyway metadata");
            return;
        }
        if (versionRankColumnExists) {
            log.info("Upgrading metadata table the Flyway 4.0 format ...");
            Resource resource = new ClassPathResource("db/migration/common/flyway_upgradeMetaDataTable_V3_to_V4.sql",
                    Thread.currentThread().getContextClassLoader());
            ScriptUtils.executeSqlScript(context.getConnection(), resource);
            log.info("Flyway metadata table updated successfully.");
            // recalculate checksums
            flyway.repair();
        }
    }
}

Step 4.

Run spring boot.

The log should show info messages similar to these:

...FlywayUpdate3To4Callback      : Upgrading metadata table the Flyway 4.0 format 
...FlywayUpdate3To4Callback      : Flyway metadata table updated successfully.

Credits

This answer is based on Eduardo Rodrigues answer by changing:

• Use Event.BEFORE_VALIDATE to trigger a flyway callback that upgrade flyway 3 to 4.
• more information on application.yml setup
• provide upgrade sql migration script

Answer 2

In case I'm not the last person on the planet to still be upgrading from 3 to 5.

Problem:

I wanted the upgrade to be transparent to other developers on the project as well as not requiring any special deployment instructions when upgrading on the live applications, so I did the following.

I had a look at how version 4 handled the upgrade:

• In Flyway.java a call is made to MetaDataTableImpl.upgradeIfNecessary
• upgradeIfNecessary checks if the version_rank column still exists, and if so runs a migration script called upgradeMetaDataTable.sql from org/flywaydb/core/internal/dbsupport/YOUR_DB/
• If upgradeIfNecessary executed, then Flyway.java runs a DbRepair calling repairChecksumsAndDescriptions

This is easy enough to do manually but to make it transparent. The app is a spring app, but not a spring boot app, so at the time I had flyway running migrations automatically on application startup by having LocalContainerEntityManager bean construction dependent on the flyway bean, which would call migrate as its init method (explained here Flyway Spring JPA2 integration - possible to keep schema validation?), so the order of bootstrapping would be:

Flyway bean created -> Flyway migrate called -> LocalContainerEntityManager created

Solution:

I changed the order of bootstrapping to:

Flyway bean created -> Flyway3To4Migrator -> LocalContainerEntityManager created

where Flyway3To4Migrator would perform the schema_table changes if needed, run the repair if the upgrade happened, and then always run flyway.migrate to continue the migrations.

@Configuration
public class AppConfiguration {

    @Bean
    // Previously: @DependsOn("flyway")
    @DependsOn("flyway3To4Migrator")
    public LocalContainerEntityManagerFactoryBean entityManagerFactory(DataSource dataSource) {
        ...
    }

    // Previously: @Bean(initMethod = "migrate")
    @Bean
    public Flyway flyway(DataSource dataSource) {
        ...
    }
}

@Component
@DependsOn("flyway")
public class Flyway3To4Migrator {
    private final Log logger = LogFactory.getLog(getClass());
    private Flyway flyway;

    @Autowired
    public Flyway3To4Migrator(Flyway flyway) {
        this.flyway = flyway;
    }

    @PostConstruct
    public void migrate() throws SQLException, MetaDataAccessException {
        DataSource dataSource = flyway.getDataSource();

        boolean versionRankColumnExists = checkColumnExists(dataSource);
        if (versionRankColumnExists) {
            logger.info("Upgrading metadata table to the Flyway 4.0 format ...");
            Resource resource = new ClassPathResource("upgradeMetaDataTable.sql", getClass().getClassLoader());
            ScriptUtils.executeSqlScript(dataSource.getConnection(), resource);
            logger.info("Metadata table successfully upgraded to the Flyway 4.0 format.");

            logger.info("Running flyway:repair for Flyway upgrade.");
            flyway.repair();
            logger.info("Complete flyway:repair.");
        }

        logger.info("Continuing with normal Flyway migration.");
        flyway.migrate();
    }

    private boolean checkColumnExists(DataSource dataSource) throws MetaDataAccessException {
        return (Boolean) JdbcUtils.extractDatabaseMetaData(
            dataSource, dbmd -> {
                ResultSet rs = dbmd.getColumns(
                        null, null,
                        "schema_version",
                        "version_rank");
                return rs.next();
            });
    }
}

A few things to note:

• At some point we will remove the extra Flyway3To4Migrator class and revert the configuration to the way it was.
• I copied the relevant upgradeMetaDataTable.sql file for my database from the v4 Flyway jar and simplified it to my table names etc. You could grab the schema and table names from flyway if you needed to.
• there is no transaction management around the SQL script, you might want to add that
• Flyway3To4Migrator calls flyway.repair(), which does a little more than DbRepair.repairChecksumsAndDescriptions(), but we were happy to accept the database must be in a good state before its run

Answer 3

If you're using Spring Boot, you can register a callback that does the upgrade on beforeMigrate(). The code is similar to @trf and looks like this:

@Component
@Order(HIGHEST_PRECEDENCE)
@Slf4j
public class FlywayUpdate3To4Callback extends BaseFlywayCallback {
    private final Flyway flyway;

    public FlywayUpdate3To4Callback(@Lazy Flyway flyway) {
        this.flyway = flyway;
    }

    @Override
    public void beforeMigrate(Connection connection) {
        boolean versionRankColumnExists = false;
        try {
            versionRankColumnExists = checkColumnExists(flywayConfiguration);
        } catch (MetaDataAccessException e) {
            log.error("Cannot obtain flyway metadata");
            return;
        }
        if (versionRankColumnExists) {
            log.info("Upgrading metadata table the Flyway 4.0 format ...");
            Resource resource = new ClassPathResource("upgradeMetaDataTable.sql",
                    Thread.currentThread().getContextClassLoader());
            ScriptUtils.executeSqlScript(connection, resource);
            log.info("Flyway metadata table updated successfully.");
            // recalculate checksums
            flyway.repair();
        }
    }

    private boolean checkColumnExists(FlywayConfiguration flywayConfiguration) throws MetaDataAccessException {
        return (boolean) JdbcUtils.extractDatabaseMetaData(flywayConfiguration.getDataSource(),
                callback -> callback
                        .getColumns(null, null, flywayConfiguration.getTable(), "version_rank")
                        .next());
    }

Notice that you don't need to manually call flyway.migrate() here.

Answer 4

The code above is not compatible with version 5. It uses deprecated classes. Here is an updated version.

import lombok.extern.slf4j.Slf4j;
import org.flywaydb.core.Flyway;
import org.flywaydb.core.api.callback.Callback;
import org.flywaydb.core.api.callback.Context;
import org.flywaydb.core.api.callback.Event;
import org.flywaydb.core.api.configuration.Configuration;
import org.springframework.context.annotation.Lazy;
import org.springframework.core.annotation.Order;
import org.springframework.core.io.ClassPathResource;
import org.springframework.core.io.Resource;
import org.springframework.jdbc.datasource.init.ScriptUtils;
import org.springframework.jdbc.support.JdbcUtils;
import org.springframework.jdbc.support.MetaDataAccessException;
import org.springframework.stereotype.Component;

import static org.springframework.core.Ordered.HIGHEST_PRECEDENCE;

@Component
@Order(HIGHEST_PRECEDENCE)
@Slf4j
public class FlywayUpdate3To4Callback implements Callback {
    private final Flyway flyway;

    public FlywayUpdate3To4Callback(@Lazy Flyway flyway) {
        this.flyway = flyway;
    }

    private boolean checkColumnExists(Configuration flywayConfiguration) throws MetaDataAccessException {
        return (boolean) JdbcUtils.extractDatabaseMetaData(flywayConfiguration.getDataSource(),
                callback -> callback
                        .getColumns(null, null, flywayConfiguration.getTable(), "version_rank")
                        .next());
    }

    @Override
    public boolean supports(Event event, Context context) {
        return event == Event.BEFORE_VALIDATE;
    }

    @Override
    public boolean canHandleInTransaction(Event event, Context context) {
        return false;
    }

    @Override
    public void handle(Event event, Context context) {
        boolean versionRankColumnExists = false;
        try {
            versionRankColumnExists = checkColumnExists(context.getConfiguration());
        } catch (MetaDataAccessException e) {
            log.error("Cannot obtain flyway metadata");
            return;
        }
        if (versionRankColumnExists) {
            log.info("Upgrading metadata table the Flyway 4.0 format ...");
            Resource resource = new ClassPathResource("db/migration/flyway_upgradeMetaDataTable_V3_to_V4.sql",
                    Thread.currentThread().getContextClassLoader());
            ScriptUtils.executeSqlScript(context.getConnection(), resource);
            log.info("Flyway metadata table updated successfully.");
            // recalculate checksums
            flyway.repair();
        }
    }
}

Answer 5

I tried to skip v4 too but didn't work. Running the repair from 3 to 5 will make the checksums correct, but won't change the schema_version format. That has changed as well.

It seems you need to go to v4 first. Even if temporarily just to run mvn flyway:validate, which will repair schema_version.

I've done this on this repo: https://github.com/fabiofalci/flyway-from-3-to-5/commits/5.0.7

The first commit is v3, the second commit is v4 (where I ran validate) and then the third commit on v5 the schema is correct.

Answer 6

It worked for me, except I had to put Event.BEFORE_VALIDATE again instead of Event.BEFORE_MIGRATE that was present in the last version of the FlywayUpdate3To4Callback Class. This is because I had a checksum not valid on a migration already run, so it needed to be fixed before validating and not before migrating. Thanks.