• Hallo liebe Userinnen und User,

    nach bereits längeren Planungen und Vorbereitungen sind wir nun von vBulletin auf Xenforo umgestiegen. Die Umstellung musste leider aufgrund der Serverprobleme der letzten Tage notgedrungen vorverlegt werden. Das neue Forum ist soweit voll funktionsfähig, allerdings sind noch nicht alle der gewohnten Funktionen vorhanden. Nach Möglichkeit werden wir sie in den nächsten Wochen nachrüsten. Dafür sollte es nun einige der Probleme lösen, die wir in den letzten Tagen, Wochen und Monaten hatten. Auch der Server ist nun potenter als bei unserem alten Hoster, wodurch wir nun langfristig den Tank mit Bytes vollgetankt haben.

    Anfangs mag die neue Boardsoftware etwas ungewohnt sein, aber man findet sich recht schnell ein. Wir wissen, dass ihr alle Gewohnheitstiere seid, aber gebt dem neuen Board eine Chance.
    Sollte etwas der neuen oder auch gewohnten Funktionen unklar sein, könnt ihr den "Wo issn da der Button zu"-Thread im Feedback nutzen. Bugs meldet ihr bitte im Bugtracker, es wird sicher welche geben die uns noch nicht aufgefallen sind. Ich werde das dann versuchen, halbwegs im Startbeitrag übersichtlich zu halten, was an Arbeit noch aussteht.

    Neu ist, dass die Boardsoftware deutlich besser für Mobiltelefone und diverse Endgeräte geeignet ist und nun auch im mobilen Style alle Funktionen verfügbar sind. Am Desktop findet ihr oben rechts sowohl den Umschalter zwischen hellem und dunklem Style. Am Handy ist der Hell-/Dunkelschalter am Ende der Seite. Damit sollte zukünftig jeder sein Board so konfigurieren können, wie es ihm am liebsten ist.


    Die restlichen Funktionen sollten eigentlich soweit wie gewohnt funktionieren. Einfach mal ein wenig damit spielen oder bei Unklarheiten im Thread nachfragen. Viel Spaß im ngb 2.0.

[MYSQL] Updateverlauf anlegen

Roin

Freier Denker

Registriert
22 Juli 2013
Beiträge
581
Hallo Leute,

ich möchte für mein aktuelles Projekt eine Art Updateverlauf anlegen.
Immer, wenn ich eine Zeile in einer Tabelle aktualisiere (sprich UPDATE), möchte ich die vorherigen Werte in einer Tabelle abspeichern, inklusive des Zeitpunktes des Updatebefehls.

Meine beiden Tabellen, die dafür herhalten sollen baue ich derzeit wie folgt auf:
[src=mysql]CREATE TABLE IF NOT EXISTS Sicherung (
ID BIGINT NOT NULL AUTO_INCREMENT,
Datum DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
mTabelle VARCHAR(64) NOT NULL,
mID INTEGER NOT NULL,
PRIMARY KEY(ID))

CREATE TABLE IF NOT EXISTS Sicherungsspalten (
ID BIGINT NOT NULL AUTO_INCREMENT,
VorgangsID BIGINT NOT NULL,
Spaltenname VARCHAR(64) NOT NULL,
Wert VARCHAR(300),
PRIMARY KEY(ID),
UNIQUE(VorgangsID, Spaltenname))[/src]

Ich möchte allerdings die Daten, die ich in meiner zweiten Tabelle speichere etwas verringern. Daher habe ich mir gedacht, nur Daten aus der Tabelle, die geupdated wird, auszulesen, die wirklich verändert werden. Ein Problem habe ich dabei aber, welches ich derzeit nicht lösen kann:
Ich möchte das Skript für x Tabellen nutzen können. Diese haben unterschiedlich viele Spalten mit unterschiedlichen Namen.
Ich habe mir gedacht, ich baue einen SELECT wie:
[src=mysql]/*pseudo*/SELECT IF(COl1 <> NeuerWert, COL1, NULL) FROM table WHERE ID=Datensatzid[/src]
Jetzt frage ich mich allerdings, wie ich durch die Spalten sozusagen iterieren kann.

Noch als Hinweis: Ich arbeite hauptsächlich mit Prepared Statements. Ich denke das dürfte die Sache etwas einfacher machen - dennoch recht aufwändig.

Wie bewerkstellige ich das am besten?

Danke bereits im Voraus!
 
Zuletzt bearbeitet:

Rakorium-M

NGBler

Registriert
14 Juli 2013
Beiträge
413
Wenn du die gesicherten Daten nicht direkt aus deinem Programm heraus abfragen willst, kannst du einfach MySQLs "Binary Log"-Feature nutzen. Dabei werden alle Statements, die Daten verändern können geloggt. Du kannst also jederzeit nachvollziehen, wann welche Änderungen durchgeführt werden, und (zusammen mit einem initialen Backup) jederzeit die Datenbank auf einen bestimmten Zustand "zurückspielen".
https://dev.mysql.com/doc/refman/5.7/en/binary-log.html
 

Roin

Freier Denker

Registriert
22 Juli 2013
Beiträge
581
  • Thread Starter Thread Starter
  • #3
