koha_fix_db_row_formats.sh

#!/usr/bin/env sh
# Automatically fix row formats for all tables in a Koha database
# See:
# - https://wiki.koha-community.org/wiki/Database_row_format
# - https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=28267
# - https://mariadb.com/kb/en/troubleshooting-row-size-too-large-errors-with-innodb/

set -eu

INSTANCE="$1"
DB_NAME="koha_$INSTANCE"

echo "Fixing row formats for koha instance $INSTANCE in database $DB_NAME ..."

TABLES_QUERY="SELECT table_name FROM information_schema.tables WHERE engine = 'InnoDB' AND row_format IN('Redundant', 'Compact') AND table_schema = '$DB_NAME';"
TABLES="$(mysql --batch --skip-column-names --execute="$TABLES_QUERY")"

for TABLE in $TABLES
do
  echo "Converting $TABLE to DYNAMIC row format ..."
  mysql --execute="ALTER TABLE $DB_NAME.$TABLE ROW_FORMAT=DYNAMIC;"
done