Safely Adding NOT NULL Columns to Your Database Tables

Recommendations from Shopify's Data Migrations team for adding NOT NULL columns safely to your own database tables, based on a recent investigation into schema change safety using using the popular Large Hadron Migrator (LHM) gem.

Shopify logo
Shopify
•
Sinclert Pérez
•7 min read•intermediate•
--
•View Original
ActiveRecordMySQLRubySQL

Overview

This article discusses the safe addition of NOT NULL columns to database tables, focusing on the use of the Large Hadron Migrator (LHM) gem for schema changes in MySQL databases. It outlines the safety definitions, potential pitfalls, and recommendations for implementing such changes without data loss.

What You'll Learn

1

How to safely add NOT NULL columns to MySQL tables using LHM

2

Why defining a DEFAULT value is crucial when adding NOT NULL columns

3

When to use UNIQUE INDEX in schema changes and its implications

Prerequisites & Requirements

  • Understanding of MySQL schema changes and database migrations
  • Familiarity with the Large Hadron Migrator (LHM) gem(optional)

Key Questions Answered

What is the procedure LHM uses for schema changes?
LHM uses a shadow-table mechanism to perform migrations with minimal downtime. It creates a new shadow table with the schema change and sets up triggers on the original table to ensure data integrity during the migration process.
What factors determine the safety of adding a NOT NULL column?
The safety of adding a NOT NULL column is determined by including a DEFAULT value, the presence of a UNIQUE INDEX, and the MySQL mode configuration (strict or non-strict). These factors help prevent data loss during the migration.
How does MySQL mode affect the addition of NOT NULL columns?
MySQL mode can significantly impact the addition of NOT NULL columns. In strict mode, failing to provide a DEFAULT value can lead to data loss, while in non-strict mode, an implicit DEFAULT value may be assigned, which could be undesirable.
What are the consequences of adding a UNIQUE INDEX during schema changes?
Adding a UNIQUE INDEX during schema changes can lead to data loss if there are duplicate values in the indexed columns before the migration. It is essential to check for duplicates before implementing such changes.

Technologies & Tools

Some links below are affiliate links. We may earn a commission if you make a purchase.

Database
Mysql
Used for managing the database schema changes and migrations.
Tool
Large Hadron Migrator (lhm)
A Ruby gem used for performing online ActiveRecord migrations safely.

Key Actionable Insights

1
Always define a DEFAULT value when adding NOT NULL columns to avoid unexpected behaviors during migrations.
This practice ensures that existing records in the original table can be populated correctly in the shadow table, preventing potential application compatibility issues.
2
Check for duplicate values before adding UNIQUE INDEX to avoid data loss.
This precaution helps maintain data integrity and prevents migration failures that could arise from existing duplicates in the database.
3
Utilize the shadow-table mechanism for schema changes to minimize downtime.
This approach allows for safe migrations without impacting the availability of the original table, ensuring that ongoing operations can continue seamlessly.

Common Pitfalls

1
Failing to provide a DEFAULT value for a NOT NULL column can lead to data loss during migrations.
This occurs because, in strict MySQL mode, operations that attempt to insert NULL values into the new column will fail, breaking compatibility with existing applications.
2
Introducing a UNIQUE INDEX without checking for duplicates can result in migration failures.
If duplicates exist in the columns being indexed, the migration will not complete successfully, leading to potential data integrity issues.

Related Concepts

Database Migrations
Schema Changes
Data Integrity
Mysql Triggers