Datenbankhistorisierung mittels Triggern

Chriz · 10.11.2011, 17:08

Hi,

ich habe eine schnelle Lösung gebraucht um alle Änderungen meiner Datenbank in einer Tabelle mitzuloggen. Sprich wenn jemand etwas ändert, möchte ich mitbekommen wer das war, wann das war und was geändert wurde. Ist noch nicht ganz ausgereift, aber sieht soweit gut aus. Hier zwei PHP-Skripte, die die Trigger für alle Tabellen der Datenbank erzeugen (bzw. in ein File schreiben, Anlegen muss selbst übernommen werden).

PHP-Code:

  <?php
mysql_connect("localhost", "***", "***") or die(mysql_error());
mysql_select_db("agb") or die(mysql_error());

$excludeTables       = array("tag");
$excludeColumns      = array("id", "edited");
$excludeTableColumns = array();

$sql1     = "SHOW FULL TABLES";
$res1     = mysql_query($sql1) or die(mysql_error());
$triggers = array();
while ($row1 = mysql_fetch_assoc($res1)) {
    $trigger = null;
    $columns = array();
    $table   = array_shift($row1);
    $type    = array_shift($row1);
    if ($type === "VIEW") {
        continue;
    }
    if (in_array($table, $excludeTables)) {
        continue;
    }
    $sql2    = "SHOW COLUMNS FROM $table";
    $res2    = mysql_query($sql2) or die(mysql_error());
    while ($row2 = mysql_fetch_assoc($res2)) {
        $column = array_shift($row2);
        if (in_array($column, $excludeColumns)) {
            continue;
        }
        if (array_key_exists($table, $excludeTableColumns) && in_array($column, $excludeTableColumns[$table])) {
            continue;
        }
        $columns[] = $column;
    }
    if (empty($columns)) {
        continue;
    }
    $trigger .= <<<MYSQL_CODE

-- {$table}

CREATE TRIGGER `{$table}_after_update` AFTER UPDATE ON `{$table}`
    FOR EACH ROW BEGIN

MYSQL_CODE;
    foreach ($columns as $column) {
        $trigger .= <<<MYSQL_CODE
        IF COALESCE(OLD.{$column} != NEW.{$column}, 1) THEN
            INSERT INTO `history` VALUES (
                NULL,
                'update',
                '{$table}',
                OLD.id,
                '{$column}',
                OLD.{$column},
                NEW.{$column},
                @AGB_PROCESS_ID,
                @AGB_USER_ID,
                CURRENT_TIMESTAMP
            );
        END IF;

MYSQL_CODE;
    }
    $trigger .= <<<MYSQL_CODE

    END;

\$\$

CREATE TRIGGER `{$table}_before_delete` BEFORE DELETE ON `{$table}`
    FOR EACH ROW BEGIN

MYSQL_CODE;
    foreach ($columns as $column) {
        $trigger .= <<<MYSQL_CODE
            INSERT INTO `history` VALUES (
                NULL,
                'delete',
                '{$table}',
                OLD.id,
                '{$column}',
                OLD.{$column},
                NULL,
                @AGB_PROCESS_ID,
                @AGB_USER_ID,
                CURRENT_TIMESTAMP
            );

MYSQL_CODE;
    }
    $trigger .= <<<MYSQL_CODE

    END;

\$\$

CREATE TRIGGER `{$table}_after_insert` AFTER INSERT ON `{$table}`
    FOR EACH ROW BEGIN

MYSQL_CODE;
    foreach ($columns as $column) {
        $trigger .= <<<MYSQL_CODE
            INSERT INTO `history` VALUES (
                NULL,
                'insert',
                '{$table}',
                NEW.id,
                '{$column}',
                NULL,
                NEW.{$column},
                @AGB_PROCESS_ID,
                @AGB_USER_ID,
                CURRENT_TIMESTAMP
            );

MYSQL_CODE;
    }
    $trigger .= <<<MYSQL_CODE

    END;

MYSQL_CODE;
    $triggers[] = $trigger;
}

$triggers   = implode(PHP_EOL . '$$' . PHP_EOL, $triggers);
$triggers   = <<<MYSQL_CODE

DELIMITER \$\$

$triggers

\$\$

DELIMITER ;

SHOW TRIGGERS;

MYSQL_CODE;
$outputFile = dirname(__FILE__) . "/" . basename(__FILE__, ".php") . ".sql";
file_put_contents($outputFile, $triggers);

echo $triggers;

