Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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


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

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.

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

Reference source:

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
@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 6 '15 at 20:36

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.