Timeout beim Einfügen von Dateien in MySQL

Computer: Software & Programmierung SQL & Datenbank - Programmierung
#1

Hallo,

ich möchte Nutzer meiner Seite eine MySQL-Datenbank füllen lassen.

Dies geschieht über eine Formular. Der Nutzer kann eine CSV-Datei auswählen und meine Software in PHP liest sie zeilenweise ein, wandelt sie für MySQL brauchbar um und fügt sie mit INSERTS ein. Dabei fasse ich immer hundert Zeilen in einen INSERT, also

$sql = ‚INSERT INTO tabelle (einheit_A, einheit_B, einheit_C, einheit_D, einheit_E, einheit_F, einheit_G) VALUES (‚a‘,‚b‘,‚c‘,‚d‘,‚e‘,‚f‘,‚g‘), (‚a‘,‚b‘,‚c‘,‚d‘,‚e‘,‚f‘,‚g‘), (‚a‘,‚b‘,‚c‘,‚d‘,‚e‘,‚f‘,‚g‘),
… 97 weitere…
, (‚a‘,‚b‘,‚c‘,‚d‘,‚e‘,‚f‘,‚g‘)‘;

Das Problem ist, daß PHP nach ca. 300 Einträgen in den Timeout geht(PowerWeb Perfect bei Strato).

Wie kann ich das Problem lösen? Ich will den Nutzer ja auch nicht ewig warten lassen ohne daß er etwas sieht.

Gründe für Rechenaufwand sind die internen Operationen (zum Beispiel wird ein Bild geladen, wenn es in der CSV-Datei als URL vorkommt, verkleinert und abgespeichert) und das Speichern in der Datenbank.

MySQL bietet da zwar für das Speichern in der Datenbank die Option an, eine Datei einzulesen aber wenn ich eine temporäre Datei erstelle, was passiert dann mit dieser, wenn 100 andere gleichzeitig den Dienst nutzen? Sie wird überschrieben.

#2

Hi

Das Problem ist, daß PHP nach ca. 300 Einträgen in den Timeout geht(PowerWeb Perfect bei Strato).

http://php.net/manual/de/function.set-time-limit.php
damit kannst du die maximale Ausführungszeit deines Skripts erhöhen

MySQL bietet da zwar für das Speichern in der Datenbank die Option an, eine Datei einzulesen aber wenn ich eine temporäre Datei erstelle, was passiert dann mit dieser, wenn 100 andere gleichzeitig den Dienst nutzen? Sie wird überschrieben.

Wenn du für jeden User eine eigene temporäre Datei anlegst, hast du das Problem nicht. Du könntest die session_id() als Dateinamen verwenden.

Ich will den Nutzer ja auch nicht ewig warten lassen ohne daß er etwas sieht.

