mysql 主从 1418_mysql 从库出现 1418

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

即可， 即系统不检查你创建的存储过程会不会导致日志不精确(会导致双机备份不同步、数

据库崩溃时自动恢复的不准备等问题)，可根据你的实际应用环境来确定是否这样处理：)