Skip navigation links
MySQL 5.1 Referenzhandbuch :: 19 Gespeicherte Prozeduren und Funktionen :: 19.4 Binärloggen gespeicherter Routinen und Trigger
20 Trigger »
Section Navigation      [Toggle]

19.4. Binärloggen gespeicherter Routinen und Trigger

Das Binärlog enthält Informationen über SQL-Anweisungen, die Datenbankinhalte ändern. Diese Informationen werden in Form von „Ereignissen“ gespeichert, welche die Modifikationen beschreiben. Das Binärlog dient zwei wichtigen Zwecken:

  • Für die Replikation sendet der Master-Server die Ereignisse, die in seinem Binärlog stehen, an seine Slaves. Diese führen die Ereignisse dann aus, um die Datenänderungen nachzuvollziehen, die auf dem Master stattgefunden haben. Siehe Abschnitt 6.2, „Replikation: Implementation“.

  • Für bestimmte Datenwiederherstellungsoperationen muss das Binärlog genutzt werden. Nach der Wiederherstellung einer Sicherungsdatei werden aus dem Binärlog die Ereignisse, die nach Erstellung der Sicherungsdatei eintraten, erneut ausgeführt. Diese Ereignisse bringen die Datenbank von dem Zeitpunkt der Sicherung auf den neuesten Stand. Siehe auch Abschnitt 5.10.2.2, „Verwenden von Datensicherungen zur Wiederherstellung“.

Dieser Abschnitt beschreibt, wie MySQL 5.1 das Binärlogging für gespeicherte Routinen (Prozeduren und Funktionen) und Trigger behandelt. Es wird beschrieben, welche Bedingungen die Implementierung zurzeit an die Verwendung gespeicherter Routinen knüpft, und diese Bedingungen werden auch begründet.

Die hier beschriebenen Probleme gründen im Wesentlichen auf die Tatsache, dass Binärlogging auf SQL-Anweisungsebene stattfindet. In einem künftigen Release von MySQL soll auch Binärlogging auf Zeilenebene eingeführt werden, wobei die Änderungen protokolliert werden, die bei der Ausführung von SQL-Anweisungen in den einzelnen Zeilen stattfinden.

Soweit nichts anderes gesagt wird, gehen wir in den nachfolgenden Bemerkungen davon aus, dass Sie Binärlogging durch Hochfahren des Servers mit der Option --log-bin eingeschaltet haben. (Siehe auch Abschnitt 5.12.3, „Die binäre Update-Logdatei“.) Wenn das Binärlog nicht eingeschaltet ist, ist weder eine Replikation möglich noch steht das Binärlog für die Wiederherstellung von Daten zur Verfügung.

Die derzeit gültigen Bedingungen für die Nutzung gespeicherter Funktionen in MySQL 5.1 werden im Folgenden zusammengefasst. Diese Bedingungen gelten nicht für gespeicherte Prozeduren, und sie gelten auch ansonsten nur dann, wenn das Binärlogging auch eingeschaltet ist.

  • Um eine gespeicherte Funktion anzulegen oder zu ändern, benötigen Sie das SUPER-Recht zusätzlich zu dem normalerweise erforderlichen CREATE ROUTINE- oder ALTER ROUTINE-Recht.

  • Wenn Sie eine gespeicherte Funktion erstellen, müssen Sie sie entweder als deterministisch deklarieren oder festlegen, dass sie keine Daten modifiziert. Andernfalls kann sie für die Datenwiederherstellung oder Replikation Unsicherheiten bergen.

  • Zur Lockerung der obigen Bedingungen für die Erstellung einer Funktion (obligatorisches SUPER-Recht und das Erfordernis, entweder eine deterministische Funktion oder eine Funktion, die keine Daten ändert, zu deklarieren) können Sie die globale Systemvariable log_bin_trust_function_creators auf 1 setzen. Diese Variable hat den Standardwert 0, lässt sich aber folgendermaßen umstellen: 

    mysql> SET GLOBAL log_bin_trust_function_creators = 1;

    Sie können diese Variable auch einstellen, indem Sie beim Starten des Servers die Option --log-bin-trust-function-creators einstellen.

    Wenn Binärlogging nicht aktiviert ist, ist log_bin_trust_function_creators unwirksam und das SUPER-Recht ist nicht erforderlich, um eine Funktion anzulegen.

