存储程序 、函数、触发程序及复制：常见问题

【王富贵】

存储程序 、函数、触发程序及复制：常见问题
MySQL 5.1存储程序和函数对复制起作用吗？  
是的，在存储程序和函数中被执行标准行为被从主MySQL服务器复制到从服务器。有少数限制，它们在20.4节，“存储子程序和 触发程序二进制日志功能”中详述。

在主服务器上创建的存储程序和函数可以被复制到从服务器上么？
是的，通过一般DDL语句执行的存储程序和函数，其在主服务器上的创建被复制到从服务器，所以目标将存在两个服务器上。对存储程序和函数的ALTER 和DROP语句也被复制。

行为如何在已复制的存储程序和函数里发生？
MySQL纪录每个发生在存储程序和函数里的DML事件，并复制这些单独的行为到从服务器。执行存储程序和函数的切实调用不被复制。

对一起使用存储程序，函数和复制有什么特别的安全要求么？
是的，因为一个从服务器有权限来执行任何读自主服务器的二进制日志的语句，指定的安全约束因与复制一起使用的存储程序和函数而存在。如果复制或二进制日志大体上是激活的（为point-in-time恢复的目的），那么MySQL DBA 有两个安全选项可选：

任何想创建存储程序的用户必须被赋予SUPER权限。
作为选择，一个DBA可以设置log_bin_trust_routine_creators系统变量为1，它将会允许有标准CREATE ROUTINE权限的人来创建一个存储程序和函数。

对复制存储程序和函数的行为有什么限制？
嵌入到存储程序中的不确定（随机）或时基行不能适当地复制。随机产生的结果，仅因其本性，是你可预测的和不能被确实克隆的。因此，复制到从服务器的随机行为将不会镜像那些产生在主服务器上的。注意， 声明存储程序或函数为DETERMINISTIC或者在log_bin_trust_routine_creators中设置系统变量为0 将会允许随即值操作被调用。

此外，时基行为不能在从服务器上重新产生，因为在存储程序中通过对复制使用的二进制日志来计时这样的时基行为是不可重新产生的，因为该二进制日志仅纪录DML事件且不包括计时约束。

最后，在大型DML行为（如大批插入）中非交互表发生错误，该非交互表可能经历复制，在复制版的非交互表中主服务器可以被部分地从DML行为更新。但是因为发生的那个错误，对从服务器没有更新。 对函数的DML行为，工作区将被用IGNORE关键词来执行，以便于在主服务器上导致错误的更新被忽略，并且不会导致错误的更新被复制到从服务器。



上述的限制会影响MySQL作 point-in-time恢复的能力吗？
影响复制的同一限制会影响point-in-time恢复。

MySQL要做什么来改正前述的限制呢？
将来发行的MySQL预期有一个功能去选择复制该如何被处理：

基于语句的复制（当前实现）。
行级别复制（它将解决所有早先描述的限制）。
触发程序对复制起作用么？
MySQL 5.1中的触发程序和复制象在大多数其它数据库引擎中一样工作，在那些引擎中，通过触发程序在主服务器上执行的行为不被复制到从服务器。取而代之的是，位于主MySQL服务器的表中的 触发程序需要在那些存在于任何MySQL从服务器上的表内被创建，以便于触发程序可以也可以在从服务器上被激活。



一个行为如何通过从主服务器上复制到从服务器上的触发程序来执行呢？
首先，主服务器上的触发程序必须在从服务器上重建。一旦重建了，复制流程就象其它参与到复制中的标准DML语句一样工作。例如：考虑一个已经插入触发程序AFTER的EMP表，它位于主MySQL服务器上。同样的EMP表和AFTER插入 触发程序也存在于从服务器上。复制流程可能是：

1.    对EMP做一个INSERT语句。

2.   EMP上的AFTER触发程序激活。

3.    INSERT语句被写进二进制日志。

4.    从服务器上的复制拾起INSERT语句给EMP表，并在从服务器上执行它。

5.    位于从服务器EMP上的AFTER触发程序激活。

20.4. 存储子程序和触发程序的二进制日志功能
，这一节介绍MySQL 5.1如何考虑二进制日志功能来处理存储子程序（程序和函数） 。这一节也适用于触发程序。

