Stored procedures, binlog and SUPER privilege

Question

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.

Answer 1

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;

Answer 2

It looks like this doesn't apply to stored procedures.

I've rewritten my code to use two procedures to do the job, instead of a statement calling a function, and that works fine.

The function above has been rewritten to be a procedure:

CREATE PROCEDURE GetRootZoneId (IN zoneId INT UNSIGNED, OUT rootZoneId INT UNSIGNED)
BEGIN
    DECLARE _id, _parentId INT UNSIGNED;
    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;

    SET rootZoneId = _id;
END;

And a second procedure calls this one to iterate on the records to update:

CREATE PROCEDURE UpdateZoneCountries() BEGIN
  DECLARE done BOOLEAN DEFAULT FALSE;
  DECLARE _id INT UNSIGNED;
  DECLARE _rootId INT UNSIGNED;
  DECLARE cur CURSOR FOR SELECT id FROM Zone WHERE countryCode IS NULL;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done := TRUE;

  OPEN cur;

  my_loop: LOOP
    FETCH cur INTO _id;
    IF done THEN
      LEAVE my_loop;
    END IF;
    CALL GetRootZoneId(_id, _rootId);

    UPDATE Zone SET rootId = _rootId WHERE id = _id;
  END LOOP my_loop;

  CLOSE cur;
END;

I successfully ran this code on RDS with the default.mysql5.6 parameter group.