Description:
Per documentation at http://dev.mysql.com/doc/refman/5.6/en/innodb-create-index-overview.html, "Make column NOT NULL" should
1) allow ALGORITHM=INPLACE if no columns contains NULL, and/or SQL_MODE doesn't include strick_all_tables
2) allow concurrent DML
But tests on 5.6.22 showed that both seem not true.
How to repeat:
Server version: 5.6.22 MySQL Community Server (GPL)
mysql> CREATE TABLE `t_alter` ( `a` int(11) NOT NULL AUTO_INCREMENT, `b` varchar(10), PRIMARY KEY (`a`), KEY `idx_b` (`b`) ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into t_alter values (1, "hi"), (2,"hello");
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
1) There is NO null values, but ALGORITHM=INPLACE is not allowed
mysql> alter table t_alter modify `b` varchar(10) not null default '', lock=none, algorithm=inplace;
ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: cannot silently convert NULL values, as required in this SQL_MODE. Try ALGORITHM=COPY.
2) Tried empty SQL_MODE, no difference.
mysql> show global variables like 'sql_mode';
+---------------+------------------------+
| Variable_name | Value |
+---------------+------------------------+
| sql_mode | NO_ENGINE_SUBSTITUTION |
+---------------+------------------------+
1 row in set (0.00 sec)
mysql> set sql_mode = '';
Query OK, 0 rows affected (0.00 sec)
mysql> alter table t_alter modify `b` varchar(10) not null default '', lock=none, algorithm=inplace;
ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: cannot silently convert NULL values, as required in this SQL_MODE. Try ALGORITHM=COPY.
3) algorithm=copy doesn't allow lock=none, basically lock=none is not supported for this alter.
mysql> alter table t_alter modify `b` varchar(10) not null default '', lock=none, algorithm=copy;
ERROR 1846 (0A000): LOCK=NONE is not supported. Reason: COPY algorithm requires a lock. Try LOCK=SHARED.
mysql>
mysql> alter table t_alter modify `b` varchar(10) not null default '', lock=none;
ERROR 1846 (0A000): LOCK=NONE is not supported. Reason: cannot silently convert NULL values, as required in this SQL_MODE. Try LOCK=SHARED.
Suggested fix:
Either a code bug or documentation bug.