二进制日志包含修改数据库内容的SQL语句的信息。这个信息以描述修改的事件的形式保存起来。

二进制日志有两个重要目的：

·         复制的基础是主服务器发送包含在二进制日志里的事件到从服务器，从服务器执行这些事件来造成与对主服务器造成的同样的数据改变，请参阅6.2节，“复制概述”。

·         特定的数据恢复操作许要使用二进制日志。备份的文件被恢复之后，备份后纪录的二进制日志里的事件被重新执行。这些事件把数据库带从备份点的日子带到当前。请参阅5.9.2.2节，“使用备份恢复”。

MySQL中，以存储子程序的二进制日志功能引发了很多问题，这些在下面讨论中列出，作为参考信息。

除了要另外注意的之外，这些谈论假设你已经通过用--log-bin选项启动服务器允许了二进制日志功能。（如果二进制日志功能不被允许，复制将不可能，为数据恢复的二进制日志也不存在。）请参阅5.11.3节，“二进制日志”。

对存储子程序语句的二进制日志功能的特征在下面列表中描述。一些条目指出你应该注意到的问题。但是在一些情况下，有你可以更改的妇五七设置或你可以用来处理它们的工作区。

·         CREATE PROCEDURE, CREATE FUNCTION, ALTER PROCEDURE,和ALTER FUNCTION 语句被写进二进制日志，CALL, DROP PROCEDURE, 和DROP FUNCTION 也一样。

尽管如此，对复制有一个安全暗示：要创建一个子程序，用户必须有CREATE ROUTINE权限,但有这个权限的用户不能写一个 子程序在从服务器上执行任何操作。因为在从服务器上的SQL线程用完全权限来运行。例如，如果主服务器和从服务器分别有服务器ID值1和2，在主服务器上的用户可能创建并调用如下一个 程序：

mysql> delimiter //mysql> CREATE PROCEDURE mysp ()    -> BEGIN    ->   IF @@server_id=2 THEN DROP DATABASE accounting; END IF;    -> END;    -> //mysql> delimiter ;mysql> CALL mysp();
CREATE PROCEDURE和CALL语句将被写进二进制日志，所以从服务器将执行它们。因为从SQL线程有完全权限，它将移除accounting数据库。

要使允许二进制日志功能的服务器避免这个危险，MySQL 5.1已经要求 存储程序和函数的创建者除了通常需要的CREATE ROUTINE的权限外，还必须有SUPER 权限。类似地，要使用ALTER PROCEDURE或ALTER FUNCTION，除了ALTER ROUTINE权限外你必须有SUPER权限。没有SUPER权限，将会发生一个错误：

ERROR 1419 (HY000): You do not have the SUPER privilege andbinary logging is enabled (you *might* want to use the less safelog_bin_trust_routine_creators variable)
你可能不想强制要求子程序创建者必须有SUPER权限。例如，你系统上所有有CREATE ROUTINE权限的用户可能是有经验的应用程序开发者。要禁止掉对SUPER权限的要求，设置log_bin_trust_routine_creators 全局系统变量为1。默认地，这个变量值为0，但你可以象这样改变这样：

mysql> SET GLOBAL log_bin_trust_routine_creators = 1;
你也可以在启动服务器之时用--log-bin-trust-routine-creators选项来设置允许这个变量。

如果二进制日志功能不被允许，log_bin_trust_routine_creators 没有被用上，子程序创建需要SUPER权限。

·         一个执行更新的非确定子程序是不可重复的，它能有两个不如意的影响：

o        它会使得从服务器不同于主服务器。

-        恢复的数据与原始数据不同。

要解决这些问题，MySQL强制做下面要求：在主服务器上，除非子程序被声明为确定性的或者不更改数据，否则创建或者替换子程序将被拒绝。这意味着当你创建一个子程序的时候，你必须要么声明它是确定性的，要么它不改变数据。两套子程序特征在这里适用：

-        DETERMINISTIC和NOT DETERMINISTIC指出一个子程序是否对给定的输入总是产生同样的结果。如果没有给定任一特征，默认是NOT DETERMINISTIC，所以你必须明确指定DETERMINISTIC来 声明一个 子程序是确定性的。

