mysql 从库报错1418分析处理--生产案例

mysql 从库报错1418分析处理--生产案例

目录

• mysql 从库报错1418分析处理--生产案例
  • 现象
  • 原因分析
  • 解决方案：
    • 方案一
    • 方案二

现象

某天管理员反应某系统主从报错如下：

Last_Errno: 1418
Last_Error: Worker 1 failed executing transaction ‘c87541e5-d534-11e9-fc56-f85990571ecd:94190xxx’ at master log mysql-bin.000078, end_log_pos 1019424988.

ERROR 1418 (HY000): 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)

Query: ‘CREATE DEFINER=xxxxxxuser@% FUNCTION currVal(seq_name VARCHAR(50)) RETURNS bigint(20)
BEGIN
DECLARE value BIGINT;
SELECT current_vlaue INTO value
FROM f_wase_base_sequence
WHRER upper(name) = upper(seq_name);
RETURNS value;
END’,Error 1418

确认主从相关变量设置：
查看报错是主库有创建不确定函数，
主库设置了 log_bin_trust_function_creators =on;
从库设置了 log_bin_trust_function_creators =off;
因主从相关此变量设置不一样导致复制中断。

原因分析

当二进制日志启用后，这个变量（log_bin_trust_function_creators）就会启用。它控制是否可以信任存储函数创建者，不会创建写入二进制日志引起不安全事件的存储函数。

如果设置为0（默认值），用户不得创建或修改存储函数，除非它们具有除CREATE ROUTINE或ALTER ROUTINE特权之外的SUPER权限。设置为0还强制使用DETERMINISTIC特性或READS SQL DATA或NO SQL特性声明函数的限制。

如果变量设置为1，MySQL不会对创建存储函数实施这些限制。此变量也适用于触发器的创建

为什么MySQL有这样的限制呢？因为二进制日志的一个重要功能是用于主从复制，而存储函数有可能导致主从的数据不一致。所以当开启二进制日志后，参数log_bin_trust_function_creators就会生效，限制存储函数的创建、修改、调用。

log_bin_trust_function_creators 最终目的就是保持mysql主从复制的一致性～

解决方案：

方案一

如果数据库没有使用主从复制，那么就可以将参数log_bin_trust_function_creators设置为1。
如果数据库使用了主从复制，强制启用以下参数，存在主从不一致的风险

从库设置了 log_bin_trust_function_creators =on;

MySQL [(none)]> show variables like '%function%';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| log_bin_trust_function_creators | OFF   |
+---------------------------------+-------+
1 row in set (0.00 sec)

MySQL [(none)]> stop slave;
Query OK, 0 rows affected, 1 warning (0.01 sec)

MySQL [(none)]> SET GLOBAL log_bin_trust_function_creators = 1;
Query OK, 0 rows affected (0.00 sec)

MySQL [(none)]> start slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)
MySQL [(none)]

这个动态设置的方式会在服务重启后失效，所以我们还必须在my.cnf中设置，加上log_bin_trust_function_creators=1，这样就会永久生效）

vim /etc/my.cnf
log_bin_trust_function_creators=1

方案二

明确指明函数的类型，如果我们开启了二进制日志， 那么我们就必须为我们的function指定一个参数。其中下面几种参数类型里面，只有 DETERMINISTIC, NO SQL 和 READS SQL DATA 被支持。这样一来相当于明确的告知MySQL服务器这个函数不会修改数据。

• 1 DETERMINISTIC 确定的
• 2 NO SQL 没有SQl语句，当然也不会修改数据
• 3 READS SQL DATA 只是读取数据，当然也不会修改数据
• 4 MODIFIES SQL DATA 要修改数据
• 5 CONTAINS SQL 包含了SQL语句

eg:

mysql> show variables like 'log_bin_trust_function_creators';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| log_bin_trust_function_creators | OFF   |
+---------------------------------+-------+
1 row in set (0.00 sec)
 
mysql> DROP FUNCTION GET_UPPER_NAME;
Query OK, 0 rows affected (0.00 sec)
 
mysql> DELIMITER //
mysql> CREATE FUNCTION GET_UPPER_NAME(emp_id INT)
    -> RETURNS VARCHAR(12)
    -> READS SQL DATA
    -> BEGIN
    ->   RETURN(SELECT UPPER(NAME) FROM TEST WHERE ID=emp_id);
    -> END
    -> //
Query OK, 0 rows affected (0.01 sec)
 
mysql> DELIMITER ;
mysql> SELECT ID,
    ->        GET_UPPER_NAME(ID)
    -> FROM TEST;
+------+--------------------+
| ID   | GET_UPPER_NAME(ID) |
+------+--------------------+
|  100 | KERRY              |
|  101 | JIMMY              |
+------+--------------------+
2 rows in set (0.00 sec)