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

A colleague asked why adding foreign_key_checks to my.cnf makes MySQL fail to start. Although the variable exists in the official documentation and can be changed with SET, MySQL reports an unknown variable error when it appears in the configuration file.

The investigation reveals that some system variables are marked with the NO_CMD_LINE flag, which prevents them from being recognized in the configuration file. By examining MySQL source code (version 8.0.32), the author discovers that variables like foreign_key_checks and autocommit have different definitions across sql/sys_vars.cc and sql/mysqld.cc.

Through a series of experiments—adding autocommit to the config, removing it from my_long_options, and creating a custom variable with the NO_CMD_LINE flag—the author confirms that variables defined with NO_CMD_LINE in sql/sys_vars.cc cannot be parsed during startup unless they are also listed in my_long_options or my_long_early_options.

To quickly determine whether a variable can be set in my.cnf, the following command can be used: /path/mysqld --verbose --help | grep "xxx" If the output contains a line like --xxx with a description, the variable is configurable via the configuration file; otherwise, it is not.

For example, running the command for foreign_key_checks yields no output, indicating it cannot be set in my.cnf. Conversely, running it for autocommit shows a description, confirming it is configurable.

Note: When using grep , replace underscores in variable names with hyphens (e.g., foreign-key-checks ).

In summary, encountering an unknown variable xxx error during MySQL startup means the variable is not supported in the configuration file, and the --verbose --help method provides a reliable way to verify configurability.