mysql: Frage zu now()

[Pit]

Hallo,

ich würde gerne eine Entwicklung in den Produktionsmodus übernehmen, möchte mir aber eine Art doppelten Boden einbauen.

Hierzu möchte ich alle sql-queries loggen, um dann ggf. bei einem aufgetretenen Fehler alle bis zum Stichpunkt angefallenen Queries in einer leeren db wieder einspielen zu können. Ich hatte zuerst überlegt, das über einen autom. erzeugten dump zu machen, das erscheint mir aber viel zu überlastet zu sein, daher die Idee mit dem Query-Log.

Klappt alles soweit ganz gut, aber mir ist ein problem aufgefallen.

Wannimmer ich in einer Query zum Einsetzen eines DateTime-Wertes now() benutze, würde ich im Falle des Wiedereinspielens einen (für mich) falschen (nämlich den zum Ausführungszeitpunkt gültigen wert) einsetzen.

Kann man das Problem angehen, ohne auf das now() in der Originalquery zu verzichten? ich frage, weil ich das relativ häufig benutzt habe...

Pit

[Henry]

Hallo Pit,

bin im Umzugsstress, daher nur kurz ohne groß zu überlegen, liegt bei dir ein Trigger auf der Hand. dies zu nutzen. Also immer wenn deine Querys ausgeführt werden, tue dies(wandle in vorheriges datum um) automatisch...

https://www.j-breuer.de/blog/programmieren-mysql-6-trigger/

Gruss
Henry

[Pit]

Hi Henry,

viel Spaß jund Erfolg beim umzug.

liegt bei dir ein Trigger auf der Hand. dies zu nutzen. Also immer wenn deine Querys ausgeführt werden, tue dies(wandle in vorheriges datum um) automatisch…

Danke für den tip und den Link.

Frage: Da ich das loggen eh über eine funktion mache: Ist ein Trigger weniger "lastig" als eine 2. Query, die dasselbe erledigt?

Pit

[TS]

Hello Pit,

liegt bei dir ein Trigger auf der Hand. dies zu nutzen. Also immer wenn deine Querys ausgeführt werden, tue dies(wandle in vorheriges datum um) automatisch… Danke für den tip und den Link.

Frage: Da ich das loggen eh über eine funktion mache: Ist ein Trigger weniger "lastig" als eine 2. Query, die dasselbe erledigt?

kannst Du bitte mal ein Funktionsdiagramm erstellen?

1. Auslöser
2. externe Geschäftsregeln
3. interne Geschäftsregeln (ante)
4. Datenbank veränderndes Statement
5. interne Geschäftsregeln (post)
6. Datenbank loggendes Statement
7. usw.

Wo liegt was in Zeitdigramm?
Sowie zwei Punkte auf zwei unterchiedlichen Zeitebenen liegen, müssen sie durch "Zwangsserialisierug und Kontrolle" (Locking, Transaktion, ...) gebunden werden.

Es ist also besser, in der API-Anprogrammierung (z. B. PHP) nur die externen Geschäftsregeln (Vorprüfung) und den Auslöser unterzubringen. In der DB-Logik wird dann das datenverändernde Statement nebst Logging geschlossen ausgelöst. Sollte hierbei etwas schiefgehen, bekommt die API-Schicht eine entsprechende Meldung und das DMS (Data modificateion statement) wird nicht ausgeführt, oder automatisch rückgängig gemacht.

Bau Dir also eine stored Routine, die die Veränderung das Logging des Statements, die Durchführung des Statements und nach Kontrolle (DB-Error-Status benutzen!) den Eintrag in den Logging-Datensatz, ob erfolgreich oder nicht erfolgreich.

Verhindere direkte Zugriffe auf die Tabellen. Baue Dir also Stored Routines für den Zugriff auf.

MySQL + MariaDB und auch fast alle anderen Rel. DBMS bieten im Rechtesystem die Möglichkeit, die Zugriffsrechte entsprechend einzustellen. Man lässt also den API-User nur noch auf die strored Routines zugreifen, aber nicht mehr direkt auf die Tabellen. Dem Stored-Routine-User gestattet man den Zugriff auf die Tabellen. Auf diesem Wege kann man auch Trigger auf (indirekte) Selects erzeugen, was auf direkte Selekts auf die Tabellen nicht geht.

Alternativ kann man für das DBMS das binary Logging aktivieren. Da würde der oben beschriebene Vorgang automatisiert als Binärlog durchgeführt werden, aber ohne Kontrollen und ohne Möglichkeit der freien Wahl des Aufsetzpunktes. Das Binärlog würde auch irgendwann aus den Fugen geraten.

