Creating MySQL function requires SUPER privileges

Question

I have a simple MySQL function for comparing versions:

CREATE FUNCTION `compareVersions` (
  versionA VARCHAR(50),
  versionB VARCHAR(50)) RETURNS INT DETERMINISTIC NO SQL
BEGIN
  DECLARE a1 INT;
  DECLARE b1 INT;
   DECLARE c1 INT;
    DECLARE d1 INT;
  DECLARE a2 INT;
  DECLARE b2 INT;
   DECLARE c2 INT;
    DECLARE d2 INT;  SET a1 = SUBSTRING_INDEX( `versionA` , '.', 1 );
  SET b1 = SUBSTRING_INDEX(SUBSTRING_INDEX( `versionA` , '.', 2 ),'.',-1);
  SET c1 = SUBSTRING_INDEX(SUBSTRING_INDEX( `versionA` , '.', -2 ),'.',1);
  SET d1 = SUBSTRING_INDEX( `versionA` , '.', -1 );
  SET a2 = SUBSTRING_INDEX( `versionB` , '.', 1 );
  SET b2 = SUBSTRING_INDEX(SUBSTRING_INDEX( `versionB` , '.', 2 ),'.',-1);
  SET c2 = SUBSTRING_INDEX(SUBSTRING_INDEX( `versionB` , '.', -2 ),'.',1);
  SET d2 = SUBSTRING_INDEX( `versionB` , '.', -1 ); 
  IF (a1 > a2) THEN 
    RETURN -1;
  ELSEIF ((a1 = a2) AND (b1 > b2)) THEN
    RETURN -1;
  ELSEIF ((a1 = a2) AND (b1 = b2) AND (c1 > c2)) THEN
    RETURN -1;
  ELSEIF ((a1 = a2) AND (b1 = b2) AND (c1 = c2) AND (d1 > d2)) THEN
    RETURN -1;
  ELSEIF ((a1 = a2) AND (b1 = b2) AND (c1 = c2) AND (d1 = d2)) THEN  
    RETURN 0;
  ELSE
    RETURN 1;
  END IF;
END $$

and its creation 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)

This is almost the same as this question, but my function does not read any SQL data, it is simple, deterministic and I see no reason why it should require any extra privileges. It is not clear to me from the documentation if the SUPER privilege is required for creating all functions (which would be ridiculous, making stored functions unavailable to many users, everyone who does not have access to their database configuration). I do not even know if the function works, this was the first thing that came to mind, but the syntax should be correct (the delimiter is set in PHPMyAdmin). Getting all data from database and comparing them in the PHP application can be done, but I think it is easiest done this way. Is it possible? Does anybody have a better solution for comparing versions?

Answer 1

You can adjust the global variable for that:

/* allows to create functions as not root */
SET GLOBAL log_bin_trust_function_creators = 1;

I use this in setup-sql files for initializing a database for restoring a dump.

Answer 2

I have circumvented the problem by making a long and therefore ugly database query in PHP. Maybe using stored procedure instead of function would be better. After searching I found in the documentation here this statement:

The current conditions on the use of stored functions in MySQL 5.5 can be summarized as follows. [...] To create or alter a stored function, you must have the SUPER privilege, in addition to the CREATE ROUTINE or ALTER ROUTINE privilege that is normally required.

So indeed, if binary logging is on, you really need SUPER privilege to create stored functions. This severely limits the use of stored functions on bigger servers and in my opinion decreases the value of the whole DBMS. Looks like the classical "it is not a bug, it is a feature". I fear to think about what happens to stored functions if the database server is restarted after changing binary logging to on.