Alter InnoDB Row Format for MariaDB

After the recent update to Nextcloud 31.0.2, I saw a mesage in the admin settings that some database tables would benefit from using a different InnoDB row format.

This post outlines how to change the row format of existing tables in a database, as I had to spend a bit of time on how to do this.

TL;DR

With a sufficiently privileged user perform the following query to change the format for a single table for a given database and table:

ALTER TABLE <db_name>.<table_name> ROW_FORMAT=DYNAMIC;

What is a InnoDB row format?

Not knowing what a InnoDB row format is, I looked it up in the MariaDB documentation. Turns out that the InnoDB row format changes how the actual rows in a database using the InnoDB storage engine are stored on the filesystem with differences in efficiency and disk space required.

The default format for new tables seems to be the DYNAMIC format that Nextcloud wanted me to change the other tables to. Those tables appear to be some of the oldest ones, possibly predating DYNAMIC being the default format.

How to change the row format?

After some searching I found this blogpost from 2019 that describes exactly what I wanted to do.

Adapting the proposed script to my specific situation, I ran the following bash script and received a list of table names that used the COMPRESSED format:

#!/usr/bin/bash

for \
  table in `mariadb --batch --skip-column-names --user=root --execute="SELECT CONCAT(TABLE_SCHEMA, '.', TABLE_NAME) FROM information_schema.TABLES WHERE ENGINE = 'InnoDB' AND ROW_FORMAT IN('Compressed') AND TABLE_SCHEMA = 'my_nextcloud_db'"`;
  do echo "ALTER TABLE ${table} ROW_FORMAT=DYNAMIC;";
done;

This returned a list of SQL statements that would convert each table using the COMPRESSED row format to using the DYNAMIC row format:

ALTER TABLE my_nextcloud_db.oc_accounts_data ROW_FORMAT=DYNAMIC;
ALTER TABLE my_nextcloud_db.oc_accounts ROW_FORMAT=DYNAMIC;
ALTER TABLE my_nextcloud_db.oc_activity_mq ROW_FORMAT=DYNAMIC;
ALTER TABLE my_nextcloud_db.oc_activity ROW_FORMAT=DYNAMIC;
# rest of file omitted

I created a backup of the database and tried out some of those SQL statements before executing all of them.

After that, the message in the Nextcloud admin settings vanished and I wrote down what I learned in this blog post to help others with the same question.