@Rakorium-M: Das ist schon fast sowas wie ich suche. Allerdings möchte ich in meiner Anwendung beispielsweise Anzeigen lassen, wann User1 eine neue Handynummer bekommen hat und was seine vorherige Nummer war. Da denke ich, sind die eigenen Tabellen für einfacher zu verwenden. Ich habe nur Probleme mit der richtigen Abfrage der Daten, ohne alle Daten auslesen zu müssen und je nach Tabelle die Spaltennamen hardcoded in meinen Code zu schreiben.
 

braegler

Aktiver NGBler

Registriert
14 Juli 2013
Beiträge
898
Dafür bieten sich doch Trigger geradezu an.
Alles schön Mysql-Server-seitig und Du musst Dich in Deiner Applikation nicht drum kümmern.

Auf die Schnelle mal ein Fundstück:
Quelle: https://www.linkedin.com/pulse/using-triggers-keep-track-mysql-table-changes-ricardo-olsen
There are many ways to log changes in MySQL tables:

MySQL server query log files;
Dumps combined with versioning;
Log created by the application;
Database diff tools;
Table triggers.
All of them are very useful but have different characteristics and drawbacks.

Server query log files is by far the easiest method, just enable it in the configuration file. The problem with this method is that it logs all queries to all tables of the database, so if your MySQL server is somewhat loaded, the performance can be severely affected.

I’ve found that triggers can enable the finest grain of control on table changes. Triggers allows to record only the changes you need to see, by whom and when the modifications were made.

Applying this technique can be moderately tricky, so I’m sharing my experience with it here.

I’ve created a log table like this:

CREATE TABLE logtable ( serialnum INTEGER PRIMARY KEY NOT NULL AUTO_INCREMENT, fwd_sql VARCHAR(512) DEFAULT "", backwd_sql VARCHAR(512) DEFAULT "", modtable VARCHAR(64) NOT NULL, moduser VARCHAR(64) DEFAULT "", ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP );

The fwd_sql field records the modifier query and the backwd_sql keeps an undo query statement. The modtable field keeps the modified table name. The fields serialnum and ts are automatically created for each record.

An example logged table is:

CREATE TABLE loggedtable ( primaryfield INTEGER PRIMARY KEY NOT NULL, intfield1 INTEGER NOT NULL, charfield2 VARCHAR(512) DEFAULT "", charfield3 VARCHAR(512) DEFAULT "" );

It’s necessary 3 triggers for each table that needs logging (exemplified as loggedtable), for inserts, deletes and updates, like this:

DROP TRIGGER IF EXISTS loggedtable_after_insert;
DELIMITER $$ CREATE TRIGGER loggedtable_after_insert -- trigger name
AFTER INSERT ON loggedtable -- table being triggered after insert queries
FOR EACH ROW BEGIN INSERT INTO logtable -- table that records the changes
( fwd_sql, backwd_sql, modtable, moduser )
VALUES ( CONCAT( "INSERT INTO loggedtable (primaryfield, intfield1, charfield2, charfield3 ) VALUES (", CAST( NEW.primaryfield AS CHAR ), ",", CAST( NEW.intfield1 AS CHAR ), ",", "'", NEW.charfield2, "'", ",", "'", NEW.charfield3, "'", ")" ), -- modifying operation
CONCAT( "DELETE FROM loggedtable WHERE primaryfield=", CAST( NEW.primaryfield AS CHAR ) ), -- undo operation
"loggedtable", -- table affected user() -- modifier user );
END $$
DELIMITER ;

DROP TRIGGER IF EXISTS loggedtable_after_delete;
DELIMITER $$
CREATE TRIGGER loggedtable_after_delete
AFTER DELETE ON loggedtable
FOR EACH ROW BEGIN INSERT INTO logtable
( fwd_sql, backwd_sql, modtable, moduser )
VALUES ( CONCAT( "DELETE FROM loggedtable WHERE primaryfield=", CAST( OLD.primaryfield AS CHAR ) ), CONCAT( "INSERT INTO loggedtable (primaryfield, intfield1, charfield2, charfield3) VALUES (", CAST( OLD.primaryfield AS CHAR ), ",", CAST( OLD.intfield1 AS CHAR ), ",", "'", OLD.charfield2, "'", ",", "'", OLD.charfield3, "'", ")" ), "loggedtable", user() );
END $$
DELIMITER ;

