Using the loose_ option prefix in my.cnf

In this blog post, I’ll look at how to use the loose_ option prefix in my.cnf in MySQL.

mysqld throws errors at startup – and refuses to start up – if a non-existent options are defined in the my.cnf file.

For example:

The MySQL manual has a solution: use the loose_ prefix option in my.cnf file.

In the prior example, if we specify loose_bogus_option=1 in the my.cnf file, instead of bogus_option=1, mysqld starts successfully and ignores that option.

This is useful in three situations:

1. Using Percona Server-specific options in a config file that might be used for MySQL Community instances.
2. Including options from a future version of MySQL.
3. Setting options for plugins before they’re loaded.

Use Case 1:

We can use this feature to make a my.cnf file that will work on both MySQL Community and Percona Server, but contains options that aren’t implemented in MySQL Community. For example:

Without the loose_ prefix on log_slow_rate_limit, MySQL Community will throw a fatal error (as log_slow_rate_limit is not implemented in MySQL Community).

Use Case 2:

If you push out the same my.cnf file to multiple versions of MySQL, you can still use features that only exist on newer versions:

Use Case 3:

Another use case is installing the Percona audit log plugin. If you specify the audit_log  options in my.cnf, and then restart the server before running INSTALL PLUGIN, mysqld will fail. If you use the loose_ prefix, mysqld will start up successfull, and the options will be read when you run INSTALL PLUGIN.

This trick also works for options given on the command line, e.g. mysqld --loose-bogus-option.