MySQL – LOCK=NONE Error Adding NOT NULL Constraint

ddlinnodbMySQL

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.

Best Answer

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

Related Solutions

Mysql – InnoDB create table error: “Row size too large”

The answers to your questions are complex, because they vary by InnoDB file format. Today, there are two formats, called Antelope and Barracuda.

The central tablespace file (ibdata1) is always in Antelope format. If you use file-per-table, you can make the individual files use Barracuda format by setting innodb_file_format=Barracuda in my.cnf.

Basic points:

One 16KB page of InnoDB data must hold at least two rows of data. Plus each page has a header and a footer containing page checksums and log sequence number and so on. That's where you get your limit of a bit less than 8KB per row.
Fixed-size data types like INTEGER, DATE, FLOAT, CHAR are stored on this primary data page and count toward the row size limit.
Variable-sized data types like VARCHAR, TEXT, BLOB are stored on overflow pages, so they don't count fully toward the row size limit. In Antelope, up to 768 bytes of such columns are stored on the primary data page in addition to being stored on the overflow page. Barracuda supports a dynamic row format, so it may store only a 20-byte pointer on the primary data page.
Variable-size data types are also prefixed with 1 or more bytes to encode the length. And InnoDB row format also has an array of field offsets. So there's an internal structure more or less documented in their wiki. [EDIT] Dead link - here looks better now.

Barracuda also supports a ROW_FORMAT=COMPRESSED to gain further storage efficiency for overflow data.

I also have to comment that I've never seen a well-designed table exceed the row size limit. It's a strong "code smell" that you're violating the repeating groups condition of First Normal Form.

Mysql Impossible WHERE and enum

I create the sample you gave with this:

drop database if exists a1ex07;
create database a1ex07;
use a1ex07
CREATE TABLE test_1(id int not null auto_increment PRIMARY KEY, val enum('A','B') not null); 
insert into test_1(val) values ('A'); 
explain select * from test_1 where val is null;  
explain select * from test_1 where val ='C';

Here is it loaded:

mysql> drop database if exists a1ex07;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> create database a1ex07;
Query OK, 1 row affected (0.00 sec)

mysql> use a1ex07
Database changed
mysql> CREATE TABLE test_1(id int not null auto_increment PRIMARY KEY, val enum('A','B') not null);
Query OK, 0 rows affected (0.05 sec)

mysql> insert into test_1(val) values ('A');
Query OK, 1 row affected (0.06 sec)

mysql> explain select * from test_1 where val is null;
+----+-------------+-------+------+---------------+------+---------+------+------+------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra            |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------+
|  1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | Impossible WHERE |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------+
1 row in set (0.00 sec)

mysql> explain select * from test_1 where val ='C';
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | test_1 | ALL  | NULL          | NULL | NULL    | NULL |    1 | Using where |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql>

In the first EXPLAIN plan, the Impossible where would be based on the definition of the column you gave, which is val enum('A','B') not null. This would help the MySQL Query Optimizer quickly rule out unneccesary evaluations, especially if the table had millions of rows.

In the second EXPLAIN plan, the Using where indicates that MySQL Query Optimizer would have to evaluate a potential value (C) to see if it resides in the table. I believe what you are looking for is for the MySQL Query Optimizer to check the definition of the column, which is enum('A','B'). It should but it probably doesn't. You should submit that as a bug report to see if Oracle, out of the goodness of their heart, will address this.

UPDATE 2012-01-12 14:08 EDT

I just looked around mysql website for any bugs regarding EXPLAIN and ENUM. At this time, there are no reported bugs of ths nature. You should submit this one ASAP.

Best Answer

Related Solutions

Mysql – InnoDB create table error: “Row size too large”

Mysql Impossible WHERE and enum

UPDATE 2012-01-12 14:08 EDT

Related Question