I’m running a fairly small Nextcloud server with MariaDB database backend. I recently followed the instructions to upgrade mysql database to use utf8mb4 and I hit a snag and it took me a while to fix it.
I had ROW_FORMAT set to DYNAMIC and PAGE_COMPRESSED set to 1. PAGE_COMPRESSED=1 causes MariaDB to compress tables with a given compression algorithm (in my case lz4, but there’s a plethora to choose from).
ROW_FORMAT cannot be set to COMPRESSED with PAGE_COMPRESSED set to 1 at the same time. Those are two different and mutually exclusive approaches to table compression.
When I run occ maintenance:repair it complained cryptically that ROW_FORMAT was not supported, which I found is a typical error when you try to set ROW_FORMAT=COMPRESSED and PAGE_COMPRESSED=1 on the same table.
After browsing through the code I found this snippet in Collation.php:
if ($characterSet === 'utf8mb4') {
// need to set row compression first
$query = $this->connection->prepare('ALTER TABLE `' . $table . '` ROW_FORMAT=COMPRESSED;');
$query->execute();
}
And here comes my question. Is it really necessary to set ROW_FORMAT=COMPRESSED on utf8mb4 tables? I’m not a database guru by any means, but I do not see any necessity to have ROW_FORMAT=COMPRESSED here. I agree that with plain uncompressed InnoDB people should be advised to set ROW_FORMAT=COMPRESSED to save space and that like, but ROW_FORMAT=DYNAMIC works too. Perhaps there are any particular reasons for that?