3

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?

  • I haven't ever done this with mysql, so maybe it won't work, but why can you not simply use transactions to isolate the instances from each other? The first instance to commit a transaction will get it to work, the others will fail because the definition of the tables has changed and the columns it would work with are no longer there, which error it can then happily ignore. – Jules May 19 '16 at 18:33
  • 1
    Shouldn't this be done as part of the deployment process? – user2669338 May 21 '16 at 10:20
  • @user2669338 That makes sense, I'm looking into seeing if we can do that. – Nick Gotch May 22 '16 at 21:52
3

Only option three

Carefully wrapping each non-idempotent statement in checks.

has any appeal for me.

This option is the only one that doesn't require the script to depend on anything else (hardware, software or otherwise) for its proper function.

  • My fear with this option was if another developer were to move to the project and not recognize such a statement it would be very easy for them to break things. I guess I could add a warning message to the script though. – Nick Gotch May 19 '16 at 19:55
0

I can see #3 introducing some potentially nasty race conditions, and even #1 could suffer from a race condition if the schema update happens right after another instance has connected to the database. If you borrow a technique from Active Record migrations and designate a table to keep track of the schema version, not only can you safely lock on that table, but you can also easily check to make sure you don't make the same schema change twice by mistake.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.