8

I have the following MySQL RoR Migrations:

class ReindexRpushNotification < ActiveRecord::Migration
  def up
    execute("DROP INDEX `index_rpush_notifications_multi` ON rpush_notifications;")
    execute("ALTER TABLE rpush_notifications ADD INDEX index_rpush_notifications_multi (delivered, failed, processing, deliver_after), ALGORITHM=INPLACE, LOCK=NONE;")
  end

  def down
    execute("DROP INDEX `index_rpush_notifications_multi` ON rpush_notifications;")
    execute("ALTER TABLE rpush_notifications ADD INDEX index_rpush_notifications_multi (delivered, failed), ALGORITHM=INPLACE, LOCK=NONE;")
  end
end

during this migration I'm trying to perform some requests (GET, COUNT, DELETE, UPDATE) but nothing work, all these requests just wait

I found an info about index creation in background here

https://stackoverflow.com/a/36064200

http://dev.mysql.com/doc/refman/5.6/en/innodb-create-index-overview.html

but it doesn't work for us

Did anybody try LOCK=NONE?

We use MySQL 5.6.23 on AWS RDS

2 Answers 2

14

Looks like I have a clue why it doesn't work

Our table have the following constraint:

CONSTRAINT `rpush_notifications_event_id_fk` 
  FOREIGN KEY (`event_id`) REFERENCES `events` (`id`) ON DELETE CASCADE

Next, I found here one interesting thing: https://blogs.oracle.com/mysqlinnodb/entry/online_alter_table_in_mysql

Online operation (LOCK=NONE) is not allowed in the following cases:

  • when adding an AUTO_INCREMENT column,
  • when the table contains FULLTEXT indexes or a hidden FTS_DOC_ID column, or
  • when there are FOREIGN KEY constraints referring to the table, with ON…CASCADE or ON…SET NULL option.

so looks like we have a 3rd case

4
  • 4
    Still, shouldn't it throw an error rather than to just proceed while locking production tables and bringing down the wrath of the masses on you? Jun 6, 2018 at 8:04
  • Classic MySql silent error. Got bitten by it today. Not happy. Nov 20, 2018 at 15:55
  • 1
    What about if you use SET FOREIGN_KEY_CHECKS=0; before index creation, and re-enable it afterwards?
    – k.liakos
    Feb 28, 2019 at 11:19
  • I tested SET FOREIGN_KEY_CHECKS=0 first and it still locked for me unfortunately.
    – William W
    Jul 11, 2023 at 23:27
-1

Consider adding a new index first, then DROP the old index. DROP should be a lot faster than ADD.

Or, if that fails, don't drop the old index.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.