Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

2 Answers 2

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;
share|improve this answer
    
DETERMINISTIC is the only way because AmazonRDS will not relent on SUPER privilege. +1 !!! –  RolandoMySQLDBA Oct 4 '13 at 13:17
1  
Just tried with DETERMINISTIC and MySQL still fails with the same error on RDS, so I assume you mean I need log_bin_trust_function_creators + DETERMINISTIC then! –  Benjamin Oct 4 '13 at 13:23
    
Also, I've rewritten my code to use stored procedures since, and it seems to work! I will detail that in an answer. –  Benjamin Oct 4 '13 at 13:25

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.