使用NOW() 函数（或它的同义）或者RAND() 函数不是必要地使也一个子程序非确定性。对NOW()而言，二进制日志包括时间戳并正确复制。RAND()只要在一个 子程序内被调用一次也可以正确复制。（你可以认为子程序执行时间戳和随机数种子作为毫无疑问地输入，它们在主服务器和从服务器上是一样的。）

-        CONTAINS SQL, NO SQL, READS SQL DATA, 和 MODIFIES SQL数据提供子程序是读还是写数据的信息。无论NO SQL 还是READS SQL DATA i都指出，子程序没有改变数据，但你必须明白地指明这些中的一个，因为如果任何这些特征没有被给出， 默认的特征是CONTAINS SQL。

默认地，要一个CREATE PROCEDURE 或 CREATE FUNCTION 语句被接受，DETERMINISTIC 或 NO SQL与READS SQL DATA 中的一个必须明白地指定，否则会产生如下错误：

ERROR 1418 (HY000): This routine 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_routine_creatorsvariable)
如果设置log_bin_trust_routine_creators 为1, 移除对子程序必须是确定的或不修改数据的要求。

注意，子程序本性的评估是基于创建者的“诚实度” ：MySQL不检查声明为确定性的子程序是否不含产生非确定性结果的语句。

·         如果子程序返回无错，CALL语句被写进二进制日志，否则就不写。当一个子程序修改数据失败了，你会得到这样的警告：

·                ERROR 1417 (HY000): A routine failed and has neither NO SQL nor·                READS SQL DATA in its declaration and binary logging is enabled; if·                non-transactional tables were updated, the binary log will miss their·                changes
这个记日志行为潜在地导致问题.如果一个子程序部分地修改一个非交互表（比如一个MyISAM表able）并且返回一个错误，二进制日志将反映这些变化。要防止这种情况，你应该在 子程序中使用交互表并且在交互动作内修改表。

在一个子程序内，如果你在INSERT, DELETE, 或者UPDATE里使用IGNORE关键词来忽略错误，可能发生一个部分更新，但没有错误产生。这样的语句被记录日志，且正常复制。

·         如果一个存储函数在一个如SELECT这样不修改数据的语句内被调用，即使函数本身更改数据，函数的执行也将不被写进二进制日志里。这个记录日志的行为潜在地导致问题。假设函数myfunc()如下定义：

·                CREATE FUNCTION myfunc () RETURNS INT·                BEGIN·                  INSERT INTO t (i) VALUES(1);·                  RETURN 0;·                END;
按照上面定义，下面的语句修改表t，因为myfunc()修改表t, 但是语句不被写进二进制日志，因为它是一个SELECT语句：

SELECT myfunc();
对这个问题的工作区将调用在做更新的语句里做更新的函数。注意，虽然DO语句有时为了其估算表达式的副效应而被执行，DO在这里不是一个工作区，因为它不被写进二进制日志。

·         在一个子程序内执行的语句不被写进二进制日志。假如你发布下列语句：

·                CREATE PROCEDURE mysp INSERT INTO t VALUES(1);·                CALL mysp;
对于这个例子来说，CREATE PROCEDURE 和CALL语句出现在二进制日志里，但INSERT语句并未出现。

·         在从服务器上，当决定复制哪个来自主服务器的事件时，下列限制被应用：--replicate-*-table规则不适用于CALL语句或子程序内的语句：在这些情况下，总是返回“复制！”

触发程序类似于存储函数，所以前述的评论也适用于触发程序，除了下列情况： CREATE TRIGGER没有可选的DETERMINISTIC特征，所以触发程序被假定为总是确定性的。然而，这个假设在一些情况下是非法的。比如，UUID()函数是非确定性的（不能复制）。你应该小心在 触发程序中使用这个函数。

触发程序目前不能更新表，但是在将来会支持。因为这个原因，如果你没有SUPER权限且log_bin_trust_routine_creators 被设为0，得到的错误信息类似于存储子程序与CREATE TRIGGER产生的错误信息。