MySQL CREATE FUNCTION fails on a shared webserver

Question

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;

MySQL server's version is 5.0.90-log.

score 2 · Accepted Answer · answered Jul 27 '10 at 19:35

2

Your user does not have super privilege. You will need to contact your webhosting provider and have them update this. If they will not grant you that option ask them to execute the script for you.

answered Jul 27 '10 at 19:35

Marco Ceppi

7,163
5
31
43

Why do I need super privilige to create a function? – cypher Jul 27 '10 at 19:36
+1: Yep, [per documentation](http://dev.mysql.com/doc/refman/5.1/en/stored-programs-logging.html) - find the 1419 reference & scroll up a little. – OMG Ponies Jul 27 '10 at 19:36
1

You need to be trusted user because of crappy mysql handling of user functions -> you can create infinite loop, which results in DoS of mysql server. – nothrow Jul 27 '10 at 19:56
1

You could also ask your provider to "relax the preceding conditions on function creation" by setting SET GLOBAL log_bin_trust_function_creators = 1; – jorgebg Oct 27 '11 at 09:38

MySQL CREATE FUNCTION fails on a shared webserver

1 Answers1