DROP TRIGGER IF EXISTS loggedtable_after_update;
DELIMITER $$
CREATE TRIGGER loggedtable_after_update AFTER UPDATE ON loggedtable
FOR EACH ROW BEGIN INSERT INTO logtable ( fwd_sql, backwd_sql, modtable, moduser )
VALUES ( CONCAT( "UPDATE loggedtable SET ", if( NEW.intfield1 = OLD.intfield1, "", CONCAT( "intfield1=", CAST( NEW.intfield1 AS CHAR ), "," ) ), if( NEW.charfield2 = OLD.charfield2, "", CONCAT( "charfield2=", "'", CAST( NEW.charfield2 AS CHAR ), "'," ) ), if( NEW.charfield3 = OLD.charfield3, "", CONCAT( "charfield3=", "'", CAST( NEW.charfield3 AS CHAR ), "'," ) ), CONCAT( "primaryfield=", CAST( NEW.primaryfield AS CHAR ) ), " WHERE ", "primaryfield=", CAST( NEW.primaryfield AS CHAR ) ), CONCAT( "UPDATE loggedtable SET ", if( NEW.intfield1 = OLD.intfield1, "", CONCAT( "intfield1=", CAST( OLD.intfield1 AS CHAR ), "," ) ), if( NEW.charfield2 = OLD.charfield2, "", CONCAT( "charfield2=", "'", CAST( OLD.charfield2 AS CHAR ), "'," ) ), if( NEW.charfield3 = OLD.charfield3, "", CONCAT( "charfield3=", "'", CAST( OLD.charfield3 AS CHAR ), "'," ) ), CONCAT( "primaryfield=", CAST( OLD.primaryfield AS CHAR ) ), " WHERE ", "primaryfield=", CAST( OLD.primaryfield AS CHAR ) ), "loggedtable", user() );
END $$
DELIMITER ;

The NEW and OLD are special objects inside triggers containing the new and old state of the row that is being modified. The function user() retrieves the user name, in the form “user@host“.

If record fields of the logged table can contain NULL, a COALESCE operation must be applied like COALESCE(OLD.charfield2, ""), or the log record may not be inserted as desired.

In MySQL, the CREATE TRIGGER command requires special rights (SUPER privilege) to be executed.

In other databases servers systems like PostgreSQL and Oracle, things should be accomplished in a similar way. Even SQLite does have triggers.
 

Roin

Freier Denker

Registriert
22 Juli 2013
Beiträge
581
  • Thread Starter Thread Starter
  • #5
Dafür bieten sich doch Trigger geradezu an.
Da werde ich wohl oder üben noch ein wenig mehr lesen müssen. Klingt etwa so, wie ich das haben möchte. Zwar müsste ich da noch etwas umschreiben, damit ich die Felder dann so auslesen kann, wie ich das möchte, aber klingt erstmal "Machbar".

Ich habe wohl einen etwas umständlichen Weg genommen und habe mir zwei Funktionen geschrieben, die mir über PDO meine beiden Tabellen bestücken, wie ich es gerne möchte.

Die Funktionen sehen wie folgt aus:
[src=php]function backupRow($tablename, $rowID, $newValues) {
global $pdo;

//Debugausgabe
var_dump($newValues);

try {
//Wenn etwas fehlschlägt soll es rückgängig gemacht werden.
//Damit ich mehrere Transactionen ineinander starten kann, habe ich eine
//erweiterte Klasse aus dem Internet verwendet
$pdo->beginTransaction();

// Nur Felder auslesen, die wirklich verändert werden.
$cols = array();
foreach($newValues as $key => $val) {
$cols[] = "IF(".$key." != :".$key.", ".$key.", NULL) AS ".$key;
}

$sql = "SELECT ".join(',',$cols)." FROM ".$tablename." WHERE ID=:id";

if($stmt = $pdo->prepare($sql)) {
//Debugausgabe
print $stmt->queryString."<br />";
// print "Erste vorbereiten ok.<br />";

// Parameter binden
$stmt->bindParam(':id', $rowID, PDO::PARAM_INT);
foreach($newValues as $key => $val) {
$stmt->bindParam(':'.$key, $val, PDO::PARAM_STR);
}

//Ausführen
$stmt->execute();
if($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
$stmt->closeCursor();

$sql = "INSERT INTO ".MYSQL_PREFIX."Sicherung (mTabelle, mID) VALUES (:table, :id)";
if($stmt = $pdo->prepare($sql)) {
$stmt->bindParam(':table', $tablename, PDO::PARAM_STR);
$stmt->bindParam(':id', $rowID, PDO::PARAM_INT);

$stmt->execute();

$vID = $pdo->lastInsertId();
$stmt->closeCursor();

if($stmt = $pdo->prepare("INSERT INTO ".MYSQL_PREFIX."Sicherungsspalten (VorgangsID, Spaltenname, Wert) VALUES (:vID, :colName, :value)")) {
$stmt->bindParam(':vID', $vID, PDO::PARAM_INT);
var_dump($row);
foreach($row as $key => $val) {
if($val != NULL) {
$stmt->bindParam(':colName', $key, PDO::PARAM_STR);
$stmt->bindParam(':value', $val, PDO::PARAM_STR);

$stmt->execute();
}
}
$stmt->closeCursor();

$pdo->commit();
return TRUE;
}
}
}
}
} catch (Exception $exc) {
$pdo->rollback();
echo $exc->getTraceAsString();
return FALSE;
}
//Debugausgabe
print "Doof gelaufen2";
return FALSE;
}