Da Trigger gespeicherten Funktionen ähneln, gelten diese Ausführungen zu Funktionen auch für Trigger, allerdings mit der folgenden Einschränkung: Da CREATE TRIGGER kein optionales DETERMINISTIC-Merkmal hat, geht man davon aus, dass Trigger immer deterministisch sind. Doch diese Grundannahme muss nicht immer gelten. So ist beispielsweise die Funktion UUID() nichtdeterministisch (und wird auch nicht repliziert). Solche Funktionen sollten Sie in Triggern nur mit Vorsicht benutzen.

Da Trigger Tabellen ändern können, kommen im Zusammenhang mit CREATE TRIGGER ähnliche Fehlermeldungen wie bei gespeicherten Funktionen vor, wenn Sie nicht das SUPER-Recht haben und log_bin_trust_function_creators den Wert 0 hat.

Im Folgenden erfahren Sie mehr über die Logging-Implementierung und ihre Implikationen.

  • Der Server schreibt CREATE PROCEDURE-, CREATE FUNCTION-, ALTER PROCEDURE-, ALTER FUNCTION-, DROP PROCEDURE- und DROP FUNCTION-Anweisungen in das Binärlog.

  • Der Aufruf einer gespeicherten Funktion wird als DO-Anweisung protokolliert, wenn die Funktion Daten ändert und in einer Anweisung auftritt, die ansonsten nicht protokolliert würde. Dadurch wird verhindert, dass Datenänderungen, die durch die Verwendung von gespeicherten Funktionen in nichtprotokollierten Anwendungen auftreten, nicht repliziert werden. So werden beispielsweise SELECT-Anweisungen nicht in das Binärlog geschrieben, aber ein SELECT kann eine gespeicherte Funktion aufrufen, die Datenänderungen hervorruft. Um damit umzugehen, wird eine DO func_name()-Anweisung in das Binärlog geschrieben, wenn die Funktion eine Änderung vornimmt. Angenommen, folgende Funktionen werden auf dem Master ausgeführt: 

    CREATE FUNCTION f1(a INT) RETURNS INT
BEGIN
  IF (a < 3) THEN 
    INSERT INTO t2 VALUES (a);
  END IF;
END;

CREATE TABLE t1 (a INT);
INSERT INTO t1 VALUES (1),(2),(3);

SELECT f1(a) FROM t1;

    Wenn die SELECT-Anweisung ausgeführt wird, wird die Funktion f1() dreimal aufgerufen. Zwei der Aufrufe fügen eine Zeile ein und MySQL schreibt für jede von ihnen eine DO-Anweisung in das Log. Somit hält MySQL folgende Anweisungen im Binärlog fest: 

    DO f1(1);
