Inkonsistentes Verhalten bei falschen Datentypen der sendData Modi

[aneudecker]

Bei Funktionen, die aus mehreren SQL Befehlen bestehen, sollten wir mit Transactions arbeiten. Das betrifft insbesondere sendData mit mode = "truncate", aber evtl. auch andere Funktionen.

[wahani]

Ist mir auch gerade aufgefallen. Sollten wir auf jeden Fall umsetzen! Ich sehe aktuell eigentlich nur Bedarf bei truncate und evtl bei update. Bei den anderen Vorgängen wir nur eine insert infile Abfrage abgesetzt. Bei anderen Punkten ist es mir aber vielleicht einfach noch nicht aufgefallen.

[aneudecker]

@wahani Mir ist nicht so ganz klar, an welcher Stelle wir das am besten umsetzen. Die Standard Befehle von DBI::dbBegin, DBI::dbCommit haben bei mir nicht funktioniert. Außerdem scheint es noch nötig zu sein, dass man SET autocommit = 0 auf der Datenbank setzt. Evlt. ist es auch besser es direkt in der Query umzusetzen? Ich gebe dir das Issue mal, da es mir hier an Expertise fehlt ...

[wahani]

Für sendData:

Wir nutzen die sendData Befehle derzeit für den Bulk-Upload von Daten. Dabei bekommt jeder Upload eine eigene Verbindung und jeder send data local infile ist ohnehin in einer Transaktion. Für den mode truncate erübrigt sich zudem die Frage, da hierbei Schema Änderungen (drop table, create table) durchgeführt werden und es dafür keinen Rollback gibt. Wenn ich es richtig sehe wird durch eine Änderung am Schema auch automatisch ein Commit ausgelöst. Also selbst wenn es möglich wäre, müssten wir dabei sehr aufpassen, ob zwischendurch einmal ein truncate genutzt wird.

@aneudecker hat sich hier das Upload Szenario geändert und wir brauchen mehrere Schreibvorgänge in einer Transaktion / Aufruf?

Für sendQuery:

Es ist natürlich möglich Daten in einem Aufruf über sendQuery in eine DB zu schreiben, aber so ist es eigentlich nicht gedacht. Für Schreibvorgänge haben wir ja gerade die sendData Methode, insofern sehe ich auch hier noch nicht den Use-Case.

Generell ist es in unseren Anwendungen so, dass wir sehr wenige, dafür aufwändige Schreibvorgänge haben. Hier noch eine Referenz zum Daten-Laden mit load data local infile.

@aneudecker @jonathan-inwt mein Fazit wäre hierbei derzeit, dass wir keinen Bedarf dafür haben, etwas an dem Modus zu ändern für jeden Upload eine eigene Verbindung aufzubauen. Damit würden wir auch nicht davon profitieren, eine Transaktion explizit zu machen, im Vergleich zu dem impliziten Vorgehen, was wir derzeit nutzen. Bitte widersprechen, wenn ich etwas übersehen habe.

[jonathan-inwt]

In sports-redshift haben wir transactions eingeführt, da redshift Primary Keys nicht erzwingt. Wir waren also gezwungen, in eine Staging-Tabelle zu schreiben, den PK zu prüfen und ggf. Daten aus der Zieltabelle zu löschen, bevor dann der eigentliche Schreibprozess erfolgen konnte. An dieser Stelle ist es eindeutig, da ein Fehler beim Schreiben Daten aus der Zieltabelle gelöscht, aber nicht wieder befüllt hätte.

Das Problem haben wir glücklicherweise mit MariaDB nicht.

Problematisch ist bei uns vor allem das inkonsistente Verhalten der vier Modi. Wenn wir beim Schreibvorgang einen Fehler produzieren (bspw. Duplicate entry for key 'PRIMARY oder data truncated), dann schreiben wir mit replace und update überhaupt keine Daten in die Datenbank, wohingegen bei insert und truncate alle Daten bis zum Fehler tatsächlich in der Datenbank landen.

[wahani]

Folgendes haben wir hierzu besprochen: Derzeit sind Transaktionen nicht das zentrale Thema. Wie bereits oben geschrieben, wir nutzen überwiegend Bulk-Uploads mit load data local infile und ein gemeinsamer Commit der kompletten Daten ist nicht unbedingt notwendig.

Das zentrale Problem ist allerdings, dass wir unterschiedliches Verhalten bei Fehlern unserer sendData-Modes haben:

• insert
  • Bei falschen Datentypen bekommen wir ein Warning (load data local infile ignoriert den mysql_mode), es wird aber geschrieben.
  • Bei einer Primary Key Verletzung bekommen wir einen Fehler, allerdings werden die Zeilen bis zu dem Fehler auf der DB persistiert (das ist ein Feature von load data local infile)
• replace
  • Bei falschen Datentypen bekommen wir einen Fehler (load data local infile respektiert den mysql_mode, wenn mit der Option REPLACE gearbeitet wird - es gibt dazu einen inaktiven Bug-Report)
  • Pramary Key Verletzungen führen nicht zu einem Fehler. Wir würden auch keine Fehler bei Duplikaten in den Daten erkennen die wir hochladen, der letzte Wert gewinnt
• truncate
  • Verhält sich genau so wie insert, nur dass zuvor die Tabelle geleert wird und bei einem Fehler ein Rollback nicht möglich ist.
• update
  • Bei falschen Datentypen bekommen wir eine Warnung
  • Primary Key Verletzungen führen zu einem Fehler (wir benutzen die insert Methode), die Zieltabelle bleibt aber unverändert

Lösungen

• Konsistentes Verhalten in Bezug auf Datentypen bekommen wir dadurch, dass wir bei truncate und update einen replace, statt insert nutzen. Dann bleibt die insert Methode die einzige Abweichung.
• Einen Rollback für den truncate bekommen wir indem wir nicht mehr den truncate nutzen. Alternativ:
  • Erstelle neue Tabelle
  • Einfügen der Daten in neue Tabelle
  • Umbenennen der neuen Tabelle und droppen der alten
• Duplikate im Primary Key könnten bereits in R gefunden und zu einem Fehler führen