InnoDB Strict Mode enforces stricter SQL compliance, returning errors instead of warnings for invalid CREATE TABLE options or potential data loss.
InnoDB strict mode is similar to SQL strict mode. When it is enabled, certain InnoDB warnings become errors instead.
InnoDB strict mode is enabled by default.
InnoDB strict mode can be enabled or disabled by configuring the innodb_strict_mode server system variable.
Its global value can be changed dynamically with SET GLOBAL:
Its value for the current session can also be changed dynamically with SET SESSION:
It can also be set in a server in an prior to starting up the server:
If InnoDB strict mode is enabled, and if a DDL statement is executed and invalid or conflicting are specified, then an error is raised. The error will only be a generic error that says the following:
However, more details about the error can be found by executing .
For example, the error is raised in the following cases:
The table option is set to a non-zero value, but the table option is set to some row format other than the row format:
The table option is set to a non-zero value, but the configured value is larger than either 16 or the value of the system variable, whichever is smaller.
The table option is set to a non-zero value, but the system variable is not set to ON.
The table option is set to a non-zero value, but it is not set to one of the supported values: [1, 2, 4, 8, 16].
The table option is set to the row format, but the system variable is not set to ON.
The table option is set to a value, but it is not set to one of the values supported by InnoDB: , , , and .
Either the table option is set to a non-zero value or the table option is set to the row format, but the system variable is set to a value greater than 16k.
The table option is set, but the system variable is not set to ON.
The table option is set, but the table is a .
The table option is set.
The table option is set to 1, so is enabled, but the table option is set to some row format other than the or row formats.
The table option is set to 1, so is enabled, but the system variable is not set to ON.
The table option is set to 1, so is enabled, but the table option is also specified.
The table option is set, but
the table option is set to 0, so is disabled.
If InnoDB strict mode is enabled, and if a table uses the row format, and if the table's is too small to contain a row, then an error is returned by the statement.
If InnoDB strict mode is enabled, and if a table exceeds its row format's , then InnoDB will return an error.
See for more information.
This page is licensed: CC BY-SA / Gnu FDL
SET GLOBAL innodb_strict_mode=ON;SET SESSION innodb_strict_mode=ON;[mariadb]
...
innodb_strict_mode=ONERROR 1005 (HY000): Can't create table `db1`.`tab` (errno: 140 "Wrong create options")SET SESSION innodb_strict_mode=ON;
CREATE OR REPLACE TABLE tab (
id INT PRIMARY KEY,
str VARCHAR(50)
)
KEY_BLOCK_SIZE=4
ROW_FORMAT=DYNAMIC;
ERROR 1005 (HY000): Can't create table `db1`.`tab` (errno: 140 "Wrong create options")
SHOW WARNINGS;
+---------+------+--------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+--------------------------------------------------------------------+
| Warning | 1478 | InnoDB: cannot specify ROW_FORMAT = DYNAMIC with KEY_BLOCK_SIZE. |
| Error | 1005 | Can't create table `db1`.`tab` (errno: 140 "Wrong create options") |
| Warning | 1030 | Got error 140 "Wrong create options" from storage engine InnoDB |
+---------+------+--------------------------------------------------------------------+
3 rows in set (0.000 sec)SET SESSION innodb_strict_mode=ON;
CREATE OR REPLACE TABLE tab (
id INT PRIMARY KEY,
str VARCHAR(50)
)
KEY_BLOCK_SIZE=16;
ERROR 1005 (HY000): Can't create table `db1`.`tab` (errno: 140 "Wrong create options")
SHOW WARNINGS;
+---------+------+--------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+--------------------------------------------------------------------+
| Warning | 1478 | InnoDB: KEY_BLOCK_SIZE=16 cannot be larger than 8. |
| Error | 1005 | Can't create table `db1`.`tab` (errno: 140 "Wrong create options") |
| Warning | 1030 | Got error 140 "Wrong create options" from storage engine InnoDB |
+---------+------+--------------------------------------------------------------------+
3 rows in set (0.000 sec)SET GLOBAL innodb_file_per_table=OFF;
SET SESSION innodb_strict_mode=ON;
CREATE OR REPLACE TABLE tab (
id INT PRIMARY KEY,
str VARCHAR(50)
)
KEY_BLOCK_SIZE=4;
ERROR 1005 (HY000): Can't create table `db1`.`tab` (errno: 140 "Wrong create options")
SHOW WARNINGS;
+---------+------+--------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+--------------------------------------------------------------------+
| Warning | 1478 | InnoDB: KEY_BLOCK_SIZE requires innodb_file_per_table. |
| Error | 1005 | Can't create table `db1`.`tab` (errno: 140 "Wrong create options") |
| Warning | 1030 | Got error 140 "Wrong create options" from storage engine InnoDB |
+---------+------+--------------------------------------------------------------------+
3 rows in set (0.000 sec)SET SESSION innodb_strict_mode=ON;
CREATE OR REPLACE TABLE tab (
id INT PRIMARY KEY,
str VARCHAR(50)
)
KEY_BLOCK_SIZE=5;
ERROR 1005 (HY000): Can't create table `db1`.`tab` (errno: 140 "Wrong create options")
SHOW WARNINGS;
+---------+------+-----------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+-----------------------------------------------------------------------+
| Warning | 1478 | InnoDB: invalid KEY_BLOCK_SIZE = 5. Valid values are [1, 2, 4, 8, 16] |
| Error | 1005 | Can't create table `db1`.`tab` (errno: 140 "Wrong create options") |
| Warning | 1030 | Got error 140 "Wrong create options" from storage engine InnoDB |
+---------+------+-----------------------------------------------------------------------+
3 rows in set (0.000 sec)SET GLOBAL innodb_file_per_table=OFF;
SET SESSION innodb_strict_mode=ON;
CREATE OR REPLACE TABLE tab (
id INT PRIMARY KEY,
str VARCHAR(50)
)
ROW_FORMAT=COMPRESSED;
ERROR 1005 (HY000): Can't create table `db1`.`tab` (errno: 140 "Wrong create options")
SHOW WARNINGS;
+---------+------+--------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+--------------------------------------------------------------------+
| Warning | 1478 | InnoDB: ROW_FORMAT=COMPRESSED requires innodb_file_per_table. |
| Error | 1005 | Can't create table `db1`.`tab` (errno: 140 "Wrong create options") |
| Warning | 1030 | Got error 140 "Wrong create options" from storage engine InnoDB |
+---------+------+--------------------------------------------------------------------+
3 rows in set (0.000 sec)SET SESSION innodb_strict_mode=ON;
CREATE OR REPLACE TABLE tab (
id INT PRIMARY KEY,
str VARCHAR(50)
)
ROW_FORMAT=PAGE;
ERROR 1005 (HY000): Can't create table `db1`.`tab` (errno: 140 "Wrong create options")
SHOW WARNINGS;
+---------+------+--------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+--------------------------------------------------------------------+
| Warning | 1478 | InnoDB: invalid ROW_FORMAT specifier. |
| Error | 1005 | Can't create table `db1`.`tab` (errno: 140 "Wrong create options") |
| Warning | 1030 | Got error 140 "Wrong create options" from storage engine InnoDB |
+---------+------+--------------------------------------------------------------------+
3 rows in set (0.000 sec)SET SESSION innodb_strict_mode=ON;
CREATE OR REPLACE TABLE tab (
id INT PRIMARY KEY,
str VARCHAR(50)
)
ROW_FORMAT=COMPRESSED;
ERROR 1005 (HY000): Can't create table `db1`.`tab` (errno: 140 "Wrong create options")
SHOW WARNINGS;
+---------+------+-----------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+-----------------------------------------------------------------------+
| Warning | 1478 | InnoDB: Cannot create a COMPRESSED table when innodb_page_size > 16k. |
| Error | 1005 | Can't create table `db1`.`tab` (errno: 140 "Wrong create options") |
| Warning | 1030 | Got error 140 "Wrong create options" from storage engine InnoDB |
+---------+------+-----------------------------------------------------------------------+
3 rows in set (0.00 sec)SET GLOBAL innodb_file_per_table=OFF;
SET SESSION innodb_strict_mode=ON;
CREATE OR REPLACE TABLE tab (
id INT PRIMARY KEY,
str VARCHAR(50)
)
DATA DIRECTORY='/mariadb';
ERROR 1005 (HY000): Can't create table `db1`.`tab` (errno: 140 "Wrong create options")
SHOW WARNINGS;
+---------+------+--------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+--------------------------------------------------------------------+
| Warning | 1478 | InnoDB: DATA DIRECTORY requires innodb_file_per_table. |
| Error | 1005 | Can't create table `db1`.`tab` (errno: 140 "Wrong create options") |
| Warning | 1030 | Got error 140 "Wrong create options" from storage engine InnoDB |
+---------+------+--------------------------------------------------------------------+
3 rows in set (0.000 sec)SET SESSION innodb_strict_mode=ON;
CREATE OR REPLACE TEMPORARY TABLE tab (
id INT PRIMARY KEY,
str VARCHAR(50)
)
DATA DIRECTORY='/mariadb';
ERROR 1005 (HY000): Can't create table `db1`.`tab` (errno: 140 "Wrong create options")
SHOW WARNINGS;
+---------+------+--------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+--------------------------------------------------------------------+
| Warning | 1478 | InnoDB: DATA DIRECTORY cannot be used for TEMPORARY tables. |
| Error | 1005 | Can't create table `db1`.`tab` (errno: 140 "Wrong create options") |
| Warning | 1030 | Got error 140 "Wrong create options" from storage engine InnoDB |
+---------+------+--------------------------------------------------------------------+
3 rows in set (0.000 sec)SET SESSION innodb_strict_mode=ON;
CREATE OR REPLACE TABLE tab (
id INT PRIMARY KEY,
str VARCHAR(50)
)
INDEX DIRECTORY='/mariadb';
ERROR 1005 (HY000): Can't create table `db1`.`tab` (errno: 140 "Wrong create options")
SHOW WARNINGS;
+---------+------+--------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+--------------------------------------------------------------------+
| Warning | 1478 | InnoDB: INDEX DIRECTORY is not supported |
| Error | 1005 | Can't create table `db1`.`tab` (errno: 140 "Wrong create options") |
| Warning | 1030 | Got error 140 "Wrong create options" from storage engine InnoDB |
+---------+------+--------------------------------------------------------------------+
3 rows in set (0.000 sec)SET SESSION innodb_strict_mode=ON;
CREATE OR REPLACE TABLE tab (
id INT PRIMARY KEY,
str VARCHAR(50)
)
PAGE_COMPRESSED=1
ROW_FORMAT=COMPRESSED;
ERROR 1005 (HY000): Can't create table `db1`.`tab` (errno: 140 "Wrong create options")
SHOW WARNINGS;
+---------+------+--------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+--------------------------------------------------------------------+
| Warning | 140 | InnoDB: PAGE_COMPRESSED table can't have ROW_TYPE=COMPRESSED |
| Error | 1005 | Can't create table `db1`.`tab` (errno: 140 "Wrong create options") |
| Warning | 1030 | Got error 140 "Wrong create options" from storage engine InnoDB |
+---------+------+--------------------------------------------------------------------+
3 rows in set (0.000 sec)SET GLOBAL innodb_file_per_table=OFF;
SET SESSION innodb_strict_mode=ON;
CREATE OR REPLACE TABLE tab (
id INT PRIMARY KEY,
str VARCHAR(50)
)
PAGE_COMPRESSED=1;
ERROR 1005 (HY000): Can't create table `db1`.`tab` (errno: 140 "Wrong create options")
SHOW WARNINGS;
+---------+------+--------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+--------------------------------------------------------------------+
| Warning | 140 | InnoDB: PAGE_COMPRESSED requires innodb_file_per_table. |
| Error | 1005 | Can't create table `db1`.`tab` (errno: 140 "Wrong create options") |
| Warning | 1030 | Got error 140 "Wrong create options" from storage engine InnoDB |
+---------+------+--------------------------------------------------------------------+
3 rows in set (0.000 sec)SET SESSION innodb_strict_mode=ON;
CREATE OR REPLACE TABLE tab (
id INT PRIMARY KEY,
str VARCHAR(50)
)
PAGE_COMPRESSED=1
KEY_BLOCK_SIZE=4;
ERROR 1005 (HY000): Can't create table `db1`.`tab` (errno: 140 "Wrong create options")
SHOW WARNINGS;
+---------+------+--------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+--------------------------------------------------------------------+
| Warning | 140 | InnoDB: PAGE_COMPRESSED table can't have key_block_size |
| Error | 1005 | Can't create table `db1`.`tab` (errno: 140 "Wrong create options") |
| Warning | 1030 | Got error 140 "Wrong create options" from storage engine InnoDB |
+---------+------+--------------------------------------------------------------------+
3 rows in set (0.000 sec)SET SESSION innodb_strict_mode=ON;
CREATE OR REPLACE TABLE tab (
id INT PRIMARY KEY,
str VARCHAR(50)
)
PAGE_COMPRESSED=0
PAGE_COMPRESSION_LEVEL=9;
ERROR 1005 (HY000): Can't create table `db1`.`tab` (errno: 140 "Wrong create options")
SHOW WARNINGS;
+---------+------+--------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+--------------------------------------------------------------------+
| Warning | 140 | InnoDB: PAGE_COMPRESSION_LEVEL requires PAGE_COMPRESSED |
| Error | 1005 | Can't create table `db1`.`tab` (errno: 140 "Wrong create options") |
| Warning | 1030 | Got error 140 "Wrong create options" from storage engine InnoDB |
+---------+------+--------------------------------------------------------------------+
3 rows in set (0.000 sec)ERROR 1118 (42000): Row size too large (> 8126). Changing some columns to
TEXT OR BLOB may help. IN current row format, BLOB prefix of 0 bytes IS stored inline.