Nudge am 21.10.2008

Böse Falle: Locale-Einstellungen mit PHP und FreeTDS

in PHP | Tags: FreeTDS, Locale, MSSQL, PHP, SQL Server

Wer sein PHP und MSSQL arbeitet, sollte sich stets eine kleine Check-Routine für den Fall eines Software-Updates aufbauen. Darin sollten Abfragen von Konstanten, Datetime und Money- bzw. Float-Werten vorkommen. Denn ein Update des MSSQL-Servers, PHP oder der FreeTDS-Bibliothek kann fatale Folgen haben.

MSSQL und PHP über FreeTDS

Eigentlich funktioniert die Verbindung wirklich gut. Sie ist stabil und performant, muss ich sagen. Im Büro läuft ein echter 4-Kern-Bolide mit SQL Server 2003 und etwa 10 GB an Daten, der uns als ERP-System dient. Die Anbindung ist für uns zum Teil geschäftskritisch und deswegen sind Fehler nicht gerade erwünscht.

Mit PHP 5.2.6 sind wir up-to-date, doch mit FreeTDS 0.64 etwas hinterher. Seit kurzer Zeit gibt es bereits die 0.82, die auch als stable markiert ist. Den Leuten von FreeTDS scheint Stabilität wirklich wichtig zu sein, allen stable-Versionen folgende Snapshots widmen sich ausschließlich Bugfixes, neue Features werden sinnvollerweise für spätere Releases vorenthalten.

Locales sind zum Haareraufen

Diese Erfahrung musste ich leider wieder mit Learning by doing machen 🙁 … Die Performance hin oder her, wenn die Daten falsch ankommen, kann man damit wenig anfangen. Die Locales betreffen die Art, wie PHP uns Daten wie Zeitstempel und Fließkommazahlen schmackhaft zubereitet. Deutschen werden also Kommas statt dem Punkt in Bruchzahlen angezeigt. Was erst einmal ganz angenehm klingt, kann aber bei der Weiterverarbeitung zu Problemen führen.

Das Datetime-Problem

Zunächst ist folgende Einstellung in der php.ini ganz wichtig:

1
2
3
4
; Specify how datetime and datetim4 columns are returned
; On => Returns data converted to SQL server settings
; Off => Returns values as YYYY-MM-DD hh:mm:ss
mssql.datetimeconvert = Off
; Specify how datetime and datetim4 columns are returned
; On => Returns data converted to SQL server settings
; Off => Returns values as YYYY-MM-DD hh:mm:ss
mssql.datetimeconvert = Off

Damit ist erst einmal sichergestellt, dass ein Datumswert immer in der Form 2008-10-21 22:50:01 ankommt, sonst kann man Vergleiche mit MySQL-Zeiten etc. vergessen. Das SQL-Server-Format kann wie hier eventuell entnehmbar variieren – das ist keine gute Grundlage für eine stabile Zusammenarbeit.

Auch wenn es damit unnütz erscheint, ist es sicher, in FreeTDS die locales nochmals auf dieses Format zu trimmen. Dazu muss man die Datei locales.conf von FreeTDS um diese Angaben erweitern:

1
2
3
4
5
6
7
[default]
    date format = %Y-%m-%d %H:%M:%S
 
[de_DE]
    date format = %Y-%m-%d %H:%M:%S
    language = Deutsch
    charset = iso_1
[default]
    date format = %Y-%m-%d %H:%M:%S

[de_DE]
    date format = %Y-%m-%d %H:%M:%S
    language = Deutsch
    charset = iso_1

Die andere Richtung wird noch komplizierter. Hier habe ich noch nicht wirklich herausgefunden, welche Locale-Einstellung die Übertragung von PHP nach MSSQL wirklich steuert. Bis vor kurzem konnte ich wie folgt Datumsangaben übermitteln:

1
CAST('YYYY-MM-DD hh:mm:ss' AS Datetime)
CAST('YYYY-MM-DD hh:mm:ss' AS Datetime)

In der Oberfläche des MS SQL Enterprise Managers, der sich sicherlich an die Locale-Einstellung des Windows-Benutzers richtet, ist bei SQL-Abfragen jedoch stets folgendes anzugeben:

1
CAST('DD.MM.YYYY hh:mm:ss' AS Datetime)
CAST('DD.MM.YYYY hh:mm:ss' AS Datetime)

Seit kurzem muss ich jedoch aus unerfindlichen Gründen das Datum auf diese Weise übermitteln:

1
CAST('DD/MM/YYYY hh:mm:ss' AS Datetime)
CAST('DD/MM/YYYY hh:mm:ss' AS Datetime)

Und das, obwohl ich das Update von FreeTDS 0.64 auf 0.82 wieder rückgängig machte. Wer weiß! Gut, wenn man eine kleine DB-Abstraktionsschicht hat, die sich um Konvertierungen von Daten kümmert. Da gibt es einen kleinen Griff, und alle Skripte laufen wieder korrekt.

Neben der CAST-Anweisung gibt es in MSSQL noch die CONVERT-Anweisung, die identisch angewendet wird und sich nach meiner Erfahrung analog zu CAST verhält.

