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.
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.
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’.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 |
SET foreign_key_checks = 0; -- this allows you to delete tables in any order -- Dropping views ... -- Dropping dbo.titleview ... DROP VIEW IF EXISTS dbo.titleview; -- Dropping procedures.. -- Dropping dbo.reptq3 ... DROP PROCEDURE IF EXISTS dbo.reptq3; -- Dropping dbo.reptq2 ... DROP PROCEDURE IF EXISTS dbo.reptq2; -- Dropping dbo.reptq1 ... DROP PROCEDURE IF EXISTS dbo.reptq1; -- Dropping dbo.byroyalty ... DROP PROCEDURE IF EXISTS dbo.byroyalty; -- Dropping tables ... -- Dropping dbo.titleauthor ... DROP TABLE IF EXISTS dbo.titleauthor; -- Dropping dbo.sales ... DROP TABLE IF EXISTS dbo.sales; -- Dropping dbo.roysched ... DROP TABLE IF EXISTS dbo.roysched; -- Dropping dbo.pub_info ... DROP TABLE IF EXISTS dbo.pub_info; -- Dropping dbo.discounts ... DROP TABLE IF EXISTS dbo.discounts; -- Dropping dbo.stores ... DROP TABLE IF EXISTS dbo.stores; -- Dropping dbo.titles ... DROP TABLE IF EXISTS dbo.titles; -- Dropping dbo.publishers ... DROP TABLE IF EXISTS dbo.publishers; -- Dropping dbo.jobs ... DROP TABLE IF EXISTS dbo.jobs; -- Dropping dbo.employee ... DROP TABLE IF EXISTS dbo.employee; -- Dropping dbo.authors ... DROP TABLE IF EXISTS dbo.authors; -- Dropping types ... SET foreign_key_checks = 1; |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 |
DROP TEMPORARY TABLE IF EXISTS Objects_To_Delete; CREATE TEMPORARY TABLE IF NOT EXISTS Objects_To_Delete ( TheOrder int NOT NULL AUTO_INCREMENT PRIMARY KEY, Object_Name CHARACTER varying (80), Object_Type CHARACTER varying (40)); INSERT INTO Objects_To_Delete (Object_Name,Object_Type) VALUES ('dbo.titleview','VIEW'), ('titles','TABLE'), ('titleauthor','TABLE'), ('stores','TABLE'), ('sales','TABLE'), ('roysched','TABLE'), ('pub_info','TABLE'), ('publishers','TABLE'), ('jobs','TABLE'), ('employee','TABLE'), ('discounts','TABLE'), ('authors','TABLE'); SET foreign_key_checks = 0; -- SELECT * FROM Objects_To_Delete Delimiter $$ DROP PROCEDURE IF EXISTS DropTheObject $$ CREATE PROCEDURE DropTheObject() BEGIN set @iiMax = 0; set @ii = 1; SELECT COUNT(*) FROM Objects_To_Delete INTO @iiMax; WHILE @ii <= @iiMax DO SET @ObjectName=(Select Object_name FROM Objects_To_Delete WHERE TheOrder=@ii); SET @ObjectType=(Select Object_Type FROM Objects_To_Delete WHERE TheOrder=@ii); SET @sqlstmt = CONCAT ('DROP ',@ObjectType,' ',@ObjectName,';'); PREPARE st FROM @sqlstmt; EXECUTE st; DEALLOCATE PREPARE st; SET @ii = @ii + 1; END WHILE; END; $$ DELIMITER ; CALL DropTheObject; DROP PROCEDURE IF EXISTS DropTheObject; DROP TEMPORARY TABLE IF EXISTS Objects_To_Delete |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
DROP INDEX if exists aunmind on dbo.authors; ALTER TABLE dbo.authors MODIFY COLUMN au_lname character varying(80) NOT NULL; ALTER TABLE dbo.authors MODIFY COLUMN au_fname character varying(80) NOT NULL; ALTER TABLE dbo.authors MODIFY COLUMN phone character varying(40) NOT NULL; ALTER TABLE dbo.authors MODIFY COLUMN address character varying(80) NULL; ALTER TABLE dbo.authors MODIFY COLUMN city character varying(40) NULL; -- 'Creating index aunmind on dbo.authors' CREATE INDEX aunmind ON dbo.authors (au_lname, au_fname); ALTER TABLE dbo.roysched ADD roysched_id INT PRIMARY KEY AUTO_INCREMENT; ALTER TABLE dbo.discounts ADD discount_id INT PRIMARY KEY AUTO_INCREMENT; -- 'Adding constraints to dbo.authors' ALTER TABLE dbo.authors ALTER COLUMN phone SET DEFAULT ('UNKNOWN'); -- 'Adding constraints to dbo.publishers' ALTER TABLE dbo.publishers ALTER COLUMN country SET DEFAULT ('USA'); ALTER TABLE dbo.publishers MODIFY COLUMN pub_name varchar(100) NOT NULL; -- 'Adding constraints to dbo.titles' ALTER TABLE dbo.titles ALTER COLUMN type SET DEFAULT ('UNDECIDED'); -- 'Adding foreign keys to dbo.sales' -- 'Adding foreign keys to dbo.sales' ALTER TABLE dbo.sales ADD CONSTRAINT FK_Sales_Stores FOREIGN KEY (stor_id) REFERENCES dbo.stores (stor_id); ALTER TABLE dbo.sales ADD CONSTRAINT FK_Sales_Title FOREIGN KEY (title_id) REFERENCES dbo.titles (title_id); |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 |
DELIMITER $$ DROP PROCEDURE IF EXISTS undo_Pubs1_1_3 $$ CREATE PROCEDURE undo_Pubs1_1_3() BEGIN DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN GET DIAGNOSTICS CONDITION 1 @p2 = MESSAGE_TEXT; SELECT CONCAT('I am sorry but ',@p2) AS 'Migration undo_Pubs1_1_3 terminated' INTO @theError; SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = @theError ; END; if EXISTS ((SELECT * FROM information_schema.REFERENTIAL_CONSTRAINTS WHERE TABLE_NAME='sales' AND CONSTRAINT_NAME='FK_Sales_Title' AND constraint_SCHEMA = DATABASE())) then ALTER TABLE sales DROP FOREIGN KEY FK_Sales_Title; END IF; if EXISTS ((SELECT * FROM information_schema.REFERENTIAL_CONSTRAINTS WHERE TABLE_NAME='sales' AND constraint_SCHEMA = DATABASE() AND CONSTRAINT_NAME='FK_Sales_Stores')) then ALTER TABLE sales DROP FOREIGN KEY FK_Sales_Stores; END IF; if EXISTS (SELECT * FROM information_schema.COLUMNS WHERE TABLE_NAME='publishers' AND COLUMN_NAME = 'country' and column_default IS not null and column_default <> 'NULL') then ALTER TABLE publishers ALTER COLUMN country DROP DEFAULT; END if; IF EXISTS((SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = N'roysched_id' AND TABLE_NAME = 'roysched' AND TABLE_SCHEMA = DATABASE())) then ALTER TABLE roysched DROP COLUMN roysched_id; END if; IF EXISTS ((SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = N'discount_id' AND TABLE_NAME = 'discounts' AND TABLE_SCHEMA = DATABASE())) then ALTER TABLE discounts DROP COLUMN discount_id; END if; DROP INDEX if exists aunmind on dbo.authors; drop TEMPORARY TABLE IF EXISTS authorsColumns; CREATE temporary TABLE authorsColumns(TheColumn national character varying(200)); INSERT INTO authorsColumns (TheColumn) SELECT column_name FROM information_schema.columns WHERE TABLE_NAME = 'authors' AND TABLE_SCHEMA = DATABASE(); if EXISTS ((SELECT * from authorsColumns WHERE TheColumn = 'au_lname')) THEN ALTER TABLE authors modify COLUMN au_lname varchar(40) NOT NULL; END if; if EXISTS ((SELECT * from authorsColumns WHERE TheColumn = 'au_fname')) then ALTER TABLE dbo.authors modify COLUMN au_fname varchar(20) NOT NULL; END if; if EXISTS ((SELECT * from authorsColumns WHERE TheColumn = 'phone')) then ALTER TABLE dbo.authors MODIFY COLUMN phone char(12) DEFAULT 'UNKNOWN' NOT NULL; END if; if EXISTS ((SELECT * from authorsColumns WHERE TheColumn = 'address')) then ALTER TABLE dbo.authors MODIFY COLUMN address varchar(40); END if; if EXISTS ((SELECT * from authorsColumns WHERE TheColumn = 'city')) then ALTER TABLE dbo.authors MODIFY COLUMN city varchar(20); END if; CREATE INDEX aunmind ON dbo.authors (au_lname, au_fname); drop TEMPORARY TABLE IF EXISTS authorsColumns; END $$ CALL undo_Pubs1_1_3 $$ DROP PROCEDURE IF exists undo_Pubs1_1_3 |
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 Teams
Ideal for organizations looking to improve collaboration and fine tune their processes during development and the deployment of database changes.
You may also like
-
Article
Flyway 7.3.0 Released
Flyway 7.3.0 is out! This release contains new features and improvements over Flyway 7.2.0. Highlights You can find a detailed list of the changes in the release notes. Migration filename placeholder It can be useful to know the full name of a migration file at the time it’s executed, for auditing purposes. The migration description is of
-
Article
Dry Runs for Database Migrations using Flyway
How Dry Run scripts work, and how they can be used to deliver a single-script release artifact to Staging, verify placeholder substitutions in SQL migration scripts, and simplify team code reviews.
-
Article
Flyway 6.0.0 Released
Flyway 6.0.0 is out! This is the first full release by Redgate, and contains many new features and improvements over Flyway 5.2.4. This version also adds more features and fixes on top of the previous beta releases. Highlights The highlights of the 6.0.0 release include: You can find a detailed list of issues fixed in the release notes. Parser
-
Article
Using ODBC in PowerShell Scripts and Callbacks
Flyway connects to database using JDBC, but when we're automating Flyway with PowerShell, we can't use JDBC directly to get access to the metadata we need to add useful reports and other functionality to Flyway's build and migration processes. This article demonstrates how to get what we need using ODBC from our PowerShell scripts, via a DSN.
-
Workshop
Level Up with Flyway - Sydney
This interactive workshop will give you an in-depth introduction to Flyway and how it can be used to solve problems relating to DevOps, plus accelerate your progress by adopting best practices. Spend the morning with our expert, Anderson Rangel, as he helps you to ‘Level Up’ with Flyway. This in-person workshop will cover a broad
-
Article
Setting up for Team-based Database Development using Flyway Desktop
This article provides a simple demonstration of how a small team of developers might set up a Flyway Desktop project to manage, automate, and control database development.
