phpmyadmin 4.6.4 ist buggy – nicht für Sicherungen verwenden !

Ich hatte heute die Aufgabe, die Daten eines WordPress-Blogs von der MySQL-Datenbank eines Hosting Providers zu sichern und den Blog dann auf eine neue Datenbank bei einem anderen Provider umzuziehen. Dabei hat ein übler Bug in der phpMyAdmin-Version 4.6.4 etliche meiner Nerven verschlissen:

Auf Exports von Tabellen mit Kommentaren oder Spalten mit bestimmten HTML-Inhalten kann man sich bei der aktuellen Version (Stand 03.10.2016) leider nicht verlassen; der nachfolgende Import scheitert unter Umständen - vermutlich wegen einer fehlenden oder fehlerhaften Maskierung von Anführungszeichen.

Es dauerte aber eine ganze Weile, bis ich anfing, der Exportfunktion von phpMyAdmin zu misstrauen - vor allem, weil mir bei dem Provider keine Datenbank-Logs zur Verfügung standen. Ich konnte den Fehler aber an einer reduzierten "posts"-Testtabelle der WordPress-Installation nachvollziehen; der Export und nachfolgende Import schlägt mit der Version 4.6.4 reproduzierbar fehl - in meinem Fall sogar mit einem internen Server-Error.

Gott sei dank war die alte Datenbank noch nicht gelöscht! Ich konnte den Export/Import-Vorgang mit der auf die Schnelle installierten phpMyAdmin-Version 4.4.15.8 anschließend anstandslos und fehlerfrei durchführen.

Solche Bugs können üble Folgen haben, wenn man nach einer "Sicherung" naiverweise den alten Datenbank-Zustand löscht, ohne vorher an einer anderen Datenbank den Reimport getestet zu haben. Auch für phpMyAdmin gilt bei Sicherungen die alte Regel:
Immer erst die ganze Kette zur Restaurierung testen, bevor man irgendetwas löscht!

Dennoch: So ein Bug ist wirklich übel! Ich bin ein wenig sauer .... im Nachhinein fand ich dann unter github folgende Bug-Einträge:
https://github.com/phpmyadmin/phpmyadmin/issues/12086
https://github.com/phpmyadmin/phpmyadmin/issues/12453
Der erste davon wurde geschlossen, obwohl er offenbar immer noch oder wieder vorhanden ist.

MySQL – SELECT INTO OUTPUT FILE – vermeide zusätzliche UNION Statements!

Wenn es um den Export relativ großer Tabellen in CSV-Dateien geht, greift man bei MySQL-Datenbanken auf eine spezielle Form des SELECT Statements, nämlich "SELECT INTO OUTPUT FILE ... ". Ich nenne das nachfolgend der Einfachheit halber ein "Export SELECT". Siehe hierzu:
http://dev.mysql.com/doc/refman/5.7/en/select-into.html

Oftmals benötigt man aber nicht nur die reinen Daten sondern eine CSV-Datei mit einer führenden Zeile, in der die Bezeichnungen der einzelnen Felder/Spalten auftauchen ("column heading"). Die gängige Empfehlung vieler Beiträge zu diesem Thema ist, das Ergebnis des oben genannten Statements "SELECT INTO OUTPUT FILE ... " per UNION mit einem "künstlichen SELECT-Statement zu verbinden, dass genau die Strings der ersten Zeile erzeugt. Siehe hierzu etwa
http://stackoverflow.com/questions/356578/how-to-output-mysql-query-results-in-csv-format (und dort einen entsprechenden Kommentar zum Lösungsansatz der Forums-Frage)
oder aber
http://www.mysqltutorial.org/mysql-export-table-to-csv/
und dort der Abschnitt zu "Export Data with Column Headings".

Entsprechende Vorschläge folgen dem Muster A

(SELECT 'Name of Column 1', 'Name of Column 2', 'Name of Column 3')
UNION
(SELECT column_name_1, column_name_2, column_name_3
FROM TABLE_NAME
INTO OUTFILE 'absolute_path_to_csv_file'
FIELDS ENCLOSED BY '"' TERMINATED BY ';' ESCAPED BY '"'
LINES TERMINATED BY '\r\n');

