主页 / dba / 问题 / 51005
Accepted
BenMorel
Asked: 2013-10-05 00:28:25 +0800 CST

存储过程、binlog 和 SUPER 权限

  • 772

我尝试在 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;

这只会在一次性查询中用于更新现有数据库表。

运行它给我以下错误：

错误 1419 (HY000)：您没有超级权限并且启用了二进制日志记录（您可能希望使用不太安全的 log_bin_trust_function_creators 变量）

此限制是否也适用于存储过程，还是仅适用于函数？

如果它也适用于存储过程，是否有任何替代方法可以在不设置的情况下执行等效代码log_bin_trust_function_creators=1？请注意，我知道如何在 RDS 上更改此设置，我只想尽可能避免这种情况。

mysql amazon-rds

2 个回答

  1. 你无法避免它。该限制也适用于存储过程。唯一的选择是拥有超级特权，但我认为在这种情况下是不可能的。

    此外，您必须将您的函数声明为DETERMINISTIC，明确告知您不会修改数据：

    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;
    • 2
  2. Best Answer

    看起来这不适用于存储过程。

    我已经重写了我的代码以使用两个过程来完成这项工作，而不是调用函数的语句，并且效果很好。

    上面的函数已被重写为一个过程：

    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;

    第二个过程调用这个来迭代要更新的记录：

    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;

    default.mysql5.6我使用参数组在 RDS 上成功运行了此代码。

    • 2

相关问题