Grundsätzlich benötigst Du erst einmal ein sauberes Konzept für die Datensicherung. Diese sollte unbedingt in regelmäßigen Abständen eine Vollsicherung berücksichtigen. Ab dieser Vollsicherung kann dann mit dem Log jeweils ein "Replay" durchgeführt werden, also wieder bis zu einem bestimmten Zeitpunkt in die Zukunft (ab Vollsicherung) durchgeführt werden.

Lass Dir aber gesagt sein, dass dein Anliegen schon etwas für extrem Fortgeschrittene ist, da es einen ganzen Rattenschwanz von vorhergehenden und nacheilenden Maßnhamen und die Betrachtung diverser Rahmenbedingungen zur Bedingung macht. Sowas baut man nicht mal eben nebenbei.

Liebe Grüße
Tom S.

[Pit]

Hi Tom,

Grundsätzlich benötigst Du erst einmal ein sauberes Konzept für die Datensicherung.

Das ist aber wieder eine andere Baustelle. (die bereits seit langem gelöst ist)

Ich wills doch gar nicht so overdressed machen, ich will doch nur einen Wiederherstellungspunkt zu jedem Zeitpunkt in der Historie haben und dahin zurück kommen.

Und dafür finde ich meinen Ansatz schon ganz gut.

Pi

[TS]

Hello,

Ich wills doch gar nicht so overdressed machen, ich will doch nur einen Wiederherstellungspunkt zu jedem Zeitpunkt in der Historie haben und dahin zurück kommen.

Es gibt kein Zurück!
Es gibt nur ein Voran aus einer zuletzt als "absolut gesicherten Position".

Deshalb gibt es auch bis heute kein Datensicherungskonzept ohne verbindlichen Aufsetzpunkt in der Vergangenheit.

Wenn Du es schaffen könntest, dass sich Daten rückwärts entwickeln lassen könnten, dann hättest Du dafür einen sehr hoch dotierten Preis verdient. Es gibt zwar interpolationsmodelle, die auch rückwärts arbeiten, aber auch die benötigen dafür Refereneznpunkte an der unteren Schranke.

Liebe Grüße
Tom S.

[Pit]

Ich wills doch gar nicht so overdressed machen, ich will doch nur einen Wiederherstellungspunkt zu jedem Zeitpunkt in der Historie haben und dahin zurück kommen.

Es gibt kein Zurück!
Es gibt nur ein Voran aus einer zuletzt als .

Wenn schon Korinthen, dann auch richtig: Ich möchte ein zurück (nenns von mir aus auch "vor") auf Null und von dortaus ein Voran bis zur "absolut gesicherten Position".

Pit

[Regina Schaukrug]

Wenn schon Korinthen, dann auch richtig:

Rezepte und Zutaten hast Du. Den Kuchen musst Du jetzt backen. Und wenn der Topf aber nun ein Loch hat - dann stopf es.

[Regina Schaukrug]

Hierzu möchte ich alle sql-queries loggen, um dann ggf. bei einem aufgetretenen Fehler alle bis zum Stichpunkt angefallenen Queries in einer leeren db wieder einspielen zu können.

Soso. Lesestoff.

Falls Du das immer noch oder gar zusätzlich manuell tun willst, dann stellt sich die Frage, warum Du nicht den Zeitpunkt mitloggst und jedes Vorkommen von now() einfach mittels eines Tools wie sed ersetzt.

um dann ggf. bei einem aufgetretenen Fehler alle bis zum Stichpunkt angefallenen Queries

Da wäre wohl noch was zu lesen. Mal von Transaktionen gehört? DIE sind dafür gedacht.

[Pit]

Hallo Regina,

Soso. Lesestoff.

Ja, den kenne ich schon. Kann ich aber nicht nutzen, da hier viel zuviel geloggt wird, was aus anderen Anwendungen kommt.

Falls Du das immer noch oder gar zusätzlich manuell tun willst, dann stellt sich die Frage, warum Du nicht den Zeitpunkt mitloggst und jedes Vorkommen von now() einfach mittels eines Tools wie sed ersetzt.

Ich logge den Zeitpunkt mit. Aber habe nicht verstanden, wie ich mittels sed now() ersetzen kann.

um dann ggf. bei einem aufgetretenen Fehler alle bis zum Stichpunkt angefallenen Queries

Da wäre wohl noch was zu lesen. Mal von Transaktionen gehört? DIE sind dafür gedacht.