DO f1(2);

    Der Server protokolliert auch eine DO-Anweisung für einen Aufruf einer gespeicherten Funktion, wenn die Funktion eine gespeicherte Prozedur aufruft, die einen Fehler verursacht. In diesem Fall schreibt der Server die DO-Anweisung zusammen mit dem erwarteten Fehlercode in das Log. Wenn auf dem Slave derselbe Fehler auftritt, ist dies das erwartete Resultat und die Replikation läuft weiter. Andernfalls bricht die Replikation ab.

  • Wenn anstelle von Anweisungen, die eine Funktion ausführt, Aufrufe gespeicherter Funktionen protokolliert werden, hat dies Folgen für die Sicherheit der Replikation. Dafür sind zwei Faktoren verantwortlich:

    • Es ist möglich, dass eine Funktion auf dem Master und den Slave-Servern unterschiedliche Ausführungspfade einschlägt.

    • Anweisungen, die auf einem Slave ausgeführt werden, werden von dem SQL-Thread des Slaves verarbeitet, der volle Berechtigungen hat.

    Die Folge davon ist, dass zwar jeder Benutzer für die Erstellung einer Funktion das CREATE ROUTINE-Recht benötigt. Doch damit könnte er eine Funktion schreiben, die eine gefährliche Anweisung enthält, die nur auf dem Slave ausgeführt wird, da sie dort von dem SQL-Thread ausgeführt wird, der über volle Berechtigungen verfügt. Wenn beispielsweise der Master-Server die Server-ID 1 und der Slave-Server die Server-ID 2 hätte, könnte ein Benutzer auf dem Master-Server eine unsichere Funktion namens unsafe_func() folgendermaßen erstellen und aufrufen: 

    mysql> delimiter //
mysql> CREATE FUNCTION unsafe_func () RETURNS INT
    -> BEGIN
    ->   IF @@server_id=2 THEN dangerous_statement; END IF;
    ->   RETURN 1;
    -> END;
    -> //
mysql> delimiter ;
mysql> INSERT INTO t VALUES(unsafe_func());

    Da die CREATE FUNCTION- und die INSERT-Anweisung in das Binärlog geschrieben werden, führt der Slave sie aus. Und da der Slave-SQL-Thread wiederum über so umfassende Berechtigungen verfügt, wird er die gefährliche Anweisung auch befolgen. Somit hat der Funktionsaufruf auf dem Master andere Folgen als auf dem Slave und ist nicht replikationssicher.

    Um Server, auf denen das Binärlogging eingeschaltet ist, vor dieser Gefahr zu schützen, benötigen die Erzeuger gespeicherter Funktionen zusätzlich zu dem üblichen CREATE ROUTINE-Recht, das ohnehin notwendig ist, auch das SUPER-Recht. Ebenso darf ALTER FUNCTION nur benutzen, wer zusätzlich zu dem ALTER ROUTINE-Recht auch das SUPER-Recht besitzt. Fehlt die SUPER-Berechtigung, wird ein Fehler ausgelöst: 

    ERROR 1419 (HY000): You do not have the SUPER privilege and
