Mysql Error 1418 This Function Has None Of Deterministic
Contents |
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies mysql error 1419 of this site About Us Learn more about Stack Overflow the company Business
Mysql Reads Sql Data
Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges mysql function deterministic Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 6.2 million programmers, just like you, helping each other. Join them; it only takes a minute: log_bin_trust_function_creators rds Sign up DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled up vote 21 down vote favorite 9 While importing the database in mysql, I have got following error: 1418 (HY000) at line 10185: This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want
Log_bin_trust_function_creators Aws
to use the less safe log_bin_trust_function_creators variable) I don't know which things i need to change. Can any one help me how to resolve this? mysql share|improve this question edited Sep 24 '14 at 11:06 Donal 13k52646 asked Sep 24 '14 at 10:57 ASR 71811132 add a comment| 2 Answers 2 active oldest votes up vote 42 down vote accepted There are two ways to fix this: Execute the following in the MySQL console: SET GLOBAL log_bin_trust_function_creators = 1; Add the following to the mysql.ini configuration file: log_bin_trust_function_creators = 1 The setting relaxes the checking for non-deterministic functions. Non-deterministic functions are functions that modify data (i.e. have update, insert or delete statement(s)). For more info, see here. Please note, if binary logging is NOT enabled, this setting does not apply. http://dev.mysql.com/doc/refman/5.0/en/stored-programs-logging.html If binary logging is not enabled, log_bin_trust_function_creators does not apply. http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#sysvar_log_bin_trust_function_creators This variable applies when binary logging is enabled. The best approach is a better understanding and use of deterministic declarations for stored functions. These declarations are used by MySQL to optimize the replication and it is a good thing to choose them caref
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this aws mysql super privilege site About Us Learn more about Stack Overflow the company Business Learn log_bin_trust_function_creators mariadb more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x
Mysql Grant Super Privilege
Dismiss Join the Stack Overflow Community Stack Overflow is a community of 6.2 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up CREATE http://stackoverflow.com/questions/26015160/deterministic-no-sql-or-reads-sql-data-in-its-declaration-and-binary-logging-i FUNCTION error “This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA” up vote 11 down vote favorite 2 Our database has a function to generate an order number. It reads a value from a Settings table, increments it, then returns the new value. For example: CREATE FUNCTION NextOrderNumber() RETURNS INTEGER UNSIGNED NOT DETERMINISTIC BEGIN DECLARE number INTEGER UNSIGNED; UPDATE Settings SET http://stackoverflow.com/questions/4920386/create-function-error-this-function-has-none-of-deterministic-no-sql-or-reads IntegerValue=LAST_INSERT_ID(IntegerValue+1) WHERE KeyName='NextOrderNumber'; SET number=LAST_INSERT_ID(); return number; END Note: Don't critique this function I know it has flaws it's just for illustration. We use this function as follows: INSERT INTO Orders(OrderNumber, ...) SELECT NextOrderNumber(), ... When binary logging is enabled, CREATE FUNCTION gives this error: 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) Regardless of what binlog_format is set, is there really a problem with the above function? According to my reading of the relevant MySQL page I can't see any reason why this function would be incompatible with replication, with either ROW or STATEMENT level binary logging. If the function is safe, setting the global log_bin_trust_function_creators=1 makes me uneasy. I don't want to disable this check for all functions, just this one. Could I instead just flag the function as NO SQL to suppress the warning? I tried it and it worked. Will this cause any problem? mysql stored-functions share|improve this question edited May 23 '13 at 5:51 Federico Razzoli 1,604615 asked Feb 7 '11 at 10:50
and Logo DesignPortfolioBlogContact DETERMINISTIC, NO SQL, or READS SQL DATA in its declarationHome » Blog » DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration http://www.jamediasolutions.com/blog/deterministic-no-sql-or-reads-sql-data-in-its-declaration.html Written by Aldwin Galapon posted on Sunday, November 11th, 2012 Read Comments (4)Recommended ( 0 )Print this PageEmail this Page 4.40 avg. rating (87% score) - 5 votes I experienced this error while trying to alter one of my stored procedures remotely on a master server. After some research, I ended up getting mysql error information from "Binary Logging of Stored Programs".From MySQL Reference in verbatim: 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.By default, for a CREATE FUNCTION statement to be accepted, at least mysql error 1418 one of DETERMINISTIC, NO SQL, or READS SQL DATA must be specified explicitly. Otherwise an error occurs: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)Further reading helped me arrive to the conclusion to the cause of this error:Cause: The error arises if the binary logging option, which is required for the replication, is turned on for the MySQL server.We can choose solutions listed below depending to our system requirements, for me, I opted on using the later.When creating or altering 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.To relax the preceding conditions on function creation (that you must have the SUPER privilege and that a function must be declared deterministic or to not mod