Erreur MariaDB : Row size too large


2 minutes à lire
Erreur MariaDB : Row size too large

Lors de la mise à jour de mon blog, j'ai eu un message d'erreur concernant la base de données propulsée par MariaDB :  

Row size too large. The maximum row size for the used table type, not counting BLOBs, is 8126. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs

lors de l'update d'une table

alter table `tags` add `twitter_image` varchar(2000) 

Voici comment j'ai résolu le problème :

alter table tags ROW_FORMAT=DYNAMIC;

Et j'ai pu réaliser la mise à jour sans encombre.

Voici un script pour détecter ce type d'incident :

#!/bin/bash
# Usage : 
#		./rowsize.sh host login password

[ -z "$3" ] && echo "Usage: $0 host user password" >&2 && exit 1

dt="tmp_$RANDOM$RANDOM"

mysql -h $1 -u $2 -p$3 -ABNe "create database $dt;"
[ $? -ne 0 ] && echo "Error: $0 terminating" >&2 exit 1

echo
echo "Created temporary database ${dt} on host $1"
echo

c=0
for d in $(mysql -h $1 -u $2 -p$3 -ABNe "show databases;" | egrep -iv "information_schema|mysql|performance_schema|$dt")
do
	for t in $(mysql -h $1 -u $2 -p$3 -ABNe "show tables;" $d)
	do
		tc=$(mysql -h $1 -u $2 -p$3 -ABNe "show create table $t\\G" $d | egrep -iv "^\*|^$t")
		
		echo $tc | grep -iq "ROW_FORMAT"
		if [ $? -ne 0 ]
		then
			tf=$(mysql -h $1 -u $2 -p$3 -ABNe "select row_format from information_schema.innodb_sys_tables where name = '${d}/${t}';")
			tc="$tc ROW_FORMAT=$tf"
		fi
		
		ef="/tmp/e$RANDOM$RANDOM"
		mysql -h $1 -u $2 -p$3 -ABNe "set innodb_strict_mode=1; set foreign_key_checks=0; ${tc};" $dt >/dev/null  2>$ef
		[ $? -ne 0 ] && cat $ef | grep -q "Row size too large" && echo "${d}.${t}" && let c++ || mysql -h $1 -u $2 -p$3 -ABNe "drop table if exists ${t};" $dt
		rm -f $ef
	done
done
mysql -h $1 -u $2 -p$3 -ABNe "set innodb_strict_mode=1; drop database $dt;"
[ $c -eq 0 ] && echo "No tables with rows size too large found." || echo && echo "$c tables found with row size too large."
echo
echo "$0 done."

A executer comme suit

./rowsize.sh localhost dbuser Pa$$Word

Related Articles

🎓 Formez vous à Linux gratuitement
3 minutes à lire
Créer son repository pour Runecast Analyzer
3 minutes à lire
Mettre à jour Node.js sous Ubuntu
1 minute à lire
🎬 Découvrez HAProxy en vidéo
1 minute à lire
Fail2ban pour nginx
1 minute à lire

GO TOP