Mysql Error 1418 Hy000 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 of this site About Us Learn more about Stack Overflow the mysql error 1419 company Business Learn more about hiring developers or posting ads with us Stack Overflow mysql reads sql data Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 6.2 log_bin_trust_function_creators rds million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled up vote 21 down mysql function deterministic 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 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
Log_bin_trust_function_creators Aws
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 carefully to have a healthy replication. DETERMINISTIC A routine is considered “deterministic” if it always produces the same result for the same input parameters and NOT DETERMINISTIC otherwise. This is mostly used with string or math processing, but not limited to that. NOT DETERMINISTIC Opposite of "DETERMINISTIC". "If neither DETERMINISTIC nor NOT DETERMINISTIC is given in the routine definition, the default is NOT DETERM
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
Aws Mysql Super Privilege
this 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 mysql grant super privilege 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: 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 http://stackoverflow.com/questions/4920386/create-function-error-this-function-has-none-of-deterministic-no-sql-or-reads SET 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
Detected You currently have javascript disabled. Several functions may not work. Please re-enable javascript to access full functionality. https://forums.phpfreaks.com/topic/169576-solved-error-hy000this-function-has-none-of-deterministic-no-sql-or-reads-sql-data/ [SOLVED] ERROR HY000:This function has none of DETERMINISTIC, NO SQL, or READS https://www.pythian.com/blog/non-deterministic-functions-and-the-binary-log/ SQL DATA Started by zohab, Aug 10 2009 09:52 AM Please log in to reply 2 replies to this topic #1 zohab zohab Advanced Member Members 223 posts Posted 10 August 2009 - 09:52 AM hi I am getting following error when i execute triggerERROR HY000: This function mysql error has none of DETERMINISTIC, NO SQL, or READS SQL DATA in itsdeclaration and binary logging is enabled (you *might* want to use the less safelog_bin_trust_function_creators variable)any idea?thanks in advance. Back to top #2 fenway fenway MySQL Si-Fu / PHP Resident Alien Staff Alumni 16,199 posts LocationToronto, ON Posted 10 August 2009 - 01:31 PM This is referring to the mysql error 1418 decllaration of an SP... Seriously... if people don't start reading this before posting, I'm going to consider not answering at all. Back to top #3 roshansn roshansn Newbie New Members 1 posts Posted 27 August 2009 - 12:35 PM Go through this link.http://mvnrepository...erministic.htmlIt will be help to fix your problem Back to top Back to MySQL Help · Next Unread Topic → 1 user(s) are reading this topic 0 members, 1 guests, 0 anonymous users Reply to quoted postsClear PHP Freaks → SQL / Database Forums → MySQL Help Privacy Policy Forum Rules & Terms of Service · Change Theme IP.Board Mobile PHP Freaks PHP Freaks - English (USA) English (USA) PHP Freaks - English (USA) Mark Community Read Forums Members Pages Mark all as read Help Community Forum Software by IP.BoardLicensed to: PHP Freaks Sign In Use Facebook Use Twitter Need an account? Register now! Username Password I've forgotten my password Remember me This is not recommended for shared computers Sign in anonymously Don't add me to the active users list Privacy Policy
ITProfessional ServicesIT Strategy + Consulting ServicesImplementation ServicesManaged ServicesTechnology PracticesAdvanced AnalyticsBig DataCloudDatabasesDevOpsInfrastructure ManagementTechnologiesAmazon Web ServicesMicrosoft AzureGoogle Cloud PlatformCassandraHadoopMySQLOracleOracle EBSMicrosoft SQL ServerResourcesOverviewWhite PaperseBooksWebinarsData SheetsArticlesPresentationsVideosClientsAboutLeadership TeamBoard of DirectorsExpertsCareersSocial ResponsibilityPythia IndexNews & EventsWorldwide OfficesContactBlog CONTACT US Your source for technical trends, tips, and best practices from Pythian experts Subscribe // non-idevice version of form ?> // idevice version of form ?> Privacy Policy Business Insights Technical Track Pythian Life Non-deterministic Functions and the Binary Log by Laine CampbellNovember 11, 2011 Posted in: Technical Track Tags: MySQL I wrote this post because I ran across this issue when debugging why tables with triggers/functions that were not getting replicated to slaves. The problem appears when binlog_format is set up as STATEMENT. You can check the ‘change log’ checking the following link for more information about default values [1].If you have non-deterministic functions [2] that insert or modify your data with dynamic SQL like this : DELIMITER $$ CREATE FUNCTION `insert_dynamic_data` (in_param VARCHAR(30)) RETURNS bigint(20) BEGIN …. INSERT INTO `table_to_ins` (param) VALUES(in_param); … /* You can also add here @@warning_count or row_count() check and return -1 to catch errors*/ return LAST_INSERT_ID(); END $$
The problem starts when you plan to user “replication” through binary logs events. In that cases, you must consider:"If a stored program that modifies data is nondeterministic, it is not repeatable. This can result in different data on a master and slave, or cause restored data to differ from the original data." [3]When you create a function, by default is NOT DETERMINISTIC, which means that for each execution could the code will be executed again. In the other hand, DETERMINISTIC will check if the parameters are the same and if is that condition comes true, will return the same result as before without executing again the code.If you want to create the function with the binlog_format in STATEMENT and log_bin activated without the log_bin_trust_function_creators, you will get the following error:Version 5.1.41: mysq