mysql恢复备份时ERROR 1418 (HY000)错误

时间:2014-03-06 07:03:33      作者:beebol      标签: function log_bin mysql      分类: Mysql

在恢复数据库的时候遇到了如下问题：

[root@Server3 ~]# mysql -f apollodb <erpdb-dump-20140226_1119.sql ERROR 1418 (HY000) at line 5685: 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)
ERROR 1418 (HY000) at line 5685: 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)

问题主要是备份中存在一个function，执行函数会有不确定的结果产生，在开启bin-log下，就会报如上错误，因为在复制和恢复时会导致与原始数据不一致，所以它会认为这是不安全的。

1、首先看下log_bin确实是开着的：

[root@localhost:Wed Jan 29 04:08:38 2014 {apollodb}]> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+1 row in set (0.00 sec)
1 row in set (0.00 sec)

2、检查erpdb-dump-20140226_1119.sql备份，确实有一个function中存在不确定值，函数中用了NOW() 。

DELIMITER ;;
/*!50003 CREATE*/ /*!50020 DEFINER=`root`@`%`*/ /*!50003 FUNCTION `createBillCode`(flowKey varchar(60)) RETURNS varchar(60) CHARSET utf8
BEGIN

        DECLARE billCode VARCHAR(60);
        DECLARE curcode int;
        DECLARE currentDate VARCHAR(8);
        select cur_code into curcode  from apollo_erp_billcode t where t.flow_key=flowKey and t.cur_date=current_date;

        SELECT DATE_FORMAT(NOW(), '%Y%m%d') INTO currentDate ;
        if curcode=null then
                insert into apollo_erp_billcode(flow_key,cur_date,cur_code) values(flowKey ,current_date,1);
                select CONCAT(flowKey, currentDate, LPAD((curcode + 1), 5, '0')) INTO billCode ;
        end if;

        RETURN billCode;
END */;;DELIMITER ;
DELIMITER ;

2、查看确实这个函数没有创建成功

[root@localhost:Wed Jan 29 04:08:41 2014 {apollodb}]>show function status;Empty set (0.00 sec)
Empty set (0.00 sec)

3、在错误输出的时候也提示了一种解决办法是将log_bin_trust_function_creators这个变量设置为ON或1。默认这个变量是0，可能改变这个值来：

[root@localhost:Wed Jan 29 04:11:36 2014 {apollodb}]> show variables like 'log_bin_trust_function_creators';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| log_bin_trust_function_creators | OFF   |
+---------------------------------+-------+
1 row in set (0.00 sec)

[root@localhost:Wed Jan 29 04:26:03 2014 {apollodb}]>set global log_bin_trust_function_creators=on;
Query OK, 0 rows affected (0.00 sec)

[root@localhost:Wed Jan 29 04:29:04 2014 {apollodb}]> show variables like 'log_bin_trust_function_creators';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| log_bin_trust_function_creators | ON    |
+---------------------------------+-------+1 row in set (0.00 sec)
1 row in set (0.00 sec)

官方有更详细的说明

http://dev.mysql.com/doc/refman/5.0/en/stored-programs-logging.html