Mysql Procedures,functions and triggers – Binary logging
If you have enabled binary logging on MySql server you are running, you may get error below while creating Stored Procedures or functions and triggers in your database:
ERROR 1418 (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)
First we need to understand, why we do binary logging, two main reasons:
a. Replication : Slaves use binary logging for replicating data from master.
b. Backup and Recovery: Can be used as backup and restored in case required.
Procedures, Functions and Triggers are also logged in binary log but not as calling statement but SQL statements inside it. Logging has to make sure that it is doing fine and replication or recovery can be done correctly, so error above is the result.
If you see this error and try to understand it, It is saying that if function has following two types it is OK with binary logging:
1. Deterministic function : There should be no use of Non-deterministic functions like UUID(), now() or RAND() which can give variable results, so it is problematic in binary logging.
2. NO SQL or READ SQL DATA: If one of these types are specified in declaration of Procedure in tells server that procedure is not modifying the data and only reads it.
How to fix this if you are sure about your procedures:
mysql> SET GLOBAL log_bin_trust_function_creators = 1;
Now you will be able to able to create your Procedures, Functions and Triggers with out this error.
Most Commented Posts
If you enjoyed this post, please consider to leave a comment or subscribe to the feed and get future articles delivered to your feed reader.
Comments
Thanks Fazileh for your comment. I spent significant time on this problem so explained here for rest of the world.
Dear friends, this sentence solves the problem, until the server is rebooted, then you have to execute again the sentence. Can it be solved defnitely? Thanks in advance
You can also set this variable by using the –log-bin-trust-function-creators=1 option when starting the server.
Hi,
Thanks for your clear explanation. It was really perfect and easy to understand. My problem solved with this article.
Good luck
Fazileh