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 defaults). 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 strict mode is relatively new, some statements that execute without errors with earlier versions of InnoDB might generate errors unless you disable strict mode.

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

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

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 statement 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.