MySQL migrations can't do zero-downtime column changes without raw SQL

rubyonrails-core
, ,
1

Rails already supports algorithm: for MySQL index operations (:default, :copy, :inplace, :instant) and for PostgreSQL (algorithm: :concurrently).

However, two gaps exist for MySQL users:

  1. No lock: option MySQL supports LOCK = {DEFAULT|NONE|SHARED|EXCLUSIVE} on DDL statements to control table locking behavior, but Rails doesn’t expose this.
  2. algorithm only works on index operations MySQL supports ALGORITHM on all ALTER TABLE statements (add_column, change_column, remove_column, rename_column), not just CREATE INDEX / DROP INDEX.

This matters for online schema changes on large production tables. For example, adding a nullable column with ALGORITHM=INSTANT, LOCK=NONE completes in milliseconds without blocking reads or writes. Without these options, users must drop down to raw SQL:

# Today — forced to use raw SQL
execute "ALTER TABLE users ADD name VARCHAR(255), ALGORITHM = INSTANT, LOCK = NONE"

# Proposed — use the migration DSL
add_column :users, :name, :string, algorithm: :instant, lock: :none

The proposed change would support algorithm: and lock: on:

add_index    :users, :email, algorithm: :inplace, lock: :none

remove_index :users, :email, algorithm: :inplace, lock: :none

add_column    :users, :name, :string, algorithm: :instant, lock: :none

change_column :users, :name, :string, null: false, algorithm: :inplace, lock: :none

remove_column :users, :name, algorithm: :inplace, lock: :none

rename_column :users, :name, :full_name, algorithm: :inplace, lock: :none

Valid lock: values would be :default:, :none, :shared, and :exclusive, mirroring how algorithm: already maps to ALGORITHM = {DEFAULT|COPY|INPLACE|INSTANT}.

I have a working implementation with tests and a CHANGELOG entry ready.

Happy to open a PR if there’s interest.

2 Likes
2

Personally I would love this. A pain point of ours is that we have to use SQL to active these outcomes because we want the ability to perform migrations while our applications are online.