Transaktionen habe ich etwas anders verstanden. Oder anders, sie gehen etwas an meiner Sorge vorbei. Ich will nämlich hier nicht normale sql-errors so abfangen, dass ich die db anschließend wieder herstellen kann, sondern mir geht es mehr um inhaltliche Fehler. Die fallen sql selber gar nicht auf. mir hingegen schon. Was natürlich dennoch sein kann, dass mir der fehler so spät auffällt, dass ich dennoch dem Fehler nachfolgenden korrekte Daten hierdurch verliere. Das habe ich (zu mienem Mißfallen natürlich) bereits einkalkuliert.

Pit

[Regina Schaukrug]

Aber habe nicht verstanden, wie ich mittels sed now() ersetzen kann.

Hm. Ist jetzt schwierig zu wissen, WAS Du nicht verstanden hast. sed ist ein stream-Editor. Durch den kann man Daten schicken und in diesen sehr performant mit Regulären Ausdrücken Ersetzungen vornehmen. Die muss man natürlich erst mal lernen. Aber schon mit Grundwissen kann man da erstaunliche Ergebnisse erzielen!

Zeilen aus dem Log sehen wohl etwa so aus:

2018-07-27 13:01:02.124569 INSERT INTO FOO (bar,tok,time) values (100,200,now())
2018-07-27 13:01:03.987459 INSERT INTO FOO (bar,tok,time) values (130,90,now())

In einer Shell geht das mit einer Zeile so:

Einzelne Zeile:

echo '2018-07-27 13:01:02.124569 INSERT INTO FOO (bar, tok, time) values (100,200,now());' | sed -r 's/^([0-9-]+ [0-9:.]+) (.*)(now\(\))(.*)$/\2"\1"\4/';

Ergebnis:

INSERT INTO FOO (bar, tok, time) values (100,200,"2018-07-27 13:01:02.124569");

Für alle Zeilen einer Datei:

#!/bin/bash
sed -i'.backup' -r -e 's/^([0-9-]+ [0-9:.]+) (.*)(now\(\))(.*)$/\2"\1"\4/' DEINE_DATEI.log

Das würde in der Datei 'DEINE_DATEI.log' alle Zeilen umbauen und die ursprüngliche Datei mit der Endung .backup sichern.

#!/bin/bash
sed -r -e 's/^([0-9-]+ [0-9:.]+) (.*)(now\(\))(.*)$/\2"\1"\4/' < DEINE_DATEI.log > Datei.sql

Das würde versuchen, die Daten aus dem Logfile "DEINE_DATEI.log" zu lesen und SQL-Zeilen in eine Datei Datei.sql zu schreiben.

Hinweis:

Enthält Dein Lpgfile Daten mit Zeilenumbrüchen, die nicht maskiert im Logfile landen, dann ist ggf. weiterer Zauber nötig.

[Pit]

Hi Regina,

Zeilen aus dem Log sehen wohl etwa so aus:

Ah, ok. Das Tool geht von einem Log aus.

Enthält Dein Lpgfile Daten mit Zeilenumbrüchen, die nicht maskiert im Logfile landen, dann ist ggf. weiterer Zauber nötig.

Tut es.

Danke für die Erklärung zu sed. Leider glaube ich, kann ichs nicht nutzen, da ich nicht über die Logdatei gehen möchte. 😟 Trotzdem gut zu wissen, dass es sowas gibt. Das kann mir in anderen Situationen durchaus nochmal nützlich sein, ich schlage öfter mal alte Posts von mir nach, weil ich weiß, dass mir einer einen Tip gegeben hatte, der seinerzeit zwar nicht optimal für mich passte , aber nun schon.

Pit

[Regina Schaukrug]

Leider glaube ich, kann ichs nicht nutzen, da ich nicht über die Logdatei gehen möchte.

Dann solltest Du mal damit rüberkommen, wie Du denn loggen willst... Aber vorher das hier lesen.

Damit löst sich Dein Problem vermutlich in Luft auf.

[Pit]

Ist etwas gegen folgende lösung einzuwenden?

$now = date('Y-m-d H:i:s');
$query = str_replace('now()',$now, $query);
$query_sql_insert="insert into sql_log (query) values (".behandleInput($query,'string').")";

Pit

[dedlfix]

Tach!

Ist etwas gegen folgende lösung einzuwenden?

Ja, sie erfordert Extraaufwand innerhalb der Anwendung. Und man darf nicht vergessen diese Logging-Statements auszuführen. Ich würde versuchen, solche adminitrativen Anforderungen aus der eigentlichen Anwendung rauszuhalten und die Möglichkeiten des DBMS dafür zu nutzen.

