I'm trying to figure out the best way to tackle running a one-time SQL script when a service instance starts up. Here's the scenario:
- We run multiple Amazon instances of our service in parallel.
- Each instance connects to the same MySQL database.
- With each service update we want to run a one-time SQL script to update the DB schema.
- We only want one instance to run the script.
- We want other instances to wait/block until the update is complete.
Most operations in the script are idempotent and so far running the script on all instances was no issue. The latest changes though include some column drops and other statements which are not idempotent. These are causing errors during the startup.
I can think of a few ways to tackle this:
- Designating a "primary" server instance to perform the update and have non-primary just check that the update has been performed.
- Locking on a specific table before running the script and only releasing the lock after the update is complete.
- Carefully wrapping each non-idempotent statement in checks.
What issues exist with these potential solutions? Is there a better approach? Is there a best-practice approach?