Buy Percona ServicesBuy Now!

Using the loose_ option prefix in my.cnf

 | October 11, 2016 |  Posted In: MySQL


loose_ optionIn 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.

Daniel Kowalewski

Daniel joined Percona in August of 2015. Previously, he earned a B.S. in Computer Science from the University of Colorado in 2006, and was a DBA there until he joined Percona. In addition to MySQL, Daniel also has experience with Oracle and Microsoft SQL Server, but he much prefers to stay in the MySQL world. Daniel lives near Denver, CO with his wife, three-year-old son, and dog. If you can't reach him, he's probably in the mountains hiking, camping, or trying to get lost.

Leave a Reply