Bug #94783 about log_bin_trust_function_creators parameter

Submitted: 26 Mar 2019 13:45 Modified: 27 Mar 2019 5:47
Reporter: hongyu dong (OCA) 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 2019 13:45] hongyu 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 2019 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 2019 3:23] hongyu dong
hi 
Sorry, I made a mistake

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.050 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.