InnoDB Strict Mode
Contents
InnoDB strict mode is similar to SQL strict mode. When it is enabled, certain InnoDB warnings become errors instead.
Enabling InnoDB Strict Mode
MariaDB starting with 10.2.2
In MariaDB 10.2.2 and later, 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
. For example:
SET GLOBAL innodb_strict_mode=ON;
Its value for the current session can also be changed dynamically with SET SESSION
. For example:
SET SESSION innodb_strict_mode=ON;
It can also be set in a server option group in an option file prior to starting up the server. For example:
[mariadb] ... innodb_strict_mode=ON;
Strict Mode Errors
Invalid Table Options
If DDL is being executed, and if the user specified invalid or conflicting table options, then an error is returned by the statement. For example:
- The
KEY_BLOCK_SIZE
table option is set to a non-zero value, but theROW_FORMAT
table option is set to some row format other than theCOMPRESSED
row format. - The
KEY_BLOCK_SIZE
table option is set to a non-zero value, but the configured value is larger than either16
or the value of theinnodb_page_size
system variable, whichever is smaller. - The
KEY_BLOCK_SIZE
table option is set to a non-zero value, but theinnodb_file_per_table
system variable is not set toON
. - The
KEY_BLOCK_SIZE
table option is set to a non-zero value, but theinnodb_file_format
system variable is not set toBarracuda
. - The
KEY_BLOCK_SIZE
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
ROW_FORMAT
table option is set to theCOMPRESSED
row format, but theinnodb_file_per_table
system variable is not set toON
. - The
ROW_FORMAT
table option is set to either theCOMPRESSED
or theDYNAMIC
row format, but theinnodb_file_format
system variable is not set toBarracuda
. TheROW_FORMAT
table option is set to a value, but it is not set to one of the supported values:REDUNDANT
,COMPACT
,DYNAMIC
, andCOMPRESSED
. - Either the
KEY_BLOCK_SIZE
table option is set to a non-zero value or theROW_FORMAT
table option is set to theCOMPRESSED
row format, but theinnodb_page_size
system variable is set to a value greater than16k
. - The
PAGE_COMPRESSION_LEVEL
table option is set, but thePAGE_COMPRESSED
table option is set to0
, so InnoDB page compression is disabled. - The
DATA DIRECTORY
table option is set, but theinnodb_file_per_table
system variable is not set toON
. - The
INDEX DIRECTORY
table option is set.
COMPRESSED Row Format
If InnoDB strict mode is enabled, and if a table uses the COMPRESSED
row format, and if the table's KEY_BLOCK_SIZE
is too small to contain a row, then an error is returned by the statement.
Row Size Too Large
If InnoDB strict mode is enabled, and if a table exceeds its row format's maximum row size, then InnoDB will return an error.
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.
See Troubleshooting Row Size Too Large Errors with InnoDB for more information.