Safely Adding NOT NULL Columns to Your Database Tables

Summary

Shopify’s Database Migrations team investigated whether Large Hadron Migrator (LHM) can safely add NOT NULL columns to existing MySQL tables with minimal downtime. LHM creates a shadow table, installs triggers for INSERT, UPDATE, and DELETE operations, and copies records in batches before renaming the tables. The experiments varied whether the new column had a DEFAULT value or UNIQUE index and whether MySQL used strict or non-strict mode, evaluating trigger compatibility and record counts. A NOT NULL column with a DEFAULT was backward compatible, but adding a UNIQUE index could cause data loss; without a DEFAULT, strict mode broke compatibility while non-strict mode used implicit defaults, which may be undesirable.

Context

The investigation examined schema-change safety when adding NOT NULL columns to existing tables through LHM’s shadow-table migration process. Safety was defined by whether operations on the original table could populate the shadow table without crashing and whether both tables contained the same number of records after migration.

Approach / What changed

The experiment simulated LHM by configuring MySQL mode, creating an original and shadow table, defining triggers, populating initial data, running INSERT, UPDATE, and DELETE operations, and comparing the resulting records. It varied DEFAULT value inclusion, UNIQUE index presence, operation type, and strict versus non-strict MySQL mode.

Takeaways

• LHM considers a schema change safe only when INSERT, UPDATE, and DELETE operations remain backward compatible and the shadow and original tables have equal record counts after migration.
• Adding a NOT NULL column without a DEFAULT can break trigger compatibility in strict MySQL mode; non-strict mode may apply an implicit default instead, such as an empty string in the experiment.
• Adding a UNIQUE index during a shadow-table migration can cause data loss when duplicate values already exist; the investigation recommends checking for duplicates first.

Related reading