function updateRow($tablename, $rowID, $newValues) {
global $pdo;

try {
$pdo->beginTransaction();

if(backupRow($tablename, $rowID, $newValues)) {

$cols = array();
foreach($newValues as $key => $val) {
$cols[] = $key." = :".$key;
}

$sql = "UPDATE ".$tablename." SET ".join(', ',$cols)." WHERE ID=:id";
if($stmt = $pdo->prepare($sql)) {
// print "<br />".$stmt->queryString."<br />";
$stmt->bindParam(':id', $rowID, PDO::PARAM_INT);
foreach($newValues as $key => $val) {
//BindValue bindet den eigentlichen Wert und nicht die
//Variable als Referenz
$stmt->bindValue(':'.$key, $val, PDO::PARAM_STR);
}
$stmt->execute();
$stmt->closeCursor();
$pdo->commit();
return TRUE;
}
}
} catch (Exception $exc) {
$pdo->rollback();
echo $exc->getTraceAsString();
return FALSE;
}
print "Doof gelaufen1";
return FALSE;
}[/src]
Die erweiterte PDO-Klasse ist die folgende:
[src=php]/**
* This class extends native PDO one but allow nested transactions
* by using the SQL statements `SAVEPOINT', 'RELEASE SAVEPOINT' AND 'ROLLBACK SAVEPOINT'
*/
class ExtendedPdo extends PDO
{

/**
* @var array Database drivers that support SAVEPOINT * statements.
*/
protected static $_supportedDrivers = array("pgsql", "mysql");

/**
* @var int the current transaction depth
*/
protected $_transactionDepth = 0;


/**
* Test if database driver support savepoints
*
* @return bool
*/
protected function hasSavepoint()
{
return in_array($this->getAttribute(PDO::ATTR_DRIVER_NAME),
self::$_supportedDrivers);
}


/**
* Start transaction
*
* @return bool|void
*/
public function beginTransaction()
{
if($this->_transactionDepth == 0 || !$this->hasSavepoint()) {
parent::beginTransaction();
} else {
$this->exec("SAVEPOINT LEVEL{$this->_transactionDepth}");
}

$this->_transactionDepth++;
}

/**
* Commit current transaction
*
* @return bool|void
*/
public function commit()
{
$this->_transactionDepth--;

if($this->_transactionDepth == 0 || !$this->hasSavepoint()) {
parent::commit();
} else {
$this->exec("RELEASE SAVEPOINT LEVEL{$this->_transactionDepth}");
}
}

/**
* Rollback current transaction,
*
* @throws PDOException if there is no transaction started
* @return bool|void
*/
public function rollBack()
{

if ($this->_transactionDepth == 0) {
throw new PDOException('Rollback error : There is no transaction started');
}

$this->_transactionDepth--;

if($this->_transactionDepth == 0 || !$this->hasSavepoint()) {
parent::rollBack();
} else {
$this->exec("ROLLBACK TO SAVEPOINT LEVEL{$this->_transactionDepth}");
}
}

}[/src]
Die Quelle habe ich gerade nicht im Kopf, findet man allerdings mit den Stichwörtern PDO, Transaction und Nested (bzw Verschachtelt).

Übergeben wird der Tabellenname, die ID der zu bearbeitenden Zeile und ein Array, das als Schlüssel die Spaltennamen und als Wert die neuen Werte enthält. Aufgerufen wird lediglich updateRow().

Mir ist allerdings noch ein kleinerer Fehler aufgefallen, den ich nicht ganz verstehe.
---
Beim Auslesen der "veränderten" Werte entsteht beispielhaft die Query:
[src=mysql]SELECT IF(Vorname != :Vorname, Vorname, NULL) AS Vorname,IF(Nachname != :Nachname, Nachname, NULL) AS Nachname FROM Mitarbeitertabelle WHERE ID=:id[/src]
Wenn die übergebenen Parameter ein Int-Wert, Null oder ein Float-Wert sind, wird wie gewünscht Null zurückgegeben (wenn der Wert gleich ist). Ist der übergebene Wert allerdings ein String wie "roin" und in der Tabelle steht bereits "roin" wird dieser ebenfalls unverändert zurückgegeben. Das Problem konnte ich bisher leider nicht lösen.
Somit habe ich in meiner Sicherungsspalten-Tabelle weiterhin überflüssig viele Werte.
 

Rakorium-M

NGBler

Registriert
14 Juli 2013
Beiträge
413
Ich vermute der SQL-Gleichheits-Operator kann dich hier noch ärgern :D
[src=mysql]SELECT IF(NULL = NULL, 1, 2); -- => 2
SELECT IF(NULL != NULL, 1, 2); -- => 2
SELECT IF(NULL != 'abc', 1, 2); -- => 2 ((NULL != X) wird zu (NULL) wird zu false)[/src]
Zumindest 2 Strings funktionieren bei mir aber:
[src=mysql]SELECT IF('roin' != 'roin', 'roin', NULL) as Vorname; -- => NULL
SELECT IF('roin' != 'roin ', 'roin', NULL) as Vorname; -- => NULL (der Vollständigkeit halber)
SELECT IF('roin' != 'ROIN', 'roin', NULL) as Vorname; -- => NULL (zumindest auf meinem Server, abhängig von den Einstellungen)[/src]
Die letzten beiden wirst du bspw. los, indem du einen vernünftigen SQL-Server wählst (bspw. PostgreSQL), oder die Strings binär vergleichst (X == binary Y).


