Migrations

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

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 by connecting to a production app server and running:

cd ~/app/current
bundle exec rake db:schema:dump    # Ruby dump
bundle exec rake db:structure:dump # SQL dump

The new dumps can be found at:

~/app/current/db/schema.rb
~/app/current/db/structure.sql

We need to manually edit them:

• Ruby dump: 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.
• SQL dump: Remove AUTO_INCREMENT values.

For an example schema dump update, refer to #3677.

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.