How to get flyway to re-run migration?

Question

OUR SYSTEM

We are trying to put migrations as .sql files under version control. Developers would write a VN__*.sql file, commit to version control and a job that runs every 5 minutes would automatically migrate to a Dev and Test database. Once the change proved to not cause problems, someone else would run a manual job to run the migration on Production.

MY PROBLEM:

I had a demo migration that created a few tables. I checked V4__DemoTables.sql into version control on my PC.

On our Linux box a job that runs every 5 minutes extracted the new file from version control, then ran the flyway.sh file. It detected the file and executed it.

But the .sql file had a typo. And we are using Neteeza which has problems with flyway automatically wrapping a migration in a BEGIN TRAN ... END TRAN. So the migration created 2 tables, then aborted before the third.

No problem I thought. I dropped the 2 tables that the .sql file did create. Checked V4__ out of version control, fixed the typo and re-submitted it.

Five minutes later the update was extracted but flyway complains that the checksum does not match. So it will NOT run the updated V4__DemoTables.sql file.

How do I get flyway to accept the updated file and update the checksum in the SCHEMA_VERSION file in case of a typo?

Reading the docs it seems like the developers suggest I should have created a new V4_1_DemoTables.sql file with the fix's. But this would have collided with the commands in the V4__ file so this seemed wrong.

So here is what the docs imply I need to do:

• Leave V4__ as a 'successful' migration according to the SCHEMA_VERSION table.

  Create V4_1_ to delete the tables that were created before the typo line in V4__.

  Create V4_2_ which has the typo fix from the original file to do all the real work.

Is this correct?

Answer 1

If the migration completes successfully, but some of the db objects are not quite right yet (typo in column name, ...), do as you said and push a follow-up script that fixes it (rename column, ...).

If the migration fails, and it didn't run on a DB with DDL transaction, the DB must be cleaned manually. This means:

• Reverting the effects of the migration on the DB
• Removing the version from the SCHEMA_VERSION table and marking the previous one as current

This second step will be automated in the future with the introduction of the flyway.repair() command.