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 Writing Rails migrations that use 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:

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.

For example, we perform the utf8mb4 upgrade on production:

pt-online-schema-change D=$DATABASE,t=$TABLE \
  --alter "ROW_FORMAT=$ROW_FORMAT CHARACTER SET utf8mb4 COLLATE $COLLATE" \
  -uroot --ask-pass --chunk-size=5k --max-flow-ctl 0 --pause-file /tmp/pauseme \
  --max-load Threads_running=15 --critical-load Threads_running=100 \
  --set-vars innodb_lock_wait_timeout=2 --alter-foreign-keys-method=auto \
  --execute

See also how to do this upgrade on your development database.

Writing Rails migrations that use pt-online-schema-change

Generally, any migrations for tables with more than 500000 rows should use pt-online-schema-change. We set up Rails migrations that do things normally in development and testing, but in staging and production will do the following:

• Output the pt-online-schema-change command for database administrators, without actually running it.
• Mark the migration as done (in the schema_migrations table).

See #3816 for example.

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).
• Only use --max-flow-ctl 0 if the environment is running Galera.