Mysql – Stored procedures, binlog and SUPER privilege

amazon-rdsMySQL

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.

Best Answer

You can't avoid it. The restriction applies also to stored procedures. The only alternative would be to have the SUPER privilege, but I don't think it's possible in this case.

Moreover, you have to declare your function as DETERMINISTIC, telling explicitly that you're not going to modify data:

CREATE FUNCTION GetRootZoneId (zoneId INT) RETURNS INT DETERMINISTIC 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;
Related Question