Why Adding foreign_key_checks to my.cnf Breaks MySQL Startup and How to Identify Non‑Configurable System Variables

A colleague asked why adding the foreign_key_checks system variable to my.cnf prevented MySQL from starting. Although the variable exists in the official documentation and can be changed at runtime with SET , MySQL reports an unknown variable 'foreign_key_checks = 0' error during startup.

Reproducing the issue shows that MySQL aborts with the unknown‑variable error when the line foreign_key_checks = 0 is present in the configuration file. Further investigation revealed that the problem is not caused by the SESSION_VAR flag but by the NO_CMD_LINE flag attached to certain system variables.

Searching the source code for other variables marked with NO_CMD_LINE uncovered autocommit . When autocommit is placed in my.cnf without being added to the my_long_options array, MySQL also fails to start, confirming that variables with the NO_CMD_LINE flag cannot be read from the configuration file unless they are exposed through my_long_options or my_long_early_options .

The key insight is that a system variable defined in sql/sys_vars.cc and marked with NO_CMD_LINE is invisible to the configuration parser; adding it to my_long_options (or my_long_early_options ) makes it configurable, as demonstrated with autocommit .

To quickly determine whether a variable can be set in my.cnf , run the MySQL server with the verbose help flag and grep for the variable name, e.g.:

/path/mysqld --verbose --help | grep "foreign-key-checks"

If there is no output, the variable cannot be configured via the file. Conversely, if the output contains a line such as --autocommit Set default value for autocommit (0 or 1) , the variable is configurable.

In summary, encountering an unknown variable xxx error for a variable documented as supported indicates that the variable is not allowed in my.cnf . The same conclusion can be drawn from the absence of a corresponding --xxx entry in the verbose help output.