In der Anwendung setze ich jetzt per

Code:

SET @AGB_USER_ID = '$user_email';

z.B. den aktuellen Benutzer.

Jede Spaltenänderung landet dann hier:

Code:

CREATE  TABLE IF NOT EXISTS `agb`.`history` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
  `event` ENUM('insert','update','delete') NULL ,
  `entity` VARCHAR(255) NULL ,
  `entity_id` VARCHAR(255) NULL ,
  `entity_property` VARCHAR(255) NULL ,
  `value_before` TEXT NULL ,
  `value_after` TEXT NULL ,
  `process_id` CHAR(13) NULL ,
  `user` VARCHAR(255) NULL ,
  `created` TIMESTAMP NOT NULL ,
  PRIMARY KEY (`id`) )
ENGINE = InnoDB

Ausgabe:

Code:

id	event	entity	entity_id	entity_property	value_before	value_after	process_id	user	created
1	update	text	2021	inter_localized_label	NULL	 	4ebbf5a781820	admin@example.com	2011-11-10 17:02:47

usw.
entity ist dabei die Tabelle, entity_id der Primärschlüsselwert, entity_property die Spalte, value_before und value_after sollten selbsterklärend sein.
process_id generiere ich mit uniqid() und setze es wie den Userwert.

Um zum Testen die Trigger auch mal entfernen zu können gibts das hier:

PHP-Code:

  <?php
mysql_connect("localhost", "***", "***") or die(mysql_error());
mysql_select_db("agb") or die(mysql_error());

$sql1  = "SHOW TRIGGERS";
$res1  = mysql_query($sql1) or die(mysql_error());
$drops = array();
while ($row1 = mysql_fetch_assoc($res1)) {
    $trigger = array_shift($row1);
    $drops[] = <<<MYSQL_CODE
DROP TRIGGER IF EXISTS `{$trigger}`;

MYSQL_CODE;
}

$drops      = implode(PHP_EOL, $drops);
$outputFile = dirname(__FILE__) . "/" . basename(__FILE__, ".php") . ".sql";
file_put_contents($outputFile, $drops);

echo $drops;

Vielleicht kanns jemand gebrauchen ..

hts · 10.11.2011, 18:55

Das sieht ja richtig gut aus!
Wenn ich daran denke, wie umständlich ich es innerhalb der Anwendung selbst gelöst habe, scheinen mir Trigger die beste Wahl zu sein.

Wenn du die Daten im Backend (MySQl-Query-Browser o.ä.) änderst, wird die History auch geloggt (mit User-ID NULL), oder?

Einziger Nachteil, den ich erkennen kann, ist, dass bei DB-Strukturänderungen ein neues SQL-Script generiert und ausgeführt werden muss. Dazu wäre es dann ganz gut, die "DROP TRIGGER IF EXISTS"-Anweisungen gleich mit im Create-Script zu integrieren.

Chriz · 10.11.2011, 19:26

Klar, die Wartung halte ich auch für etwas schwierig, eigentlich mag ich Prozeduren und Trigger nicht, aber die Alternative war eben pro UPDATE/DELETE/INSERT selbst nochmal welche abzusenden. Und selbst da hatte ich nicht mal mehr alle benötigten Infos. OLD und NEW sind da schon recht hilfreich. Beim Änderungscheck einer Spalte habe ich noch eine Erweiterung eingefügt:
COALESCE(OLD.value != NEW.value, NOT(OLD.value IS NULL AND NEW.value IS NULL))
Jetzt werden NULL-Wertänderungen auch wirklich korrekt erkannt. Mit dem DROP IF EXISTS geb ich dir Recht, allerdings hats in einem Skript zwischenzeitlich nicht geklappt, weiß der Geier obs am DELIMITER lag, da hatte ich es kurzerhand ausgelagert.

Performance ist auch deutlich höher als bei manuellem Logging.

Zusätzlich hab ich noch ne operation_id für jede Trigger-Aktion hinzugefügt (auch wieder mit SET @OPERATION_ID = MD5(UUID())), damit die Änderung von zwei Spaltenwerten eines Datensatzes auch später als eine Aktion erkannt werden. Greade der INSERT und DELETE Trigger erzeugt ja mehrere Datensätze, die man in der Ausgabe später zusammen anzeigen möchte.

Hab demnach folgende Identifier:
session_id
request_id
operation_id
user_id

Später kann man dann sehen wer in welcher Sitzung was genau geändert hat. Gut wäre es, wenn man die Daten dann noch aggregieren würde, andernfalls läuft die DB evtl. zu. Muss ich mal schauen wie das im Livebetrieb ist.

