how to evade table lock while migration at rails 5

Question

I want to migrate and alter table using such kind of phrase.

 execute "ALTER TABLE `#{table}` CHANGE `#{column.name}` `#{column.name}` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

but while alter database , table will be locked . how can i migrate without down time.

rails5 supports such kind of work around? (rails6 seems supporting advisory lock )

i am using mysql 5.7

https://blog.saeloun.com/2019/09/09/rails-6-disable-advisory-locks.html#:~:text=Rails%20uses%20an%20advisory%20lock,servers%20by%20using%20shared%20connections

Answer 1

You can tell MySQL to not lock the table during this operation, and also use the INPLACE algorithm.

class YourMigration < ActiveRecord::Migration[7.0]
  def up
    execute <<-SQL.squish
      ALTER TABLE your_table
      CHANGE column_name CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
      ALGORITHM=INPLACE, LOCK=NONE;
    SQL
  end

  def down
    execute <<-SQL.squish
      ALTER TABLE your_table
      CHANGE column_name CHARACTER SET utf8mb4_unicode_ci COLLATE utf8mb4,
      ALGORITHM=INPLACE, LOCK=NONE;
    SQL
  end
end