Das CREATE ROUTINE-Recht ist erforderlich, um gespeicherte Routinen zu erzeugen.

Das ALTER ROUTINE-Recht wird benötigt, um gespeicherte Routinen zu ändern oder zu löschen. Dieses Recht wird automatisch dem Erzeuger einer Routine erteilt.

Das EXECUTE-Recht ist notwendig, um gespeicherte Routinen auszuführen, wird aber ebenfalls dem Erzeuger einer Routine automatisch erteilt. Das voreingestellte SQL SECURITY-Merkmal einer Routine ist DEFINER und erlaubt es Benutzern, mit denen die Routine verbunden ist, und die darüber hinaus Datenbankzugriff haben, die Routine auszuführen.

Wenn die Routine aufgerufen wird, wird implizit ein USE db_name ausgeführt (und wieder rückgängig gemacht, wenn die Routine endet). USE-Anweisungen sind in gespeicherten Routinen nicht zulässig.

Den Namen einer Routine können Sie mit einem Datenbanknamen qualifizieren, etwa wenn Sie eine Routine benutzen möchten, die nicht in der aktuellen Datenbank vorliegt. Um beispielsweise eine Prozedur p oder eine Funktion f aus der Datenbank test aufzurufen, können Sie CALL test.p() oder test.f() sagen.

Wird eine Datenbank gelöscht, so werden alle ihre gespeicherten Routinen mitgelöscht.

Funktionieren die gespeicherten Prozeduren von MySQL 5.1 mit Replikation?

Ja. Standardaktionen, die in gespeicherten Prozeduren und Funktionen ausgeführt werden, werden von einem MySQL-Master-Server auf einen Slave-Server repliziert. Es gibt einige wenige Beschränkungen, die in Abschnitt 19.4, „Binärloggen gespeicherter Routinen und Trigger“ genauer ausgeführt werden.

Werden gespeicherte Prozeduren und Funktionen, die auf einem Master-Server angelegt wurden, auf einen Slave repliziert?

Ja, gespeicherte Prozeduren und Funktionen, die mit normalen DDL-Anweisungen auf einem Master-Server angelegt wurden, werden auf einen Slave repliziert, sodass die Objekte auf beiden Servern vorhanden sind. ALTER- und DROP-Anweisungen für gespeicherte Prozeduren und Funktionen werden ebenfalls repliziert.

Wie werden Aktionen repliziert, die innerhalb von gespeicherten Prozeduren und Funktionen stattfinden?

MySQL zeichnet jedes DML-Ereignis auf, das in einer gespeicherten Prozedur auftritt, und repliziert diese Einzelaktionen auf einen Slave-Server. Die eigentlichen Aufrufe der gespeicherten Prozeduren werden nicht repliziert.

Gespeicherte Funktionen, die Daten ändern, werden als Funktionsaufrufe ins Log geschrieben, und nicht als die DML-Ereignisse, die innerhalb der Funktionen stattfinden.

Gibt es spezielle Sicherheitsanforderungen, um gespeicherte Prozeduren und Funktionen mit Replikation zu benutzen?

Ja. Da ein Slave-Server das Recht hat, jede Anweisung auszuführen, die er aus dem Binärlog seines Masters liest, gibt es besondere Sicherheitsvorkehrungen für die Verwendung von gespeicherten Prozeduren mit Replikation. Wenn Replikation oder Binärlogging im Allgemeinen (für die Point-of-Time-Recovery) aktiv ist, haben MySQL-DBAs zwei Sicherheitsoptionen:

Welche Beschränkungen gelten für die Replikation von Aktionen gespeicherter Prozeduren und Funktionen?

Nichtdeterministische (zufällige) oder zeitabhängige Aktionen, die in gespeicherten Prozeduren eingebettet sind, werden eventuell nicht richtig repliziert. Nach dem Zufallsprinzip entstandene Ergebnisse sind von Natur aus unvorhersehbar und lassen sich nicht genau reproduzieren. Somit spiegeln Zufallsaktionen, die auf einen Slave repliziert werden, nicht die Aktionen des Masters wider. Wenn Sie gespeicherte Funktionen als DETERMINISTIC deklarieren oder die Systemvariable log_bin_trust_function_creators auf 0 setzen, können keine Operationen mit Zufallswerten aufgerufen werden.

Auch zeitabhängige Aktionen lassen sich nicht auf einen Slave replizieren, da das Timing solcher Aktionen in einer gespeicherten Prozedur nicht durch das für die Replikation eingesetzte Binärlog reproduzierbar ist. Dieses zeichnet nur DML-Ereignisse auf und berücksichtigt keine Zeiteinschränkungen.

Auch in nichttransaktionssicheren Tabellen, mit denen bei größeren DML-Aktionen (wie beispielsweise Massen-Einfügeoperationen) Fehler auftreten, können Replikationsprobleme entstehen, wenn ein Master aufgrund der DML-Aktivität teilweise aktualisiert wurde, während der Slave wegen eines Fehlers diese Änderung nicht mitgemacht hat. Dies kann man umgehen, indem man die DML-Aktionen einer Funktion mit dem Schlüsselwort IGNORE ausführt, damit Änderungen auf dem Master, die Fehler auslösen, ignoriert und Änderungen, die keine Fehler auslösen, auf den Slave repliziert werden.

Beeinträchtigen die vorgenannten Beschränkungen die Fähigkeit von MySQL, eine Point-in-Time-Recovery durchzuführen?

Dieselben Beschränkungen, die sich auf die Replikation auswirken, wirken sich auch auf die Point-in-Time-Recovery aus.

Was unternimmt MySQL, um diese Beschränkungen auszuräumen?

Ab MySQL 5.1.5 können Sie zwischen anweisungs- und zeilenbasierter Replikation wählen. Die ursprüngliche Implementierung der Replikation beruht auf dem anweisungsbasierten Binärlogging. Zeilenbasiertes Binärlogging löst die oben beschriebenen Probleme. Mehr zum Thema finden Sie unter Abschnitt 6.3, „Zeilenbasierte Replikation“.

Funktionieren Trigger mit Replikation?

Trigger und Replikation funktionieren in MySQL 5.1 genau wie in den meisten anderen Datenbank-Engines: Aktionen, die auf einem Master mithilfe von Triggern ausgeführt werden, werden nicht auf einen Slave-Server repliziert. Dagegen müssen Trigger auf Tabellen, die auf einem MySQL-Master-Server liegen, auch auf den entsprechenden Tabellen des MySQL-Slave-Servers angelegt werden, um auf den Slaves ebenso wie auf dem Master aktiviert werden zu können.

Wie werden Trigger-Aktionen auf einem Master ausgeführt, der auf einen Slave repliziert wurde?

Erstens müssen die Trigger, die auf dem Master vorhanden sind, auf dem Slave-Server rekonstruiert werden. Wenn dies erledigt ist, läuft die Replikation so ab wie bei jeder anderen DML-Standardanweisung, die an einer Replikation beteiligt ist. Betrachten Sie als Beispiel die Tabelle EMP mit dem Insert-Trigger AFTER, die auf einem MySQL-Master-Server liegt. Dieselbe EMP-Tabelle mit demselben AFTER-Insert-Trigger liegt auch auf dem Slave-Server. Der Replikationsfluss verliefe folgendermaßen:

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“.

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.

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:

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:

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:

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:

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.