Zitat:

Wenn du die Daten im Backend (MySQl-Query-Browser o.ä.) änderst, wird die History auch geloggt (mit User-ID NULL), oder?

Genau, im Prinzip könnte man sicherlich auch hier wieder ein COALESCE(@USER_ID, 'system') einfügen, aber NULL ist mir aussagekräftig genug. Ist halt irgendwie etwas unsauber Variablen zu benutzen, dies ggf. nicht gibt, aber eine andere Lösung, wie man den User erkennt ist mir nicht eingefallen mit Triggern.

Zitat:

Einziger Nachteil, den ich erkennen kann, ist, dass bei DB-Strukturänderungen ein neues SQL-Script generiert und ausgeführt werden muss.

Ja - und vor allem muss man kommunizieren, dass man Trigger einsetzt und eine Anleitung geben, wie man sie ändert, erweitert oder neu setzt. Werd dazu in meinem Projekt noch - neben den Skripten oben - ne README.txt anlegen.

Edit: Beim AFTER INSERT und BEFORE DELETE-Trigger kann ich die INSERTs für die Spalten der Tabelle noch mal in ein INSERT zusammenfassen, könnte auch nochmal was an Performance rausholen.

hts · 10.11.2011, 20:37

Zitat:

Zitat von Chriz

... aber die Alternative war eben pro UPDATE/DELETE/INSERT selbst nochmal welche abzusenden. Und selbst da hatte ich nicht mal mehr alle benötigten Infos. OLD und NEW sind da schon recht hilfreich.

Ja, so habe ich es momentan. Beim Update schreibe ich die Werte in die Tabelle und lese sie anschließend wieder aus, um die geänderten Felder festzustellen und dann ggfs. die Inserts in die History-Tabelle durchzuführen.
Klingt umständlich - ist es auch

Hatte auch den Grund gehabt, dass wenn MySQL Daten abschneidet/rundet, bekommt man tatsächliche Änderungen nur mit, wenn man die Daten wieder ausliest, selbst wenn man die Originaldaten vorliegen hat.

Aber die Trigger haben für mich auch den Vorteil, dass eben auch Änderungen direkt am Backend gespeichert werden. Manchmal muss ich nämlich Daten direkt ändern, wenn Benutzer irgendwas verbockt haben

- und das ist dann eben nicht Protokolliert (manuell ändere ich nicht auch noch die History ab).

Also danke für deine Anregung, werde es für ein Rewrite oder Redesign im Hinterkopf behalten!

EDIT
Achso, weil du gerade beim Optimieren bist.
Du brauchst beim Loggen nicht immer den alten und neuen Wert zu speichern, einer ergibt sich aus den aktuellen Daten bzw. aus den letzten der History. Lässt sich so zwar besser handeln, aber bläht die History auch mächtig auf.

Chriz · 10.11.2011, 21:23

Da ist was dran. War nur nicht sicher, ob ich aus den Daten wirklich einen Änderungsstream herausbekomme.

Chriz · 17.11.2011, 13:32

Ich habe übrigens festgestellt, dass ein thread_stack von 128K nicht reicht. 196K reicht bei meinen Tabellen dann aber aus. Je nach Anzahl und Art der Tabellenspalten kann sich das aber noch erhöhen.

Themen-Optionen
Druckbare Version zeigen Jemanden per E-Mail auf dieses Thema hinweisen
Thema bewerten
Thema bewerten:

