Product articles Flyway Database migrations
Rollbacks, Undos and…

Rollbacks, Undos and Undonts

Rollback scripts are designed to allow us to recover safely from a failed deployment that leaves the database in an indeterminate state. They must check exactly what needs to be reverted before doing so. If you work with an RDBMS that cannot support transaction DDL rollback they are vital. This article proposes a strategy where you create and test a rollback file, at the same time as the forward migration, and reuse it as a Flyway undo script.

Guest post

This is a guest post from Phil Factor. Phil Factor (real name withheld to protect the guilty), aka Database Mole, has 30 years of experience with database-intensive applications.

Despite having once been shouted at by a furious Bill Gates at an exhibition in the early 1980s, he has remained resolutely anonymous throughout his career.

He is a regular contributor to Simple Talk and SQLServerCentral.

Have you ever got a message like this from Flyway?

‘Flyway : ERROR: Migration of schema zzzz to version “xxxx- yyyy yyyyy yyyy” failed! Please restore backups’ and roll back database and code!’

My guess is that you are using a relational database system such as MySQL, MariaDB, or SQLite that cannot roll back DDL code within a transaction. Flyway has hit a problem while running a migration but then found that it can’t rollback the transaction. The database will be left in an ‘indeterminate state’ and the migration will be listed as ‘failed‘ by Flyway, which won’t report a version until you clean up the mess.

If your database can roll back DDL transactions with a transaction, then you’re much less likely to see this message, because Flyway does the work for you, and you’ll be back at the existing version of the database. If not, then you will, as the error suggests, have two choices. Either you can roll back to the previous version with a script, or you can restore the previous version from a backup. I’ve already described the best way to recover from this problem in a previous article, Dealing with Failed SQL Migrations in MariaDB or MySQL. If you have, alongside the migration script that you’re developing, created an idempotent rollback script that you can execute directly against the database, then you have far fewer worries and development work becomes a lot more tranquil.

This rollback script just cleans up the parts of the migration file that succeeded before the error occurred. You just give a regretful chuckle and a wry smile before executing this file, via MySQL.EXE or the IDE (HeidiSQL in my case), and then running Flyway repair to ‘fix’ the Flyway Schema History table.

‘Undo’ migration scripts and rollback scripts

Flyway’s undo(U) migration files are for a different purpose from the ‘rollback’ file. Flyway Undo scripts revert a database from a specific version to a previous version. In other words, it has a securely defined start point. This also means that we can chain undo scripts together to move back several versions at once. There is plenty of use for a Flyway undo script, particularly when working in an isolated branch, but it won’t work on any failed migration that has left the database in an unknown state, and therefore with an unknown starting point for the undo.

A rollback script by contrast, must get you back safely from an unknown version, produced by a migration that part-failed leaving the database in an indeterminate state, to the starting version. Therefore, a rollback script must check what needs to be reverted before doing so. A rollback script can become a Flyway undo script, once the migration script is successfully committed, but a Flyway undo script cannot be used for a rollback unless you modify it heavily.

In this article, we’ll focus on the techniques you can use to revert a database cleanly to the current version if an attempted migration has left it in an indeterminate state, or even if you are just testing a possible migration by executing its code outside Flyway.

The ‘manual’ rollback: tidying up loose ends from a failed test or migration

Rollback scripts are often used in development work. For example, when running a database test, one generally creates temporary database objects such as procedures or tables to create the ‘test-harness’. You run the tests on your routines or batches to make sure that they work as specified, and then tear down the temporary harness. You don’t want any part of this harness in the build. The rollback script undoes, or tears down, all the components of the change that you make to the database.

In general, it is useful develop both the migration and rollback files at the same time. If you create the two together, you can, for example, iterate the development of your code, in an isolated branch, building and rolling back until it passes all its tests. At this stage, both scripts would be run manually via your IDE.

Having created this rollback script, when creating the forward migration, it makes sense to subsequently add it to the flyway location as a Flyway undo file. A Flyway undo file (U prefix) will always be executed in the correct context. See Secrets of Flyway Teams Undo for more on the basics of how Flyway undo files work. The result will be a Flyway undo script that will only execute on the intended version, and that can deal with the uncertainty of how far a migration got before hitting an error.

Flyway undo script

If, instead, you leave the rollback file as it is, you’ll want to add code to check the database version first: “does the last successful version = the version I’m designed to roll back to?“. With any rollback file, we must avoid the nightmare possibility of it being executed in the wrong context, even perhaps on a production system, at which point that casual DROP TABLE statement, run out of context without any thought about the data, could take on a sudden seriousness.

Conditional idempotent rollbacks

To quote my previous (SQL Server-specific) article on this topic, an idempotent script is one that “can be applied several times without changing the result beyond the first time it is run. It is really a fancy way of saying that your code must check first whether the database object has already had the change before you make the change.

Rollback SQL files are generally idempotent, meaning that they will work only with whatever bits of your migration are left after it fails. The migration might fail before it changed anything at all, or it succeed in making all its additions, deletions and amendments but then fail an integration test. It is impossible to predict at what point things went wrong.

The objective is to make a series of ‘conditional amendments’ that return the database to a known state, by removing whatever database objects were created by your migration, adding any that were removed, and returning any altered objects safely to their previous state. For example, before your rollback made a column nullable, it would first have to check that the column exists and that it’s currently NOT NULL.

Conditional amendments aren’t always a good idea, especially with forward migrations. An error here might be a wake-up call. For some types of usage, the intermediate error you’ll get if you try to, for example, change the datatype of a column that isn’t there, is a useful safety net. However, conditional amendments can be a godsend for cleaning up a failed migration.

