Spring-boot: Provide a clean migration path for Flyway

[axelfontaine]

For some reason Spring Boot 1.5 stuck with an ancient version of Flyway (Flyway 3.2.1 released almost 3 years ago on 2015-03-20).

Flyway 4.0 was released 2 years ago on 2016-02-29 and it introduced a new schema history table format. Flyway transparently migrated older schema history all throughout the 4.0 series which lasted until 4.2.0 which was released over a year later on 2017-04-27.

Flyway 5.0 was released on 2017-12-07 and dropped support for this migration as by that point users already had 22 months to migrate automatically. And they can still do so today, and here lies the clue, by first migrating to 4.2.0 and then to 5.0.x.

Now in terms of Flyway support, Spring Boot went straight from 2015 (Flyway 3.2.1 in Spring Boot 1.5) to 2018 (Flyway 5.0.x in Spring Boot 2.0), completely skipping the smooth transition period we had put in place.

This is not an issue for new users. They get the latest and greatest and it all works fine.

For existing users however, blindly upgrading causes the following issue: https://github.com/flyway/flyway/issues/1925

This is a problem.

Spring Boot should offer a better upgrade story to those users. At the very least it should document that when upgrading, users should first manually override the dependency version to 4.2.0 to give Flyway a chance to upgrade its internal structures, before making the move to 5.0.x.

If a 1.6 release of Spring Boot is planned, including Flyway 4.2.0 in there would certainly also help as Spring Boot users could then smoothly upgrade from Spring Boot 1.5 to 1.6 to 2.0.

[philwebb]

Thanks for bringing this to our attention. I'm not sure why we didn't get to the Flyway 4.0 upgrade in Spring Boot 1.5. Unfortunately we're not planning a Spring Boot 1.6 release so we'll need to find a way to easy the upgrade path in Boot 2.0.

It looks like the flyway upgrade to 5.0 was pretty much just a POM change. Hopefully that means we support 4.0 and 5.0 in Boot 2.0 and allow people to temporarily downgrade so they can migrate their schema.

[philwebb]

I wonder if we can also add a failure analyzer to catch guide users. This is the exception from https://github.com/flyway/flyway/issues/1925

Error Code : 1364
Message    : Field 'version_rank' doesn't have a default value

    at org.flywaydb.core.internal.schemahistory.JdbcTableSchemaHistory.doAddAppliedMigration(JdbcTableSchemaHistory.java:171) ~[flyway-core-5.0.6.jar:na]
    at org.flywaydb.core.internal.schemahistory.SchemaHistory.addAppliedMigration(SchemaHistory.java:146) ~[flyway-core-5.0.6.jar:na]
    at org.flywaydb.core.internal.command.DbMigrate.doMigrateGroup(DbMigrate.java:378) ~[flyway-core-5.0.6.jar:na]
    at org.flywaydb.core.internal.command.DbMigrate.access$400(DbMigrate.java:52) ~[flyway-core-5.0.6.jar:na]
    at org.flywaydb.core.internal.command.DbMigrate$5.call(DbMigrate.java:297) ~[flyway-core-5.0.6.jar:na]
    at org.flywaydb.core.internal.util.jdbc.TransactionTemplate.execute(TransactionTemplate.java:75) ~[flyway-core-5.0.6.jar:na]
    at org.flywaydb.core.internal.command.DbMigrate.applyMigrations(DbMigrate.java:294) ~[flyway-core-5.0.6.jar:na]
    at org.flywaydb.core.internal.command.DbMigrate.migrateGroup(DbMigrate.java:259) ~[flyway-core-5.0.6.jar:na]
    at org.flywaydb.core.internal.command.DbMigrate.access$300(DbMigrate.java:52) ~[flyway-core-5.0.6.jar:na]
    at org.flywaydb.core.internal.command.DbMigrate$4.call(DbMigrate.java:179) ~[flyway-core-5.0.6.jar:na]
    at org.flywaydb.core.internal.command.DbMigrate$4.call(DbMigrate.java:176) ~[flyway-core-5.0.6.jar:na]
    at org.flywaydb.core.internal.database.mysql.MySQLNamedLockTemplate.execute(MySQLNamedLockTemplate.java:60) ~[flyway-core-5.0.6.jar:na]
    at org.flywaydb.core.internal.database.mysql.MySQLConnection.lock(MySQLConnection.java:78) ~[flyway-core-5.0.6.jar:na]
    at org.flywaydb.core.internal.schemahistory.JdbcTableSchemaHistory.lock(JdbcTableSchemaHistory.java:148) ~[flyway-core-5.0.6.jar:na]
    at org.flywaydb.core.internal.command.DbMigrate.migrateAll(DbMigrate.java:176) ~[flyway-core-5.0.6.jar:na]
    at org.flywaydb.core.internal.command.DbMigrate.migrate(DbMigrate.java:145) ~[flyway-core-5.0.6.jar:na]
    at org.flywaydb.core.Flyway$1.execute(Flyway.java:1206) ~[flyway-core-5.0.6.jar:na]
    at org.flywaydb.core.Flyway$1.execute(Flyway.java:1168) ~[flyway-core-5.0.6.jar:na]
    at org.flywaydb.core.Flyway.execute(Flyway.java:1650) ~[flyway-core-5.0.6.jar:na]
    at org.flywaydb.core.Flyway.migrate(Flyway.java:1168) ~[flyway-core-5.0.6.jar:na]
    at org.springframework.boot.autoconfigure.flyway.FlywayMigrationInitializer.afterPropertiesSet(FlywayMigrationInitializer.java:66) ~[spring-boot-autoconfigure-2.0.0.RC1.jar:2.0.0.RC1]
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1769) ~[spring-beans-5.0.3.RELEASE.jar:5.0.3.RELEASE]
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1706) ~[spring-beans-5.0.3.RELEASE.jar:5.0.3.RELEASE]
    ... 18 common frames omitted
