InnoDB Strict Mode

InnoDB strict mode is similar to SQL strict mode. When it is enabled, certain InnoDB warnings become errors instead.

Enabling InnoDB Strict Mode

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;

InnoDB Strict Mode Errors

Invalid Table Options

If DDL is executed, and if invalid or conflicting table options are specified, then an error is returned by the statement when InnoDB strict mode is enabled. For example:

• The KEY_BLOCK_SIZE table option is set to a non-zero value, but the ROW_FORMAT table option is set to some row format other than the COMPRESSED row format.
• The KEY_BLOCK_SIZE table option is set to a non-zero value, but the configured value is larger than either 16 or the value of the innodb_page_size system variable, whichever is smaller.
• The KEY_BLOCK_SIZE table option is set to a non-zero value, but the innodb_file_per_table system variable is not set to ON.
• The KEY_BLOCK_SIZE table option is set to a non-zero value, but the innodb_file_format system variable is not set to Barracuda.
• 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 the COMPRESSED row format, but the innodb_file_per_table system variable is not set to ON.
• The ROW_FORMAT table option is set to either the COMPRESSED or the DYNAMIC row format, but the innodb_file_format system variable is not set to Barracuda. The ROW_FORMAT table option is set to a value, but it is not set to one of the supported values: REDUNDANT, COMPACT, DYNAMIC, and COMPRESSED.
• Either the KEY_BLOCK_SIZE table option is set to a non-zero value or the ROW_FORMAT table option is set to the COMPRESSED row format, but the innodb_page_size system variable is set to a value greater than 16k.
• The PAGE_COMPRESSION_LEVEL table option is set, but the PAGE_COMPRESSED table option is set to 0, so InnoDB page compression is disabled.
• The DATA DIRECTORY table option is set, but the innodb_file_per_table system variable is not set to ON.
• 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.