Phil Factor

20 August 2024

• Flyway
• Database migrations

Flyway’s Repair Command Explained Simply

The “Flyway Commands Explained Simply” series of articles provides simple, visual explanations of what each Flyway command does, how it works, how and how not to use it:

What does Flyway repair do?

Before Flyway performs a command, it runs a validation process, described in detail in Flyway’s Validate Command Explained Simply. It checks how the database was created, as recorded in the Flyway Schema History (FSH) table, to make sure that it can recreate the current version of the database accurately. If it detects a problem that means it can’t, subsequent attempts to migrate the database will fail with validation errors and be rolled back. Flyway will not perform the command until you intervene and fix the issue.

Once you’re certain that the current state of the database, and the set of migration files for the project, are exactly as intended, you may then need to run flyway repair before you can proceed with further migrations on the affected databases. The Repair command will modify the details recorded in the schema history table of the connected database, for applied migrations, to make them consistent with the data and metadata of the current set of Flyway migration files. Migrations can now proceed normally.

What problems will Flyway Repair fix?

Flyway Repair will deal with validation problems relating to migrations that failed partway through but could not be rolled back, as well as migration files that were retrospectively ‘removed’ or altered, as summarized in the following table:

Having run a Repair, Flyway will report the actions taken and list all the files whose entries in the table have been changed. This is easiest viewed if you request an output type of JSON using the -outputType=json parameter.

Flyway repair cannot be used to fix “resolved migration not applied” validation errors relating to files retrospectively injected into an applied sequence, which are listed as Ignored in the FSH. Instead, we need to use undo migrations, or the clean command, or consider use of –outOfOrder and –skipExecutingMigrations parameters, for example if an ignored migration was due to a retrospective hotfix.

Safely running Flyway Repair

You should never run Flyway Repair until you’ve fixed the issue that Flyway has discovered. The routine assumes that you’ve done so. Flyway knows that something isn’t right but doesn’t know why, which is why the command is ‘repair’ rather than ‘fix’; all it does is modify entries in the FSH to “conform to the new reality”

In normal usage it is rare to need the Flyway Repair command, but there are some situations when it may be necessary, such as to remove all traces of a part-failed migration.

Remove all records for a failed migration

A migration run might fail part way through with a terminal error, and Flyway can’t roll back all the changes. This should be a rare occurrence, but if you are using an RDBMS such as MySQL, MariaDB or H2 that has no rollback of DDL statements, it may happen occasionally. For example, see this sad sea of red…

1 row created.
1 row created.
1 row created.
1 row created.
flyway : ERROR: Migration of schema "dbo" to version "1.2 RecklessAddition" failed! Please restore backups and roll back 
database and code!
At line:34 char:5
+     flyway $Extraparameters migrate }
+     ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (ERROR: Migratio...abase and code!:String) [], RemoteException
    + FullyQualifiedErrorId : NativeCommandError

The Flyway info command will reveal one or more migrations recorded in the FSH table with a status of ‘failed’.