dedlfix.

[Pit]

Hi dedlfix,

Ja, sie erfordert Extraaufwand innerhalb der Anwendung. Und man darf nicht vergessen diese Logging-Statements auszuführen.

Insbesondere Punkt 2 ist hier nicht zu vernachlässigen, da geb ich Dir recht. Da hab ich auch die größte Sorge vor.

Ich würde versuchen, solche adminitrativen Anforderungen aus der eigentlichen Anwendung rauszuhalten und die Möglichkeiten des DBMS dafür zu nutzen.

Wenn ich die denn alle zur verfügung hätte... 😕

Pit

[dedlfix]

Tach!

Hierzu möchte ich alle sql-queries loggen, um dann ggf. bei einem aufgetretenen Fehler alle bis zum Stichpunkt angefallenen Queries in einer leeren db wieder einspielen zu können.

Dazu brauchst du nicht alle, sondern nur die datenverändernden Statements.

Ich hatte zuerst überlegt, das über einen autom. erzeugten dump zu machen, das erscheint mir aber viel zu überlastet zu sein, daher die Idee mit dem Query-Log.

Im Allgemeinen sind die Dumps recht fix. Bei Performance ist es eigentlich immer empfehlenswert, weniger zu raten und stattdessen mehr zu messen. Beobachte also besser mal das wirkliche Laufzeitverhalten bei realistischer Datenmenge.

Wenn du mit Query-Log das eingebaute von MySQL meinst ... das ist zu viel des Guten, weil es auch alle Leseoperationen mitschreibt.

Wannimmer ich in einer Query zum Einsetzen eines DateTime-Wertes now() benutze, würde ich im Falle des Wiedereinspielens einen (für mich) falschen (nämlich den zum Ausführungszeitpunkt gültigen wert) einsetzen.

Und das ist das nächste Problem. Alle nicht-deterministischen Funktionen (beispielsweise auch RAND()) liefern unter Umständen andere Ergebnisse beim nächsten Aufruf.

Kann man das Problem angehen, ohne auf das now() in der Originalquery zu verzichten?

Es gibt auch noch das Binary-Log. Das schreibt nur die Schreiboperationen mit. Aber auch das Binary-Log hat Probleme bei nicht-deterministischen Funktionen, aber nur, wenn man es im falschen Log-Format betreibt.

dedlfix.

[Pit]

Hi dedlfix,

mir stehen im Produktivmodus gar nicht alle schönen Dimge zur verfügung, die mysql von Hause aus mitbringt. mysql Server mit Optionen neu hochfahren gehört sicher dazu, insofern bin ich gezwungen, mir in solchen dingen selber zu helfen 😟

Danke trotzdem für den Tip.

Pit

[Regina Schaukrug]

Hierzu möchte ich alle sql-queries loggen, um dann ggf. bei einem aufgetretenen Fehler alle bis zum Stichpunkt angefallenen Queries in einer leeren db wieder einspielen zu können.

Das ist nicht ideal. Deinen Äußerungen entnehme ich, dass Du Einträge ignorieren willst.

Besserer Vorschlag:

Füge der Tabelle eine Spalte 'invalid' (Typ: bolean, default: false) hinzu. Lass die indexieren. Setze bei Deiner Prüfung invalid ggf. auf true.

Bei allen Abfragen ergänze die Where-Clausel: where ... and invalid=false

Du kannst dann nämlich mit einem einzigen SQL-Befehl das ignore auf true stellen und hast nicht den Horror mit dem Wiedereinlesen eines Dumps - der ja Stillstandszeiten verursacht.

Wenn das nicht geht, dann hast Du ein Design-Problem.

[Pit]

Hierzu möchte ich alle sql-queries loggen, um dann ggf. bei einem aufgetretenen Fehler alle bis zum Stichpunkt angefallenen Queries in einer leeren db wieder einspielen zu können.

Das ist nicht ideal. Deinen Äußerungen entnehme ich, dass Du Einträge ignorieren willst.

Jain… Ich habe ein funktionierendes System, einen Terminkalender. Nun möchte ich diesem einige zusätzliche Funktionen hinzufügen. Ich habe bei der Entwicklung gemerkt, dass ich mir hierbei relativ leicht Inkonsistenzen in meinem Datensystem einhandeln kann. Derzeit ist alles im Lot, aber wer weiß schon, ob es tatsächlich so ist. Daher meine Idee, in so einem Fall bis zum Punkt X das System jederzeit wiederherstellen zu können und somit tatsächlich ggf. ein paar einträge ignorieren zu müssen.