Festzustellen ist hierzu Folgendes:

Ein solches Statement funktioniert zwar und produziert das gewünschte Ergebnis; aber es reduziert die Performance des Daten-Exports in eine Datei drastisch - speziell wenn man mit großen Tabellen und mehreren Millionenen oder gar Hunderten von Millionen Records arbeitet. Dann entfaltet sich das UNION-Statement zu einem echten Performance-Killer!

In einem konkreten Testbeispiel konnte ich den Export aus einer Datei mit einer Million Records vergleichen. Der Export mit einem Union Statement dauerte fast 50 Sekunden; läßt man die Kopfzeile dagegen nach dem Muster B

SELECT column_name_1, column_name_2, column_name_3
FROM TABLE_NAME
INTO OUTFILE 'absolute_path_to_csv_file'
FIELDS ENCLOSED BY '"' TERMINATED BY ';' ESCAPED BY '"'
LINES TERMINATED BY '\r\n';

weg, so dauerte der Export nur 1,8 Sekunden. Das Verhältnis wird noch schlimmer bei größeren Record-Zahlen der Tabelle.

Ein wenig tieferes Nachdenken und eine SQL-Analyse mit "Explain" machen auch klar, woran das liegt: Der Einsatz von UNIONS erfordert das Zusammenfügen der Resultsets der verschiedenen SELECTs. Dazu muss jedes Resultset aber erstmal generiert werden. Dazu müssen temporäre Datenhaltungsstrukturen aufgebaut werden. Dies kollidiert jedoch mit der ansonsten speziell optimierten Funktionalität des "SELECT INTO OUTPUT FILE"-Statements, nämlich die Tabelleninhalte aus den Datenbankfiles mit speziellen Methoden direkt auszulesen und sofort in eine Datei zu schreiben.

Ich kann daher nur jedem raten, beim Export wirklich großer Tabellen in CSV-Dateien auf jegliche Kombination des "SELECT INTO OUTPUT FILE"-Statements mit anderen SELECTS unter Rückgriff auf "UNION" zu verzichten. Das zieht ggf. den Verzicht auf eine erste führende Zeile nach sich.

Änderung und Nachtrag 08.01.2015:
In Fällen mit kleineren Dateien von bis zu n x 100 MByte kann es - je nach RAM-Ausstattung des Systems - u.U. besser sein, zunächst den reinen Daten-Export (gem. Muster B) durchzuführen und danach die erzeugte Datei mit PHP File-Funktionen oder Systemfunktionen nachzueditieren und die gewünschte erste Zeile mit der Feldinformation hinzuzufügen. Das ist aber zu testen. In meinem Testfall mit 1 Million Datensätzen war das File kleiner als 50 MByte und lag auf einer SSD. Zudem ist das System hinsichtlich der Plattenzugriffe gut gepuffert. In diesem Fall erhöhte sich die benötigte Zeit durch das Hinzufügen der Kopfzeile untr PHP kaum spür- und messbar über die 1,8 Sekunden hinaus. Die Änderung war gegenüber dem oben erwähnten Zeitunterschied des UNION-Statements völlig vernachlässigbar.

Bei Dateigrößen von mehreren hundert Mbyte oder gar im GByte-Bereich wird ein nachträgliches Editieren unter PHP aber ebenfalls zu Problemen führen, da das File erst einmal zu lesen und dann mit dem String für die erste Zeil zu konkatenieren ist. Man muss in Tests prüfen,

  • ob das für solche Operationen erlaubte Memory unter PHP hinreichend ist (und es ggf. anpassen)
  • und ob sich die Lesezeit unter PHP als kleiner oder größer als die von MySQL aufgebrachte Zeit für ein UNION herausstellt.

Am besten erscheint es mir, bei wirklich großen Datenmengen im Sinne der Performance schlicht und einfach auf die führende Kopfzeile zu verzichten. Derjenige, der das exportierte CSV-File in irgendeiner Weise nutzen will, muss eben über die Feldreihenfolge Bescheid wissen.