Nextcloud Performance Fix: ROW_FORMAT=DYNAMIC

Converting InnoDB tables to ROW_FORMAT=DYNAMIC in Nextcloud is not something that can be solved with a quick fix.

It required careful research and testing to understand the best approach and potential pitfalls. This documentation consolidates those findings, providing a clear, step-by-step guide to help others perform the conversion safely and efficiently.

Using ROW_FORMAT=DYNAMIC is recommended for optimal performance with utf8mb4 encoding, as ignoring it can lead to issues with large indexes or future updates.

Error message in administrative dashboard: Incorrect row format found in your database. ROW_FORMAT=Dynamic offers the best database performances for Nextcloud. Please update row format on the following list: …

Step 1: Finding your Nextcloud database settings

Before making any database changes, you need to know your database name. You can find it quickly from the Nextcloud configuration file.

Finding Your Nextcloud Database Settings:

grep db /var/www/nextcloud/config/config.php

Step 2: Backup First

Before touching the database, always make a full backup. This ensures you can roll back if something goes wrong.

Database Backup:

# Export the Nextcloud database
mariadb-dump -u root -p nextcloud > nextcloud_db_backup_$(date +%F).sql

File Backup (optional but recommended):

tar -czf nextcloud_files_backup_$(date +%F).tar.gz /var/www/nextcloud

✅ With this, you can restore both your database and Nextcloud files if needed.

Step 3: Prepare Your Database-Engine for DYNAMIC Row Format (Optional)

Make sure your MySQL/MariaDB is configured to support DYNAMIC row format.

  1. Edit the database config file (usually /etc/my.cnf.d/server.cnf or /etc/mysql/my.cnf):
[mysqld]
innodb_file_per_table=1
innodb_file_format=Barracuda
innodb_default_row_format=DYNAMIC
  1. Restart the database:
sudo systemctl restart mariadb

Step 4: Convert Tables to DYNAMIC

To improve database performance and eliminate warnings, you can convert all InnoDB tables in your Nextcloud database to ROW_FORMAT=DYNAMIC. Follow these steps carefully:

Step 4.1: Checking Table Engines and Row Formats in Nextcloud

When managing a Nextcloud database, it’s useful to know which tables use modern InnoDB with dynamic or compressed row formats and which still rely on legacy engines like MyISAM. This helps to optimize performance and ensures compatibility with utf8mb4.

You can quickly get an overview using a single MySQL command:

mysql -uroot -p -e "
SELECT engine, row_format, COUNT(*) AS table_count
FROM information_schema.tables
WHERE table_schema='nextcloud'
GROUP BY engine, row_format
ORDER BY engine, row_format;
"

Sample Output

enginerow_formattable_count
InnoDBDynamic42
InnoDBCompressed5
InnoDBCompact2
MyISAMFixed1
AriaFixed3

What This Tells You

  • Dynamic / Compressed InnoDB tables → modern, recommended for utf8mb4 and large indices
  • Compact InnoDB tables → older InnoDB format, might need conversion
  • MyISAM / Aria tables → legacy engines, usually less efficient and lack some InnoDB features

This simple check helps administrators identify tables that might need optimization or migration to the recommended formats, improving database reliability and performance.

Step 4.2: MyISAM

  • Classic storage engine, mostly deprecated, non-transactional.
  • DYNAMIC works fine and can be changed with ALTER TABLE.
  • ROW_FORMATs: FIXED, DYNAMIC, COMPRESSED
  • Downsides: no transactions, no crash recovery, no MVCC → mainly for legacy or small tables.

Create a file named:

nextcloud_rowformat_fix.sql

Add the following content:

USE database_name;

SELECT CONCAT('ALTER TABLE ', table_name, ' ROW_FORMAT=DYNAMIC, KEY_BLOCK_SIZE=0;')
FROM information_schema.tables
WHERE table_schema='database_name'
AND ENGINE='InnoDB'
AND ROW_FORMAT <> 'Dynamic';

Replace database_name with the actual name of your Nextcloud database.

Execute the SQL File

mariadb -u root -p < nextcloud_rowformat_fix.sql

Sample Output:

