安全地将NOT NULL列添加到你的数据库表中

The Database Migrations team is in charge of creating and maintaining all the internal tools for Shopify teams to carry out schema changes safely and with minimal downtime. One of our latest investigations involved using the popular Large Hadron Migrator (LHM) gem to perform schema changes in MySQL databases, after safety-checking that the migrations can be performed without losing any data. In particular, when adding a NOT NULL columns to an existing table.

数据库迁移团队负责为Shopify团队创建和维护所有的内部工具，以便安全地进行模式变更，并将停机时间降到最低。我们最近的一项调查涉及到使用流行的Large Hadron Migrator (LHM)宝石来执行MySQL数据库的模式变化，在安全检查后，迁移可以在不丢失任何数据的情况下进行。特别是在向现有表格添加NOT NULL 列时。

In this post, I'll share what we learned and our recommendations for doing so to your own database tables.

在这篇文章中，我将分享我们学到的东西，以及我们对自己的数据库表这样做的建议。

Defining Schema Change Safety

定义模式变化的安全性

First, it’s important to understand how schema changes safety is defined.

首先，了解如何定义模式变化安全是很重要的。

For this purpose, we should be aware of the procedure LHM uses to execute migrations, as it uses the shadow-table mechanism to ensure there is minimal downtime while the migrations are being carried out. In a nutshell, LHM creates a new table (known as shadow table) with the schema change applied, and sets up triggers on the original table to populate any data related operations (INSERT, UPDATE and DELETE) to the shadow table. Then, it starts copying records, in batches, from the original table to the shadow one. When all records have been copied to the shadow table, LHM automatically renames the two tables and then drop the triggers from the original one.

为此，我们应该了解LHM在执行迁移时使用的程序，因为它使用了影子表机制，以确保在迁移过程中的停机时间最小。简而言之，LHM创建了一个新的表（称为影子表），并应用了模式变更，在原表上设置了触发器，将任何与数据相关的操作（INSERT,UPDATE 和DELETE ）填充到影子表中。然后，它开始分批将记录从原表复制到影子表。当所有的记录都被复制到影子表中时，LHM会自动重新命名这两个表，然后从原始表中删除触发器。

This procedure ensures there’s minimal downtime (see MySQL rename table limitations), while migrations are taking place, but it introduces a new set of potential problems, as we need to make sure that the batched insertions don’t drop records in the process, which...