Trigger sind aber wohl tatsächlich die "schönste" Lösung. Mal ein Beispiel wie das aussehen könnte: [src=mysql]-- 2 Beispiel-Tabellen
DROP TABLE IF EXISTS mitarbeiter;
CREATE TABLE mitarbeiter(
id int NOT NULL PRIMARY KEY auto_increment,
vorname VARCHAR(100) NOT NULL,
nachname VARCHAR(100) NOT NULL
);

-- Hier landen die Änderungen
DROP TABLE IF EXISTS mitarbeiter_changes;
CREATE TABLE mitarbeiter_changes(
id int NOT NULL PRIMARY KEY auto_increment,
mitarbeiter_id int NOT NULL REFERENCES mitarbeiter,
column_name VARCHAR(20) NOT NULL,
old_value VARCHAR(100) NOT NULL,
new_value VARCHAR(100) NOT NULL
);


-- Schwarze Magie - die ; im Trigger-Code dürfen den Create-Trigger-Befehl nicht vorzeitig beenden
delimiter #
-- Trigger löst bei jedem Update der Tabelle aus - einmal für jede geänderte Zeile
CREATE TRIGGER mitarbeiter_updates BEFORE UPDATE ON mitarbeiter
FOR EACH ROW
BEGIN
-- Wenn sich der Vorname geändert hat -> speichern
-- Der Trick mit "binary" bringt MySQL dazu, einen "vernünftigen" Vergleich zu machen
-- Ich hab die Spalten oben als NOT NULL deklariert - damit spar ich mir einen extra "IS NULL"-Test
IF OLD.vorname != binary NEW.vorname THEN
INSERT INTO mitarbeiter_changes (mitarbeiter_id, column_name, old_value, new_value)
VALUES (OLD.id, 'vorname', OLD.vorname, NEW.vorname);
END IF;
-- Das Gleiche für alle anderen Spalten, die dich interessieren
IF OLD.nachname != binary NEW.nachname THEN
INSERT INTO mitarbeiter_changes (mitarbeiter_id, column_name, old_value, new_value)
VALUES (OLD.id, 'nachname', OLD.nachname, NEW.nachname);
END IF;
END#
delimiter ; -- </Schwarze Magie>


-- Ein paar Testdaten
INSERT INTO mitarbeiter (vorname, nachname) VALUES
('AA', 'AB'),
('BA', 'BB'),
('CA', 'CB');
SELECT * FROM mitarbeiter_changes; -- => Empty Set

-- Mitarbeiter benennt sich um (mehrfach)
UPDATE mitarbeiter SET vorname = 'Heinz' WHERE id = 1;
UPDATE mitarbeiter SET vorname = 'Heinz' WHERE id = 1;
-- Mehrere Mitarbeiter werden umbenannt
UPDATE mitarbeiter SET nachname = 'roin';

SELECT * FROM mitarbeiter_changes; /* =>
+----+----------------+-------------+-----------+-----------+
| id | mitarbeiter_id | column_name | old_value | new_value |
+----+----------------+-------------+-----------+-----------+
| 1 | 1 | vorname | AA | Heinz |
| 2 | 1 | nachname | AB | roin |
| 3 | 2 | nachname | BB | roin |
| 4 | 3 | nachname | CB | roin |
+----+----------------+-------------+-----------+-----------+
*/[/src]
Ich denke den Rest kannst du selber einbauen.
 

Roin

Freier Denker

Registriert
22 Juli 2013
Beiträge
581
  • Thread Starter Thread Starter
  • #7
Ich vermute der SQL-Gleichheits-Operator kann dich hier noch ärgern
Da hast du wohl recht. Ich wusste nicht, dass ein Vergleich mit NULL in SQL so unschön ist...

[src=mysql]
DROP TABLE IF EXISTS mitarbeiter;
CREATE TABLE mitarbeiter( -- .........)[/src]
Wieso machst du eigentlich erst einen DROP und erstellst die Tabelle dann? Ist es nicht sinnvoller ein IF NOT EXISTS in den CREATE-Befehl einzubauen? Sonst verliert man doch die ganzen Daten, die bereits drinstehen.
Ich führe zumindest bei jedem Ausführen meiner Anwendung diese Create-Befehle aus, um eventuelle Fehler zu vermeiden und Ausgaben von möglicherweise sensiblen Daten zu verhindern (Fehlermeldungen, die normalerweise nicht angezeigt werden sollten).

Ich habe nun den Trigger etwas an meine Bedürfnisse angepasst:

[src=php]/**
* Erstellt eine Query zum Erstellen eines Triggers für ein UPDATE der
* übergebenen Tabelle.
*
* @param type $tablename
* @param type $spaltennamen
* @return string
*/
function createTriggerQuery($tablename, $spaltennamen) {
if(empty($tablename) || !is_array($spaltennamen)) {
return "";
}

$sql = "delimiter ##
CREATE TRIGGER IF NOT EXISTS ".$tablename."_updates BEFORE UPDATE ON ".$tablename."
FOR EACH ROW -- Trigger löst bei jedem Update der Tabelle aus - einmal für jede geänderte Zeile
BEGIN
INSERT INTO ".MYSQL_PREFIX."Sicherung (mTabelle, mID)
VALUES (".$tablename.", OLD.id);
SET @lID = LAST_INSERT_ID();";
foreach($spaltennamen as $colname) {
$sql .= "
-- ".$colname ."
IF OLD.".$colname." != binary NEW.".$colname." THEN
INSERT INTO ".MYSQL_PREFIX."Sicherungsspalten (VorgangsID, Spaltenname, Wert)
VALUES (@lID, '".$colname."', OLD.".$colname.");
END IF; ";
}
$sql .= "
END##
delimiter ;";

return $sql;
}

