Flyway is a database migration tool that uses SQL scripts (which they call migrations) to create and modify the database schema and the data stored in the database. The migrations have a version number, which is used to apply them in order. When the migrations run for the first time, they are added to a schema history table, which by default is called flyway_schema_history. This table keeps a checksum for each migration, which is verified whenever the application starts up (to make sure the migration has not changed after it was applied to the database). As a general guide, we should create new migrations to make changes, instead of changing existing migrations.
This article discusses common errors that you might run into while working with Flyway and possible solutions. Flyway provides tools such as Flyway Repair, which can be used to automatically update the flyway_schema_history table. I have not been able to use that tool on my project due to security restrictions, so my solutions make changes directly to the flyway_schema_history table. I’m assuming you’re working in a development environment; some of the solutions might not be appropriate if you’re in a production environment.
I created this sample app, which can be useful for troubleshooting Flyway errors using a simple environment.
Table of Contents
Flyway failed to initialize
Flyway failed to initialize: none of the following migration scripts locations could be found:
– classpath:db/migration
Cause #1: Your application doesn’t have any migrations yet.
Disable flyway by adding the following property to the application properties:
spring.flyway.enabled=falseCode language: JavaScript (javascript)Cause #2: Your migrations are not in the default folder: resources/db/migration.
Specify the folder where your migration scripts are located in the application properties. For example, if the migrations are located in resources/database, use:
spring.flyway.locations=classpath:databaseMigration checksum mismatch
org.flywaydb.core.api.exception.FlywayValidateException: Validate failed: Migrations have failed validation
Migration checksum mismatch for migration version X.X
-> Applied to database : 2145208862
-> Resolved locally : -1101818402. Either revert the changes to the migration, or run repair to update the schema history.
Cause: A migration that was already applied to the database has been modified. This changes the migration checksum, which is used by Flyway on startup to validate that existing have not changed, thus failing validation.
Avoid changing migrations that already ran. Any changes should be done in a new migration script.
Alternate solution when working in a development environment
If you’re working in a development environment, it might be acceptable to modify a migration after it has been applied to the database. In that case, you can fix this error by deleting the migration record from the flyway_schema_history table. This will cause the updated migration to run. Note that if the original and updated migrations have statements such as CREATE, DROP, etc. you will need to first undo the changes done by the original migration in the database or you will run into SQL exceptions.
If the changes made to the migration were just a formatting change (e.g. you added some blank lines or changed the indentation), you can update the checksum directly in the flyway_schema_history table so it matches the checksum of the updated migration, instead of running the migration script again.
Detected applied migration not resolved locally
org.flywaydb.core.api.exception.FlywayValidateException: Validate failed: Migrations have failed validation
Detected applied migration not resolved locally: X.X. If you removed this migration intentionally, run repair to mark the migration as deleted.
Cause: The code is missing a migration script that was previously applied to the database.
This can happen when multiple people are creating Flyway migrations at the same time. In that case, update your code, so it includes the migration script specified in the error message.
If the migration script was deleted on purpose, then delete the migration record from the flyway_schema_history table to fix this error. Note that deleting the migration record does not undo the changes done by the migration when it first ran.
FlywaySqlScriptException
org.flywaydb.core.internal.sqlscript.FlywaySqlScriptException:
Migration VX.X.X__script_name.sql failed
Cause: There was a SQL error while running a migration script.
Flyway includes a detailed error message about what went wrong; the solution will vary depending on the SQL error. After you fix the migration, delete the failed migration record from flyway_schema_history and start the app again.
This is an example of an error message provided by Flyway; in this case the migration failed because it was trying to create a table that already existed:
SQL State : 42S01
Error Code : 1050
Message : Table 'application_user' already exists
Location : database/V1.0__create_user_table.sql
Line : 1
Statement : CREATE TABLE application_user(
id BIGINT PRIMARY KEY AUTO_INCREMENT NOT NULL,
first_name VARCHAR(100) NOT NULL,
last_name VARCHAR(100) NOT NULL,
user_role VARCHAR(100) NOT NULL,
date_created TIMESTAMP
)Code language: plaintext (plaintext)Since I test SQL scripts by running them locally, the most common SQL exceptions I’ve run into have to do with version compatibility. For example, if I’m running MySQL 8.0 locally, but the application uses MySQL 5.6 and I write a migration script that uses the ALTER statement with the rename column clause to update a column name; since the rename column clause is only available starting with MySQL 8.0, the script fails with a SQL error when executed on the older MySQL server.
Error when running a previously failed migration
org.flywaydb.core.api.exception.FlywayValidateException: Validate failed: Migrations have failed validation
Detected failed migration to version X.X (script_name). Please remove any half-completed changes then run repair to fix the schema history.
Cause: A migration failed the first time it was run (e.g. it had a SQL exception). You fixed the migration script, but run into this error when you start the app.
Since you have made changes to the script (causing the checksum to change) you will need to delete the migration record from flyway_schema_history, so the updated script runs. You can identify migrations that failed the first time they ran using the success column, which shows ‘0’.
Found more than one migration with version X.X
org.flywaydb.core.api.FlywayException: Found more than one migration with version X.X
Offenders:
-> C:\flyway-project\target\classes\database\VX.X__script1.sql (SQL)
-> C:\flyway-project\target\classes\database\VX.X__script2.sql (SQL)
Cause: You have two migrations with the same version.
This can happen when multiple people are creating Flyway migrations at the same time. The version number must be unique for each migration. To resolve this error, rename one of the migrations to the next available version.
Detected resolved migration not applied to database
Detected resolved migration not applied to database: X.X. To ignore this migration, set -ignoreIgnoredMigrations=true. To allow executing this migration, set -outOfOrder=true
This can happen when multiple people are creating Flyway migrations at the same time, since by default, Flyway expects the migrations to be applied in order according to the version number.
Let’s say you worked on migration version 2.0 and applied it to the database. After that another developer submits their code changes, which include a new migration version 1.4. When you deploy the application, you will get the error above since you have a migration to be applied (version 1.4), which is prior to the latest migration already applied to the database (2.0).
If you’re in a development environment, you can include the out-of-order flag in your application properties to allow migrations to be applied regardless of their version number. Note that this property makes the database migration not reproducible; if you ran the migrations from scratch, you could end up with different results (even errors) since the migrations will run in order this time.
spring.flyway.outOfOrder=trueCode language: JavaScript (javascript)