64.MySQL-Error ‘Unknown error 1418‘‘ on query-优快云博客

本文链接：https://blog.youkuaiyun.com/weixin_43346403/article/details/129200345

文章描述了一个在MySQL主从复制过程中遇到的错误，具体是由于创建非确定性存储过程或函数导致的数据不一致问题。解决方法包括在客户端设置全局变量`log_bin_trust_function_creators`为1，或者在MySQL启动参数和配置文件中添加相应设置，允许非确定性函数在二进制日志中记录。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1.主从复制时报错。

Worker 8 failed executing transaction '6b3376bc-8d60-11ed-b1cf-00505699f49f:68966' at master log mybinlog.000009,
end_log_pos 92870206; Error 'Unknown error 1418'' on query.
Default database: 'pub_sys'.
Query: 'CREATE DEFINER=`root`@`%` FUNCTION `FUNC_NEXTID`(`I_TABLENAME` varchar(60)) RETURNS bigint(12)
BEGIN
   DECLARE NEXTID BIGINT(12);
   DECLARE TABLENAME VARCHAR(60);
   SET TABLENAME=I_TABLENAME;
   SET NEXTID=12;
SELECT IFNULL(MAX(ID),-1)
INTO NEXTID
FROM t_sequence
WHERE UPPER(NAME) = UPPER(TABLENAME);
IF NEXTID = -1 THEN
INSERT INTO t_sequence (NAME, ID) VALUES (TABLENAME, 1);
SET NEXTID= 1;
ELSE
#v1.0.2 update之后本记录处于锁定状态，这样并发时也不会出现相同ID
UPDATE t_sequence SET ID = ID + 1 WHERE UPPER(NAME) = UPPER(TABLENAME);
SELECT IFNULL(MAX(ID),-1)
INTO NEXTID
FROM t_sequence
WHERE UPPER(NAME) = UPPER(TABLENAME);
END IF;
RETURN (N);