This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary

Aug 27, 2009 Posted by Roshan Priyadarshana
Today I was struggling while creating a funtion on mysql database. It can be executed with the root privilageds but shows an error massage when it is executed with low privilages. The error massage is shown bellow.
ERROR HY000: 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)
>>To avoid this error you must do one of the following.
  1. Specify the one or more of DETERMINISTIC, NO SQL, and/or READS SQL DATA key words in your stored function definition.
  2. Set the value of "log_bin_trust_function_creators" to '1',
    (mysql> SET GLOBAL log_bin_trust_function_creators = 1;)
You can also set this variable by using the --log-bin-trust-function-creators=1 option when starting the server.

>>The current conditions on the use of stored functions in MySQL 5.0 can be summarized as follows. These conditions do not apply to stored procedures and they do not apply unless binary logging is enabled.
  1. 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.
  2. When you create a stored function, you must declare either that it is deterministic or that it does not modify data. Otherwise, it may be unsafe for data recovery or replication.
>>For more information refer this article "Binary Logging of Stored Programs".
Labels: ,
  1. hi
    it's so useful for me.

  2. dinesh

    good post.. solved my problem in seconds. Thanks

  3. Anonymous

    Wow it useful for me.
    thanks alot

  4. coolshah

    in which file we r making those changes? is it in db or mysql ?

  5. coolshah

    can u please specify please I am new to this. is it in my.ini file? thanks in advance

  6. no.. it is not in the ini file... login to mysql using terminal. If you use windows go to the mysql client and execute command. If you use linux you can directly login to mysql using terminal.

  7. sumiface
  8. Anonymous
  9. it works for me, thanks.

  10. It help me a lot, thank you.

  11. thank you your post, it is useful for me!

  12. Thanks for posting the quick fix....

  13. Anonymous
  14. Thank god... found some simple explanation.
    please let me know the difference between DETERMINISTIC VS READS SQL DATA.
    And what is NO SQL??
    plz reply.

  15. Anonymous

    thanks boss.. it worked pap

Post a Comment