To guard against ignored typos and syntax errors in SQL, or other unintended consequences of various
combinations of operational modes and SQL statements, InnoDB provides a strict mode
of operations. In this mode, InnoDB raises error conditions in certain cases, rather than issuing a warning and
processing the specified statement (perhaps with unintended behavior). This is analogous to
sql_mode in MySQL, which controls what SQL syntax MySQL accepts, and
determines whether it silently ignores errors, or validates input syntax and data values. Since InnoDB strict
mode is relatively new, some statements that execute without errors with earlier versions of MySQL might
generate errors unless you disable strict mode.
The setting of InnoDB strict mode affects the handling of syntax errors on the
statements. The strict mode also enables a record size check, so that an
UPDATE never fails due to the record being too large for the selected page size.
Oracle recommends enabling
innodb_strict_mode when using the
KEY_BLOCK_SIZE clauses on
statements. Without strict mode, InnoDB ignores conflicting clauses and creates the table or index, with only a
warning in the message log. The resulting table might have different behavior than you intended, such as having
no compression when you tried to create a compressed table. When InnoDB strict mode is on, such problems
generate an immediate error and the table or index is not created, avoiding a troubleshooting session later.
InnoDB strict mode is set with the configuration parameter
innodb_strict_mode, which can be specified as
OFF. You can set the value on the command line when you start
mysqld, or in the configuration file
my.ini. You can also enable or disable
InnoDB strict mode at runtime with the statement
OFF. Changing the
GLOBAL setting requires the
SUPER privilege and affects the operation of all clients that subsequently
connect. Any client can change the
SESSION setting for
innodb_strict_mode, and the setting affects only that client.