Migrations

Please refer to the Active Record Migrations guide for working with migrations in general.

Table of Contents Updating schema dumps Ruby dump Schema dump Why not dumping from development environments? Using pt-online-schema-change

Updating schema dumps

We track both types of schema dumps in our repository: Ruby and SQL. Every once in a while, we generate new versions of these dumps (refer to #4000 for an example update).

Ruby dump

Run on a production app server:

cd ~/app/current
bundle exec rake db:schema:dump
# Ruby dump at ~/app/current/db/schema.rb

We need to manually change the version in ActiveRecord::Schema.define to the latest migration timestamp. Then we need to check the existing pull requests for migrations and bump their timestamps to later.

Schema dump

Run on a database server (preferably a secondary one):

mysqldump --no-data --routines --skip-comments --result-file=structure.sql otwarchive_production

then manually:

• remove AUTO_INCREMENT values;
• append a dump of the schema_migrations table, generated from bundle exec rails r "puts ActiveRecord::Base.connection.dump_schema_information".

The resulting output will be similar to that of bundle exec rake db:structure:dump (which we can't use because we've started provisioning app and database servers differently, and app servers no longer have mysqldump available).

Why not dumping from development environments?

We avoid getting new versions of these dumps from development environments because we may include changes that are not actually in production, for example:

• Timestamps of WIP migrations that only exist in development.
• Migrations that we overlook and never run in production, e.g. AO3-5862.
• Changing Rails defaults, e.g. Rails 5.1 started using BIGINT for primary keys by default while most of our existing tables in production are still using INT.

Using pt-online-schema-change

We use Percona Toolkit's pt-online-schema-change tool when we need to change a large table without going into maintenance mode. This lets us make changes without blocking writes to the table.

Generally, any migrations for tables with more than 500000 rows should use pt-online-schema-change in staging and production.

Since August 2023, this can be done simply by adding the following line to any standard migration (example):

uses_departure! if Rails.env.staging? || Rails.env.production?

Notes:

• Because of how pt-online-schema-change works (creating a new table with the desired changes and slowly copying data over in the background), any migrations that add a unique index will also silently remove duplicate rows in the table at the same time. Sometimes this is exactly what we want (e.g. AO3-5597 which cleans up duplicate kudos), sometimes we need to fix the duplicate rows pre-migration to keep them (e.g. AO3-3469).