//Trigger für Mitarbeitertabelle
$spalten = array();
$spalten[] = "Vorname";
$spalten[] = "Nachname";
$spalten[] = "Geburtstag";
$spalten[] = "LoginName";
//$spalten[] = "LoginPass"; //Veränderungen am Passwort sollten nicht gespeichert werden
$spalten[] = "Strasse";
$spalten[] = "Ort";
$spalten[] = "PLZ";
$spalten[] = "Telefon";
/* ... */

$sql = createTriggerQuery(MYSQL_PREFIX."Mitarbeiter", $spalten);

if(!$pdo->query($sql)) {
echo "Trigger für Mitarbeitertabelle konnte nicht erstellt werden.";
}
[/src]
Ich werde ihn jetzt mal testen und schauen, ob ich ggf. noch irgendwo einen Bug drin habe.

EDIT: BUG!
[src=mysql]delimiter ##
CREATE TRIGGER IF NOT EXISTS prefix_Mitarbeiter_updates BEFORE UPDATE ON prefix_Mitarbeiter
FOR EACH ROW -- Trigger löst bei jedem Update der Tabelle aus - einmal für jede geänderte Zeile
BEGIN
INSERT INTO prefix_Sicherung (mTabelle, mID)
VALUES (prefix_Mitarbeiter, OLD.id);
SET @lID = LAST_INSERT_ID();
-- Vorname
IF OLD.Vorname != binary NEW.Vorname THEN
INSERT INTO prefix_Sicherungsspalten (VorgangsID, Spaltenname, Wert)
VALUES (@lID, 'Vorname', OLD.Vorname);
END IF;
-- Nachname
IF OLD.Nachname != binary NEW.Nachname THEN
INSERT INTO prefix_Sicherungsspalten (VorgangsID, Spaltenname, Wert)
VALUES (@lID, 'Nachname', OLD.Nachname);
END IF;
-- Geburtstag
IF OLD.Geburtstag != binary NEW.Geburtstag THEN
INSERT INTO prefix_Sicherungsspalten (VorgangsID, Spaltenname, Wert)
VALUES (@lID, 'Geburtstag', OLD.Geburtstag);
END IF;
-- LoginName
IF OLD.LoginName != binary NEW.LoginName THEN
INSERT INTO prefix_Sicherungsspalten (VorgangsID, Spaltenname, Wert)
VALUES (@lID, 'LoginName', OLD.LoginName);
END IF;
-- Strasse
IF OLD.Strasse != binary NEW.Strasse THEN
INSERT INTO prefix_Sicherungsspalten (VorgangsID, Spaltenname, Wert)
VALUES (@lID, 'Strasse', OLD.Strasse);
END IF;
-- Ort
IF OLD.Ort != binary NEW.Ort THEN
INSERT INTO prefix_Sicherungsspalten (VorgangsID, Spaltenname, Wert)
VALUES (@lID, 'Ort', OLD.Ort);
END IF;
-- PLZ
IF OLD.PLZ != binary NEW.PLZ THEN
INSERT INTO prefix_Sicherungsspalten (VorgangsID, Spaltenname, Wert)
VALUES (@lID, 'PLZ', OLD.PLZ);
END IF;
-- Telefon
IF OLD.Telefon != binary NEW.Telefon THEN
INSERT INTO prefix_Sicherungsspalten (VorgangsID, Spaltenname, Wert)
VALUES (@lID, 'Telefon', OLD.Telefon);
END IF;
-- Mobil
IF OLD.Mobil != binary NEW.Mobil THEN
INSERT INTO prefix_Sicherungsspalten (VorgangsID, Spaltenname, Wert)
VALUES (@lID, 'Mobil', OLD.Mobil);
END IF;
-- Mail
IF OLD.Mail != binary NEW.Mail THEN
INSERT INTO prefix_Sicherungsspalten (VorgangsID, Spaltenname, Wert)
VALUES (@lID, 'Mail', OLD.Mail);
END IF;
END##
delimiter ;[/src]
[src=html4strict]<br />
<b>Fatal error</b>: Uncaught exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'delimiter ##
CREATE TRIGGER IF NOT EXISTS prefix_Mitarbeiter_updates BE' at line 1' in /data/web/1/000/062/906/256824/htdocs/test/prefix/mysql_tables.php:84
Stack trace:
#0 /data/web/1/000/062/906/256824/htdocs/test/prefix/mysql_tables.php(84): PDO->query('delimiter ##\n ...')
#1 /data/web/1/000/062/906/256824/htdocs/test/prefix/ini.php(15): require_once('/data/web/1/000...')
#2 /data/web/1/000/062/906/256824/htdocs/test/prefix/employees_edit.php(2): require_once('/data/web/1/000...')
#3 {main}
thrown in <b>/data/web/1/000/062/906/256824/htdocs/test/prefix/mysql_tables.php</b> on line <b>84</b><br />[/src]