Besserer Vorschlag:

Füge der Tabelle eine Spalte 'invalid' (Typ: bolean, default: false) hinzu. Lass die indexieren. Setze bei Deiner Prüfung invalid ggf. auf true.

Welche Prüfung?

Bei allen Abfragen ergänze die Where-Clausel: where ... and invalid=false

Verstehe ich leider nicht. Wie gesagt, es geht um inhaltliche Fehler, nicht um sql-Fehler.

Pit

[Regina Schaukrug]

Besserer Vorschlag:

Füge der Tabelle eine Spalte 'invalid' (Typ: bolean, default: false) hinzu. Lass die indexieren. Setze bei Deiner Prüfung invalid ggf. auf true.

Welche Prüfung?

Na Deiner Prüfung auf "kaputte" Daten, also "inhaltliche Fehler".

Bei allen Abfragen ergänze die Where-Clausel: where ... and invalid=false

Verstehe ich leider nicht. Wie gesagt, es geht um inhaltliche Fehler, nicht um sql-Fehler.

Ja. Dann werden die markierten Datensätze (die mit inhaltlichen Fehlern) ignoriert und Du kannst diese untersuchen ggf. reparieren, ggf. löschen, ohne die Datenbank zu leeren und alles neu einzulesen.

[Pit]

Hi,

Welche Prüfung?

Na Deiner Prüfung auf "kaputte" Daten, also "inhaltliche Fehler".

Verstehe… Aber ich habe nicht vor, jeden einzelnen Datensatz zu prüfen, das müßte ich manuell machen und meine DB ist phantastisch maschinenlesbar, aber die Fehlerquote einer manuellen Prüfung dürfte hoch werden. Ich dachte eher daran, so eine art Wiederherstellungspunkt zu ermitteln. Also einen Zeitpunkt, zu dem ich wieder zurück möchte. Nachteil ist natürlich, dass hierbei Daten verloren gehen.

Ja. Dann werden die markierten Datensätze (die mit inhaltlichen Fehlern) ignoriert und Du kannst diese untersuchen ggf. reparieren, ggf. löschen, ohne die Datenbank zu leeren und alles neu einzulesen.

Wie gesagt, je besser die Daten vom DB handelbar sind, desto schwerer wirds für uns Menschen. Versuch z.b. mal ein großes Nested Set zu rekonstruieren, wenn der Wurm drin ist... ich trau es mir nicht zu 😉

Pit

[Regina Schaukrug]

Ich dachte eher daran, so eine art Wiederherstellungspunkt zu ermitteln.

Dann überleg mal, wozu eine DATENBANK wohl gut ist:

INSERT INTO tabelle (
                     foo, 
                     bar,
                     baz,
                     insert_date_time,
                     invalid
             ) 
             VALUES (
                     'Toller Termin',
                     'St. Nirgendwo',
                     'mit Pit',
                     NOW(),
                     FALSE
             )
;

UPDATE tabelle SET 
                      invalid=TRUE 
               WHERE
                      insert_date_time='2018-07-28 18:39:40'
;

Aber falls Du die Datenbank gar nicht benutzen willst: Sowas geht auch mit Textdateien:

Weitere Alternative: Schau mal nach, ob Dein Hoster sqlite oder sqlite3 unterstützt (phpinfo() hilft dabei). Da sind die Daten direkt in Dateien, die Du also direkt manipulieren - auch kopieren - kannst.

[TS]

Hello,

Weitere Alternative: Schau mal nach, ob Dein Hoster sqlite oder sqlite3 unterstützt (phpinfo() hilft dabei). Da sind die Daten direkt in Dateien, die Du also direkt manipulieren - auch kopieren - kannst.

Das ist auch immer noch ein Argument für das MyISAM-Format, auch, wenn Andere das nicht einsehen wollen.

Liebe Grüße
Tom S.

[dedlfix]

Tach!

Weitere Alternative: Schau mal nach, ob Dein Hoster sqlite oder sqlite3 unterstützt (phpinfo() hilft dabei). Da sind die Daten direkt in Dateien, die Du also direkt manipulieren - auch kopieren - kannst.

Das ist auch immer noch ein Argument für das MyISAM-Format, auch, wenn Andere das nicht einsehen wollen.

Wenn die Datenmengen so gering sind, dass diese Vorschläge erwägbare Alternativen wären, dann kann man auch Dumps ziehen, ohne Performance-Probleme zu befürchten.

dedlfix.