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.