1

I am able to create mysql stored procedures but not stored functions.

example:

DELIMITER $$;
DROP FUNCTION IF EXISTS `interkm_db1`.`temp`$$
CREATE FUNCTION `interkm_db1`.`temp` ()
    RETURNS int
BEGIN
    return 1;
END$$
DELIMITER ;$$

Error Code : 1418 This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you might want to use the less safe log_bin_trust_function_creators variable)

It still complains that it does not have SUPER privilege when I use the DETERMINISTIC statement.

This is an ISP hosted mysql installation. 

SHOW VARIABLES LIKE "%version%";
protocol_version    10
version 5.1.61-0+squeeze1-log
version_comment (Debian)
version_compile_machine i486
version_compile_os  debian-linux-gnu

MORE DETAIL I only have all PRIVILEGES to my database on

GRANT ALL PRIVILEGES ON interkm_db1.* TO 'interkm_1'@'%' GRANT USAGE ON . TO 'interkm_1'@'%' IDENTIFIED BY PASSWORD '-------' GRANT ALL PRIVILEGES ON interkm_db1.* TO 'interkm_1'@'%'

Sydwell
  • 111
  • 4
  • Are you doing this as root? Can you post the exact statement you are executing and the exact error message? – Khaled May 30 '12 at 15:03

2 Answers2

3

Reference source: http://dev.mysql.com/doc/refman/5.1/en/stored-programs-logging.html

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. (Depending on the DEFINER value in the function definition, SUPER might be required regardless of whether binary logging is enabled. See Section 13.1.9, “CREATE PROCEDURE and CREATE FUNCTION Syntax”.)

Since this is a shared installation (ISP-hosted), you're unlikely to get super privileges. That allows a great degree of full-system control.

Besides that, you must also include one of the 3 things mentioned so the binlogging / replication system knows how to handle your function had you had the privileges to use it.

Jeff Ferland
  • 20,239
  • 2
  • 61
  • 85
  • But I created stored functions on the same installation before! What has changed? – Sydwell May 30 '12 at 15:31
  • @Sydwell as simply as I can say: I have no way of knowing what in your environment may have changed. – Jeff Ferland May 30 '12 at 17:24
  • @JeffFerland how do you solve the problem when you have to remove the function form the MySQL db? I mean you have to code it in php but it seems like you loose some functionality. Also, I know that in MS-SQL it's better to have the code on the MS-SQL server because it gets "compiled" and optimized, what about MySQL? Thanks! – Adam Mendoza Jan 06 '15 at 20:36
0

Solved by recreating database!

I guess it is probably a standard solution, but I almost panic when it happen to me.

So to those that encounter a similar problem.

Try recreating your database.

As to why it broke in the first place, I still don't have a clue

Sydwell
  • 111
  • 4