Is dropping rows from schema_version the right thing to do?

Question

We use Flyway 4 via Java API against MySQL 5.6 (which does not support transactional DDL). We run into infrequent cases where migrations fail, sometimes logical errors and sometimes because someone mucked with the database and things aren't in the right state.

So I'm wondering, should we delete rows from schema_version in this case and let the migration re-run, with either fixes to the migration or fixes to the database? We generally run a repair before migrate to fix any failed migrations, but then add a new one.

See also: Should I be worried about creating idempotent migrations while using Flyway?

Answer 1

I've been using Flyway 3 for the past couple of years and have just upgraded to 4 recently. So far it's successfully managed about 30 or so different projects and fits nicely into the delivery pipeline.

In that two year's we have definitely experienced a couple of migrations that made it out of a developers machine that we have wanted to eliminate from history. In those cases, deleting a row from schema_version is usually the answer - it's quicker, less process intensive than repair and has less risk in our situation. That being said, it's only ever made it as far as development / testing environments - so there has always been an not-prod environment that has received only the working migrations and no failed / undesired ones.

We definitely aim to make failed migrations a build time problem. So migration versions are validated, migrations themselves are executed and a component run against that migrated database as part of the build for a component. I'd say this largely contributes to our infrequent issues when dealing with such a large number of migration managed projects.

Can't recall where I read it and if it was in the context of Flyway, but it was something along the lines of The price we pay for this kind of automation is discipline - so hearing something like "someone mucked around with the database manually" would definitely be a red flag for me.

In conclusion...

Minimizing the opportunities for failures I think is the key. But when failures do happen, having your strategy defined to meet an acceptable level of risk is the best you can do - repair and deleting of schema_version both have their drawbacks.