Flyway does not handle implicity committed statements when flyway process crashes

Question

Ran into this situation recently using SpringBoot (1.2.3) and Flyway (3.1), and could not find much about how to handle:

Server was spinning up and executing a long running alter table add column statement against a mysql database (5.6) 20-30mins. As the script was running the server process was hard terminated since it was not responding to health checks in a given timeframe. Since the MySQL server was processing the statement, it continued to process the statement to completion but the script was not marked as failed or success. When another server was spun up, it tried to execute the script which failed cause the column already existed.

Given that the server could crash at anytime for any reason during a long running script, other than idempotent scripts or a manual db upgrade process, would like to understand established patterns for handling this situation.

Possibly a setting that indicates the server platform uses implicit commits so mark it as run when the script is sent to the server?

Answer 1

You bring up a good point but unfortunately, I don't think Flyway or Spring Boot have any native support for this.

One workaround, ugly as it is, is to implement the beforeEachMigrate and afterEachMigrate callbacks that Flyway provides. You could use them to maintain a separate migration table that keeps track of which migrations have been started and which ones have been completed. Then, if it contains unfinished migrations the next time your application starts, you can shut it down with a descriptive error message.

I recommend creating a feature request about it. If you do, please link us to it!