binary logging is enabled (you *might* want to use the less safe
log_bin_trust_function_creators variable)

    Wenn Sie von den Erzeugern von Funktionen nicht verlangen möchten, dass sie das SUPER-Recht besitzen (zum Beispiel, wenn in Ihrem System alle Benutzer, die das CREATE ROUTINE-Recht haben, erfahrene Anwendungsentwickler sind), dann können Sie die globale Systemvariable log_bin_trust_function_creators auf 1 setzen. Das können Sie auch tun, indem Sie beim Serverstart die Option --log-bin-trust-function-creators einstellen. Wenn kein Binärlogging aktiviert ist, ist log_bin_trust_function_creators wirkungslos und zum Anlegen von Funktionen ist kein SUPER-Recht erforderlich.

  • Wenn eine Funktion, die Änderungen vornimmt, nichtdeterministisch ist, so ist sie auch nicht wiederholbar. Dies kann zwei unangenehme Folgen haben:

    • Ein Slave entspricht nicht mehr dem Master.

    • Wiederhergestellte Daten entsprechen nicht mehr den Originaldaten.

    Um diese Probleme in den Griff zu bekommen, stellt MySQL folgende Anforderung: Auf einem Master-Server ist die Erzeugung und Änderung einer Funktion nur möglich, wenn diese als deterministisch deklariert ist oder keine Daten ändert. Hierbei kommen zwei Arten von Funktionsmerkmalen ins Spiel:

    • Die Merkmale DETERMINISTIC und NOT DETERMINISTIC zeigen an, ob eine Funktion für dieselben Eingabewerte auch immer dieselben Ergebnisse produziert. Da die Standardeinstellung, wenn nichts anderes angegeben wird, NOT DETERMINISTIC lautet, müssen Sie explizit das Merkmal DETERMINISTIC angeben, um klarzustellen, dass eine Funktion deterministisch ist.

      Durch Verwendung der Funktion NOW() (oder ihrer Synonyme) oder RAND() wird eine Funktion nicht unbedingt deterministisch. Für NOW() zeichnet das Binärlog den Zeitstempel auf und repliziert richtig. Die Funktion RAND() repliziert ebenfalls korrekt, sofern sie in einer Funktion nicht mehrmals aufgerufen wird. (Den Ausführungs-Zeitstempel der Funktion und den Zufallszahlen-Seedwert können Sie als implizite Eingaben betrachten, die bei Master und Slave identisch sind.)

    • Die Merkmale CONTAINS SQL, NO SQL, READS SQL DATA und MODIFIES SQL DATA geben Informationen darüber, ob die Funktion Daten liest oder schreibt. NO SQL oder READS SQL DATA zeigt an, dass eine Funktion keine Daten ändert, aber Sie müssen eines dieser Merkmale explizit angeben, da ansonsten der Standardwert CONTAINS SQL ist.

    Damit eine CREATE FUNCTION-Anweisung akzeptiert wird, muss nach Voreinstellung DETERMINISTIC bzw. entweder NO SQL oder READS SQL DATA explizit angegeben werden. Andernfalls tritt ein Fehler auf: 

    ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL,
or READS SQL DATA in its declaration and binary logging is enabled
(you *might* want to use the less safe log_bin_trust_function_creators
variable)

    Wenn Sie log_bin_trust_function_creators auf 1 setzen, wird nicht mehr gefordert, dass Funktionen entweder deterministisch sind oder keine Daten ändern.

    Wie die Natur einer Funktion eingeschätzt wird, hängt von der „Ehrlichkeit“ ihres Erzeugers ab: MySQL prüft nicht, ob eine als DETERMINISTIC deklarierte Funktion auch tatsächlich keine Anweisungen enthält, die nichtdeterministische Ergebnisse produzieren.

  • Aufrufe an gespeicherte Prozeduren werden auf Anweisungs- statt auf CALL-Ebene protokolliert. Das bedeutet, dass der Server nicht die CALL-Anweisung protokolliert, sondern diejenigen Anweisungen der Prozedur, die tatsächlich ausgeführt werden. Dadurch treten auf den Slave-Servern dieselben Änderungen wie auf dem Master ein. So werden Probleme vermieden, die entstehen könnten, wenn eine Prozedur auf verschiedenen Rechnern verschiedene Ausführungspfade hat.

    Im Allgemeinen werden Anweisungen, die in einer gespeicherten Prozedur ausgeführt werden, nach denselben Regeln in das Binärlog geschrieben, die auch für eigenständig ausgeführte Anweisungen gelten. Die Protokollierung von Prozeduranweisungen wird besonders vorsichtig gehandhabt, da die Ausführung von Anweisungen innerhalb einer Prozedur nicht dasselbe ist wie außerhalb einer Prozedur:

    • Eine zu protokollierende Anweisung könnte Verweise auf lokale Prozedurvariablen enthalten. Da diese Variablen außerhalb des Prozedurkontextes gar nicht vorhanden sind, kann eine Anweisung, die eine solche Variable benutzt, nicht wörtlich protokolliert werden. Stattdessen wird für das Log jede Referenz auf eine lokale Variable durch das folgende Konstrukt ersetzt: 

      NAME_CONST(var_name, var_value)

      var_name ist der Name der lokalen Variablen und var_value eine Konstante, die den Wert der Variablen zum Zeitpunkt der Protokollierung der Anweisung anzeigt. NAME_CONST() hat den Wert var_value und den „Namen“ var_name. Somit erhalten Sie folgendes Ergebnis, wenn Sie diese Funktion direkt aufrufen: 

      mysql> SELECT NAME_CONST('myname', 14);
