How to tell if a particular MariaDB / MySQL server supports the Barracuda storage format?

Question

In creating some DDL-generating code for MySQL / MariaDB (inside WordPress as it happens) I need to find out whether the RDBMS server I'm talking to can support the newer Barracuda / DYNAMIC / InnoDB storage format. (That format allows indexing longer VARCHAR columns, whereas the older Antelope format requires prefix indexes for columns containing more than 768 bytes, 191 utfmb4 characters.)

Most users of this code aren't experts, and the majority of WordPress sites still use MySQL 5.5 or 5.6. (Because budget hosting vendors.)

Is there a good-practice way to query the RDBMS (via SQL) to determine whether Barracuda is available?

Now I have a nasty if-else cascade to figure this out, here written in pseudocode.

if mysql and version >= 8 then yes
else if mariadb and version >= 10.3 then yes
else if mariadb and version >= 10 and 'innodb_large_prefix' is ON or 1 then yes
else if version <= 5.5.62 then no
else if version <= 5.6.4 then no
else yes

Is there a better / cleaner / more reliable way to figure this out? Is there a standard -- best-practice -- way?

Notice that the very useful 'innodb_large_prefix' setting came and went; it's not in the latest versions.

Answer 1

All MySQL versions after 5.5.5 support Barracuda, and set innodb_file_format to Barracuda by default. That is, it allows Barracuda file format by default, but you still had to specify ROW_FORMAT to DYNAMIC or COMPRESSED explicitly.

Here's an excerpt from the archived MySQL 5.5 documentation:

https://docs.oracle.com/cd/E19957-01/mysql-refman-5.5/storage-engines.html#innodb-file-format-enabling

The configuration parameter innodb_file_format controls whether such commands as CREATE TABLE and ALTER TABLE can be used to create tables that depend on support for the “Barracuda” file format.

In MySQL 5.5.5 and higher, the default value is “Barracuda”.

(In fact, MySQL 5.1 could support Barracuda too, if you enabled the InnoDB plugin instead of the old "builtin" InnoDB.)

In MySQL 5.7, they introduced the innodb_default_row_format option, with a default value of DYNAMIC, so tables would be Barracuda by default.

Also in MySQL 5.7, the innodb_large_prefix was introduced, enabled by default. The only reason you'd disable it is to create tables that are compatible with a downgraded version.

In MySQL 8.0, the variable innodb_file_format is removed. It's moot, because Barracuda is always enabled and is always the default file format. I.e. you can't downgrade from 8.0 to MySQL 5.0 (before Barracuda was implemented) without doing a logical dump and restore.

The innodb_large_prefix variable has also been removed in 8.0. It's never disabled in the latest MySQL version.

Answer 2

Rather than a version check, you could assume a new version, and catching the #1709 - Index column size too large error, fall back to the 767 byte index prefixed version of the table creation/alter.