Product articles Redgate Flyway Testing migration scripts
Dealing with Failed SQL Migrations in…

Dealing with Failed SQL Migrations in MariaDB or MySQL

This article explains the fastest ways to restore the previous version of the database, to recover from a failed Flyway migration that leaves the database in an indeterminate state, and then how to adapt your database development process to avoid these problems.

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.

Although it is easy to get started with Flyway, there are times when real-life can trip you up slightly. This happens when you use it with MariaDB or MySQL. Unfortunately, MariaDB’s own documentation explains that with these databases, most DDL statements cannot be rolled back.

To demonstrate, we’ll extend our the PubsMariaDB project, for running Flyway migrations on The Pubs database in MariaDB, from PowerShell. I explained how to set up and run this project in Flyway with MariaDB for Those of a Nervous Disposition. It’s a very simple process, but you’ll need to do that first, if you want to work through the examples here.

A failed MariaDB migration

Let’s say we’ve migrated our database to v1.1.6, and we now try to migrate to the next version, but there is an error in the v1.1.7 migration script (the v1.1.7 script I provide in the project folder doesn’t have any errors; you’ll need to introduce one):

Flyway : ERROR: Migration of schema 'dbo' to version "1.1.7 - Add Indexes" failed! Please restore backups and roll back database and code!
At line:1 char:1
+ Flyway @MyArgs migrate -target="1.1.7"
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (ERROR: Migratio...abase and code!:String) [], RemoteException
    + FullyQualifiedErrorId : NativeCommandError

We get an unspecified error with:

Migration of schema ‘dbo’ to version “1.1.7 – Add Indexes” failed! Please restore backups and roll back database and code!‘.

And if we run…

…we get…

+-----------+---------+------------------------------+--------+---------------------+---------+
| Category  | Version | Description                  | Type   | Installed On        | State   |
+-----------+---------+------------------------------+--------+---------------------+---------+
|           |         | << Flyway Schema Creation >> | SCHEMA | 2021-08-02 10:07:27 | Success |
| Versioned | 1.1.1   | Initial Build                | SQL    | 2021-08-02 10:07:35 | Success |
| Versioned | 1.1.2   | Pubs Original Data           | SQL    | 2021-08-02 10:07:41 | Success |
| Versioned | 1.1.3   | UseNVarcharetc               | SQL    | 2021-08-02 10:07:47 | Success |
| Versioned | 1.1.4   | RenameConstraintsAdd tables  | SQL    | 2021-08-02 10:07:56 | Success |
| Versioned | 1.1.5   | Add New Data                 | SQL    | 2021-08-02 10:08:12 | Success |
| Versioned | 1.1.6   | Add Tags                     | SQL    | 2021-08-02 10:08:16 | Success |
| Versioned | 1.1.7   | Add Indexes                  | SQL    | 2021-08-02 11:43:07 | Failed  |
+-----------+---------+------------------------------+--------+---------------------+---------+

The Problem: migrations that execute DDL can’t be rolled back

Why didn’t Flyway roll back the failed migration and let us try again? Why is it asking us to ‘Please restore backups and roll back database and code‘!? And, most relevant, how do we fix the code if we don’t know what the error was and where?

Firstly, why did it fail to roll back? After all, the ODBC standard, and the subsequent JDBC standard, allows transactions that can be committed or rolled back. Also, Flyway uses JDBC’s manual-commit mode to rollback a failed migration, a fact that I exploited previously in Testing Flyway Migrations Using Transactions.

Unfortunately, this doesn’t work with MariaDB (or MySQL). Although it is a relational database, and so it should conform to the industry standard for ACID transactions, unfortunately, it doesn’t. It can roll back DML transactions that you’d get in a typical OLTP database, but all the important DDL operations are immediately committed and can’t be rolled back.

We are advised, instead, to restore from a backup. In our case, we don’t really need to do this, because this is a toy database on a development server, and it is the work of a moment to rebuild it from scratch to the previous version.

However, if this were a live production server being upgraded with important new functionality, then a failed migration to a production server is a big problem. We’ve left the database halfway through an upgrade, in an indeterminate state. In a real production database, I’ve taken up to 24 hours to do a rebuild.

