FlywaySqlException: Unable to insert row for version `11` in Schema History table `schema_version`: Field `version_rank` doesn't have a default value

Question

I'm getting this error when running my Spring Boot app with a new migration. It has worked for 10 migrations so far. The field does indeed not have a default value. There shouldn't be a need for a default because Flyway should be inserting the value 11 in that field.

Caused by: org.flywaydb.core.internal.exception.FlywaySqlException:
Unable to insert row for version '11' in Schema History table `app`.`schema_version`
--------------------------------------------------------------------------------------------
SQL State  : HY000
Error Code : 1364
Message    : Field 'version_rank' doesn't have a default value

    at org.flywaydb.core.internal.schemahistory.JdbcTableSchemaHistory.doAddAppliedMigration(JdbcTableSchemaHistory.java:174) ~[flyway-core-5.1.4.jar:na]
    at org.flywaydb.core.internal.schemahistory.SchemaHistory.addAppliedMigration(SchemaHistory.java:170) ~[flyway-core-5.1.4.jar:na]
    at org.flywaydb.core.internal.command.DbMigrate.applyMigrations(DbMigrate.java:299) ~[flyway-core-5.1.4.jar:na]
    at org.flywaydb.core.internal.command.DbMigrate.migrateGroup(DbMigrate.java:244) ~[flyway-core-5.1.4.jar:na]
    at org.flywaydb.core.internal.command.DbMigrate.access$100(DbMigrate.java:53) ~[flyway-core-5.1.4.jar:na]
    at org.flywaydb.core.internal.command.DbMigrate$2.call(DbMigrate.java:163) ~[flyway-core-5.1.4.jar:na]
    at org.flywaydb.core.internal.command.DbMigrate$2.call(DbMigrate.java:160) ~[flyway-core-5.1.4.jar:na]
    at org.flywaydb.core.internal.database.mysql.MySQLNamedLockTemplate.execute(MySQLNamedLockTemplate.java:60) ~[flyway-core-5.1.4.jar:na]
    at org.flywaydb.core.internal.database.mysql.MySQLConnection.lock(MySQLConnection.java:80) ~[flyway-core-5.1.4.jar:na]
    at org.flywaydb.core.internal.schemahistory.JdbcTableSchemaHistory.lock(JdbcTableSchemaHistory.java:150) ~[flyway-core-5.1.4.jar:na]

pom.xml

<dependency>
  <groupId>org.flywaydb</groupId>
  <artifactId>flyway-core</artifactId>
  <version>5.1.4</version><!--$NO-MVN-MAN-VER$-->
</dependency>

application.properties

# Prevent complaints when starting migrations with existing tables.
flyway.baselineOnMigrate = true
flyway.table=schema_version

schema_version

| Field          | Type          | Null | Key | Default           | Extra |
+----------------+---------------+------+-----+-------------------+-------+
| version_rank   | int(11)       | NO   | MUL | NULL              |       |
| installed_rank | int(11)       | NO   | MUL | NULL              |       |
| version        | varchar(50)   | NO   | PRI | NULL              |       |
| description    | varchar(200)  | NO   |     | NULL              |       |
| type           | varchar(20)   | NO   |     | NULL              |       |
| script         | varchar(1000) | NO   |     | NULL              |       |
| checksum       | int(11)       | YES  |     | NULL              |       |
| installed_by   | varchar(100)  | NO   |     | NULL              |       |
| installed_on   | timestamp     | NO   |     | CURRENT_TIMESTAMP |       |
| execution_time | int(11)       | NO   |     | NULL              |       |
| success        | tinyint(1)    | NO   | MUL | NULL              |       |

How am I going to create a migration to add a default value if the migrations don't even work?

Flyway 5.1.4, Spring Boot 5.1.13, mysql Ver 15.1 Distrib 10.1.30-MariaDB, for CYGWIN (i686)

Answer 1

It looks like this problem occurs if you upgrade from Flyway 3.x straight to 5.x, bypassing 4.x.

Spring Boot 1.5 uses Flyway 3.x by default, whereas Spring Boot 2.x uses Flyway 5.x.

From the Flyway 5.0.0 release notes:

Important note for users upgrading from Flyway 3.x: This release no longer supports a schema history table upgrade from Flyway 3.x. You must upgrade to Flyway 4.2.0 first before upgrading to Flyway 5.0.0.

From the Spring Boot 2 Migration Guide:

Upgrading to Spring Boot 2 will upgrade Flyway from 3.x to 5.x. To make sure that the schema upgrade goes smoothly, please follow the following instructions:

• First upgrade your 1.5.x Spring Boot application to Flyway 4 (4.2.0 at the time of writing), see the instructions for Maven and Gradle.

• Once your schema has been upgraded to Flyway 4, upgrade to Spring Boot 2 and run the migration again to port your application to Flyway 5.

The migration guide also links to a blog post with an alternative approach.

There was issue raised about Spring Boot skipping Flyway 4.x and one against Flyway for the version_rank exception you were getting.

Looks like quite a few others have hit this same problem.

Answer 2

Had to

• manually add a default value to the field by writing it into the top of the migration that already ran but would not save

  alter table schema_version alter column version_rank set default 0;
  

• comment the other lines in the migration because they already ran

  -- alter table myTable add column createdAt date;
  -- ...
  

• migrate again to add the row to schema_version

  mvn flyway:migrate -Dflyway.user=user -Dflyway.password= -Dflyway.url=jdbc:mysql://localhost:3306/app -Dflyway.table=schema_version
  

• uncomment the lines in the migration so they will be applied down the pipeline

• validate

  mvn flyway:validate -Dflyway.user=user -Dflyway.password= -Dflyway.url=jdbc:mysql://localhost:3306/app -Dflyway.table=schema_version
  
  [ERROR] -> Applied to database : 1445435712
  [ERROR] -> Resolved locally    : -1275756780
  

• copy the checksum into the schema_version table manually so it won't complain in development anymore

  update schema_version set checksum = -1275756780 where version = 11;
  

Hopefully it will just work in staging & production.

Maybe FlyWay should use Rails migrations to manage its own schema?

I know DB versioning is a hard problem and FlyWay is supposed to make it easier, but sometimes it feels like it's more work than just using custom SQL scripts or manual deployment checklists. I couldn't find any mention of this problem, and my last migration was around the end of July. Maybe everyone had stopped using it by now? I've only been using it since May and have had so many problems (table name change, checksum calculation change, now this).