Bug #94783 about log_bin_trust_function_creators parameter
Submitted: 26 Mar 13:45 Modified: 27 Mar 5:47
Reporter: donghongyu dong Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:8.0.15 OS:CentOS
Assigned to: CPU Architecture:Any

[26 Mar 13:45] donghongyu dong
Description:
Regarding the parameter log_bin_trust_function_creators, I tested different results in two versions of mysql.

How to repeat:
binlog_format = row

in mysql 5.7.25:
mysql> delimiter //
mysql> CREATE FUNCTION f2()
    -> RETURNS CHAR(36) CHARACTER SET utf8
    -> BEGIN
    ->   RETURN UUID();
    -> END;
    -> //
Query OK, 0 rows affected (0.01 sec)

mysql> delimiter ;
mysql> show variables like '%function%';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| log_bin_trust_function_creators | OFF   |
+---------------------------------+-------+
1 row in set (0.00 sec)

in mysql 8.0.15:
mysql> delimiter //
mysql> CREATE FUNCTION f2()
    -> RETURNS CHAR(36) CHARACTER SET utf8
    -> BEGIN
    ->   RETURN UUID();
    -> END;
    -> //
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)

mysql> show variables like '%function%';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| log_bin_trust_function_creators | OFF   |
+---------------------------------+-------+
1 row in set (0.00 sec)
[26 Mar 15:33] Shane Bester
Hi,

Are you sure log_bin is enabled on your 5.7 instance?
See below my test:

mysql>
mysql> delimiter $
mysql> drop function if exists f2 $
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql>  CREATE FUNCTION f2()
    ->  RETURNS CHAR(36) CHARACTER SET utf8
    ->  BEGIN
    ->    RETURN UUID();
    ->  END
    -> $
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)
mysql>
mysql> delimiter ;
mysql> select @@log_bin,@@binlog_format,version(),@@log_bin_trust_function_creators;
+-----------+-----------------+-----------+-----------------------------------+
| @@log_bin | @@binlog_format | version() | @@log_bin_trust_function_creators |
+-----------+-----------------+-----------+-----------------------------------+
|         1 | ROW             | 8.0.15    |                                 0 |
+-----------+-----------------+-----------+-----------------------------------+
1 row in set (0.00 sec)

------------

mysql> \r
Connection id:    2
Current database: test

mysql> delimiter $
mysql> drop function if exists f2 $
Query OK, 0 rows affected, 1 warning (0.05 sec)

mysql>  CREATE FUNCTION f2()
    ->  RETURNS CHAR(36) CHARACTER SET utf8
    ->  BEGIN
    ->    RETURN UUID();
    ->  END
    -> $
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)
mysql>
mysql> delimiter ;
mysql> select @@log_bin,@@binlog_format,version(),@@log_bin_trust_function_creators;
+-----------+-----------------+------------+-----------------------------------+
| @@log_bin | @@binlog_format | version()  | @@log_bin_trust_function_creators |
+-----------+-----------------+------------+-----------------------------------+
|         1 | ROW             | 5.7.25-log |                                 0 |
+-----------+-----------------+------------+-----------------------------------+
1 row in set (0.00 sec)

mysql>
mysql>
[27 Mar 3:23] donghongyu dong
hi 
Sorry, I made a mistake