Database row format

From Koha Wiki
Jump to navigation Jump to search

When upgrading Koha to versions newer than 20.05, some users see database errors like "Row size too large". This is typically due to an old database row format being used. You want to be using "DYNAMIC" rather than "COMPACT" or "REDUNDANT".

If you've set up a new database server, you're unlikely to see this problem. But if you're using an old database server or have upgraded your database from old versions to newer versions, then you might have this problem.

Here is a script to resolve the problem by converting all row formats to "DYNAMIC". It takes a while to run. Afterwards, you should not see the "Row size too large" while upgrading. 

#!/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


Script usage:

$ ./FILENAME.sh INSTANCE


An alternate script you can run from koha-shell would be: 

#!/usr/bin/env perl

use Modern::Perl;
use C4::Context;
use feature 'say';

my $db =  C4::Context->config("database");
my $dbh = C4::Context->dbh;

my $sth = $dbh->prepare("SELECT table_name FROM information_schema.tables WHERE engine = 'InnoDB' AND row_format IN('Redundant', 'Compact') AND table_schema = '$db'");
$sth->execute();

while ( my $row = $sth->fetchrow_hashref() ) {
    my $table = $row->{table_name};
    print "Working on $table....";
    $dbh->do(qq{ALTER TABLE $db.$table ROW_FORMAT=DYNAMIC});
    say "done!";
}

For more in-depth information about this problem, see:

Retrieved from "https://wiki.koha-community.org/w/index.php?title=Database_row_format&oldid=34741"