InnoDB Strict Mode enforces stricter SQL compliance, returning errors instead of warnings for invalid CREATE TABLE options or potential data loss.
ERROR 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.