I tried to create this function on Amazon RDS for MySQL:
CREATE FUNCTION GetRootZoneId (zoneId INT) RETURNS INT READS SQL DATA BEGIN DECLARE _id, _parentId INT; SELECT zoneId INTO _parentId; my_loop: LOOP SELECT id, parentId INTO _id,_parentId FROM Zone WHERE id = _parentId; IF _parentId IS NULL THEN LEAVE my_loop; END IF; END LOOP my_loop; RETURN _id; END;
This would only be used in a one-off query to update an existing database table.
Running it got me the following error:
ERROR 1419 (HY000): You do not have the SUPER privilege and binary logging is enabled (you might want to use the less safe log_bin_trust_function_creators variable)
Does this restriction apply to stored procedures as well, or just functions?
If it does apply to stored procedures as well, is there any alternative to execute an equivalent code without setting
log_bin_trust_function_creators=1? Please note that I know how to change this setting on RDS, I'd just like to avoid that if possible.