Aug 25, 2018

[HDGEM] How to fix MySQL ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled

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 DETERMINISTICNO SQL, or READS SQL DATA must be specified explicitly. Otherwise an error occurs:
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)
This function is deterministic (and does not modify data), so it is safe:
CREATE FUNCTION f1(i INT)  RETURNS INT  DETERMINISTIC  READS SQL DATA  BEGIN    RETURN 88;  END;  
This function uses UUID(), which is not deterministic, so the function also is not deterministic and is not safe:
CREATE FUNCTION f2()  RETURNS CHAR(36) CHARACTER SET utf8  BEGIN    RETURN UUID();  END;  
This function modifies data, so it may not be safe:
CREATE FUNCTION f3(p_id INT)  RETURNS INT  BEGIN    UPDATE t SET modtime = NOW() WHERE id = p_id;    RETURN ROW_COUNT();  END;  
Assessment of the nature of a function is based on the "honesty" of the creator: MySQL does not check that a function declared DETERMINISTIC is free of statements that produce nondeterministic results.


--
Posted By Blogger to HDGEM at 11/01/2016 09:56:00 AM