I am looking for a solution in achieving zero downtime schema upgrade with the assumption that every schema upgrade is backwards-compatible with the previous application version which makes things easier. Since the database is already huge, the upgrade may take more than 2 days to complete and the business cannot afford to stop their operations that long.

I found a candidate solution in https://stackoverflow.com/a/40365479 where its steps are comprise of:

  • Create a temp table
  • Creates triggers on the first table (for inserts, updates, deletes) so that they are replicated to the temp table
  • In small batches, migrate data
  • When done, rename table to new table, and drop the other table

But my concern is would not it cause a potential race condition problem in the last step where for instance, we have the original table 'users' and the new table 'users_new', and our goal is to replace the original table with the new table? Because to accomplish that, won't it be needed to execute two separate DDL operations such as

DROP TABLE users; ALTER TABLE users_new RENAME users

And by doing so, what if a query was executed against the users table during the time between the execution of 1st and 2nd DDL commands?

  • Please also consider checking the logic in the solution provided here although its tagged as PostgreSql but still logic can be adapted and see if it fits your need. Jan 4 '17 at 9:49
  • 1
    You are re-inventing the wheel. See Percona's pt-online-schema-change. They are years ahead of you in working out the kinks.
    – Rick James
    Jan 4 '17 at 18:52

pt-online-schema-change in the Percona Toolkit is used in production in many companies for many years. It's more than a candidate solution.

It avoids the race condition by dropping old table later.

RENAME TABLE users TO user_old, users_new TO users;
DROP TABLE users_old;

Check out the source code around "Step 5: Rename tables: orig -> old, new -> orig"

pt-osc creates three triggers - on update, on delete and on insert. So when any changes are made during copying from users to users_new, the triggers reflect the change in users_new. So by a moment right before the RENAME, users and users_new are identical.

  • How about the race condition? Is it not possible to happen since during migration, an INSERT users_new...SELECT users can be executed such that all entries selected are locked and will only be released after the rows are successfully inserted to users_new?
    – Xegara
    Jan 5 '17 at 5:20
  • I don't understand how race condition is possible here. The app makes its changes to users, pt-osc runs INSERT INTO users_new SELECT FROM users. And triggers make changes in users_new. How exactly do you think the race condition can happen?
    – akuzminsky
    Jan 5 '17 at 5:23
  • I was just thinking if a race condition is possible when a deletion of an entry happened between the selection of that entry in the old table and adding that entry to the new table such that the delete trigger for that entry is executed just before the entry was placed in the new table. Is that possible? I just read in the mysql documentation that SELECT * FROM does not lock its table.
    – Xegara
    Jan 5 '17 at 5:28
  • Maybe I can use INSERT INTO users_new SELECT * FROM users LOCK IN SHARE MODE?
    – Xegara
    Jan 5 '17 at 5:30
  • SELECT FROM doesn't lock the table, but INSERT INTO users_new SELECT FROM users will grab a shared lock on users. Thus, DELETE FROM users will wait.
    – akuzminsky
    Jan 5 '17 at 5:30

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.