Antelope to Barracuda File Format

During your upgrades or on the Server Environment Checks page, you might see a message like this:

This message is advising you should move to the better InnoDB Barracuda format on MySQL and MariaDB.

From the MySQL documentation:

• Antelope is the original InnoDB file format, which previously did not have a name. It supports COMPACT and REDUNDANT row formats for InnoDB tables and is the default file format in MySQL 5.6 to ensure maximum compatibility with earlier MySQL versions that do not support the Barracuda file format.
• Barracuda is the newest file format. It supports all InnoDB row formats including the newer COMPRESSED and DYNAMIC row formats. The features associated with COMPRESSED and DYNAMIC row formats include compressed tables, efficient storage of off-page columns, and index key prefixes up to 3072 bytes (innodb_large_prefix).

To perform the table upgrades, you use the Moodle CLI and run the following commands (prefix with sudo if required):

Script for detection of row size problems in MySQL InnoDB tables.

By default InnoDB storage table is using legacy Antelope file format
which has major restriction on database row size.
Use this script to detect and fix database tables with potential data
overflow problems.

Options:
-i, --info            Show database information
-l, --list            List problematic tables
-f, --fix             Attempt to fix all tables (requires SUPER privilege)
-s, --showsql         Print SQL statements for fixing of tables
-h, --help            Print out this help

First check for problematic tables:

php admin/cli/mysql_compressed_rows.php -l

The -l parameter lists tables that need compacting/compressing e.g.

mdl_data                        Compact     (needs fixing)
mdl_data_fields                 Compact     (needs fixing)
mdl_enrol_paypal                Compact     (needs fixing)
mdl_lti                         Compact     (needs fixing)
mdl_user                        Compact     (needs fixing)
mdl_user_info_field             Compact     (needs fixing)

To fix, run the command with the -f fix paramter.

php admin/cli/mysql_compressed_rows.php -f

mdl_data                         ... Compressed
mdl_data_fields                  ... Compressed
mdl_enrol_paypal                 ... Compressed
mdl_lti                          ... Compressed
mdl_user                         ... Compressed
mdl_user_info_field              ... Compressed

To confirm all is well, run the list command again or check the environments page. There is also a handy -i parameter to check your database platform details:

php admin/cli/mysql_compressed_rows.php -i
Database version:      5.7.23-log
Database name:         moodle
Database engine:       InnoDB
innodb_file_per_table: ON
innodb_file_format:    Barracuda