Phil Factor

5 April 2024

• Flyway
• Database Deployments
• Applying hotfixes

How to Fix or Avoid ‘Ignored’ Migrations in Flyway

Flyway is designed to tell you when you’ve made a mistake but not to punish you for it. When, for example, you add a migration file with an ‘out of sequence’ version number, Flyway won’t execute the file. Instead, it just ignores it and lets you know. Why? Flyway has assumed that you’ve made a mistake because any retrospective alterations to an existing sequence of migrations will change what is now defined as the current version. It can’t read your mind (yet), so you need to specify what you intended.

The problem of retrospective fixes in Flyway

Imagine that you’ve suddenly spotted an error in the chosen datatype for one of the columns in the V1.1.3 file. You need to fix the mistake, but the database has already moved on to V1.1.4. How do you do it?

Well, you might try to modify the existing V1.1.3 file to correct the mistake, but Flyway will subsequently raise a validation error, suggesting you either need to put the migration file back how it was or run a repair:

flyway : ERROR: Validate failed: Migrations have failed validation
At line:1 char:1
+ flyway migrate '-target=1.1.5'
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (ERROR: Validate...iled validation:String) [], RemoteException
    + FullyQualifiedErrorId : NativeCommandError
 
Migration checksum mismatch for migration version 1.1.3
-> Applied to database : 1488297774
-> Resolved locally    : -380831323
Either revert the changes to the migration, or run repair to update the schema history.

Flyway won’t let you tamper with a migration file once it’s been applied because that would change what is now defined as the current version (V1.1.4). Neither can you, instead, correct the error by inserting a new migration file (e.g. V1.1.3.1) into the existing sequence. Flyway marks the new migration file as ‘ignored’ in the Schema History table…

…And if you retry the migration, you still see “validate failed” error albeit in a slightly different guise:

flyway : ERROR: Validate failed: Migrations have failed validation
At line:1 char:1
+ flyway migrate '-target=1.1.4'
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (ERROR: Validate...iled validation:String) [], RemoteException
    + FullyQualifiedErrorId : NativeCommandError
 
Detected resolved migration not applied to database: 1.1.3.1.
To ignore this migration, set -ignoreMigrationPatterns='*:ignored'. To allow executing this migration, set -outOfOrder=true.
Need more flexibility with validation rules? Learn more: https://rd.gt/3AbJUZE

When we run Flyway migrate, Flyway will first execute a process called validate that examines all the migration files that are specified by the configuration. It must do this to make sure that the database can be reliably recreated from its scripts, meaning that nothing is missing, altered or added.

It examines the sequence of files that have been used to achieve the current version and if all is well, it executes the pending migrations with a higher version number to reach the version you specify. However, you’ll see the “validate failed” error if Flyway detects insertions of new files with a lower version than the current one, or deletions of files or alterations to files already applied. In fact, Flyway will no longer execute any ‘Ignored’ file even if you first migrate to a lower version via undo migration files. In other words, it’s permanently out of order.

How to resolve the problem

Sometimes, an ‘out of sequence’ migration happens just because a developer makes a ‘typo’ when adding a new migration, giving it the wrong version number in the filename. It can also occur if a developer retrospectively adds a migration step for running tests, defining new user permissions or roles, or importing data for an older version of the database that is still in use.

However, the most common causes of ‘out of sequence’ migrations are the need to fix a problem with a previous migration, or to fix a mistake in a deployment with a hotfix. When a problem is detected with a migration, either before or after release, then ideally the fix will be enshrined in a migration file and immediately added to the migrations in the Flyway project before the development source has moved on to a new version.

If not, then they can cause problems because, as we’ve seen, we can’t retrospectively inject the file into a migration chain simply by giving it a version number that fits into the right place in the sequence. However, we will often need to have Flyway execute these ‘ignored’ files. So how do we do it without having to clean and recreate all existing copies of the database that are already at a higher version?

The ‘Out-Of-Order’ parameter to the rescue

Flyway has a slightly strange feature that allows you to execute a migration step retrospectively out of the correct version order. It’s not one of those features you’d want to use often, but it helps to resolve the sort of problems we’ve described.

To change Flyway’s behavior when it detects the addition of a file with a lower version number than the current version of the database, simply alter the Flyway configuration so that the outOfOrder parameter is set to true. Once you’ve done this, migrations that suddenly appear retrospectively will be executed, but will have a different state after being applied by Flyway. In the schema history table, they will be flagged as ‘Out of Order‘ rather than ‘Successful‘. With Flyway’s info command, the files will appear as ‘Pending‘ if they haven’t yet been used.