Caused by: java.sql.SQLException: Field 'version_rank' doesn't have a default value
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:965) ~[mysql-connector-java-5.1.45.jar:5.1.45]
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3973) ~[mysql-connector-java-5.1.45.jar:5.1.45]
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3909) ~[mysql-connector-java-5.1.45.jar:5.1.45]
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2527) ~[mysql-connector-java-5.1.45.jar:5.1.45]
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2680) ~[mysql-connector-java-5.1.45.jar:5.1.45]
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2484) ~[mysql-connector-java-5.1.45.jar:5.1.45]
    at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1858) ~[mysql-connector-java-5.1.45.jar:5.1.45]
    at com.mysql.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2079) ~[mysql-connector-java-5.1.45.jar:5.1.45]
    at com.mysql.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2013) ~[mysql-connector-java-5.1.45.jar:5.1.45]
    at com.mysql.jdbc.PreparedStatement.executeLargeUpdate(PreparedStatement.java:5104) ~[mysql-connector-java-5.1.45.jar:5.1.45]
    at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1998) ~[mysql-connector-java-5.1.45.jar:5.1.45]
    at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeUpdate(ProxyPreparedStatement.java:61) ~[HikariCP-2.7.6.jar:na]
    at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeUpdate(HikariProxyPreparedStatement.java) ~[HikariCP-2.7.6.jar:na]
    at org.flywaydb.core.internal.util.jdbc.JdbcTemplate.update(JdbcTemplate.java:334) ~[flyway-core-5.0.6.jar:na]
    at org.flywaydb.core.internal.schemahistory.JdbcTableSchemaHistory.doAddAppliedMigration(JdbcTableSchemaHistory.java:165) ~[flyway-core-5.0.6.jar:na]
    ... 40 common frames omitted

[wilkinsona]

I'm not sure why we didn't get to the Flyway 4.0 upgrade in Spring Boot 1.5

It was due to our standard policy of not moving to a new major version of a dependency in a minor release of Boot.

[snicoll]

@axelfontaine Why isn't that done automatically though? If I have a 3.x schema and I upgrade to 4 then to 5 it works but it doesn't if I ugprade from 3.x to 5? What is the rationale behind that?

[axelfontaine]

@snicoll Any internal structure change is performed automatically. But only in the next major release. This has been our policy from the start. You can upgrade transparently from one major to the next. We have long deprecation periods and 12+ month windows for this to give people the chance to upgrade at their best convenience with almost zero effort on their part.

If however, like was the case here, someone hasn't upgraded is almost 3 years and Flyway is several major revisions further, they need to migrate to the intermediate version(s) first.

All it takes is starting the Flyway 3.x app with Flyway 4.x once, then restarting it again with Flyway 5.x, done.

Note: the reason we limit it to one major release is simple. It lets us reduce our maintenance burden after a while by removing old cruft that only benefits very few users by that point. On the rare occasions such a change happens it is always very clearly documented in the release notes.

@philwebb I like the failure analyzer suggestion, as nobody RTFM.

[snicoll]

I've added a note in the migration guide

[snicoll]

@axelfontaine I am trying to reproduce the migration error to see how we can improve the user experience with a failure analyzer.

I've created a sample project

Once I've created the schema with 1.5.x(everything is fine), I upgrade to 2.0.0.RC1 to simulate the problem. Unfortunately, I get this exception instead:

Error creating bean with name 'flywayInitializer' defined in class path resource [org/springframework/boot/autoconfigure/flyway/FlywayAutoConfiguration$FlywayConfiguration.class]: Invocation of init method failed; nested exception is org.flywaydb.core.api.FlywayException: Validate failed: Migration checksum mismatch for migration version 1
-> Applied to database : 1661564199
-> Resolved locally    : 1160454704

Is that another issue?

[axelfontaine]

@snicoll Yes. There are a number of issue that can occur as these were all the changes that were performed automatically for users: https://github.com/flyway/flyway/issues/1117

I am aware this involves a bit more work, but if you wanted to detect this scenario proactively you could also check for the presence of the version_rank column in Flyway's schema history table.

[snicoll]

