Mysql – Non Super User cannot alter stored procedures and functions

There is no way to define stored procedures or stored functions (or events) that are global.

One approach is to create a shared common schema and then qualify the calls to the functions and procedures with the name of that schema (CALL shared.the_procedure();).

This is something I do with my collection of custom date/time calculation functions (e.g., SELECT date_time.next_quarter_start_after(NOW())), and with the ever-so-handy common_schema framework, which, of course, lives in `common_schema`.

If you take that approach, you have to remember then when a routine is running, the "current" database automatically changes, and the return value of the DATABASE() function returns the name of the schema under which the routine was defined, not your session's current database. It changes back when the routine exits, so if used in a trigger, it doesn't break anything surrounding it but you don't have a way of knowing from inside the routine what the current database was, if you needed to know.

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;