MySQL Bugs: #38168: Restrict behaviour of InnoDB by using non strict mode

Bug #38168	Restrict behaviour of InnoDB by using non strict mode
Submitted:	16 Jul 2008 11:07	Modified:	17 Jul 2008 17:06
Reporter:	Susanne Ebrecht	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	5.1	OS:	Any
Assigned to:	Paul DuBois	CPU Architecture:	Any

Description:
InnoDB behaves restrict even when strict mode isn't set.

drop table t, t1;
ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails

Show variables like 'sql_mode%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_mode      |       | 
+---------------+-------+

How to repeat:
create table t(id serial) engine=innodb;
create table t1(i bigint(20) unsigned, foreign key(i) references t(id))engine=innodb;

drop table t, t1;

This should not fail by using non strict mode.

Suggested fix:
...

Strict mode does not affect this.

This should be pointed out in documentation.

When you are already have database experience and you read our documentation, then you expect that with disabling strict mode also FK checks will be disabled.

A sentence at the doc that FK checks are not included in disabling strict mode would be fine.

I have never heard of anyone expecting strict mode to affect foreign key checking.  Strict mode affects checking of data value contents.

To disable foreign key checking, use the FOREIGN_KEY_CHECKS system variable:

http://dev.mysql.com/doc/refman/5.0/en/server-session-variables.html#option_mysqld_foreign...

"a client program can instruct the server how strict or forgiving to be about accepting input data, enable or disable behaviour relating to standard SQL conformance ..."

There is no word about that this exclude foreign key checks. Input data also can be an alphabetic sorted lists of "create tables statements" where tables with foreign key will be created before the referenced tables.

Also here about strict mode:
"which imposes certain restrictions on what values are acceptable as database input. By default, MySQL is forgiven about accepting values that are missing, out of range, or malformed."

Database input also can be "create table". Accepting values that are missing also can be a "references tab(bla)" where tab is missing.

Our documentation isn't strict enough here with definition what is meant with data and input. I always thought this will include constraints like unique, default, not null or foreign key and if we would have also check constraints.

Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.

Added a note that strict mode does not affect foreign key checking and that FOREIGN_KEY_CHECKS can b used for that to this section:

http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html