Fixing messed charset in SQL tables

This should be rare but if, when upgraded, extended characters are messed (e.g. á instead of á) here's a procedure to fix them manually. I've only tested it with an UTF-8 console though.

  • Export offending tables (oldrecorded, oldprogram, program1), record and recorded most probably). Keep a copy of the exported files just in case.
  • Apply fixing script found below. Requires bash and iconv
  • DROP the tables
  • Re-import them

Script

Notes

  • Add any extra non-English character used in your language to the CHARS line
  • Save in UTF-8
#!/bin/bash
set -e

CHARS=( á é í ó ú à è ì ò ù Á É Í Ó Ú À È Î Ò Ù ñ Ñ · ¿ ¡ ä ë ï ö ü Ä Ë Ï Ö Ü ç Ç € ¢ £ ¥ ŀ ß æ œ )
TABLES=( oldrecorded oldprogram program record recorded )

echo "Translations:" >&2
for char in ${CHARS[*]}; do
	broken=$(echo $char | iconv -flatin1 -tutf8)
	char=$(echo $char | iconv -tutf8) # For non-UTF-8 systems (not tested)
	echo "$broken -> $char" >&2
done

for table in ${TABLES[*]}; do
	if [ ! -f $table.sql ]; then
		echo "Table $table skipped" >&2
	continue ; fi

	echo "Fixing $table" >&2
	for char in ${CHARS[*]}; do
		broken=$(echo $char | iconv -flatin1 -tutf8)
		char=$(echo $char | iconv -tutf8) # For non-UTF-8 systems (not tested)
		sed -i "s/$broken/$char/g" $table.sql
	done
done
1) note program should fix itself in the next guide updat; can be TRUNCATE'd to be sure and then run mythfilldatabase or wait for the next automatic update
mythtv/0.22_upgrade/fix_messed_charset.txt · Last modified: 2010/02/15 17:46 by Toni Corvera
 
Except where otherwise noted, content on this wiki is licensed under the following license: CC Attribution-Noncommercial-Share Alike 3.0 Unported
Recent changes RSS feed Donate Powered by PHP Valid XHTML 1.0 Valid CSS Driven by DokuWiki