LOCK=NONE when adding NOT NULL constraint fails with "cannot silently convert NULL values" error despite no NULL values being present 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&gt; CREATE TABLE `Test` (
    -&gt;   `id` int(10) auto_increment PRIMARY KEY,
    -&gt;   `stuff` varchar(100) DEFAULT NULL
    -&gt; ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.08 sec)
mysql&gt; 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 NO_ENGINE_SUBSTITUTION.
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&gt; CREATE TABLE `Test` (
    -&gt;   `id` int(10) auto_increment PRIMARY KEY,
    -&gt;   `stuff` varchar(100) DEFAULT NULL
    -&gt; ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.38 sec)

mysql&gt; 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