add column without metadata lock in mysql 5.6

Question

I am using MySQL Server version: 5.6.15 Homebrew. I need to add new null column. alter table requires no metadata lock. In order to avoid this MySQL 5.6 introduced algorithm=inplace. Here is the reference articles http://www.mysqlperformanceblog.com/2013/07/05/schema-changes-whats-new-in-mysql-5-6/.

Executing the following sql throws

mysql> alter table candidate_candidate add column responsible_user_id int, algorithm=INPLACE, lock=NONE;
ERROR 1845 (0A000): ALGORITHM=INPLACE is not supported for this operation.
Try ALGORITHM=COPY.

Is there a way to add column without affecting app read/write ?

Answer 1

I have executed the very same query on 5.6.14 and got a positive result:

alter table candidate_candidate add column responsible_user_id int, algorithm=INPLACE, lock=NONE;
Query OK, 0 rows affected (0.48 sec)
Records: 0  Duplicates: 0  Warnings: 0

So I am sure that this kind of operation can be done online. You are probably experiencing some of the exceptions shown at: http://dev.mysql.com/doc/refman/5.6/en/innodb-create-index-limitations.html

My bet would be on having outdated temporal datatypes, foreign keys or fulltext indexes. Any of those on the table? Recently migrated from 5.5? If I am wrong, can you show the full CREATE TABLE?

Answer 2

After upgrading to MySql 5.6. All your ALTER queries on particular table need to be run using algorithm=copy. Once you've done that successfully, from next time onwards when you're doing ALTER queries, you can use algorithm = inplace and it will be done without metalock.