• Flyway
• Database Versioning
• Configuration, Authentication

Exploring the Flyway Schema History Table

How Flyway uses the Schema History table

Flyway tracks the version of each database since it knows exactly which versioned migration files were applied to build each version. Once applied, it won’t let you alter them subsequently. To do this, it needs to keep all the information required within the database, in a bookkeeping table called the flyway_schema_history table. Flyway always puts this table in the default schema. If there is only one schema in the database, it is easy to work out which is the default schema. Where the RDBMS has a construct of a ‘default schema’, Flyway can use that. If you have more than one schema, you need to provide a list of them to Flyway as a schemas parameter. Flyway assumes that the default schema is the first one in the list unless you explicitly specify otherwise by setting the defaultSchema parameter.

When Flyway is presented with a database, it tries to locate its metadata table. If it does not exist, it will create one and then searches for migration files in the file location (or list of file locations) that you specify. You can write these migration files in either SQL or Java.

When Flyway has located all the files, it sorts them based on their version number and applies them to the target database, in order. It stores, it its history table, the version number, description, type (SQL or Java) and filename, together with a checksum.

When Flyway applies a migration file successfully, it also records in the table the date, the time it took to run and who ran the migration. Each time it runs a migration, it recalculates the checksum for each file that has already been applied to the database and will raise an error if it has been changed subsequently.

To accommodate evolving database schemas, Flyway:

• Scans the application classpath (current working directory) and locations for available migration files.
• Compares migration files against the history table. If a version number in the filename is lower or equal to a version marked as current, it is ignored
• Marks any remaining migration files as pending migrations. These are sorted based on the version number
• Executes every migration file with a version number in version order, between the current version and the version specified as target. If no target version is specified, it executes them all.
• Updates the metadata table accordingly, as each migration is applied

The flyway_schema_history table is exclusively for use by Flyway. It is a bad idea to edit or update it directly. It is best to read the information contained in the table by using Flyway Info. This information can be obtained as a JSON file by setting the command-line parameter accordingly with - outputType=json. If you need to alter the contents of the table, it is almost always possible to do so via a Flyway action.

Locating the flyway_schema_history table

Although the default schema is by far the simplest place to store Flyway’s metadata table, you have the complication that if you need to generate a script of the contents of the database, such as when you create a build script, or object-level scripts, these will then include this table, unless you explicitly exclude it. As this schema history table records the state of the database, this isn’t necessarily a bad thing, but the table isn’t really part of the database. It can complicate any static source control system that you’re using. Generally, you can tell any utility tool that you’re using, such as SQL Compare, to ignore certain tables, so it is just a matter to setting every tool you use accordingly.

On most database systems, you can place the flyway_schema_history table in a different schema. The problem with putting it in a different schema is in the differing ways that databases support schemas. It is fine in SQL Server or PostgreSQL, for example, but not in MySQL or SQLite.

Flyway is, as always, very accommodating. You can place the schema table anywhere you like, just by changing a couple of configuration items. This can be done as an environment variable, a configuration file setting or a parameter.

The default Schema

Flyway always puts the flyway_schema_history table in the default schema. On start-up, Flyway sees whether you have specified what schema should be the default schema, which you do using the flyway.defaultSchema setting. If you haven’t specified a default schema, Flyway uses the first schema in the list you provide in flyway.schemas. If you haven’t specified these, Flyway uses the default schema for the database connection, if the database system supports this. Schema names are case-sensitive.

When Flyway has worked out what schemas you are using, it will check whether each one exists. If it doesn’t, then Flyway automatically attempts to create it.

If Flyway is asked to clean a database of all its contents, it removes the contents of each of the schemas, in the order of the flyway.schemas list and them creates and fills a fresh flyway_schema_history table. If Flyway creates a schema, it will then drop the schema when performing the ‘clean’ action.

The name of the History table

You might, for some reason want to change the name of the schema history table from its default of flyway_schema_history. You can do this with the configuration item flyway.table.

The Big Red Buttons

More than once, in James Bond movies, as the villain approaches, Bond, temporarily at bay, looks around frantically and spies a big red button on the wall. Quick as a flash, he presses it. There are distant screams and then billowing smoke, getting louder and closer. The villain looks around, horrified, as his lair is destroyed.

These two settings, flyway.table, and flyway.defaultschema, seem to me to be equivalent to these big red buttons. Why are they there, you wonder, because they need quite a bit of thought before you use them, safely. And you need to be confident that you understand Flyway’s configuration system. Get them wrong and you’ll see a sea of red characters on the screen, or worse.

Let’s say you decide to press one, or both, red buttons, changing the name and location of Flyway’s metadata table, for one of your projects. Firstly, you must change those two settings. Because these are project-wide settings, they need to go in project-scoped flyway.conf files that are read before every Flyway action. Otherwise, it is too easy to use the wrong settings. If you inadvertently use the wrong settings, and don’t find the history table, you’ll get messages like this.

