MySQL create index with LOCK=NONE still locks a table

Question

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

Answer 1

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

• Joe Yahchouchi: 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?
• Alexander Torstling: Classic MySql silent error. Got bitten by it today. Not happy.
• k.liakos: What about if you use SET FOREIGN_KEY_CHECKS=0; before index creation, and re-enable it afterwards?
• William W: I tested SET FOREIGN_KEY_CHECKS=0 first and it still locked for me unfortunately.

Answer 2

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.