Mysql – add column without metadata lock in thesql 5.6

MySQLmysql-5.6

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 ?

Best Answer

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?

DISCLAIMER : Not a Full Expert on MySQL 5.6

Looks like the InnoDB Plugin is complaining about the present ibdata1 file.

If data and index pages exist inside ibdata1, it's probably using Antelope as the innodb_file_format.

There are four(4) things you can try:

SUGGESTION #1 : UNINSTALL AND REINSTALL MySQL 5.6

Uninstall MySQL
mv /var/lib/mysql /var/lib/mysql_old
Reinstall MySQL

SUGGESTION #2 : Using a cleaned-up datadir

mv /var/lib/mysql /var/lib/mysql_old
mkdir /var/lib/mysql
mkdir /var/lib/mysql/mysql
cp /var/lib/mysql_old /var/lib/mysql/mysql
chown -R mysql:mysql /var/lib/mysql/mysql
service mysql start

This will construct brand spanking new InnoDB Files

/var/lib/mysql/ibdata1
/var/lib/mysql/ib_logfile0
/var/lib/mysql/ib_logfile1

SUGGESTION #3 : Switch innodb_file_format (May be risky)

This may be a little risky but add this to my.cnf

[mysqld]
innodb_file_format=Barracuda
innodb_file_format_max=Barracuda

and restart mysql

SUGGESTION #4 : GIVE IT A TRY !!!

Mysql – LOCK=NONE when adding NOT NULL constraint fails with “cannot silently convert NULL values” error despite no NULL values being present

This seems to be a bug that was introduced in MySQL 5.6.10 and has since been fixed (although there is no bug report on http://bugs.mysql.com that I can find). The behaviour described in the question does not occur in the latest version of MySQL.

Observed behaviour in MySQL 5.7.5-m15:

mysql> CREATE TABLE `Test` (
    ->   `id` int(10) auto_increment PRIMARY KEY,
    ->   `stuff` varchar(100) DEFAULT NULL
    -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.38 sec)

mysql> ALTER TABLE Test MODIFY stuff varchar(100) NOT NULL, LOCK=none;
Query OK, 0 rows affected (0.97 sec)
Records: 0  Duplicates: 0  Warnings: 0