MySQL CREATE FUNCTION 在共享 Web 服务器上失败

发布于 2024-09-11 07:25:34 字数 1103 浏览 11 评论 0原文

我有以下功能。当我尝试在网络服务器上创建它时，它失败了

您没有 SUPER 权限并且已启用二进制日志记录（您可能希望使用不太安全的 log_bin_trust_function_creators 变量）

我从未在任何其他虚拟主机（以及完全相同的功能）中遇到过相同的问题，如何我可以解决这个问题吗？（我无法更改 MySQL 设置）。

CREATE FUNCTION `has_action_access`(in_role VARCHAR(255), in_request VARCHAR(255), in_action VARCHAR(255)) RETURNS tinyint(1)
    READS SQL DATA
    DETERMINISTIC
BEGIN

    DECLARE current_role VARCHAR(255);  
    DECLARE found, cont TINYINT(1) DEFAULT 0;
    SET current_role = in_role;

    findattempt: REPEAT
        SELECT COUNT(*) FROM cyp_action_access WHERE request = in_request AND action = in_action AND role = current_role INTO found;

        IF found = 0 THEN
            SELECT COUNT(*) FROM cyp_roles WHERE name = current_role INTO cont;
            IF cont = 1 THEN
                SELECT inherits FROM cyp_roles WHERE name = current_role INTO current_role;
                END IF;
            END IF;
    UNTIL (cont = 0 OR found > 0) END REPEAT;
    RETURN found;
END;

MySQL服务器的版本是5.0.90-log。

原文

I have the following function. When I try to create it on a webserver, it fails with

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)

I never had the same problem with any other webhosting (and the exact same function), how can I fix this? (I can't change MySQL settings).

CREATE FUNCTION `has_action_access`(in_role VARCHAR(255), in_request VARCHAR(255), in_action VARCHAR(255)) RETURNS tinyint(1)
    READS SQL DATA
    DETERMINISTIC
BEGIN

    DECLARE current_role VARCHAR(255);  
    DECLARE found, cont TINYINT(1) DEFAULT 0;
    SET current_role = in_role;

    findattempt: REPEAT
        SELECT COUNT(*) FROM cyp_action_access WHERE request = in_request AND action = in_action AND role = current_role INTO found;

        IF found = 0 THEN
            SELECT COUNT(*) FROM cyp_roles WHERE name = current_role INTO cont;
            IF cont = 1 THEN
                SELECT inherits FROM cyp_roles WHERE name = current_role INTO current_role;
                END IF;
            END IF;
    UNTIL (cont = 0 OR found > 0) END REPEAT;
    RETURN found;
END;