Even a restore from a backup could take hours, but having reached this sorry state, it is the best option we have, although an even better option is to take steps to avoid these failed DDL migrations in the first place, and to have an idempotent undo or rollback script that is effective at whatever point it failed.

I’ll show you ways to restore to the previous version, after a failed migration, and then how to avoid getting into these difficulties in the first place, by writing and testing your migration code in your IDE before you run it in Flyway. We can develop a rollback script, in tandem, for when things go wrong. I’ll discuss how we might generate undo code for a failed migration.

Restoring after a failed migration

Flyway documentation advises a restore from backup. What backup (you ask nervously)? Whatever else you do, it is a good precaution in development to generate a build script for each version that will allow you to restore it, should a migration to the next version fail. This only needs to be done once for each version and provides very little overhead in time.

Backup

Probably the fastest way to backup MySQL and MariaDB databases is to use the MySQLDump utility to do a scripted build, i.e., to generate the SQL to create and populate the tables for a specific version.

My preferred alternative is to use the scripting process within HeidiSQL to generate a build script for every version. Right-click on the dbo database/schema and you’ll see a menu item ‘Export Database as SQL’:

export database as SQL in HeidiSQL

One can, when necessary, then do a restore from this build script. In HeidiSQL it is as easy as loading it into a query and executing it. It will drop the schema and rebuild it, adding the data.

Restore

The restore recommended by MariaDB uses the MariaDB distribution’s mysql.exe. This is easy in a DOS batch or by keying in the command but is convoluted to script in PowerShell because the mysql.exe seems to require the query to be presented from the input stream. PowerShell doesn’t have an input stream, so I used the trick of executing a batch script from PowerShell, using Invoke-Item on a file of the .bat filetype. In this case, I’m restoring to version 1.1.5.

If this works, you then can tidy up the migration by running:

As an alternative, which I blushingly suggest is a much better strategy, you can restore using ODBC

As with the previous method, one then must execute Flyway Repair before trying the migration again.

Using an IDE to develop and test a migration in a branch

The most obvious and intuitive way to avoid failed Flyway migrations is to use a suitable IDE, such as HeidiSQL, to develop your code in a branch, and only submit it to Flyway’s migration directory only once it is fully tested.

Whenever you need a branch to work on the latest migration script, and you need a clean copy of the version of the database from which you are migrating, you use Flyway, passing it different parameters, to create it from a build script. If something goes wrong with a subsequent migration, Flyway can clean up and recreate the starting version very quickly, since it doesn’t need to recapitulate the database through maybe hundreds of migration scripts.

To illustrate the point, let’s say I need to write a migration to bring the code up to version 1.1.8. To develop the code, I use HeidiSQL, as I did for all the previous migrations. I gradually work through the code, writing testing each routine individually, edging forwards until finally the entire migration works. Having complete the first routine, I want to run a test migration.

First, I need to create a build script to the previous version (1.1.7). This must exclude the Flyway Schema history table:

creating a build script for a database in HeidiSQL.

Save the build script to a separate ‘branch’ project. In this example, I just added a branch directory within the project, in parallel with the Scripts directory and saved it in there, under the name V1.1.7__Branch.sql. I’ve also added the new migration script I’m developing, V1.1.8__TitlesByEditionView.sql:

Creating a branch project directory for testing migrations

Next, I create a new instance of MariaDB assigned to a different port, using the MariaDB installer, and in it create the dbo and people schemas.

Now we’re ready to migrate the branch project to V1.1.8. In the Flyway PowerShell script, I set up the connection parameters so that Flyway now accesses the new branch project. The target database (‘dbo’) is currently blank. We let Flyway tell us what it thinks:

It tells us:

Flyway Community Edition 7.8.1 by Redgate
Database: jdbc:mariadb://PhilFactorsServer:3306/dbo (MariaDB 10.6)
Schema version: << Empty Schema >>
+-----------+---------+---------------------+------+--------------+---------+
| Category  | Version | Description         | Type | Installed On | State   |
+-----------+---------+---------------------+------+--------------+---------+
| Versioned | 1.1.7   | Branch              | SQL  |              | Pending |
| Versioned | 1.1.8   | TitlesByEditionView | SQL  |              | Pending |
+-----------+---------+---------------------+------+--------------+---------+