Ähnliche Themen
Thema	Autor	Forum	Antworten	Letzter Beitrag
Upload von Bildern mit überschrift und Kommentar mittels PHP	Ginger Ale	PHP Einsteiger	8	11.09.2011 17:08
[Erledigt] Abfrage mittels date_format	noop	Datenbanken	8	05.08.2011 15:21
[Erledigt] Active User Logging (mittels XMLHttpRequest angestoßen)	Matthias N.	Software-Design	5	01.11.2010 10:30
[Erledigt] Bilderlink mittels php und MYSQL verschleiern? Wie?	fabil	PHP Tipps 2010	5	12.07.2010 21:00
JQuery Upload mittels Array/hiddenfields	Das_Pfau	JavaScript, Ajax und mehr	7	13.03.2010 11:03
Bearbeitung einer XML Datei mittels PHP	kayyy	PHP Tipps 2009	3	15.12.2009 15:02
UPDATE/INPUT mehrerer Datensätze mittels Array/Schleife?	dieter_danger	PHP Tipps 2009	6	12.07.2009 13:38
Variable mittels hidden-Form übergeben	OoNanaoO	PHP Tipps 2009	5	03.06.2009 14:31
Wireshark SSL Handshake debugging-Handshake protokoll mittels PHP + Curl	brian johnson	Off-Topic Diskussionen	0	06.11.2008 11:54
Übergabe von eingegebenem String mittels Formular und POST?!	vampsoftchef	PHP Tipps 2006	7	18.10.2006 20:06
mysql mittels php abfragen- array problem		PHP Tipps 2005-2	6	20.07.2005 15:21
[Erledigt] Dateiupload mittels POST		PHP-Fortgeschrittene	5	04.02.2005 07:07
[Erledigt] Neuen Benutzer mittels PHP in MYSQL Anlegen		PHP-Fortgeschrittene	1	28.10.2004 17:22
[Erledigt] Baumstrukturjavascript mittels PHP generieren		HTML, Usability und Barrierefreiheit	4	27.08.2004 11:56

10.11.2011, 17:08
Chriz Moderator Registriert seit: 11.05.2008 Beiträge: 6.269	Datenbankhistorisierung mittels Triggern Hi, ich habe eine schnelle Lösung gebraucht um alle Änderungen meiner Datenbank in einer Tabelle mitzuloggen. Sprich wenn jemand etwas ändert, möchte ich mitbekommen wer das war, wann das war und was geändert wurde. Ist noch nicht ganz ausgereift, aber sieht soweit gut aus. Hier zwei PHP-Skripte, die die Trigger für alle Tabellen der Datenbank erzeugen (bzw. in ein File schreiben, Anlegen muss selbst übernommen werden). PHP-Code: <?php mysql_connect("localhost", "*", "") or die(mysql_error()); mysql_select_db("agb") or die(mysql_error()); $excludeTables = array("tag"); $excludeColumns = array("id", "edited"); $excludeTableColumns = array(); $sql1 = "SHOW FULL TABLES"; $res1 = mysql_query($sql1) or die(mysql_error()); $triggers = array(); while ($row1 = mysql_fetch_assoc($res1)) { $trigger = null; $columns = array(); $table = array_shift($row1); $type = array_shift($row1); if ($type === "VIEW") { continue; } if (in_array($table, $excludeTables)) { continue; } $sql2 = "SHOW COLUMNS FROM $table"; $res2 = mysql_query($sql2) or die(mysql_error()); while ($row2 = mysql_fetch_assoc($res2)) { $column = array_shift($row2); if (in_array($column, $excludeColumns)) { continue; } if (array_key_exists($table, $excludeTableColumns) && in_array($column, $excludeTableColumns[$table])) { continue; } $columns[] = $column; } if (empty($columns)) { continue; } $trigger .= <<<MYSQL_CODE -- {$table} CREATE TRIGGER `{$table}_after_update` AFTER UPDATE ON `{$table}` FOR EACH ROW BEGIN MYSQL_CODE; foreach ($columns as $column) { $trigger .= <<<MYSQL_CODE IF COALESCE(OLD.{$column} != NEW.{$column}, 1) THEN INSERT INTO `history` VALUES ( NULL, 'update', '{$table}', OLD.id, '{$column}', OLD.{$column}, NEW.{$column}, @AGB_PROCESS_ID, @AGB_USER_ID, CURRENT_TIMESTAMP ); END IF; MYSQL_CODE; } $trigger .= <<<MYSQL_CODE END; \$\$ CREATE TRIGGER `{$table}_before_delete` BEFORE DELETE ON `{$table}` FOR EACH ROW BEGIN MYSQL_CODE; foreach ($columns as $column) { $trigger .= <<<MYSQL_CODE INSERT INTO `history` VALUES ( NULL, 'delete', '{$table}', OLD.id, '{$column}', OLD.{$column}, NULL, @AGB_PROCESS_ID, @AGB_USER_ID, CURRENT_TIMESTAMP ); MYSQL_CODE; } $trigger .= <<<MYSQL_CODE END; \$\$ CREATE TRIGGER `{$table}_after_insert` AFTER INSERT ON `{$table}` FOR EACH ROW BEGIN MYSQL_CODE; foreach ($columns as $column) { $trigger .= <<<MYSQL_CODE INSERT INTO `history` VALUES ( NULL, 'insert', '{$table}', NEW.id, '{$column}', NULL, NEW.{$column}, @AGB_PROCESS_ID, @AGB_USER_ID, CURRENT_TIMESTAMP ); MYSQL_CODE; } $trigger .= <<<MYSQL_CODE END; MYSQL_CODE; $triggers[] = $trigger; } $triggers = implode(PHP_EOL . '$$' . PHP_EOL, $triggers); $triggers = <<<MYSQL_CODE DELIMITER \$\$ $triggers \$\$ DELIMITER ; SHOW TRIGGERS; MYSQL_CODE; $outputFile = dirname(__FILE__) . "/" . basename(__FILE__, ".php") . ".sql"; file_put_contents($outputFile, $triggers); echo $triggers; In der Anwendung setze ich jetzt per Code: SET @AGB_USER_ID = '$user_email'; z.B. den aktuellen Benutzer. Jede Spaltenänderung landet dann hier: Code: CREATE TABLE IF NOT EXISTS `agb`.`history` ( `id` INT UNSIGNED NOT NULL AUTO_INCREMENT , `event` ENUM('insert','update','delete') NULL , `entity` VARCHAR(255) NULL , `entity_id` VARCHAR(255) NULL , `entity_property` VARCHAR(255) NULL , `value_before` TEXT NULL , `value_after` TEXT NULL , `process_id` CHAR(13) NULL , `user` VARCHAR(255) NULL , `created` TIMESTAMP NOT NULL , PRIMARY KEY (`id`) ) ENGINE = InnoDB Ausgabe: Code: id event entity entity_id entity_property value_before value_after process_id user created 1 update text 2021 inter_localized_label NULL 4ebbf5a781820 admin@example.com 2011-11-10 17:02:47 usw. entity ist dabei die Tabelle, entity_id der Primärschlüsselwert, entity_property die Spalte, value_before und value_after sollten selbsterklärend sein. process_id generiere ich mit uniqid() und setze es wie den Userwert. Um zum Testen die Trigger auch mal entfernen zu können gibts das hier: PHP-Code: <?php mysql_connect("localhost", "", "*") or die(mysql_error()); mysql_select_db("agb") or die(mysql_error()); $sql1 = "SHOW TRIGGERS"; $res1 = mysql_query($sql1) or die(mysql_error()); $drops = array(); while ($row1 = mysql_fetch_assoc($res1)) { $trigger = array_shift($row1); $drops[] = <<<MYSQL_CODE DROP TRIGGER IF EXISTS `{$trigger}`; MYSQL_CODE; } $drops = implode(PHP_EOL, $drops); $outputFile = dirname(__FILE__) . "/" . basename(__FILE__, ".php") . ".sql"; file_put_contents($outputFile, $drops); echo $drops; Vielleicht kanns jemand gebrauchen .. __________________ "Nuschel ich?" - "Was?"


