3

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 NO_ENGINE_SUBSTITUTION.

  • SQL-Fiddle (version 5.6.6) does not show any errors. Which version exactly do you have? And what is the SQL_MODE? – ypercubeᵀᴹ Jun 16 '14 at 12:49
  • @ypercube Hmm, weird. Version is 5.6.16 and SELECT @@GLOBAL.sql_mode and SELECT @@SESSION.sql_mode give me NO_ENGINE_SUBSTITUTION. – Mark Amery Jun 16 '14 at 12:55
  • And the table is empty, right? Weird. – ypercubeᵀᴹ Jun 16 '14 at 12:58
  • @ypercube Yep, empty table - the test case above I executed in a newly-created database and then copied directly out of my terminal. (Also note that the same error happens if I first insert some rows without nulls in the relevant column, and happens on my live database on a ~20-million-row table with no NULLs. The failure is not limited to the special case where the table is empty.) – Mark Amery Jun 16 '14 at 13:00
  • Well, the error reason 1846 was added at 5.6.10. That explains why SQL-Fiddle does not show it. I guess you have to wait for someone who knows more details - you can also raise an issue/bug at the official MySQL site or search if there is already something similar. – ypercubeᵀᴹ Jun 16 '14 at 13:09
0

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

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.