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.

Or as shell script which prompts for user, password and database name:

#!/bin/bash

read -p "Enter Database Name: " DB_NAME
read -p "Enter Username: " DB_USER
read -s -p "Enter Password: " DB_PASS
echo

mysql -u "$DB_USER" -p"$DB_PASS" "$DB_NAME" -Bse "
SELECT CONCAT('ALTER TABLE \`', TABLE_NAME, '\` ROW_FORMAT=DYNAMIC;') 
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_SCHEMA = '$DB_NAME' 
AND ENGINE = 'InnoDB'
" | mysql -u "$DB_USER" -p"$DB_PASS" "$DB_NAME"