One of the problems with MySQL is that is has no easy way of doing conditional logic in a batch. Instead, you must create a procedure to check for an object’s existence before you alter or delete it, or for its absence before you create it. Again, you don’t always need conditional logic, and the result is always simpler without it.

Here is perhaps the simplest example of a rollback script, subsequently turned into a Flyway undo script, for the MySQL pubs database. The script, U1.1.1__Undo_Initial_Build.sql, will remove the objects created by the V1.1.1 build script, but only if they exist. As I’ve already explained, this should be unnecessary, in most RDBMSs, but it gives us a Flyway undo file for free. It disables foreign key checks at the start, meaning we can delete the objects in any order, which makes things simpler.

A tool such as SQL Compare or Flyway Desktop can auto-generate this sort of simple undo script for you, if you use the ObjectExistenceChecks option to include the ‘guard clauses’.

In scanning this undo script, you’ll appreciate the value of Flyway’s checks to ensure that it is only executed in the right context (i.e., only ever on V1.1.1 of the database)!

You might think that, if you are faced with a lot of tables, you could merely specify the tables in a list and use prepared statements to do the deletion. Obviously, in this case it wouldn’t be worth the effort, but it will do as an illustration. Normally, you’d want to do this sort of routine for an unruly tidy-up where you don’t know the names of all the tables to delete and so you must fill the temporary table using a metadata query.

What about dropping procedures? Here, MySQL lets us down. Although this code works for views and tables, it won’t work for procedures. In fact, it is difficult to find the reasons behind the list of allowed DDL SQL Syntax permitted in Prepared Statements. If the task you need is supported in a prepared statement, this way of operating on a list of database objects can be very useful.

Fortunately, the undo of Pubs 1.1.1 just involved creating tables and their indexes, procedures and a view. Likewise, the UNDO of Pubs 1.1.2, which also started its life as a rollback script, merely undoes the business of inserting data and adding foreign keys,.

However, life isn’t generally that simple. The U1.1.3 script for Pubs is a bit more interesting. To illustrate why, here is the V1.1.3 forward migration:

This code might crash out, leaving Flyway in an indeterminate state, so if we want a rollback to go smoothly, we need conditional amendments to make sure that we don’t undo something that was never done. The resulting U1.1.3 script, that evolved from the rollback script, might look like this:

So, what is going on here? MySQL requires that the IF…END IF logic is only allowed within a procedure, we must create a temporary procedure to do the undo migration. We also need an error handler to mop up and report any error to ensure that we don’t let an undo fail in an indeterminate state. I’m being over-cautious in this case because none of these modification statements would result in an error if the state was a part-execution of the pubs V1.1.3 script, but I just want to show the technique.

OK, the first undo you write is the most painful. After that it gets a lot better. I offer further advice, but using SQL Server to illustrate it, in Creating Idempotent DDL Scripts for Database Migrations.

Rolling back data changes

In this article, we’ve so far assumed that a rollback file can become an undo. This is true up to a point but undos, unlike rollbacks, can be chained together to allow regression through several versions. If a database that is being ‘undone’ contains data that you need to keep, there is a danger that the previous version can’t accommodate the data. If, for example, you reduce the length of a string back to what it was in the target version then you will need to ensure that the resulting datatype will hold the data that is in the table without truncation or error.

Ultimately, the chain of migrations, both forward and backward, must be usable to update all copies, including production, and your production database server will have different data in it to your development copies. The preservation of data can get complicated.

If you mix data with metadata, as happens in a few of the migration scripts in my sample Pubs Flyway project, your undo scripts must only DELETE data that matches the corresponding INSERT. Don’t assume that you can delete all rows, via a truncation, unless the corresponding migration makes it clear that the table was empty before it inserted rows by, for example, creating the table.

There are other complications too. In cases where you can’t simply delete all the data, you may find your undo script will need to reset an autoincrement or identity record. If you disable foreign key checks before you start, to avoid the need to delete data from tables in the correct dependency order, remember to re-enable them after you’re done. If possible, make sure that the database system checks the data when a constraint is reenabled, for performance reasons as well as the more obvious reason of data integrity. Always test a data change and its corresponding rollback by doing the operation at least twice

The dangers of conditional create, drop and modify

In most database systems, including MySQL, CREATE and DROP statements can be accompanied with the clauses IF NOT EXISTS or IF EXISTS. If this were a universal benefit, they’d have always been in the language. If we know which tables might, or might not, have been created in our failed migration, then they’re fine to use. However, if you start to use them from habit, then that can cause problems.

When you get an error with DROP or CREATE statements, then it is, generally, a good indication of something awry. If you are sure of the context, the table won’t be there, why if is failing because the table is there? If the context is wrong, or if you’re maybe executing the wrong file, you ought to check before continuing, rather than make the CREATE conditional.

Summary

Developing a database in Flyway is a pleasure when using an RDBMS with which Flyway can automatically roll-back failed migrations. Otherwise, you are involved in the mopping up. Flyway can still be a pleasure even with RDBMSs that cannot support transaction DDL rollback, but you need a strategy where you create a rollback file at the same time as the forward migration. These rollback files are slightly unusual for two reasons.

  • Firstly, because they will cope with ‘mess’, by which I mean that you don’t know how much of the migration was done before the point of failure. You must reverse the changes only if they were made.
  • Secondly, they need to be executed against the database directly, rather than using Flyway, so you need to ensure that the database is in the state that the script was designed for.

These rollback files can be subsequently converted into regular Flyway undo files when the migration is successfully applied. I hope that examples in this and referenced articles help you to introduce you to this type of versatile undo file.

Tools in this post

Flyway

Version control for your database. Robust schema evolution across all your environments and technologies.

Find out more