1
CONVERT(Datetime, 'DD/MM/YYYY hh:mm:ss')
CONVERT(Datetime, 'DD/MM/YYYY hh:mm:ss')

Das Check-Script sollte diese und die Ausgabe der GetDate()-Funktion ermitteln, so dass man immer mal schnell überprüfen kann, wie MSSQL gerade drauf ist.

Das Money- oder Float-Problem

Ein zweiter Typ, der echt nervig sein kann, sind float-Werte. Diese werden von PHP intern trotz zB einer deutschen Kommaeinstellung richtig weiterbehandelt. Holt man sich solche komma-beladenen Werte jedoch aus der MSSQL-Datenbank, so ignoriert PHP die Kommas und alle Werte verhundertfachen sich! Das kann richtig Ärger geben, wenn man zum Beispiel mit Preisen oder Geldbeträgen hantiert!

Zum Nachvollziehen: Man setze die kompletten Locales auf Deutsch per:

1
@setlocale(LC_ALL, 'de_DE@euro', 'de_DE.ISO-8859-1');
@setlocale(LC_ALL, 'de_DE@euro', 'de_DE.ISO-8859-1');

Dann fährt man sein Script ab und fragt zum Beispiel ab:

1
SELECT 10.0/6 AS Konstante;
SELECT 10.0/6 AS Konstante;

Was erhält man? Unglaublicherweise liefert der Server oder FreeTDS (ja, wer ist denn nun schuld? ;-)) “1.66666666” – mit Punkt! Dagegen kann man sich irgendwo einen Money-Wert ziehen, fiktiv zum Beispiel:

1
SELECT AVG(Preis) FROM Produkte;
SELECT AVG(Preis) FROM Produkte;

Naja, ihr ahnt schon, was jetzt kommt: So etwas wie “7,82” oder ähnlich. Klingt erst einmal nicht schlimm. PHP gibt die Werte ja auch mit Komma aus, was soll daran falsch sein? Der Clou kommt wie immer im zweiten Schritt. Nehmen wir mal an, wir hätten uns diesen Wert in die Variable $avg_preis gespeichert. Natürlich wollen wir mit dem Preis weiterarbeiten, und speichern ihn irgendwo anders in der MSSQL-Datenbank:

1
2
mssql_query("INSERT INTO PreisCheck (AvgPreis, Datum) VALUES (".
   (float) $avg_preis.", GetDate());");
mssql_query("INSERT INTO PreisCheck (AvgPreis, Datum) VALUES (".
   (float) $avg_preis.", GetDate());");

Da haben wir den Salat: Jetzt steht in der Datenbank eine 782 statt der 7.82 oder 7,82 die dahingehören. Daher empfehle ich, die Umgebungsvariable LC_ALL in Frieden zu lassen, und wer Locale-Settings wirklich braucht (zum Beispiel im Falle FPDF) nur LC_MESSAGES zu setzen:

1
@setlocale(LC_MESSAGES, 'de_DE@euro', 'de_DE.ISO-8859-1');
@setlocale(LC_MESSAGES, 'de_DE@euro', 'de_DE.ISO-8859-1');

Ganz sicher geht man, wenn man vor dem Eintragen von Float- oder Money-Werten die Werte richtig durch die Mangel nimmt:

