Rails Migrations MySQL algorithm: :concurrently alternative

Question

I have this rails migration, I'm adding an index to a very large table and am aware of the fact that introducing a migration that would lock the table and potentially block build processing on Semaphore is quite risky. So I used the safe route, and triggered a concurrent index build instead

class AddIndexToEventsTable < ActiveRecord::Migration[5.2]
disable_ddl_transaction!

def change
  add_index :events, [:status, :created_at], algorithm: :concurrently
end
end

but after migrating, it turns out to be unsuccessful here's the error:

rake aborted!
StandardError: An error has occurred, all later migrations canceled:

Algorithm must be one of the following: :default, :copy, :inplace

Im using rails 5.2.5

How can I replicate the functionality algorithm: :concurrently has with PostGres with MYSQL.

Your Rails version is less important than the mysql version https://stackoverflow.com/questions/4244685/create-an-index-on-a-huge-mysql-production-table-without-table-locking . It could be you won't actually have a problem depending on version — Joel Blum, Apr 23 '21 at 10:38

score 1 · Answer 1 · answered Jan 26 '22 at 03:38

To make sure you don't have any locks the option you want is

LOCK=NONE

Sadly I do not believe rails migrations have support for this option. One possible solution is to manually build the SQL and run it with execute.

An example can be seen below:

class AddIndexToEventsTable < ActiveRecord::Migration[5.2]
disable_ddl_transaction!

 def change
    execute <<~SQL
      ALTER TABLE events
      ADD INDEX index_events_on_status_created_at(status, created_at),
      ALGORITHM=DEFAULT,
      LOCK=NONE;
    SQL
 end
end

Rails Migrations MySQL algorithm: :concurrently alternative

1 Answers1