Gebe ich die Query oben in die MySQLDumper-Konsole ein Kriege ich eine ähnliche Fehlermeldung:
MySQL meldet:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'delimiter ## CREATE TRIGGER IF NOT EXISTS prefix_Mitarbeiter_updates BE' at line 1

Fehler bei der Anfrage:
delimiter ## CREATE TRIGGER IF NOT EXISTS prefix_Mitarbeiter_updates BEFORE UPDATE ON prefix_Mitarbeiter FOR EACH ROW -- Trigger löst bei jedem Update der Tabelle aus - einmal für jede geänderte Zeile BEGIN INSERT INTO prefix_Sicherung (mTabelle, mID) VALUES (prefix_Mitarbeiter, OLD.id);

Auch wenn ich "IF NOT EXISTS" weglasse, kriege ich den gleichen Fehler. Trigger sind aber bei meinem Anbieter erlaubt - das habe ich extra vorher noch nachgeguckt.

EDIT2:
Der Fehler liegt wohl erstmal irgendwie mit dem Delimiter-Befehl zusammen. Über phpmyAdmin konnte ich den Trigger (ohne IF NOT EXISTS) erstellen. Anschließend konnte ich zwar kein UPDATE mehr ausführen, weil irgendwas unbekannt war, aber sonst konnte ich ihn immerhin erstellen. Wie löse ich das Problem mit dem Delimiter?
 
Zuletzt bearbeitet:

Rakorium-M

NGBler

Registriert
14 Juli 2013
Beiträge
413
Wieso machst du eigentlich erst einen DROP und erstellst die Tabelle dann? Ist es nicht sinnvoller ein IF NOT EXISTS in den CREATE-Befehl einzubauen? Sonst verliert man doch die ganzen Daten, die bereits drinstehen.
Ich führe zumindest bei jedem Ausführen meiner Anwendung diese Create-Befehle aus, um eventuelle Fehler zu vermeiden und Ausgaben von möglicherweise sensiblen Daten zu verhindern (Fehlermeldungen, die normalerweise nicht angezeigt werden sollten).
Damit ich beim Testen meines Beispiels einfach den ganzen Code in die Konsole kopieren kann, und nicht jedes Mal die Tabellen von Hand leeren muss. Und ein CREATE IF NOT EXISTS würde Änderungen an der Tabellen-Definition nicht übernehmen. In Produktiv-Systemen kann man da aber etwas vorsichtiger sein, stimmt.

Der Fehler liegt wohl erstmal irgendwie mit dem Delimiter-Befehl zusammen. Über phpmyAdmin konnte ich den Trigger (ohne IF NOT EXISTS) erstellen. Anschließend konnte ich zwar kein UPDATE mehr ausführen, weil irgendwas unbekannt war, aber sonst konnte ich ihn immerhin erstellen. Wie löse ich das Problem mit dem Delimiter?
Versuch mal, ein einzelnes # als Delimiter zu verwenden. Oder den Delimiter-Befehl als eigenes Query abzusenden (query 1: "delimiter #", query 2: "CREATE TRIGGER", query 3: "delimiter ;").
 

Roin

Freier Denker

Registriert
22 Juli 2013
Beiträge
581
  • Thread Starter Thread Starter
  • #9
Versuch mal, ein einzelnes # als Delimiter zu verwenden.
Das habe ich bereits ausprobiert - das hat das Problem nicht gelöst. Ich habe auch andere zeichen ($ | ...) verwendet und habe dennoch eine Fehlermeldung erhalten.
Wie erwähnt - über phpmyadmin konnte ich den Befehl ausführen, allerdings habe ich dort in dem extra Feld den Delimiter eingegeben und nicht im eigentlich SQL-Code.


Oder den Delimiter-Befehl als eigenes Query abzusenden (query 1: "delimiter #", query 2: "CREATE TRIGGER", query 3: "delimiter ;").
Das werde ich jetzt mal probieren.
EDIT: Hat nicht geklappt
 
Zuletzt bearbeitet:

Rakorium-M

NGBler

