| 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: | |
| Category: | MySQL Server | Severity: | S3 (Non-critical) |
| Version: | 8.0.15 | OS: | CentOS |
| Assigned to: | CPU Architecture: | Any | |
[26 Mar 2019 15:33]
MySQL Verification Team
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
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)