In an attempt to reduce downtime on our production servers we are considering using one of the following tools to do schema migrations without any downtime:

  1. Percona Toolkit — pt-online-schema-change
  2. GitHub’s Online Schema Migrations for MySQL
  3. Facebook’s OnlineSchemaChange

Once we had started the process of testing these tools in lower database levels it became clear that some mix of foreign keys and potentially the frequency of locks on certain tables was making percona fail to succeed and in more than one case, corrupt the database.

To give an example of this corruption I will use table_a :

  1. Makes table_a_new
  2. Copies data to table_a_new
  3. Start moving foreign keys from table_b referencing table_a to table_a_new
  4. Timeout midway through
  5. Tries to rollback but still times out due to queries/locks on table_b
  6. The tool fails with half foreign keys pointing to table_a_new and the other half pointing to table_a

Some of these tables need to be fairly transactional as they relate to selling limited inventory with a high level of concurrent users; however, there is the possibility that they are queried more often than necessary dues to inefficient use of an ORM.

Two of the three tools above (gh-ost and fb osc) specifically do not support any form of foreign keys.

Is this an indicator that we should be considering dropping our foreign keys if we want to be able to do schema migrations without downtime?

Are there any other considerations that should be made before exploring this option?

  • 1
    My friend Michael J. Swart has a series on schema migrations (SQL Server specific, but you can still use the concepts) at michaeljswart.com/2018/01/…. Dropping FKs for the sake of a bad tool is not the answer. – Randolph West Nov 18 at 22:38

Your Answer

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

Browse other questions tagged or ask your own question.