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.)
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.