5

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 ?

  • are you sure alter table requires NO metadata lock? I guess it requires such a lock. – asgs Jul 16 '18 at 9:33
3

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?

3

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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