1
2
INSERT INTO PreisCheck (AvgPreis, Datum) VALUES (".
   str_replace(",",".", (float) str_replace(",",".",$avg_preis)).", GetDate());
INSERT INTO PreisCheck (AvgPreis, Datum) VALUES (".
   str_replace(",",".", (float) str_replace(",",".",$avg_preis)).", GetDate());

Das erste str_replace() (das innere, was weiter hinten steht) wird gebraucht, um Daten, die aus dubiosen Quellen kommen, das Fürchten zu lehren. Im zweiten Schritt wird der Wert in ein float “gecastet” – damit auch getrimmt und von Unrat befreit. Sehr wichtig, vor allem bei angreifbaren Webanwendungen. Zuletzt wird dem Wert doch noch einmal das Komma entzogen, falls PHP denkt, es müsse da unbedingt etwas formatieren.

Damit ist unser DB-Zugriff auch vor ungewollten Fehlkonfigurationen geschützt. Natürlich sind Freunde kleiner handlicher Helferfunktionen unseren tippeifrigen Brüdern um Meilen voraus. 🙂 Ein paar dieser Abfragenn nach Money- und Floats sollte man wieder in sein Check übernehmen, so dass man alle Fälle abgedeckt hat.

Fazit: Performance ist gut, Kontrolle wie immer besser

Ich hoffe, das hat etwas Licht in dieses doch mystische Kapitel PHP und MSSQL bringen können. Manche schwören auf andere Einstellungen, aber wer mit MySQL großgeworden ist, wird das YYYY-MM-DD-Format wie auch den Punkt statt Komma in einer Zahl zu schätzen wissen. Egal wie der Benutzer seinen Rechner umkrempelt – die App muss richtig ticken. Also lieber alles auf plain “C” lassen und hier und da die Ausgabe umschreiben, als sich hinterher mit Datensalat rumzuärgern.

Und jetzt noch ein richtiger Absacker-Spruch zur Motivation: Kommas sind doch was für Weicheier. 🙂


Das mark ich mir: Alltagz Mr Wong Yigg Del.icio.us Yahoo MyWeb Blinklist Google folkd
 

5 responses to “Böse Falle: Locale-Einstellungen mit PHP und FreeTDS”

  1. Breideric says:

    Hertzlichen Dank euch,

    Etwas an der php.ini rum spielen war tatsächlich die
    Lösung. Habe die max_execution_time, max_input_time
    und das memory_limit enorm vergrössert, nun läuft
    es.

    Danke und Gute Zeit
    Breideric

  2. Nudge says:

    Hallo Breideric,

    die Übertragung sollte auf jeden Fall mehr 100000 Datensätze überstehen, je nach Memory Limit.

    Ist denn bekannt, wie das Script abbricht?
    Dazu würde ich an deiner Stelle die Fehlermeldung studieren, mit der PHP abbricht. Du findest sie entweder auf der Kommandozeile, wenn du interaktiv arbeitest, oder in einem Logfile, unter Unix und Linux in /var/log/user.log oder ähnlich.

    Wenn die CSV-Datei sehr groß ist, dann kann es sein, dass PHP das eingestellte Memory Limit überschreitet, falls Du versuchst, die Datei als ganzes zuerst einzulesen. Ebenso könnte das Memory Limit ein Problem sein, wenn du zwar die Datei seriell einliest, später aber die Statements oder Ergebnisse zwischenspeicherst, zum Beispiel in einem Hash. Dann kann es sein, dass der Hash größer als das Memory Limit wird.

    In solch einem Fall hilft
    a) das Anheben des Memory Limit. Das ist standardmäßig auf 8 MB eingestellt. Bei uns brauchen wir manchmal über 128 MB, je nach Datenmenge. Insofern steht das bei uns sogar auf 256 MB. Voraussetzung ist natürlich, dass nicht allzu viele parallel laufende Scripte dies ausreizen.
    Die zu modifizierende Config-Variable heißt memory_limit.
    b) Datei nur seriell einlesen, Daten seriell rausschreiben, zB in ein Log-File, aber davon nichts zwischenspeichern.

    Bricht das Script dagegen stets nach 30 Sekunden, 10 Minuten (oder ähnlich ab), dann sind Zeitlimits das Problem. PHP unterscheidet mehrere Zeitlimits: Das erste ist die max_input_time, die besagt, wieviele Sekunden PHP dafür aufwenden soll, GET, POST, Parameter etc. zu parsen. Ich denke, dies ist hier nicht der Fall, denn immerhin werden ja mehrere 1000 Datensätze eingelesen. Das heißt, das Script läuft schon mal los. Dann gibt es noch die max_execution_time, die besagt, wieviele Sekunden ein PHP-Script maximal laufen darf. Diese sollte im Standardfall bei 30 Sekunden liegen, was für einen Webserver OK ist. Lässt man PHP im CLI-Modus (auf der Kommandozeile) laufen, dann sind selbst 10 Minuten, also 600 Sekunden oft nicht ausreichend, gerade für Migrationsscripte bei großen Datenbanken.

    Ist dies der Fall, könntest du versuchen
    a) die max_execution_time auf einen sehr hohen Wert setzen, zB 3600 = 1 Stunde.
    b) die max_execution_time auf 0 setzen, was soviel bedeutet wie unendlich.

    In beiden Fehlerfällen wäre es vielleicht ratsam, drastische Veränderungen an den PHP-Grundeinstellungen im Skript selbst zu überschreiben, um andere Scripte im Rahmen zu halten.
    Dazu kannst du die Funktion ini_set() am Anfang des Scripts benutzen.

    Viel Erfolg!

  3. Breideric says:

    Guten Tag, ich habe auch ein Problem mit dem MSSQL. Ich probiere Daten aus einem CSV.File zu übertragen. Es sind Grössere Datenmengen, nach ca. 30-60 ooo Datensätzen brechen die php-Scripts immer ab? Haben Sie eine Ide an was dies liegt, ich müsste die Daten alle 4 Stunden übertragen?
    Ich Splitte erst das file mit 120 ooo Datensätzen in verschiedene csv-Dateien, da die gesamte nicht eingelesen wird? Ein anderes php-Script liest nun die verschiedenen Daten je 10 000 Datensätze ein.
    Aber es werden nie alle Dateien übertragen in die DB. Haben Sie eine Idee? Kann ich mit einem Script nur eine bestimmte Anzahl Daten nach SQL übertragen.
    Bitte um hilfe, ich komme einfach nicht mehr weiter….

  4. Nudge says:

    Klappt: SELECT CONVERT(Datetime, ‘2009/10/11 12:13:14’, 20);
    -> Danke für den Tipp!

  5. Vinzenz Mai says:

    CONVERT und die Angabe von style hilft 🙂

    siehe: http://msdn.microsoft.com/de-de/library/ms187928.aspx

Leave a Reply

Your email address will not be published. Required fields are marked *