That exception is very generic, I am not sure I'll be able to add a failure analyzer for it.

I am aware this involves a bit more work, but if you wanted to detect this scenario proactively you could also check for the presence of the version_rank column in Flyway's schema history table.

Unfortunately, the failure analyzer infrastructure is triggered on a context that has failed (and is closed) so retrieving the DataSource or JdbcTemplate is not an option. Attempting to create that again in the FailureAnalyzer can lead to several other problems.

[snicoll]

We've discussed this and given the lack of a dedicated exception, we can't provide a FailureAnalyzer for this.

[krm1312]

I realize this is closed, but, this hurts upgrades. I don't suppose making boot 2.0 use flyway 4 by default is a valid option (or at least officially supported)? Otherwise, it seems the boot upgrade process has to be split across two releases of every upgrading application and deployments of those applications also must not go from V.N (sb 1.5) -> V.N+2 (sb 2) without first upgrading to V.N+1 (sb 1.5 + flyway 4).

[philwebb]

@krm1312 We decided that making Boot 2.0 use Flyway 4 rather than 5 would be worse than having a two step migration. We're not going to reverse that decision now. Do the migration instructions not work for you?

[JorgenRingen]

Also realise this is closed, but are there any known risks involved in running Spring Boot 1.5.x combined with Flyway 4.2.0? We're going to production with some new applications running Spring Boot 1.5.10 very soon. Won't have time to upgrade to Spring Boot 2.0 before release, so considering launching with Spring Boot 1.5.x and Flyway 4.2.0 to make the Spring Boot 2.0 upgrade smoother in the future. Haven't experienced any issues in testing, but wanted to make sure since it's a major-version bump at a late stage in a project.

[axelfontaine]

@JorgenRingen Many users run Spring Boot 1.5 with Flyway 4.2.0 and 5.0.x. Dependency upgrade is just like any other dependency:

• Check the release notes (We are very diligent about documenting every little change that could result in an incompatibility.)
• Upgrade in dev and validate
• Roll out in test and validate
• Roll out in prod

[krm1312]

@philwebb We will make it work and congrats on boot 2. The issue is not that the migration instructions don't work. The issue is an operational and communication issue. We don't have one instance of our application in production, we have 100s and they are under control of various operations teams/customers. So, this will end up with a big red "You must upgrade to version X before upgrading to version Y" in our release notes.

[wimdeblauwe]

For anybody that wants to avoid deploying 2 versions, I documented a workaround here: https://wimdeblauwe.wordpress.com/2018/08/30/tip-on-migration-to-spring-boot-2-when-using-flyway/

[philwebb]

Thanks @wimdeblauwe, I've added a link to your blog from the migration guide.

[schmidp]

My solution if anyone cares:

/**
 * Because Flyway changed its checksum algorithm and schema table and Spring skipped Flyway 4.0 we need to manually
 * handle the migration:
 * https://github.com/spring-projects/spring-boot/issues/12033
 *
 * So we are going to leave the repair in here until we know that all instances of this app have been migrated
 * Before running this application you need to update the flyway schema history table by running:
 *
 * CREATE TABLE `flyway_schema_history` (
 *   `installed_rank` int(11) NOT NULL,
 *   `version` varchar(50) DEFAULT NULL,
 *   `description` varchar(200) NOT NULL,
 *   `type` varchar(20) NOT NULL,
 *   `script` varchar(1000) NOT NULL,
 *   `checksum` int(11) DEFAULT NULL,
 *   `installed_by` varchar(100) NOT NULL,
 *   `installed_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
 *   `execution_time` int(11) NOT NULL,
 *   `success` tinyint(1) NOT NULL,
 *   PRIMARY KEY (`installed_rank`),
 *   KEY `flyway_schema_history_s_idx` (`success`)
 * ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 *
 * INSERT INTO flyway_schema_history (installed_rank, version, description, type, script, checksum, installed_by, installed_on, execution_time, success)
 * SELECT installed_rank, version, description, type, script, checksum, installed_by, installed_on, execution_time, success FROM schema_version;
 *
 * DROP TABLE schema_version;
 *
 */
@Bean
public FlywayMigrationStrategy cleanMigrateStrategy() {
    FlywayMigrationStrategy strategy = new FlywayMigrationStrategy() {
        @Override
        public void migrate(Flyway flyway) {
            flyway.repair();
            flyway.migrate();
        }
    };

    return strategy;
}

[hendisantika]

So, What should I to get issue Field version_rank doesn't have a default value on Spring Boot 2.1.3 get solved @axelfontaine ?

Sorry, Maybe I misleading.
Thanks

[chasegawa]

Hendi, the about SQL will create a new table that moves the relevant fields to a new table. The flyway.repair() command will correct the checksums.

We use a custom table name - I think we just need to drop the version_rank column from our table and then do the repair.

[hendisantika]

OK then. Thanks @chasegawa

[Amjad-hossain]

https://github.com/spring-projects/spring-boot/issues/12033#issuecomment-437221237
Just a Perfect solution!! Salute BOSS