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
Strict Mode Errors: Conflicting Table Options
If DDL is being executed, and if the user specified 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 it is not set to a supported value. - The
PAGE_COMPRESSION_LEVEL
table option is set, but thePAGE_COMPRESSED
table option is set to0
, so InnoDB page compression is disabled.
Strict Mode Errors: 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.
Strict Mode Errors: Maximum Row Sizes
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.