Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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'@'%'

share|improve this question

closed as off topic by Jeff Ferland, Khaled, Tim Brigham, Michael Hampton, rnxrx Aug 23 '12 at 16:56

Questions on Server Fault are expected to relate to professional server, networking, or related infrastructure administration within the scope defined by the community. Consider editing the question or leaving comments for improvement if you believe the question can be reworded to fit within the scope. Read more about reopening questions here.If this question can be reworded to fit the rules in the help center, please edit the question.

    
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 Answers 2

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.

share|improve this answer
    
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

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

share|improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.