ITPub博客

首页 > Linux操作系统 > Linux操作系统 > mysql 从库出现 1418

mysql 从库出现 1418

原创 Linux操作系统 作者:hl_fen 时间:2011-12-29 10:05:46 0 删除 编辑

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 16.91.95.1
                  Master_User: rep
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000017
          Read_Master_Log_Pos: 285365779
               Relay_Log_File: zfjg_jhnode-relay-bin.000005
                Relay_Log_Pos: 3894
        Relay_Master_Log_File: mysql-bin.000017
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 1418
                   Last_Error: Error '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)' on query. Default database: 'ihis'. Query: 'CREATE DEFINER=`root`@`%` FUNCTION `FUNC_GET_PERNAME_STRING`(`f_string` VARCHAR(40),`f_delimiter` VARCHAR(40)) RETURNS varchar(250) CHARSET gb2312
BEGIN
declare result varchar(250 ) default '';  
declare str_key varchar(40 ) default  ''; 
DECLARE record_not_found INTEGER DEFAULT 0;
 declare  cur_1 CURSOR FOR SELECT pername FROM `VIEW_RESULTQUERYINFO`  where cxbh  = f_string and houseid = f_delimiter  group by cxbh,certificate,pername;
 DECLARE CONTINUE HANDLER FOR NOT FOUND SET record_not_found = 1;
open cur_1;  
        allperson: LOOP
                     FETCH cur_1 INTO str_key;
                     IF record_not_found THEN
                             LEAVE allperson;
                     END IF;
                     SET result = CONCAT(result,"
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 4861
              Relay_Log_Space: 285364974
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 1418
               Last_SQL_Error: Error '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)' on query. Default database: 'ihis'. Query: 'CREATE DEFINER=`root`@`%` FUNCTION `FUNC_GET_PERNAME_STRING`(`f_string` VARCHAR(40),`f_delimiter` VARCHAR(40)) RETURNS varchar(250) CHARSET gb2312
BEGIN
declare result varchar(250 ) default '';  
declare str_key varchar(40 ) default  ''; 
DECLARE record_not_found INTEGER DEFAULT 0;
 declare  cur_1 CURSOR FOR SELECT pername FROM `VIEW_RESULTQUERYINFO`  where cxbh  = f_string and houseid = f_delimiter  group by cxbh,certificate,pername;
 DECLARE CONTINUE HANDLER FOR NOT FOUND SET record_not_found = 1;
open cur_1;  
        allperson: LOOP
                     FETCH cur_1 INTO str_key;
                     IF record_not_found THEN
                             LEAVE allperson;
                     END IF;
                     SET result = CONCAT(result,"
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1
1 row in set (0.00 sec)

By default, for a CREATE PROCEDURE or CREATE FUNCTION statement to be accepted, DETERMINISTIC or one of NO SQL and READS SQL DATA must be explicitly specified. Otherwise an error occurs:

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_creators
variable)
而它这么做的原因是:

Stored routine logging differs before and after MySQL 5.0.6. Before MySQL 5.0.6,
statements that create and use stored routines are not written to the binary log, but
statements invoked within stored routines are logged. Suppose that you issue the
following statements:

CREATE PROCEDURE mysp INSERT INTO t VALUES(1);
CALL mysp;

For this example, only the INSERT statement will appear in the binary log. The

CREATE PROCEDURE and CALL statements will not appear. The absence of routine-related

statements in the binary log means that stored routines are not replicated correctly.
It also means that for a data recovery operation, re-executing events in the binary log
does not recover stored routines.

由于双机备份的需求和数据库崩溃时的正确恢复等的原因,由于加入了存储过程的支持,日志数据
的记录比记录简单的纯数据操作SQL复杂的多,也使得备份数据和原始数据一致性的难度更大,要保
证数据的正确性,只能加了一些限制条件:

A non-deterministic routine that performs updates is not repeatable, which can have two

undesirable effects:

It will make a slave different from the master.

  • Restored data will be different from the original data.

  • To deal with these problems, MySQL enforces the following requirement: On a master
    server, creation and alteration of a routine is refused unless the routine is declared to be
    deterministic or to not modify data. This means that when you create a routine, you must
    declare either that it is deterministic or that it does not change data.

    解决办法是:

    If you set log_bin_trust_routine_creators to 1, the requirement that routines be

    deterministic or not modify data is dropped.

    1. mysql> SET GLOBAL log_bin_trust_function_creators = 1;
    2. 系统启动时 --log-bin-trust-function-creators=1
    3. 在my.ini(linux下为my.conf)文件中 [mysqld] 标记后加一行内容为 log_bin_trust_function_creators=1

    即可, 即系统不检查你创建的存储过程会不会导致日志不精确(会导致双机备份不同步、数
    据库崩溃时自动恢复的不准备等问题),可根据你的实际应用环境来确定是否这样处理:)

    来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/76790/viewspace-714119/,如需转载,请注明出处,否则将追究法律责任。

    下一篇: 没有了~
    请登录后发表评论 登录
    全部评论

    注册时间:2011-12-07

    • 博文量
      4
    • 访问量
      5611