MySQL开启二进制日志后对创建过程函数的限制及分析

背景

今日项目更换服务器，数据库迁移后项目跑起来验证部分功能时出错，发现是数据库函数忘记创建了，这问题低级又简单，不就创建个函数嘛！打开工具执行创建命令

DELIMITER $$
CREATE FUNCTION `funName`(agentId int,lev int) RETURNS varchar(100) DETERMINISTIC

...
函数内容
...

END$$

DELIMITER ;

执行的时候报错了：

You do not have the SUPER privilege and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)

解决

解决过程很简单，把错误内容复制百度咯，具体解决过程如下：

• 检查系统开启二进制日志
  

• 启用二进制日志时，需要设置log_bin_trust_function_creators = 1才行

  1. 用root用户登录：mysql -u root -p
  2. set global log_bin_trust_function_creators = 1；

拓展

文献

log_bin_trust_function_creators这个参数是干什么用的呢？继续深入下：

官方文档如是介绍：

This variable applies when binary logging is enabled. It controls whether stored function creators
can be trusted not to create stored functions that will cause unsafe events to be written to the binary
log. If set to 0 (the default), users are not permitted to create or alter stored functions unless they
have the SUPER privilege in addition to the CREATE ROUTINE or ALTER ROUTINE privilege. A
setting of 0 also enforces the restriction that a function must be declared with the DETERMINISTIC
characteristic, or with the READS SQL DATA or NO SQL characteristic. If the variable is set to 1,
MySQL does not enforce these restrictions on stored function creation. This variable also applies to
trigger creation.

意思是：启用二进制日志记录时，此变量就会起作用，它控制是否存储函数创建者可以信任不要创建将导致不安全事件写入二进制文件的存储函数
日志。 如果设置为0（默认值），除具有CREATE ROUTINE或ALTER ROUTINE特权外，还具有SUPER特权。否则不允许用户创建或更改存储的功能。设置为0还强制执行必须使用DETERMINISTIC声明函数的限制特性，或者具有READS SQL DATA或NO SQL特性。 如果变量设置为1，MySQL对存储函数的创建不施加这些限制，此变量也适用于触发创建。

分析

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

• 如果您没有使用主从复制，那么可以把参数log_bin_trust_function_creators设置为1，方便操作。

set global log_bin_trust_function_creators=1;

这个动态设置的方式会在服务重启后失效，所以我们还必须在mysql.cnf配置文件中加上log_bin_trust_function_creators=1

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

• DETERMINISTIC 不确定的；

• NO SQL 没有SQl语句，当然也不会修改数据；
• READS SQL DATA 只是读取数据，当然也不会修改数据；
• MODIFIES SQL DATA 要修改数据；
• CONTAINS SQL 包含了SQL语句。