Are innodb_large_prefix and innodb_file_format settings backwards compatible?

Posted on

Question :

I got the following message when I tried to install a piece of software trough the command line. I use a mySQL db.

* innodb_large_prefix = ON  is required.
* innodb_file_format = Barracuda is required.

I have several questions I couldn’t find an answer for by just searching Google:

  • Can I just change these to the required settings without breaking other software of the server?
  • Suppose it does break, can I just switch back?
  • Do I need to check for my mySQL db version prior to changing this?

Answer :

First of all, never trust what a random internet person tells you about your environment; the only way to be sure is by doing your own testing. It is very difficult to give an absolute correct answer without knowing all the details of your environement.

Answering broadly your question “Is it safe to enable barracuda and large_prefix_index?” the answer is yes, it is safe and backwards compatible.

barracuda is a new file format that allows 2 extra row formats: Dynamic and Compressed. In general, the internal row format is transparent for the application, although some new features may require the new formats (large_prefix_index and optimized blob storage). Both of those features are enabled by default on the newest MySQL and MariaDB versions, which normally only happens for mature and backwards compatible options. In my personal opinion, there not a reason to use a legacy format on the latest server versions unless you are maintaining older installations.

Note that older data doesn’t automatically get converted to the new format, that is why if something is wrong, you can just return to the old options and nothing will have changed. To get advantage of the new format you will need to create new tables or rebuild those after the options change (they are just “defaults”, they don’t automatically change anything on the tables).

In particular, large_prefix_index allows for larger indexes, which is usually necessary to allow for full UTF-8 compatibility. In particular, I have seen many applications fail because they require the utf8mb4 character set, which require up to 4 bytes per character, and not having that option enabled makes table creation fail. That is likely your case.

“Do I need to check for my mySQL db version prior to changing this?” Indeed you do, older versions of MySQL/MariaDB don’t support that option. Newer versions don’t have the option available (so they may fail to boot if provided such options) because the option is hardcoded to the new value. In general, MySQL supports older formats, but warns you to rebuild them when running mysql_upgrade if they are considered incompatible.

Is there any scenario where that can fail?- I can see it failing in very very specific scenarios where redundant or compact row formats are expected, but those should be rare- in my opinion all applications that are maintained should be compatible with barracuda/compressed row formats (Antelope and file formats in general are deprecated). If you have legacy applications that require a very special mysql confuguration consider keeping an older mysql installation or not rebuilding the tables to the new format, but I would consider that a huge technical debt and not best practices.

Leave a Reply

Your email address will not be published.