1. Probleme bei Datenbankkonvertierung UTF8 und Latin1

    Stefan · 14.06.09, 17:28 Uhr · Allgemeines, Movable Type, PHP, Tutorials · Tags: , , , ,

    Wer kennt es nicht: Man hat eine uralte MySQL-Datenbank die ursprünglich im latin1-Format gespeichert wurde (MySQL Default latin1_swedish, da MySQL eine schwedische Firma ist) und im Laufe der Zeit wurden Tabellen sowohl in latin1_swedish, utf8_unicode und utf8_general gespeichert. Mit viel tricksen kommen zwar noch im Frontend die richtigen Buchstaben raus, aber sobald man ein Datenbank-Upgrade wegen einer neuen Anwendungsversion braucht, hat man ein Problem.

    So haben wir derzeit eine ca. 4 Jahre alte Datenbank auf der ursprünglich eine MovableType Version 3.x lief und wir zwischenzeitlich auf MTOS 4.12 upgraden konnten. Mittlerweile sind dort mehrere Blogs gehostet und wir wollen auf die neueste Version updaten, allerdings erden dort zu viele Tabellenstrukturen angefasst, so dass wir jetzt probleme mit unseren Umlauten bekommen, wie wir es bei einem Testupdate zur Version 4.25 festgestellt haben. Also bleibt uns nun nichts anderes übrig, als die Datenbank ein für alle mal zu Cleanen. Momentan schaut unsere Tabellenstruktur wie folgt aus (sowas kennen sicherlich einige von euch):

    Original Tabellenstruktur

    Je nachdem wann eine Tabelle erzeugt wurde, wurde sie in einem anderen Charset anglegt. Das führt dann dazu, dass bereits im PHPMyAdmin die Zeichen nicht mehr korrekt angezeigt werden können:

    phpmyadmin_orig_entry

    Egal was man jetzt über phpMyAdmin versucht, die Tabellen zu konvertieren, es wird definitiv fehlerhaft werden. Auch wenn man den normalen mysqldump mit den Standardeinstellungen verwendet, kommt nix gutes bei Raus. um jetzt den wahren Zeichensatz herauszufinden, logge ich mich auf dem Server direkt in die MySQL-Konsole ein:

    mysql -ubenutzername -ppasswort -Ddatenbank

    danach suche ich mir einen Datensatz in einer Tabelle, der unter Garantie verschiedene Sonderzeichen enthält. Aber Achtung: Nicht nur öäü, denn diese können in verschiedenen Zeichensätzen korrekt dargestellt werden, sondern auch nach langen Gedankenstrich — etc. Ausschau halten.

    Mit folgender einfachen Abfrage im Falle von unserer MovableType Installation haben wir direkt den entsprechenden Datensatz in der Konsole gefunden:

    select entry_text from mt_entry where id=124;

    und siehe da, was uns vorhin in der phpMyAdmin-Oberfläche noch zerhakt aussah, schaut jetzt in der MySQL-Konsole korrekt aus:

    mysql_zeichen

    Wir sehen oben jede Menge Sonderzeichen, z.B. auch unten stehende Anführungszeichen. Damit ist das Encoding hier korrekt. Wenn es hier also korrekt ausschaut, haben wir auch eine Chance, es korrekt aus der Datenbank herauszulassen. Um die in der MySQL-Konsole momentan eingestellten Zeichensätze herauszufinden, geben wir einfach folgende Zeile ein:

    status;

    Danach sollte ein Screen wie in folgendem Beispiel kommen:

    mysql_status

    Wie wir sehen, ist die Datenbank als UTF8 angelegt, die Verbindung läuft allerdings im Latin1 Zeichenformat. Für uns bedeutet das, dass wir zwingend sicherstellen müssen, die Datenbank auch wirklich im Latin1 zu exportieren! Das kann man z.B. mit nachfolgendem Linux Befehl erzwingen (Nicht in der MySQL-Konsole eingeben!). Wichtig dabei ist vor allem die explizite Angabe des Latin1-Zeichensatzes, da sonst das Standard-Format der Datenbank hergenommen wird (wie im obigen Screen zu sehen, wäre das UTF8, was die Umlaute allerdings falsch kodiert):

    mysqldump --opt --default-character-set=latin1 --set-charset=latin1 --skip-add-drop-table -hlocalhost -ubenutzername -p"passwort" datenbank --tables tabellenname > tabellenname_dump.sql

    Da wir verschiedenste Tabellen in verschiedenen Formaten vorliegen, müssen wir in diesem Fall zur Sicherheit jede Tabelle separat exportieren. Das geschieht mit dem Flag --tables tabellenname in obigem Befehl.

    Da das Ganze bei 50 Tabellen sehr mühsam ist, habe ich mir daher ein PHP-Script geschrieben (Nicht jeder Benutzer hat Shell-Zugriff auf seinen Server, so dass man das Script auch für Shared-Hoster hernehmen kann): Die Werte für Datenbank müssen natürlich immer angepasst werden:

    <?php
    // Bitte hier Ihre Daten eintragen
    $host= 'localhost';
    $user= 'benutzername';
    $pass= 'passwort';
    $db=   'datenbank';
    $basepath = dirname(__FILE__);
    
    mysql_connect($host,$user, $pass);
    mysql_selectdb($db);
    // hole alle tabellen
    $res = mysql_query('show tables');
    while ($row = mysql_fetch_array($res))  {
        // Befehl ausführen und speichern in unterverzeichnis 'tables'
        system(sprintf(
            'mysqldump --opt --default-character-set=latin1 --set-charset=latin1 --skip-add-drop-table -h%s -u%s -p"%s" %s --tables %s > %s/%s.sql',
            $host,
            $user,
            $pass,
            $db,
            $row[0],
            $basepath . '/tables',
            $row[0]
        ));
        print $row[0] . ' done <br>';
    }

    Jetzt sollten wir einen export aller Tabellen mit deren Namen im Verzeichnis tables haben.

    Wenn wir uns diesen Dump jetzt einmal in einem Editor öffnen, der auch UTF8-beherrscht und auf dieses Zeichenformat umstellen, werden wir feststellen, dass alle Sonderzeichen nach wie vor korrekt dargestellt werden. Wenn wir uns jetzt das Create Table-Statement ansehen, fallen uns einige Sachen auf:

    Ganz am Anfang findet man einige Zeilen, die mit /*!40xx beginnen. Da ist die interessante Zeile die, die SET NAMES latin1 enthält. Aber Moment: Wir haben doch die Datei eben im Editor im UTF8-Format geöffnet und alle Sonderzeichen waren doch korrekt! Also ändern wir diese Zeile ab in SET NAMES utf8, was das Nutzen von UTF8 beim nächsten Import erzwingt. Über dem Create-Table-Statement stet ein SET character_set_client = utf8;, was in unserem Fall korrekt ist, steht dort allerdings latin1, ersetzen wir es durch utf8.

    Im Create-Table-Statement finden wir noch jede Menge collate utf8_unicode_ci, da wir UTF8 einfügen wollen, ist das in Ordnung, wenn dort allerdings latin1 steht läuft was falsch. Sicherheitshalber entfernen wir alle vorkommen dieser Zeilen.

    Unter dem Create-Table-Statement finden wir noch eine Zeile wie diese: DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci. Das Default Charset muss auf utf8 stehen, das ist korrekt, Collate setze ich aus persönlichen Vorlieben immer auf utf8_general_ci.

    Hat man das alles geändert, ist der Dump clean! Wir brauchen ihn nur noch die Datei korrekt im UTF8-Format abspeichern und wieder in die neue Datenbank importieren:

    mysql -hlocalhost -ubenutzername -p"passwort" -Ddatenbank < cleaned_tabellenname.sql

    Jetzt schaut unsere Datenbank auch wieder gut aus:

    phpmyadmin_tables_cleaned

    Und selbst den Eintrag an sich im PHPMyAdmin kann man auch wieder korrekt lesen:

    phpmyadmin_cleaned_entry

    Den kompletten Ablauf kann man sich natürlich noch in ein handliches Script schreiben, dass man möglichst wenig Aufwand hat.

    Wichtig: Dieses Tutorial ist nicht immer 1:1 anwendbar, es soll vielmehr eine mögliche Vorgehensweise beschreiben, wie man das Problem mit Modifizierungen lösen kann.

  2. Kommentar schreiben

    XHTML: Du kannst diese Tags nutzen: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

Gehe zur polyCODER Startseite