MySQL/MariaDB

Change row format of tables

In some cases it is neccessary to change the row format of tables to "dynamic" (for example when setting up a database for Nextcloud).

To change the format of existing tabley, you can generated a script for that using the following command:

SELECT CONCAT(
  'ALTER TABLE `', TABLE_SCHEMA, '`.`', TABLE_NAME, '` ',
  'ROW_FORMAT=DYNAMIC;'
) AS _alter
FROM INFORMATION_SCHEMA.TABLES
WHERE ENGINE='InnoDB' AND ROW_FORMAT <> 'DYNAMIC';

The output of this command can then be used the change the row format of all tables which don't use the "dynamic" format yet.