Safely Adding NOT NULL Columns to Your Database Tables - Shopify

Adding NOT NULL Columns Without Breaking Your Database

Schema changes are among the riskiest operations you can perform on a production database. Shopify's Database Migrations team recently investigated how the Large Hadron Migrator (LHM) gem handles one particularly tricky case: adding a NOT NULL column to an existing MySQL table. The findings apply to any tool that uses the same shadow-table migration strategy.

LHM minimizes downtime by creating a new "shadow" table with the desired schema change, then setting up triggers on the original table to forward INSERT, UPDATE, and DELETE operations to the shadow table while batched record copies are in progress. When the copy finishes, the tables are renamed. This approach assumes that every write to the original table can be replicated to the shadow table without issues—but that assumption doesn't always hold when a NOT NULL column is introduced.

What Makes a Schema Change Safe

A migration is considered safe when it meets two conditions:

• Backward compatibility: After the migration starts, INSERT, UPDATE, and DELETE statements against the original table must successfully populate the shadow table via triggers.
• No data loss: Once the migration completes, the record count in the shadow table must match the original table.

When a NOT NULL column is added, existing rows have no value for that column. The outcome depends on three variables: whether a DEFAULT value is defined, whether a UNIQUE INDEX is added, and whether MySQL is running in strict or non-strict mode.

Testing the Risky Combinations

The investigation simulated LHM's behavior step by step. First, the MySQL mode was configured and the original table created. Second, a shadow table was built as LHM would create it with the new column definition. Third, triggers were set up to replicate operations between the two tables.

With that setup in place, the team ran INSERT, UPDATE, and DELETE statements against the original table and compared the results in both tables.

The experiment above demonstrates a concrete failure case: when a NOT NULL column with a DEFAULT value is added along with a UNIQUE INDEX, and MySQL runs in strict mode, INSERT operations can silently drop records. The original and shadow tables end up with different row counts.

Safety Matrix for NOT NULL Column Additions

Running this experiment across all combinations of operation type, DEFAULT clause, UNIQUE INDEX, and MySQL mode produces the following results:

*In cases marked with an asterisk, the record counts match, but MySQL assigns an implicit default value (such as an empty string) to the new column for existing rows. The actual value depends on the column's data type, per MySQL's implicit default handling rules.

Guidelines for Safe Migrations

The results point to two clear recommendations:

1. Always define a DEFAULT value. Adding a NOT NULL column without a DEFAULT clause is a recipe for trouble. In strict mode, existing applications that write to the table will start failing because their INSERT and UPDATE statements can't be applied to the shadow table. In non-strict mode, the migration succeeds but silently populates the new column with an implicit default value that is likely not what you intended.
2. Treat UNIQUE indexes with extreme care. A UNIQUE INDEX on the new column can cause data loss if duplicate values exist in the original table when the migration runs. Before adding a unique constraint through a shadow-table tool, verify that the covered columns contain no pre-existing duplicates.

Any schema-change tool built on the shadow-table mechanism inherits these risks. Checking for these conditions before launching a migration is the only reliable way to avoid data loss and downtime.