MySQL server won't start with foreign_key_checks=0

I'm running Ubuntu with MySQL version 5.5.44


mysqld Ver 5.5.44-0ubuntu0.14.10.1 for debian-linux-gnu on x86_64 ((Ubuntu))

I have a DB with foreign keys. I would like to have the foreign key check disabled at the system level (not at the session).

I saw the system var foreign_key_checks that seems to control this behavior but when I set it in /etc/mysql/my.cnf (by adding foreign_key_checks = 0) the server won't start. The error log looks like:

150729 15:38:18 [Warning] Using unique option prefix myisam-recover instead of myisam-recover-options is deprecated and will be removed in a future release. Please use the full name instead.
150729 15:38:18 [Note] Plugin 'FEDERATED' is disabled.
150729 15:38:18 InnoDB: The InnoDB memory heap is disabled
150729 15:38:18 InnoDB: Mutexes and rw_locks use GCC atomic builtins
150729 15:38:18 InnoDB: Compressed tables use zlib 1.2.8
150729 15:38:18 InnoDB: Using Linux native AIO
150729 15:38:18 InnoDB: Initializing buffer pool, size = 128.0M
150729 15:38:18 InnoDB: Completed initialization of buffer pool
150729 15:38:18 InnoDB: highest supported file format is Barracuda.
150729 15:38:18  InnoDB: Waiting for the background threads to start
150729 15:38:19 InnoDB: 5.5.44 started; log sequence number 56348937
150729 15:38:19 [ERROR] /usr/sbin/mysqld: unknown variable 'foreign_key_checks=0'
150729 15:38:19 [ERROR] Aborting

How can I configure my MySQL server to start with foreign key checks off at the system level?

Edit: behavior is the same with MySQL 5.6.25:


mysqld Ver 5.6.25-0ubuntu0.15.04.1 for debian-linux-gnu on x86_64 ((Ubuntu))

Root Cause

foreign_key_checks is not a global option you can preset at startup. Why ?

When you click on that link to the Documentation on foreign_key_checks, there is no chart that says it is global or session scope. Other options will specify the scope in a chart

• ft_boolean_syntax says Variable Scope Global ; Dynamic Variable Yes

• innodb_doublewrite says Variable Scope Global ; Dynamic Variable No

• max_allowed_packet says Variable Scope Global ; Dynamic Variable Yes

• sort_buffer_size says Variable Scope Global,Session ; Dynamic Variable Yes

With foreign_key_checks, it is session scope only.

Further proof of this is when you go to the command line and run this

mysqld --help --verbose > /tmp/mysqld.txt
vi /tmp/mysqld.txt

When you look inside the text file, you do not see --foreign-key-checks as a command line startup option. The other four variables I showed you can be used on the command line and in my.cnf.

If you have data to load, you can set that before loading:

SET foreign_key_checks = 0;
source mydata.sql

Proof of this is a standard mysqldump header ( first 10 lines )

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

Please note line 8

/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;

What does the bottom of a mysqldump look like ? Like this:

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

Please note the line

/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;

Epilogue

Bottom Line: foreign_key_checks is not meant of use in my.cnf. That make the most sense because (if allowed) starting mysqld with that would be damaging to referential integrity from startup.

StackExchange Database Administrators Q#108509, answer score: 7