innodb_strict_mode
When innodb_strict_mode
is ON
, InnoDB
returns errors rather than warnings for certain conditions. The default value is OFF
.
Strict mode helps guard against ignored typos and syntax errors in SQL, or other unintended consequences of various combinations of operational modes and SQL statements. When innodb_strict_mode
is ON
, 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.
The innodb_strict_mode
setting affects the handling of syntax errors for CREATE TABLE
, ALTER TABLE
andCREATE INDEX
statements. innodb_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.
Oracle recommends enabling innodb_strict_mode
when using ROW_FORMAT
and KEY_BLOCK_SIZE
clauses on CREATE TABLE
, ALTER TABLE
, and CREATE INDEX
statements. When innodb_strict_mode
is OFF
, 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.
You can turn innodb_strict_mode
ON
or OFF
on the command line when you start mysqld
, or in the configuration file my.cnf
or my.ini
. You can also enable or disable innodb_strict_mode
at runtime with the statement SET [GLOBAL|SESSION] innodb_strict_mode=
, where mode
is either mode
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
, and the setting affects only that client.
参考:
http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_strict_mode
posted on 2014-04-11 21:10 Still water run deep 阅读(1939) 评论(0) 编辑 收藏 举报