Rollback and preconditions

[ben_lb]

Hi,

I have a set of changeLogs that apply changes to an existing DB. The existing DB is tagged with version X. My master.xml script for the update changeLogs has a precondition to check that we are running against version X. This seems to work OK. At the end of the update, the database is tagged with version Y (using a new dummy changeLog entry and a tag).

I have been using the doc examples for creating a dummy changeLog to tag the previous version, i.e.

I currently use the following structure to apply updates for build 2.0.0 over build 1.0.0:

SELECT ISNULL(MAX(id),0) FROM databasechangelog WHERE author='MajorVersion'

<include file="update/2.0.0/111.xml"/>
<include file="update/2.0.0/116.xml"/>
<include file="update/2.0.0/123.xml"/>

<changeSet author="MajorVersion" id="200"/-->

In addition to this, I tag these update changes with tag 200.

Now - if I want to rollback these changeLogs - this isn’t going to work using the same master.xml as the preconditions will now fail.

Some questions:

1. How would you recommend I structure the files so that the rollback functionality works on a DB tagged with a newer version? A rollback is only likely to be run after a full update has completed. I could create a rollback.xml in each major version folder which includes the same files in the master.xml, but has an updated precondition. Is there best-practice in this area?

2. What’s the value of using a dummy changeLog (setting MajorVersion = 100) rather than using just LiquiBase’s own tag functionality?

Thanks!

Ben

[nvoxland]

I think you should be able to just use the tag functionality and not need to bother with the empty MajorVersion changeset and precondition.  It ends up duplicating the main liquibase logic, but not in as native of a matter and so is generally unnecessary.  There are times that it is helpful, but for most cases it is overkill and doesn’t really add much value (in my opinion).

Liquibase is designed to be a single stream of applied changes and certain points can be tagged as your version numbers.  It’s a lot like how SVN ends up looking.  Some people prefer to have their version numbers more hard coded into the changeSets, but it is not really needed and does run into issues like you mention.

Nathan

[ben_lb]

Hi Nathan,

The only reason for the dummy changeSet was that it seemed to be suggested as best practice in the Oracle tutorial.

I will look to remove this and stick with simple tags.

What’s the best way of deploying changes for different releases without the dummy change set?

Let’s say I have 3 releases of the product (1.0, 2.0 and 3.0), and I maintain update change sets for each within a distinct update folder. Between each major release there are several incremental changes that are applied (identified by a unique ID).

My update/2.0/master.xml for version 2.0 updates includes the following:

My update/3.0/master.xml for version 3.0 updates includes the following:

If I am updating a v1.0 to v3.0 database, I would need to perform the following liquibase operations (including tagged at various points):

liquibase --changeLogFile=update/2.0/master.xml update
liquibase tag 20
liquibase --changeLogFile=update/3.0/master.xml update
liquibase tag 30

However, if I am updating a v2.0 to v3.0 database, could I run the same set of scripts? I’m guessing the 2.0 updates will simply be ignored as they’ll already be in the database. And equally the tag 20 operation will not make any changes, as the database should already be at 20.

This should allow me to rollback to either 20 or 30, whilst only requiring 1 set of database update scripts.

Does this seem a reasonable solution?

The trouble then comes if I create a new install of v2.0 and wish to update it to v3.0. My new install is based on new install changeSets (i.e. they are defined differently to the update changeSets listed above). If I attempted to run the 2.0 updates to this, liquiBase would attempt to modify a schema that already had the changes applied to it - causing a fatal error. This is where I can see the benefit of a precondition check. One solution might be for the precondition to check for the value of the highest tag value, something like:

SELECT MAX(tag) FROM databasechangelog

I do have one problem though - in cases where I am upgrading an existing non-liquibase setup - I need a changeSet to exist before I can write a tag - so will probably continue to write a dummy changeSet for these situations.

Ben

[nvoxland]

I think it’s generally best to not think about trying to think about what “version” your database is at.  You want to think of it as having a set of changes applied to it.  Whenever you do an update, liquibase checks what changes have not been applied, and applies them in order from top to bottom. 

Since you would ship your changelog files along with your application and update the database at startup, it doesn’t matter what changes were there before, liquibase will make sure that all the changes needed to run the current version is there.  The value of the tag support is to support rolling back if something bad happens and isn’t really designed to give the database a version.  It is simply a bookmark of what had been ran at a particular time so that you can go back to it if you need to.