ALTER TABLE oc_direct_edit ROW_FORMAT=COMPACT; ALTER TABLE oc_direct_edit ROW_FORMAT=DYNAMIC;
ALTER TABLE oc_jobs ROW_FORMAT=COMPACT; ALTER TABLE oc_jobs ROW_FORMAT=DYNAMIC;
ALTER TABLE oc_circles_share_lock ROW_FORMAT=COMPACT; ALTER TABLE oc_circles_share_lock ROW_FORMAT=DYNAMIC;
ALTER TABLE oc_storages ROW_FORMAT=COMPACT; ALTER TABLE oc_storages ROW_FORMAT=DYNAMIC;
ALTER TABLE oc_twofactor_totp_secrets ROW_FORMAT=COMPACT; ALTER TABLE oc_twofactor_totp_secrets ROW_FORMAT=DYNAMIC;
...

Step 4.3: InnoDB (MariaDB 10.2+ with Barracuda)

  • Supports DYNAMIC and COMPRESSED row formats via Barracuda file format.
  • Important: existing COMPRESSED InnoDB tables cannot simply be altered to DYNAMIC. You need to dump and recreate the table.

Dump current DB

mariadb-dump -u root -p --single-transaction --skip-extended-insert nextcloud > nextcloud_dump_migration.sql

Replace row format in dump

sed -i 's/ROW_FORMAT=COMPRESSED/ROW_FORMAT=DYNAMIC/g' nextcloud_dump_migration.sql

Create new DB

mariadb -u root -p -e "CREATE DATABASE nextcloud_dynamic CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;"

Restore dump to new database

mariadb -u root -p nextcloud_dynamic < nextcloud_dump_migration.sql

Copy privileges from old DB to new DB

To identify the database user, refer to your Nextcloud configuration. See Step 1: Finding Your Nextcloud Database Settings for guidance.

mariadb -u root -p -e "GRANT ALL PRIVILEGES ON nextcloud_dynamic.* TO 'nc_user'@'localhost'; FLUSH PRIVILEGES;"

Update Nextcloud config

Before proceeding with the migration, make sure the database can be cleanly converted to ROW_FORMAT=DYNAMIC. See Step 6: Verify the Conversion for details.

To ensure that the user has the correct permissions on the new database, you can run the following command for a local user:

mariadb -u root -p -e "SHOW GRANTS FOR 'username'@'localhost';"

Output should be:

+------------------------------------------------------------------------------------------------------------------+
| Grants for nextcloud@localhost                                                                                   |
+------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `dbo_cloud`@`localhost` IDENTIFIED BY PASSWORD '*ABCDEFGHIJK12345679'                      |
| GRANT ALL PRIVILEGES ON `nextcloud`.* TO `dbo_cloud`@`localhost` WITH GRANT OPTION                               |
| GRANT ALL PRIVILEGES ON `nextcloud_dynamic`.* TO `dbo_cloud`@`localhost`                                         |
+------------------------------------------------------------------------------------------------------------------+

Replace username with your database user. This will display all privileges assigned to that user on the local server.

'dbname' => 'nextcloud_dynamic',

Step 5: Clear Nextcloud Cache (Optional)

After converting the tables, clear the Nextcloud cache and repair the installation:

sudo -u www-data php /var/www/nextcloud/occ maintenance:repair

Step 6: Verify the Conversion

Check that all tables now have the correct row format:

mariadb -u root -p -e "SELECT table_name, row_format FROM information_schema.tables WHERE table_schema='nextcloud' AND row_format <> 'Dynamic';"

If nothing is returned, all tables are properly converted, otherwise:

+-----------------------------+------------+
| table_name                  | row_format |
+-----------------------------+------------+
| oc_direct_edit              | Compressed |
| oc_jobs                     | Compressed |
| oc_circles_share_lock       | Compressed |
| oc_storages                 | Compressed |
| oc_twofactor_totp_secrets   | Compressed |
...

Step 7: Celebrate or Restore

  • ✅ If everything looks good: enjoy better performance and cleaner Nextcloud database.
  • 🔄 If something went wrong: restore from backup:
mariadb -u root -p nextcloud < nextcloud_backup.sql

By carefully following these steps, your Nextcloud database is now properly configured with ROW_FORMAT=DYNAMIC where needed, ensuring better compatibility and performance. You’ve documented the process, verified the changes, and can move forward with confidence. With a clean and optimized database, future updates and features will run smoothly, and your Nextcloud instance is more resilient for the long term.

Related Posts