Das würde ich dann per AJAX lösen. Es gibt AJAX-upload-Skripte im Netz (schau mal auf google oder hier http://www.easy-coding.de/wiki/php/upload-fortschrit…) die man dafür benutzen kann, dann hast du einen Uploader der dir den Fortschritt anzeigt. Nach dem Upload der CSV-Datei (auf dem Server wird sie nach der session_id() benannt z.B. tmp/af54afa44f5a4afdeed444.tmp), und dann die MySQL-integrierte LOAD_CSV (oder wie die nochmal heißt) funktion nutzen.
Die ist sowiso schneller. Das liegt daran, dass das Datenbanksystem nach jedem INSERT einen commit machen muss (das kostet zeit) wenn man keine Transaktion startet oder Prepared-Statements benutzt (was du garantiert nicht tust ^^)

Hoffe ich konnte helfen

#3

Es gibt zwei Möglichkeiten:

  1. Das Timeout mit der Funktion set_time_limit() höher setzen: http://de.php.net/manual/de/function.set-time-limit.php

  2. Für die temporäre Datei einen eindeutigen Dateinamen generieren lassen, der nicht doppelt vorkommt:
    http://de.php.net/manual/de/function.tempnam.php

#4

Das Problem ist, daß PHP nach ca. 300 Einträgen in den Timeout geht(PowerWeb Perfect bei Strato).

http://php.net/manual/de/function.set-time-limit.php
damit kannst du die maximale Ausführungszeit deines Skripts
erhöhen

Das kann ich natürlich nicht selbst ändern und Strato ändert das nicht.

MySQL bietet da zwar für das Speichern in der Datenbank die Option an, eine Datei einzulesen aber wenn ich eine temporäre Datei erstelle, was passiert dann mit dieser, wenn 100 andere gleichzeitig den Dienst nutzen? Sie wird überschrieben.

Wenn du für jeden User eine eigene temporäre Datei anlegst,
hast du das Problem nicht. Du könntest die session_id() als
Dateinamen verwenden.

Interessante Idee.

Ich will den Nutzer ja auch nicht ewig warten lassen ohne daß er etwas sieht.

Das würde ich dann per AJAX lösen. Es gibt AJAX-upload-Skripte
im Netz (schau mal auf google oder hier
http://www.easy-coding.de/wiki/php/upload-fortschrit…)
die man dafür benutzen kann, dann hast du einen Uploader der
dir den Fortschritt anzeigt. Nach dem Upload der CSV-Datei
(auf dem Server wird sie nach der session_id() benannt z.B.
tmp/af54afa44f5a4afdeed444.tmp), und dann die
MySQL-integrierte LOAD_CSV (oder wie die nochmal heißt)
funktion nutzen.

Interessant.

#5

Hallo,

PHP hat normalerweise einen Timeout von 30 Sekunden. Wenn du diesen Wert erhöhen willst, kannst du

set\_time\_limit

verwenden. Jedesmal, wenn du diese Funktion aufrufst, wird der Timeout auf so viele Sekunden in der Zukunft gesetzt. Wenn du also, z.B., nach 10 Sekunden Ausführungszeit

set\_time\_limit(30)

ausführst, darf das Skript weitere 30 Sekunden laufen, bevor es abgebrochen wird. Wenn ich rechenintensive Operationen mit Datenbanken durchführe (z.B. 30.000 Zeilen einfügen), rufe ich

set\_time\_limit

normalerweise immer alle 100 Einträge auf, das reicht im Regelfall um Timeout zu verhinden.

Allerdings löst das nicht das Problem, dass die User nichts zu sehen bekommen. Um das zu lösen, würde ich dir raten, das Skript zum Einlesen per AJAX-Aufruf im Hintergrund auszuführen. Dann kannst du dem User ein Ladesymbol anzeigen, das per Javascript ersetzt wird, wenn die Operation fertig ist.

Zum Problem mit den Temp-Dateien würde ich dir empfehlen, jede hochgeladene Datei mit einem einzigartigen Namen zu versehen, beispielsweise einen Dateinamen im Format Jahr-Monat-Tag-Stunde-Minute-Sekunde-[zufällig generierter Hash].csv. Das würde dann Dateinamen wie z.B.

2012-09-08-16-38-20-fb17de25072cbe083e244a5651ed0541c0867f52.csv

erzeugen, die fast garantiert einzigartig sind und nicht überschrieben werden.

Ich hoffe, das hat geholfen. Wenn etwas unklar ist, frag bitte nach.

Freundliche Grüße,

Paul

#6

Hi!
Ich muss ja nicht erwähnen, dass die Operationen die dazwischen sind, also Bild laden und auch noch verkleinern, sehr viel (wenn man es x mal hintereinander macht) Zeit kosten :wink:

Hast Du mal überlegt, die Sache in zwei Schritten auszuführen? -Also zuerst den Kram in die Datenbank packen und dann später mit einer Query die operationen durchführen?

Letzteres könnte dann kumuliert für ALLE Uploads geschehen. Machst halt noch n Feld mit rein, welches Dir sagt ob die Sache bereits verarbeitet wurde oder nicht. Wenn Du Krams hochlädst, wird der Datensatz standardmäßig mit „nicht verarbeitet“ gekennzeichnet und irgendwann startest Du ein Script (Cron) welches alle Datensätze durchgeht und Bilder holt, konvertiert und speichert.

Dieser Schritt wird zwar auch ne Weile brauchen, aber der Benutzer wird nicht lange aufgehalten und es besteht nicht die Gefahr, dass nicht alle Datensätze eingelesen werden weil mitten im Konvertieren der Timeout kommt :wink:

Beste Grüße
Andy

#7

Hast Du mal überlegt, die Sache in zwei Schritten auszuführen?
-Also zuerst den Kram in die Datenbank packen und dann später
mit einer Query die operationen durchführen?

Ich will dem Nutzer gleich mitteilen, ob es Probleme mit seinen Daten gibt - etwa ob die Bilddateiurl falsch ist(404), oder eine falsche Größe hat.

Ich habe es jetzt so gelöst, daß er im Hintergrund konvertiert und wenn er fertig ist, kriegt der Nutzer eine eMail, die ihm sagt, ob es Fehler gab. Das ist es ja in etwa, was du meinst, oder?

#8

Moin,

mein Tipp wäre die CSV-Datei einfach auf dem Server abzulegen und dann in einem neuen PHP-Skript diese zu verarbeiten. Gerne auch cron-getrieben.

Kannst dir auch ein paar Message-Queues anschauen. Gearman oder Rabbit MQ wären da Beispiele, aber vielleicht ist das zu viel.

Der einfachste Fall wäre natürlich das Time-Out hochzusetzen, damit hast du aber immer noch das Usability-Problem

Gruß,
Nils

#9

Hi!

Solche Plausibilitätschecks, also ob das Bild überhaupt vorhanden ist, kannst Du auch während des Uploads durchführen. Allerdings solltest bei der Abfrage der URL nicht das HTTP GET sondern HEAD anwenden damit Du nicht mit Daten überflutet wirst :smile:

#10

Hallo donatus,

eine knifflige Frage! Ich hoffe, ich habe Dich richtig verstanden: Im beschriebenen Use Case lädt ein Nutzer eine CSV-Datei hoch, die Du in PHP verarbeitest. Zeile für Zeile laufen verschiedene Vorgänge ab, die auch zeitaufwendig sind. Irgendwann kommt dann der Timeout seitens PHP.

Vielleicht kannst Du den Timeout auch bei Strato verändern: http://php.net/manual/de/function.set-time-limit.php

Ich würde den Nutzer aber nicht warten lassen. Vielleicht bricht er ja ab und bekommt keine Rückmeldung. Mein Vorschlag für ein Vorgehen:

  1. Upload CSV

  2. Einfügen der CSV-Daten in eine temp. Tabelle:

    $sql = 'INSERT INTO tabelle_temp (timestamp, tempid, einheit_A, einheit_B, …

  3. Feedback an den Nutzer: „Daten werden verarbeitet“ und Reload der Seite mit Parameter t = ‚4711‘

  4. Daten ziehen:

    SELECT * FROM tabelle_temp WHERE timestamp = 4711 LIMIT 100

  5. Abarbeiten der Schritte (Bilder verarbeiten etc.)

  6. Abgearbeitete Zeilen löschen:

    DELETE FROM tabelle_temp WHERE tempid = …

  7. gehe zu 3. oder fertig

Alternativ, wie bspw. beim Upload eines Youtube-Videos: Du gibst dem Nutzer nur Feedback, dass der Upload geklappt hat. Dein Script, das die temp. Tabelle abarbeitet, wird per Cronjob aufgerufen und nimmt immer die ältesten Einträge:

SELECT \* FROM `tabelle_temp` ORDER BY `timestamp` ASC LIMIT 100

In einer zweiten Tabelle hältst Du fest, welcher Nutzer welche CSV schon abgearbeitet hat. Der Nutzer kann einen Kaffee trinken gehen, sein Browser-Fenster schließen… Ganz egal. Wenn er auf die Seite zurück kommt, sieht er dann den Status seiner Verarbeitung.

Cronjobs sind laut dieser Seite auch bei PowerWeb Perfect verfügbar: http://www.strato.de/hosting/power-hosting/

Viel Erfolg!

#11

Hallo,

mit welcher Programmiersprache erfolgt das Insert?
Codebeispiel?

#12

Hallo,

ja das Problem liegt einfach daran, dass dein Hoster die ExecutionTime der PHP-Skripte begrenzt hat. Das macht er deshalb, um zu verhindern, dass fehlerhafte Programme (etwa in Endlosschleifen) den ganzen Server lahmlegen :smile:

Du könntest beispielsweise die 300 Zeilen-CSV in 6*50 Arbeitsschritten bearbeiten. Eventuell mit anschließender header()-Anweisung, die das nächste Skript ruft. Wobei ich nicht weiss, ob dabei die Laufzeit vom Interpreter wieder auf 0 gesetzt wird. Zur Not per HTML Meta-Reload…

Gruß,
Daniel

#13

Siehe Funktion set_time_limit()
Wenn dass nicht klappt wird es wohl keine Möglichkeit geben: Diese Einstellung in der php.ini wirst du bei Strato nicht Ändern können. Bin von Strato weg, waren mir zu unseriös: Haben versucht mein Vertrag ohne klaren Hinweis teurer zu machen - ist so weder zulässig, noch 'ne gute Art mit den Kunden zumzugehen.

Die Lösung des Problems heißt also unter Umständen Anbieter wechseln - Kann dir leider nicht sagen welcher Anbieter Timeoutänderungen zulässt. Ein Anbieter fällt mir da spontan auch ein, dem ich das zutraue, aber dann werde ich bestimmt noch wegen AGB-Verletzungen oder sowas zensiert und hier rausgeworfen.

Zur temporären Datei:
Vielleicht hilft dir ja tmpfile().

Weiterhin fallen mir da noch ein paar weitere hässliche Lösungen mit stückweise einlesen ein, aber ich glaub die vergessen wir schnell wieder :wink:

Ich hoffe ich habe dein Problem beim Überfliegen erfasst und beantwortet.

Viel Erfolg.

#14

Hallo,

wenn ich das richtig verstanden habe, dann wird der Timeout durch php gemeldet.
Wenn der Server die Defaulteinstellung hat, dann wird jedes PHP-skript nach 30 Sekunden mit einem Timeout beendet.

Also bleiben 3 Möglichkeiten von denen wohl nur die letzte praktikabel sein dürfte:
1.) Die Zeit des Timeout erhöhen. Wird aber im Regelfall nicht gehen, da das normalerweise vom Provider nicht erlaubt wird.

2.) Das Script so abändern, dass die Operation innerhalb des Timeouts abläuft. Könnte aus deinen genannten Gründen etwas schwer sein und wenn die CSV-datei recht groß sein darf, sind potenziell zu viele Einträge erlaubt um es zu garantieren.

3.) Die Operation quantisieren. Also die Operation in mehrere Operationen, die nacheinander einzeln ausgeführt werden aufteilen.
Zum Beispiel die Datei in eine temporäre Datei speichern (wenn der Dateiname entsprechend benamt ist, können parallel auch andere den Dienst benutzen. Beispielsweise kann der Name die sessionId des Users enthalten oder evtl. gibt es zwischenzeitlich in php eine Funktion, die einem eine temporäre Datei anlegt?)
und auslesen, wieviele Einträge verarbeitet werden müssen, um den User zu informieren.

Danach die Einträge in z.B. 100er Schritten abarbeiten, wobei der User (oder Javascript) immer den nächsten Schritt per neuem Request anstößt. So erhält man dann immer ein neues Timelimit und kann dem User eine Art Fortschrittsanzeige anbieten.

Der Hacken dabei: Wenn der User die Browser schließt, wird wie beim Timeout nicht alles verarbeitet. Außerdem tümpeln dann alte temporäre Dateien auf dem Server rum, die man regelmäßig aufräumen muss.

Hoffe, ich konnte helfen.

Lg
Jens