+--------+
| myname |
+--------+
|     14 |
+--------+

      NAME_CONST() ermöglicht es, eine selbstständige Anweisung auf einem Slave so auszuführen, dass sie denselben Effekt hat wie die Originalanweisung, die innerhalb einer gespeicherten Prozedur auf dem Master-Server ausgeführt wurde.

    • Eine zu protokollierende Anweisung könnte Referenzen auf benutzerdefinierte Variablen enthalten. Also schreibt MySQL eine SET-Anweisung in das Binärlog, um zu gewährleisten, dass die Variable auf dem Slave mit demselben Wert wie auf dem Master existiert. Wenn beispielsweise eine Anweisung die Variable @my_var benutzt, steht vor dieser Anweisung im Binärlog folgende Anweisung, wobei value der Wert ist, den @my_var auf dem Master hat: 

      SET @my_var = value;

    • Prozeduraufrufe können in einer committeten oder zurückgerollten Transaktion auftreten. Früher wurden CALL-Anweisungen auch dann protokolliert, wenn sie in einer zurückgerollten Transaktion vorkamen. Seit MySQL 5.0.12 wird der Transaktionskontext berücksichtigt, sodass die transaktionsbedingten Aspekte der Prozedurausführung korrekt repliziert werden. Das bedeutet, dass der Server in der Prozedur nur diejenigen Anweisungen protokolliert, die auch tatsächlich ausgeführt werden und Daten ändern. Nach Bedarf protokolliert er darüber hinaus auch BEGIN-, COMMIT- und ROLLBACK-Anweisungen. Wenn beispielsweise eine Prozedur nur Transaktionstabellen ändert und innerhalb einer Transaktion ausgeführt wird, die zurückgerollt wird, werden solche Änderungen nicht ins Log geschrieben. Tritt die Prozedur hingegen in einer committeten Transaktion auf, werden BEGIN- und COMMIT-Anweisungen mit den Updates protokolliert. Die Anweisungen einer in einer zurückgerollten Transaktion ausgeführten Prozedur werden nach denselben Regeln protokolliert, die auch gelten würden, wenn die Anweisungen selbstständig ausgeführt worden wären:

      • Änderungen an Transaktionstabellen werden nicht protokolliert.

      • Änderungen an anderen Tabellen werden protokolliert, weil sie durch ein Rollback nicht rückgängig gemacht werden.

      • Änderungen an einem Mix von Transaktionstabellen und anderen Tabellen werden innerhalb eines BEGIN-ROLLBACK Blocks protokolliert, damit die Slaves dieselben Änderungen und Rollbacks wie der Master vornehmen.

  • Ein Aufruf einer gespeicherten Prozedur wird nicht auf Anweisungsebene in das Binärlog geschrieben, wenn die Prozedur innerhalb einer gespeicherten Funktion aufgerufen wird. In einem solchen Fall wird lediglich die Anweisung protokolliert, welche die Funktion aufruft, (wenn diese innerhalb einer protokollierten Anweisung benutzt wird), oder eine DO-Anweisung (wenn sie in einer Anweisung benutzt wird, die nicht protokolliert wird). Daher sollten Sie vorsichtig mit gespeicherten Funktionen sein, die eine Prozedur aufrufen, selbst wenn die Prozedur ansonsten sicher ist.

Dies ist eine Übersetzung des MySQL-Referenzhandbuchs, das sich auf dev.mysql.com befindet. Das ursprüngliche Referenzhandbuch ist auf Englisch, und diese Übersetzung ist nicht notwendigerweise so aktuell wie die englische Ausgabe. Das vorliegende deutschsprachige Handbuch behandelt MySQL bis zur Version 5.1.