Registriert
14 Juli 2013
Beiträge
413
Anscheinend ist "delimiter" ein Kommando, das vom MySQL-Commandline-Client verarbeitet wird, nicht von der Datenbank selber. Da PDO::exec() anscheinend 1 Query pro Aufruf erwartet, kann man die Delimiter-Befehle auch weglassen (inkl. dem # am Ende).
Ausführlichere Erklärung: https://stackoverflow.com/a/25826535
 

Roin

Freier Denker

Registriert
22 Juli 2013
Beiträge
581
  • Thread Starter Thread Starter
  • #11
Da PDO::exec() anscheinend 1 Query pro Aufruf erwartet, kann man die Delimiter-Befehle auch weglassen (inkl. dem # am Ende)

Genau das habe ich gerade auch gefunden und versuche es gerade, aber bisher klappt es weiterhin nicht. Ich kriege eine Errormeldung ála
Cannot execute queries while other unbuffered queries are active. Consider using PDOStatement::fetchAll(). Alternatively, if your code is only ever going to run against mysql, you may enable query buffering by setting the PDO::MYSQL_ATTR_USE_BUFFERED_QUERY attribute.
Allerdings verstehe ich noch nicht ganz, wieso dieser Error auftritt.

EDIT: Obwohl ich das Attribute gesetzt habe (True), habe ich leider keinen Erfolg beim Ausführen.
 
Zuletzt bearbeitet:

Roin

Freier Denker

Registriert
22 Juli 2013
Beiträge
581
  • Thread Starter Thread Starter
  • #13
Derzeit versuche ich weiterhin über PHP (PDO) den Trigger anzulegen. Ändern der Daten hat eben geklappt, als ich den Trigger über phpmyadmin angelegt habe. (Ich hatte zuvor zwei Anführungsstriche vergessen.

Den Trigger anzulegen ist weiterhin das Problem.

Ich habe nun anstatt
[src=php]//Statt demhier
$pdo->query($sql);

//habe ich nun das versucht:
$stmt = $pdo->prepare($sql);
$stmt->execute();
$stmt->closeCursor();[/src]
Weiterhin ohne Erfolg.
 

Rakorium-M

NGBler

Registriert
14 Juli 2013
Beiträge
413
Versuch mal $pdo->exec($sql) statt $pdo->query($sql). Du erwartest ja auch kein Ergebnis.
 

Roin

Freier Denker

Registriert
22 Juli 2013
Beiträge
581
  • Thread Starter Thread Starter
  • #15
Versuch mal $pdo->exec($sql) statt $pdo->query($sql).
Das klappt!
Da habe ich bisher nie drüber nachgedacht, ob ich ein Ergebnis haben will oder nicht. Ich dachte, ich kann query() immer nutzen. Wenn ich keinen Rückgabewert brauche, frage ich den einfach nur nicht ab. Da sollte ich wohl noch ein paar andere Zeilen in meiner Anwendung anpassen.
Ich lese das nochmal genau nach, wann ich was nutze.

Kurze Zusammenfassung:
prepare() --> execute()
Formatiert ein Statement korrekt und macht es übersichtlicher, welche Werte in die Query eingefügt werden.
Liefert ein Statement-Objekt zurück bzw. es können die Daten aus dem Objekt ausgelesen werden (fetch()).

query()
Liefert ein Statement-Objekt zurück und es können ebenfalls die Daten ausgelesen werden. Allerdings wird die auszuführende Query nicht vorbereitet (prepare()), sondern direkt ausgeführt.

exec()
Führt eine Anweisung aus und liefert die Anzahl der betroffenen Zeilen zurück.

EDIT:
Leider finde ich nichts, wie es bei den Tabellen ist. Ich suche etwas wie CREATE TRIGGER IF NOT EXISTS. Bei Tabellen gibt es das. Bei Triggern existiert nur DROP TRIGGER IF EXISTS. Da ich den Trigger erstellen möchte, wenn er noch nicht vorhanden ist und sonst nichts tun möchte, werde ich die exec()-Anweisung wohl einfach in einen try-Block schachteln und beim catch-Block nichts ausgeben lassen.
 
Zuletzt bearbeitet:

Rakorium-M

NGBler

Registriert
14 Juli 2013
Beiträge
413
Tabellen/Trigger erstellen ist doch ohnehin nur bei der (einmaligen) Installation relevant. Und zumindest beim Trigger kannst du auch bedenkenlos "DELETE IF EXISTS" nutzen - du verlierst ja keine Daten damit.
"CREATE IF NOT EXISTS" könnte in manchen Situationen dazu führen, dass eine alte/kaputte Version des Triggers nicht aktualisiert wird.
 

Roin

Freier Denker

Registriert
22 Juli 2013
Beiträge
581
  • Thread Starter Thread Starter
  • #17
Bei dem Trigger hast du recht. Das gleiche gilt mehr oder minder für die Tabellen und die unbedigt notwendigen Daten in den Tabellen (zum Beispiel, dass ID=1 Admin ist, wenn kein anderer Admin definiert wurde usw...).

Für die Entwicklung behalte ich das bei. Später wenn es zum eigentlichen Anwenden kommt, werde ich wohl auf die "Installation" umstellen.
 

tux

NGBler

Registriert
24 Aug. 2013
Beiträge
238
Ort
München
Ich denke, Du wirst um Trigger nicht herumkommen.

Für jede Tabelle, wo Du einzelne Felder wegsichern möchtest, wirst Du einen oder mehrere Trigger benötigen, der auf BEFORE UPDATE oder AFTER UPDATE reagieren.
Welche von den beiden Methoden Du verwendest, hängt immer vom Anwendungsfall ab. Das lässt sich so einfach nicht über alle Felder festlegen. Hier ist auf jeden Fall eine gute Erklärung.

Wir haben das bei uns in der Firma auch mit DB Triggern gelöst und eine gute GUI gebaut, die die Änderungen pro Feld sichtbar macht.

Edit: Formulierung angepasst
 
Oben