To guard against ignored typos and syntax errors in SQL, or other unintended consequences of various combinations of operational modes and SQL commands, the InnoDB storage engine provides a “strict mode” of operations. In this mode, InnoDB will raise error conditions in certain cases, rather than issue a warning and process the specified command (perhaps with some unintended defaults). This is analogous to MySQL’s sql_mode, which controls what SQL syntax MySQL will accept, and determines whether it will silently ignore errors, or validate input syntax and data values. Note that there is no strict mode with the built-in InnoDB, so some commands that execute without errors with the built-in InnoDB will generate errors with the InnoDB storage engine, unless you disable strict mode.

In the InnoDB storage engine, the setting of InnoDB strict mode affects the handling of syntax errors on the CREATE TABLE, ALTER TABLE and CREATE INDEX commands. Starting with InnoDB storage engine version 1.0.2, the strict mode also enables a record size check, so that an INSERT or UPDATE will never fail due to the record being too large for the selected page size.

Using the new clauses and settings for ROW_FORMAT and KEY_BLOCK_SIZE on CREATE TABLE and ALTER TABLE commands and the CREATE INDEX can be confusing when not running in strict mode. Unless you run in strict mode, InnoDB will ignore certain syntax errors and will create the table or index, with only a warning in the message log. However if InnoDB strict mode is on, such errors will generate an immediate error and the table or index will not be created, thus saving time by catching the error at the time the command is issued.

The default for strict mode is off, but in the future, the default may be changed. It is best to start using strict mode with the InnoDB storage engine, and make sure your SQL scripts use commands that do not generate warnings or unintended effects.

InnoDB strict mode is set with the configuration parameter innodb_strict_mode, which can be specified as on or off. You can set the value on the command line when you start mysqld, or in the configuration file my.cnf (Unix operating systems) or my.ini (Windows). You can also enable or disable InnoDB strict mode at runtime with the command SET [GLOBAL|SESSION] innodb_strict_mode=mode, where mode is either ON or 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, which affects only that client.