Add setup warning in case row_format=compressed is still used

[PVince81]

Since #30129 any new install of NC >= 24 will have row_format=dynamic with MariaDB.

Also, we saw reports where having "compressed" might negatively affect performance.

We should look into the following:

• add a setup check that verifies that all known tables have row_format=dynamic instead of compressed.
• if possible: add an occ command that migrates the tables to the new format. To be verified whether it's a quick thing or whether it can take hours for lots of data

@icewind1991 @juliushaertl @nickvergessen @CarlSchwan @szaimen

[nickvergessen]

if possible: add an occ command that migrates the tables to the new format. To be verified whether it's a quick thing or whether it can take hours for lots of data

We have that already as a repair step and it's also reachable via occ db:convert-mysql-charset ?
https://github.com/nextcloud/server/blob/master/lib/private/Repair/Collation.php#L82

Also your initial statement is wrong, every new install will NOT have compressed anymore, but dynamic.

[PVince81]

sorry, I typed it wrong. fixed now.

hmm, the command occ db:convert-mysql-charset seems a bit obscure.
but if it does the job then we could direct to it from the setup check then

[ChristophWurst]

My observation on the dev env that was installed circa 2016 and only upgraded but never reset so far:

At some point I must have switched to UTF8 MB4 but that was at a time where we still created tables with row format compressed.

The query in \OC\Repair\Collation::getAllNonUTF8BinTables only checks the COLLATION_NAME and CHARACTER_SET.

Would it make sense to add a setup check and repair step for the row format specifically?

MariaDB [nextclouddev]> SELECT COUNT(*), ROW_FORMAT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE 'oc_%' GROUP BY ROW_FORMAT;
+----------+------------+
| COUNT(*) | ROW_FORMAT |
+----------+------------+
|       53 | Compact    |
|      316 | Compressed |
|       52 | Dynamic    |
+----------+------------+
3 rows in set (0,021 sec)

My personal production instance of Nextcloud 27 confirms that not all upgrade paths lead to a fully dynamic row format for all tables:

MariaDB [(none)]> SELECT COUNT(*), ROW_FORMAT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE 'oc_%' GROUP BY ROW_FORMAT;
+----------+------------+
| COUNT(*) | ROW_FORMAT |
+----------+------------+
|      214 | Compressed |
|       19 | Dynamic    |
+----------+------------+
2 rows in set (0,008 sec)

[artonge]

@Altahrim can you take a look and tell us whether we should create a setup check or a repair step?
The question is, if the conversion from compressed to dynamic takes too much time, then a repair step could be an issue, but we at least need to tell the admin about it.

Example code for the future implementation:

• Example of setup check: https://github.com/nextcloud/server/blob/ae0106ef9e9b4a91d385fff7490e17963e73c40d/apps/settings/lib/SetupChecks/MysqlUnicodeSupport.php
• Example of repair step: https://github.com/nextcloud/server/blob/ae0106ef9e9b4a91d385fff7490e17963e73c40d/lib/private/Repair/Collation.php

[ChristophWurst]

I would advice against a repair step. This migration takes very long and it roughly doubles the required disk space. Admins need to plan for both.

[Altahrim]

I would also vote for a setup check :)

[ChristophWurst]

MariaDB enterprise enforces the dynamic row format as well: https://mariadb.com/docs/server/service-management/upgrades/enterprise-server/from-community-server-es10-6/#Convert_InnoDB_Row_Format

[sorbaugh]

@Altahrim let's schedule this