It admires the 1.22 MB of this migration file. We let it run the first file to take it up to version 1.1.7

Flyway Community Edition 7.8.1 by Redgate
Database: jdbc:mariadb://Philf01:3306/dbo (MariaDB 10.6)
Successfully validated 2 migrations (execution time 00:00.268s)
Creating Schema History table 'dbo'.'flyway_schema_history' ...
Current version of schema 'dbo': << Empty Schema >>
Migrating schema 'dbo' to version "1.1.7 - Branch"
999 rows affected
3 rows affected
1000 rows affected
5000 rows affected
14 rows affected
1000 rows affected
1000 rows affected
8 rows affected
8 rows affected
86 rows affected
21 rows affected
6 rows affected
35 rows affected
1000 rows affected
1499 rows affected
Successfully applied 1 migration to schema 'dbo', now at version v1.1.7 (execution time 00:03.284s)

In my case, this took just over three seconds. Don’t get too excited, because it is quite a small database, and this is our branch database, not the master. It is identical but we are just using this to develop the 1.1.8 migration.

So, we’re feeling lucky and run the v1.1.8 migration:

Migrating schema 'dbo' to version "1.1.8 - TitlesByEditionView"
>Flyway : ERROR: Migration of schema 'dbo' to version "1.1.8 - TitlesByEditionView" failed! Please restore backups and roll back database and code!
At line:1 char:1
+ Flyway @MyBranch migrate
+ ~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (ERROR: Migratio...abase and code!:String) [], RemoteException
    + FullyQualifiedErrorId : NativeCommandError
 
ERROR:

Oops! So, we now quickly mop up:

No need for repair. All is now OK and only three seconds of my life wasted. It could have been worse. We are now ready to fix the problem and make another attempt. By developing migrations in a branch, we can repeatedly run these Clean/Migrate cycles until it works and checks out with all our tests.

We can then merge this branch back into develop, just by copying the V1.1.8__TitlesByEditionView.sql across to the main Scripts directory. Don’t feel tempted to copy the V1.1.7__Branch.sql across!

Idempotent ‘Undo’ code for rolling back a failed migration

Idempotent ‘undo’ code is useful in Flyway. Even if you have run every imaginable test on a migration, the unimaginable can still happen. I describe this ‘Undo’ technique in Creating Idempotent DDL Scripts for Database Migrations, but with MySQL and MariaDB, we would concentrate on this technique for undoing whatever part of a script was executed.

We develop the undo code at the same time as we write the migration so that every change is accompanied by an undo. Testing this code becomes as important as testing the migration and is best done in development.

The logic we use is to test to see if, for example, that extra column that we are adding exists (such as by using SHOW COLUMNS FROM 'table' LIKE 'Columname'; or the information schema) and only removing if it does. We would restore that table that we deleted halfway through the migration, and we would undo those indexes, but only if they exist. We apply the same logic where data is removed during a migration. We need to preserve it and, if necessary, restore it. If we hit a problem with the migration and see that unpleasant but uninformative red screen, we run our Idempotent ‘Undo’ code. We then run Flyway Repair. Note that a Flyway ‘undo’ is different from an idempotent undo because the Flyway ‘undo’ will only undo completed migrations.

For a deployment, there must always be an additional, well tested, backup and restore strategy that is independent of the database structure or data. Where possible, a fallback should adopt the snapshot technology of the underlying storage solution because this cuts down the time that the production database remains offline – it can be several orders of magnitude faster than traditional backups and restores. However, it is always wise to have, as an insurance policy, the backups recommended for that database system that are provided by the supplier.

Conclusions

MariaDB or MySQL can be made to work well with Flyway, but they are unusual in certain aspects and can therefore spring surprises. MariaDB is a real multi-user relational database with many years of robust usage, but you might find that you need to change some development practices to get the best from it. I’m hoping that you’ll discover they joys of using the combination of an IDE and Flyway to develop migrations and get around the limitations of transaction rollback. You’ll discover just how flexible and accommodating Flyway can be.

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.

Find out more

Redgate Flyway

DevOps for the Database

Find out more