How to upgrade from flyway 3 directly to flyway 5

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

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

Tags:

Flyway

Recent Posts