I'm trying to use MySQL's online DDL feature to add a
NOT NULL constraint to an InnoDB table without blocking writes to the table. According to manual, this should be possible - the linked table summarising MySQL 5.6's online DDL capabilities has a 'YES' in the Allows concurrent DML column of the Make column NOT NULL row.
However, when I actually try this, it fails under absolutely all circumstances I've tried. Even if the column I'm trying to add the
NOT NULL constraint to does not contain any
NULL values, I get the following strange error:
ERROR 1846 (0A000): LOCK=NONE is not supported. Reason: cannot silently convert NULL values, as required in this SQL_MODE. Try LOCK=SHARED.
Here's an ultra-simple test case at the MySQL shell:
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.08 sec) mysql> ALTER TABLE Test MODIFY stuff varchar(100) NOT NULL, 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.
How (if at all) can I get around this and add a
NOT NULL constraint while permitting concurrent DML?
In case it's relevant: my exact MySQL version is 5.6.16 and my SQL_MODE is