Not so long ago I was creating the following function in MySQL:

DELIMITER //CREATE FUNCTION NextVal (vname VARCHAR(30), input_tenant_id INT )
RETURNS INT
BEGIN
  UPDATE sequences
SET next = next + 1
WHERE name = vname and tenant_id= input_tenant_id;
  RETURN (SELECT next FROM sequences WHERE name = vname and tenant_id=input_tenant_id);
END
  //
  DELIMITER ;

Since code is fine, my expectation was that it will execute without any issues, but I got an error instead:

Error  Code: 1418. This function has none od DETERMINISTIC, NO SQL, or READ SQL DATA in its declaration and binary logging is enabled (you *might* want to use less safe log.)

Solved MySQL issue: This function has none of Deterministic, No SQL or READS SQL DATA

I researched a bit to find out how to solve this issue, and this is what I found out. If we're creating a function that does some data modification, it's considered non-deterministic. Those are functions that do update, insert, or delete statements. Those functions have some strict checking, and this  setting relaxes the checking for non-deterministic functions:

SET GLOBAL log_bin_trust_function_creators = 1;

After this setting, I didn't have the issue I mentioned anymore.

Pin It

Newsletter

Please enable the javascript to submit this form