Can't create a Non-Deterministic functions in MySQL with binlog_format=ROW (or MIXED)

Question

In MySQL 8.0, even with binlog_format set to "ROW" or "MIXED" I seem to be unable to create a non-deterministic function. e.g:

DELIMITER //
CREATE FUNCTION nonDeterministicFunc()
    RETURNS CHAR(4)
BEGIN
    RETURN LPAD(HEX(FLOOR(RAND() * 0xffff)), 4, '0');
END
//
DELIMITER ;

gives the error:

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)

I'm aware that during replication, if binlog_format is set to STATEMENT then the slave DB will re-call the function and get a different result, but I thought when set to "ROW" or "MIXED" it would replicate the row changes not the function calls and so non-deterministic functions were fine?

The comment from MySQL support Margaret Fisher seems to say this is a feature: https://bugs.mysql.com/bug.php?id=101480#c511192

"This ensures that if the binary logging format changes after the function is created, it will still work."

I understand the danger that someone changes the replication format later.... but surely that is an extreme corner case and that adding non-deterministic functions should to be allowed when replication is currently set to ROW or MIXED?

Or am I completely missing the obvious?

Answer 1

Any session may override the binlog_format, so the MySQL Server cannot rely on the global setting to ensure that the function is safe. If the function's code performs an INSERT/UPDATE/DELETE, and the session has been changed to binlog_format=STATEMENT, it would cause a different change on the replica than on the source.

I recommend you add the NO SQL option to your function definition to satisfy the error check.