MySQL Bugs: #101480: Non-deterministic function with binlog

Bug #101480	Non-deterministic function with binlog_format=ROW
Submitted:	5 Nov 2020 14:24	Modified:	5 Nov 2020 18:58
Reporter:	lalit Choudhary	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	5.7,8.0	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
An attempt to create a non-deterministic function (either by omitting DETERMINISTIC or by specifying NOT DETERMINISTIC) fails with an error, even when using row-based binlogs. 
example from doc:

mysql [localhost] {msandbox} (test) > SHOW VARIABLES LIKE 'binlog_format';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW   |
+---------------+-------+
1 row in set (0.01 sec)
	
mysql [localhost] {msandbox} (test) > delimiter //
mysql [localhost] {msandbox} (test) > CREATE FUNCTION f3() 
    -> 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 [localhost] {msandbox} (test) > 
mysql [localhost] {msandbox} (test) > delimiter ;

mysql [localhost] {msandbox} (test) > CREATE FUNCTION f3() 
    -> RETURNS CHAR(36) CHARACTER SET utf8
    -> NOT DETERMINISTIC
    -> 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 [localhost] {msandbox} (test) > 
mysql [localhost] {msandbox} (test) > delimiter ;

As per the MySQL documentation at https://dev.mysql.com/doc/refman/8.0/en/stored-programs-logging.html, which states:

"When you attempt to execute a stored function, if binlog_format=STATEMENT is set, the DETERMINISTIC keyword must be specified in the function definition. If this is not the case, an error is generated and the function does not run, unless log_bin_trust_function_creators=1 is specified to override this check (see below). For recursive function calls, the DETERMINISTIC keyword is required on the outermost call only. If row-based or mixed binary logging is in use, the statement is accepted and replicated even if the function was defined without the DETERMINISTIC keyword."

How to repeat:
Test case: 

mysql [localhost] {msandbox} (test) > SHOW VARIABLES LIKE 'binlog_format';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW   |
+---------------+-------+
1 row in set (0.01 sec)
	

mysql [localhost] {msandbox} (test) > CREATE FUNCTION f3() 
    -> RETURNS CHAR(36) CHARACTER SET utf8
    -> NOT DETERMINISTIC
    -> 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 [localhost] {msandbox} (test) > 
mysql [localhost] {msandbox} (test) > delimiter ;

mysql [localhost] {msandbox} (test) > delimiter //
mysql [localhost] {msandbox} (test) > CREATE FUNCTION f3() 
    -> 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 [localhost] {msandbox} (test) > 
mysql [localhost] {msandbox} (test) > delimiter ;

****************
Working case:  it allows creating a function with a DETERMINISTIC keyword, 

mysql [localhost] {msandbox} (test) > CREATE FUNCTION f2() 
    -> RETURNS CHAR(36) CHARACTER SET utf8
    -> DETERMINISTIC
    -> BEGIN
    ->   RETURN UUID();
    -> END ;
    -> //
Query OK, 0 rows affected, 1 warning (0.02 sec)

mysql [localhost] {msandbox} (test) > 
mysql [localhost] {msandbox} (test) > delimiter ;
mysql [localhost] {msandbox} (test) > 

mysql [localhost] {msandbox} (test) > show create function f2\G
*************************** 1. row ***************************
            Function: f2
            sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
     Create Function: CREATE DEFINER=`msandbox`@`localhost` FUNCTION `f2`() RETURNS char(36) CHARSET utf8
    DETERMINISTIC
BEGIN
  RETURN UUID();
END
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: latin1_swedish_ci
1 row in set (0.00 sec)

mysql [localhost] {msandbox} (test) > 

mysql [localhost] {msandbox} (test) > select f2();
+--------------------------------------+
| f2()                                 |
+--------------------------------------+
| ecb0e351-1f70-11eb-857d-38dead00d7d2 |
+--------------------------------------+
1 row in set (0.00 sec)

Suggested fix:
There is not much we can found in the documentation for this case when binlog_format=ROW

Thank you for the bug report.

Posted by developer:
 
Thanks for the comment! I have now checked on this and in fact the quoted text applies to the situation of executing a stored function, not of creating a stored function. The situation of creating a stored function is documented in the previous item and applies to all binary logging formats, as you saw:

 When you create a stored function, you must declare either that it is deterministic or that it does not modify data. Otherwise, it may be unsafe for data recovery or replication.
By default, for a CREATE FUNCTION statement to be accepted, at least one of DETERMINISTIC, NO SQL, or READS SQL DATA must be specified explicitly. Otherwise an error occurs: 

This ensures that if the binary logging format changes after the function is created, it will still work. The difference is only made at execution time when the actual binary logging format is known. So the existing documentation is accurate.

The documentation clearly says: " If row-based or mixed binary logging is in use, the statement is accepted and replicated even if the function was defined without the DETERMINISTIC keyword. " at https://dev.mysql.com/doc/refman/8.0/en/stored-programs-logging.html

I read it that I can create non-deterministic functions with row-based binary log format without enabling variable log_bin_trust_function_creators.