In your case, tagging the database after each version worth of changes can help you with undoing changes right away if the upgrade fails, but you shouldn’t need to use them to manage what needs to be applied, liquibase does that already.

Nathan

[ben_lb]

Hi Nathan,

Thanks for the update. I’m pretty happy with how Liquibase handles updates to a fresh install - and will only use the tags as a means of rolling back to known states. Whilst we don’t really care about the concept of a version (as you say - it’s just a collection of changes) - it’s vital for our customers to be able to undo updates back to a know ‘version tag’ - as this is where most of our upgrades have failed in the past using our existing database upgrade scripts.

I still have an outstanding issue though - and that is how I should handle upgrading an existing customer database where we are using LiquiBase for the first time. We basically need to be able to support 2 scenarios:

1. A customer performing a fresh install of our version X product all via LiquiBase changeSets - i.e. every DB element is defined within its own changeSet with a unique ID/author.

2. A customer upgrading from our version X-1 product (which was previously constructed via SQL scripts) to the new version X product using a set of LiquiBase update scripts.

I don’t see how I can use the same set of LiquiBase update scripts to get the same set of changeSets applied - as in case 1) many of the schema elements will already be defined with their own unique ID/author; and in case 2) we will typically need to create alterations to the previous schema definitions using a completely different set of LiquiBase changeSets. This was the only reason I thought the suggestion of using preCondiftions might satisfy these requirements. I’d welcome any suggestions on the best practice on handling these sort of scenarios.

In my mind, the only way around this would be to define the ‘install’ changeSets to match those of the version X-1 product - then any updates would always be applied to the essentially the same set of schema elements. The downside of course is that a fresh install is creating an old set of schema elements and then bringing them up to date with the latest version. This might not matter for version X - but imagine what a fresh install of a version X+10 product might be - a whole collection of modifications and updates to a dated schema. Not the most efficient way of performing a fresh install. The Oracle tutorial on the LiquiBase site suggests that the way to handle this is to unify the ‘install’ changeSets at each new major release, and make sure update scripts are provided between the major releases. Is this still the recommended solution?

Thanks,

Ben

[nvoxland]

If you are concerned about being able to roll back to a previous version, you may want to look at the futureRollbackSQL command.  Given an existing database and an updated changelog file, it will generate the SQL needed to roll the database back to the current state after it has been upgraded.  It will let you know if there are any changeSets that can’t be rolled back (because there isn’t an automatically generated statement and there is no custom rollback tag) and it will allow you to manually inspect the SQL so you know what would happen in case of a downgrade.

What I generally do when needing to support full-liquibase installs and pre-liquibase (legacy) installs, is to use preconditions like you said.  I’ll generally have a single generated changelog file (or simple database dump, depending on your needs) and have the precondition check for the existence of a table that would be there if you have a legacy install.  Something like

If it is there, the precondition fails and marks the changeset as ran and it doesn’t need to be checked again and the new changes are ran.  If the precondtion passes, we run the changeSet that builds the database as it existed pre-liquibase then continues on.

To make it work easiest, you will want a single changeSet that contains the entire pre-liquibase database.  It’s generally not advisable to have multiple createTable statements in a single changeSet, but since in this case it may make sense.  If you generated the changeLog with the generateChangeLog command, you may need to do some search/replace to remove all the created changeSet tags.  If you don’t care about cross-database support, it would be easiest to have your database generate a creation sql script on it’s own and then simply have your changeSet use the tag.  The diff tool of liquibase used by the generateChangeLog can miss some database objects, and so this method will ensure that your post-liquibase created database are exactly the same as the legacy ones. 

In regards to your question on managing install changeSets vs. upgrade changesets, I generally think it’s best to only have upgrade changests and not worry about the overhead of bringing your database through all the previous versions to get to the current state on install.  It isn’t the most efficient way, but that is the way you tested the upgrades previously and it is worth having it less efficient in favor of having a simpler and better tested upgrade path.  If there are changeSets that are extremely inefficient and now unneeded for new installs, you can manage them in a way that works for you, but I think they should be the exception, not the rule.

Nathan

Nathan

[ben_lb]

Many thanks for the advice.

I’ll look to structure our schema deployment around updates rather than trying to consolidate the creation scripts at each major release. As you say it certainly reduces risk with updates.

Ben