Flyway : ERROR: Found non-empty schema(s) [dbo] but no schema history table. Use baseline() or set baselineOnMigrate to true to 
initialize the schema history table.
At line:1 char:1
+ Flyway  migrate
+ ~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (ERROR: Found no... history table.:String) [], RemoteException
    + FullyQualifiedErrorId : NativeCommandError

…and the migration will fail.

In this case it couldn’t find the history table you specified, tried to create a new one but discovered that there was an already-existing database, so it fails safely by rolling back the transaction. It takes very little imagination to see what could go wrong if a database ends up with more than one history table, one in the default place and the other maybe under a different name or different schema. There will be objects in the database that aren’t part of the version that is created by Flyway. If you change a single value for a Flyway parameter, you end up with a different history and consequently a sea of red onscreen on the next migration.

Even if you succeed, you’d need to explore the implications of changing the default schema. With SQL Server, you can’t change the default schema for a user, from a connection. You must instead alter the default schema for that user, before the connection is used. If you don’t specify a schema for an object that you create, then it always ends up in the default schema to which you’ve been assigned. With PostgreSQL it ends up in Public, but you can alter the user’s search path for unqualified object names. MariaDB and MySQL both think that a schema is the same as a database, so the default schema would be the current schema specified by the USE command. In short, there is no common understanding of what the default schema is.

How to change the name and location of the schema history table for an existing project

With all the trepidation that I expressed in the previous paragraph, I decided to answer the question, ‘how do I safely move the flyway history table, for an existing project, to a new location and name?‘. The answer is that, where possible, you would need to do a ‘clean’ of the database followed by re-running the migrations. It could be run as a simple operation then.

To be sure of getting the right name and schema of the flyway history table, To do this, ”you need to set the current working directory/classpath to the directory containing the flyway.conf, or reference the flyway.conf file in the -configfiles parameter.

We’ll now demonstrate this. We’ve decided to have a separate flyway schema with just the Flyway history table in it. We’ll take our Flyway project and add a config file with the necessary spec in it. Just to make this more difficult, I’m using a SQL callback that reads the history table to get the current version, after every successful migration. It then writes it into the extended properties of the database, so we have a version stamp on the database. We’ll have to make sure that it still works if we change the name of the table.

I’ve included all the migration scripts, the SQL callback that adds the version number (afterMigrate__Add_Version_EP.sql) and the flyway.conf file that I used in the Scripts folder of my PubsFlywayHistorySchema project, on GitHub.

In the extra flyway.conf file for the project, we specify the location and name of our non-default Flyway schema history table. We’ll call it MigrationHistory and it will be located in the flyway schema. Now we can just put this flyway schema outside the realm of source control and any other code constraints.

We place the config file in the working directory for the project that contains all the project-specific information. Normally, you’d also have placeholders for all your project-specific information, such as the name of the project, its description and so on.

We want to check that specifying the ‘flyway’ schema as being ‘default’ doesn’t affect anything else in the database. We can be pretty sure it is safe because this ‘default’ isn’t the same as the schema that SQL Server will consider to be the default schema for the connection. We therefore use SQL Compare to compare the two resulting databases to make sure that they are the same.

We’ll do a conventional PowerShell-based migration, passing our parameters on the command-line. The code would be a lot simpler were it not for the need for keeping passwords securely. The latest version is stored here as ChangedHistoryTable.ps1

Getting all this to work was not entirely uneventful. In the SQL callback, I had to use a placeholder to hold the name of the history table because it wasn’t, in the Flyway version I used, possible to access the ‘table’ config item from a Flyway placeholder in a SQL script even though it was possible to get the Value of the defaultSchema Here is the relevant code to get around this, complete with its placeholders.

Now to see if the schema history table is in the right place

And we see that nothing has ‘leaked’ into the flyway schema. A quick peep at the table suggests that all is well:

Conclusions

It isn’t inevitable that it all ends in tears if you move the schema of the flyway_schema_history or change its name. The problem is in coordinating matters, so flyway always knows about the changed location of the table when doing an action. Otherwise, you can end up with problems. If you change the working directory, the info command tells you that none of the migrations have been applied. If you then try to migrate them, it sensibly throws an error saying that there are objects in the database but no schema history table. OK, nobody got hurt. If you then do a clean, it only drops the dbo schema because that is the only one it knows of and cannot be certain that anything in any other schema is within the project. If I then migrate, it goes ahead, but only works because I take care in the code not to assume that objects don’t already exist. Of course, the problem now is that the flyway_schema_history table now has its default name and is back in dbo. This could be avoided if Flyway were to read and use the flyway.conf file in the script directory/folder, but it doesn’t. It reads it but doesn’t use the settings.

My conclusion therefore is that the mechanism for changing the schema history table works but requires a more reliable way of informing callback processes where it now is. It also requires that Flyway reads and uses and flyway.conf file that is in one of the locations of config files specified in the project. In the meantime, it is serviceable but just a bit too risky for a hardboiled and experienced developer like me.

Tools in this post

Redgate Flyway

DevOps for the Database

Find out more