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);
2.原因
在MySQL中,存储过程或者函数必须显示声明是:确定性(DETERMINISTIC)的还是不可改变的(NOT DETERMINISTIC)。如果在主从服务器都执行相关的存储过程或者函数,将导致数据不一致。
3.解决方法。
1.在客户端上执行
SET GLOBAL log_bin_trust_function_creators = 1;
2.MySQL启动时,加上--log-bin-trust-function-creators选贤,参数设置为1
3.在MySQL配置文件
my.ini或my.cnf中的
[mysqld]
log-bin-trust-function-creators=1