title: Create index locks MySQL 5.6 table. How to avoid that?
body: <p>I need to create an index on a large InnoDB production table and want to do this without locking the table in any way. I am using MySQL 5.6 (.38-83.90). </p>
  
  <p>I tried</p>
  
  <pre><code>create index my_index on my_table(col1, col2); 
  </code></pre>
  
  <p>Neither columns are primary keys. col1 is a foreign key.</p>
  
  <p>Well, this totally locked the table. Other queries were stalled with <strong>"Waiting for table metadata lock"</strong> bringing my website to its knees. I had to kill the <em>create index</em> query.</p>
  
  <p>From this <a href="https://dev.mysql.com/doc/refman/5.6/en/innodb-create-index-overview.html" rel="nofollow noreferrer">https://dev.mysql.com/doc/refman/5.6/en/innodb-create-index-overview.html</a> I thought that it would not lock the table: "... no syntax changes are required... The table remains available for read and write operations while the index is being created or dropped."</p>
  
  <p>I see that I can set LOCK=NONE or LOCK=SHARED, but I don't see that it should be necessary or, if it is, which one I need to use.</p>
  
  <p>"You can specify LOCK=NONE to assert that concurrent DML is permitted during the DDL operation. <em>MySQL automatically permits concurrent DML when possible.</em>"</p>
  
  <p>"You can specify LOCK=SHARED to assert that concurrent queries are permitted during a DDL operation. <em>MySQL automatically permits concurrent queries when possible.</em>"</p>
  
  <p>None of the limitations <a href="https://dev.mysql.com/doc/refman/5.6/en/innodb-create-index-limitations.html" rel="nofollow noreferrer">https://dev.mysql.com/doc/refman/5.6/en/innodb-create-index-limitations.html</a> seem to apply to my case.</p>
  
  <p>What am I missing? </p>