Flyway Enterprise Edition 10.11.0 by Redgate
Database: jdbc:oracle:thin:SYSTEM/********@clone-external.red-gate.com:30110:ORCLCDB (Oracle 19.0)
Schema version: 1.1
+-----------+---------+------------------------------+--------+---------+----------+
| Category  | Version | Description                  | Type   | State   | Undoable |
+-----------+---------+------------------------------+--------+---------+----------+
|           |         | << Flyway Schema Creation >> | SCHEMA | Success |          |
| Versioned | 1.1     | ClassicNorthwind-Oracle      | SQL    | Success | No       |
| Versioned | 1.2     | RecklessAddition             | SQL    | Failed  | No       |
+-----------+---------+------------------------------+--------+---------+----------+

If you correct the error and try again, Flyway will refuse to run the migration because it first runs the Validate process and this will fail if the file is marked as having failed.

Flyway Enterprise Edition 10.11.0 by Redgate
Database: jdbc:oracle:thin:SYSTEM/********@clone-external.red-gate.com:30110:ORCLCDB (Oracle 19.0)
flyway : ERROR: Validate failed: Migrations have failed validation
At line:27 char:25
+             $commands | foreach{ flyway $Extraparameters $_ | where {
+                                  ~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (ERROR: Validate...iled validation:String) [], RemoteException
    + FullyQualifiedErrorId : NativeCommandError
 
Detected failed migration to version 1.2 (RecklessAddition).
Please remove any half-completed changes then run repair to fix the schema history.
Need more flexibility with validation rules? Learn more: https://rd.gt/3AbJUZE

If this is a development database, it’s probably quicker to use flyway clean and then rerun the migrations. If it’s a production database, hopefully you’ve a rollback procedure all prepared and tested that will restore any of the fully or partially altered database objects to their state at the previous successful version. Then, run the Flyway Repair command to remove the record of these failed migrations in the Flyway Schema History table:

Flyway Enterprise Edition 10.11.0 by Redgate
Database: jdbc:oracle:thin:SYSTEM/********@clone-external.red-gate.com:30110:ORCLCDB (Oracle 19.0)
Successfully repaired schema history table "dbo"."flyway_schema_history" (execution time 00:01.097s).
Manual cleanup of the remaining effects of the failed migration may still be required.

Flyway simply removes the failed migration entry from FSH and the next time the validation process is run, as by the info command for example, the migration is added with the status marked as ‘pending’.

An alternative, for an experienced user of RDBMSs such as MySQL is to manually remove the failed migration entries from the FSH. Armed with the name and location of the Flyway schema table, which is configurable and so can change, you can run:

or

Now, when you run the migration again, and you’ve corrected the SQL that caused the error, you’ll see the migration successfully applied. This is generally the approach used in an afterMigrateError callback to clean up the Flyway schema history table after a failed MySQL migration. A working version might need to use the built-in placeholders to determine the settings for the name and location of the Flyway Schema History table.

Mark all missing migrations as “deleted”

There are many simple reasons why the migrations available to Flyway can seem to have changed or ‘disappeared’. Perhaps there is a network fault. Perhaps you provided the wrong list of locations or just used the wrong project folder for the migration. If you blindly run repair without first investigating the cause, you’ll cause more problems than you started with, and then need to mend the damage caused by the repair.

For example, let’s say Flyway reports certain files as missing. You assume they were deliberately removed and run repair and it marks them as Deleted. In fact, they were still there but Flyway couldn’t find them because the locations parameter was wrong. You would then need fix the broken reference and manually remove all the rows marked as Deleted in the FSH, with a SQL Delete statement.

If files are only missing in the sense that Flyway can’t currently find them, all you need to do is tell Flyway where they are. If they are deliberately missing, perhaps because you consolidated several migrations into a single file, then either you can run repair to mark the missing files as deleted (and to adjust checksums of the consolidated file), or you can consider alternative strategies using Flyway configuration options (covered shortly).

Fixing discrepancies caused by file alterations

If you alter the contents of a migration file that’s already applied to a database, even if you’ve just added comments or format the SQL, the file checksum will have changed, and you’ll need to use the Repair command to reconcile these differences, before proceeding with further migrations. Before you do so, it is always best to check in your version control system to establish what has changed and to make sure that no metadata changes were made by the alterations in the file.

If you select one or more applied migration files in the migration locations and decide to improve the descriptions in the filenames or change the file type, then you’ll also need to run repair to reconcile the entries in FSH. One might think it would be done automatically, but Flyway needs confirmation that it wasn’t an accident.

Alternatives strategies using configuration parameters

If the file is ‘temporarily misplaced’, you might consider the flyway.skipExecutingMigrations configuration item in a parameter when running migrate to update the FSH, telling Flyway that the migrations are applied again, but without actually executing them (this feature requires Flyway Teams or Enterprise edition).

If an applied migration file has purposefully been removed from the migration location, Flyway provides a configuration item, -ignoreMigrationPatterns, that is accepted by the Repair command (as well as Validate) to list any missing migration files that should be ignored. If we specify for -ignoreMigrationPatterns a list of the migrations files that are no longer required for the new version, then Flyway will just issue a warning instead of an error and continues normally. The only time this will fail is when the most recently applied migration is removed, Flyway has no way to know it is missing and will mark it as ‘future’ instead.

Conclusion

The Flyway Repair command can help to resolve inconsistencies, and recover from failed migrations, but it should be used only to tidy up after a problem that Flyway’s validation process detected, has been solved. You first need to understand and fix the problem, and carefully review the reasons for metadata discrepancies to be sure that it won’t result in version drift.

Although the validation process will also detect many errors of operation and configuration, it is most essential to avoid the version drift that precipitates many deployment problems, when you’ve done your testing and sign-off on a release that upgrades a different database to the one that is actually in production.