Mister Ad PHP Code Flüsterer Registriert seit: 21.08.2005 Beiträge: 4682 PHP-Kenntnisse: Fortgeschritten

10.11.2011, 18:55
hts Erfahrener Benutzer Registriert seit: 07.09.2010 Beiträge: 724 PHP-Kenntnisse: Fortgeschritten	Das sieht ja richtig gut aus! Wenn ich daran denke, wie umständlich ich es innerhalb der Anwendung selbst gelöst habe, scheinen mir Trigger die beste Wahl zu sein. Wenn du die Daten im Backend (MySQl-Query-Browser o.ä.) änderst, wird die History auch geloggt (mit User-ID NULL), oder? Einziger Nachteil, den ich erkennen kann, ist, dass bei DB-Strukturänderungen ein neues SQL-Script generiert und ausgeführt werden muss. Dazu wäre es dann ganz gut, die "DROP TRIGGER IF EXISTS"-Anweisungen gleich mit im Create-Script zu integrieren.

10.11.2011, 21:23
Chriz Moderator Registriert seit: 11.05.2008 Beiträge: 6.269	Da ist was dran. War nur nicht sicher, ob ich aus den Daten wirklich einen Änderungsstream herausbekomme. __________________ "Nuschel ich?" - "Was?"

17.11.2011, 13:32
Chriz Moderator Registriert seit: 11.05.2008 Beiträge: 6.269	Ich habe übrigens festgestellt, dass ein thread_stack von 128K nicht reicht. 196K reicht bei meinen Tabellen dann aber aus. Je nach Anzahl und Art der Tabellenspalten kann sich das aber noch erhöhen. __________________ "Nuschel ich?" - "Was?"