In this example, we’ve given the ‘fix’ migration a version number for the correct point in the sequence, directly after the flawed V1.1.3 file, but we’re instructing Flyway to apply it ‘out of order’ on this copy of the database, and then we’d need to do the same on any others where the file is marked as ‘ignored’. For any copies that are at versions V1.1.3 or lower, or that we build from scratch from the same migration files, there will be no problem as it will be applied directly after the flawed migration file.

The ‘SkipExecutingMigrations’ parameter

Imagine your team does a release of the database (V1.4, say) that goes so badly wrong that the DBA must do a hotfix. The right way to do this is to have Flyway apply the hotfix, using a versioned migration file that increments the version to V1.4.1, to show that the hotfix has been applied. However, instead, the DBA applies it directly to the production server using an IDE.

Sometimes, it is unavoidable, and I’ve done it myself deliberately more than once, in cases where the service was compromised, and an urgent fix was needed. Other times, you may not have access to the migration files, or you may need to run diagnostic queries alongside the code to do the fix and must work fast. More commonly, the idea of creating and applying the fixes as a migration step just gets lost in the excitement of a deployment hitch.

Whichever way it happened, the ad-hoc changes you had to make to Staging or Production must be ‘retrofitted’ to the collection of migrations, as a new version increment. As we’ve seen already, we can still add the patch as a V1.4.1 migration file. This allows us to put a true representation of what is in production into the main and develop branches. We can also use the outOfOrder parameter to allow any copies of the database already at a later version (V1.5, say) to receive the patch, retrospectively (assuming changes made to produce V.1.5 haven’t invalidated the original patch).

However, we have one more problem. Even though we’ve already applied the patch to production, it still has the previous version number (V1.4). When we run the next Flyway deployment (to release V1.5, say), Flyway will attempt to reapply the V1.4.1 patch migration and this will likely cause an error, unless the migration contains rigorous checks that stop it running if the alterations have already been made.

Therefore, as soon as we add V1.4.1 to the migration chain, we need to inform Flyway that the production database is already at this version and for this we need the SkipExecutingMigrations trick. Instead of running a normal flyway migrate, you first check with flyway info that there is just the single pending migration that contains what was the ad-hoc code for the patch. Then you run:

Flyway will not actually try to execute the contents of the migration on this environment, instead just updating the schema history to say it’s been applied. Now, all the environments are back in sync, and development can continue. If there were more than just the single pending migration with the patch code, you can skip past the patch by specifying the patch version as the end version of the migration, using the -target parameter along with SkipExecutingMigrations. Then the subsequent migrations can be applied as normal.

Using undo migrations

The Undo migrations of Flyway Teams will solve several of the problems that would otherwise lead to files being inserted retrospectively and then ignored. Not only does an undo migration put the database back to a previous version, but it will then allow you to clear out all your mistakes from the earlier versions that you’ve now reversed instead of trying to insert extra out-of-order migrations. If we ‘undo’ from V5 back to V2, Flyway will cease to care about changes we make to V3 and V4 for that database because it only checks files for changes whose version numbers are lower or equal to the current version. You won’t need to have inserted, and subsequently ignored, files if you’ve reversed back far enough.

Once you’ve attuned your brain to the idea of creating an undo file for each migration, development in a branch is suddenly a lot more carefree, unless you are sharing the database. It is like a world where your rash statements on the service formerly known as Twitter can be undone, along with all the responses to it, and the entire world forgets about them.

So, it is with undo. It allows you to move freely to-and-fro between versions, trying things out, without suffering the consequences. One problem, though: it isn’t particularly geared for teamwork: it’s best used in teams that do branching and merging on independent copies of the database.

Conclusions

It was once believed to be impossible to maintain strict versioning of databases. Nowadays, the are several tools that allow development teams to know for certain the version of all their copies of a database, and to allow them to be migrated to the version they need. This makes it much easier to deliver functionality, but it also means that you need ways of building in flexibility without compromising the principle that all databases at the same version must be identical in their metadata. There are times in development where you need freedom to experiment and try out different ways of approaching a database task. To do this, it is good to have built-in ways of allowing Flyway to audit the processes and maintain the versions. Flyway can alert you to a problem such as a file that’s changed or a new version file that’s appeared with an odd version number, but it needs you to guide it towards the best solution. It’s not easy to be certain of your intentions when a problem happens.