Mysql Error Error 1418
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 mysql error 1419 the company Business Learn more about hiring developers or posting ads with us Stack
Mysql Set Log_bin_trust_function_creators
Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of mysql function reads sql data 6.2 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
Mysql Function Deterministic
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 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 aws mysql super privilege 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 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 defini
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 company
Log_bin_trust_function_creators Rds
Business Learn more about hiring developers or posting ads with us Stack Overflow Questions
Mysql Grant Super Privilege
Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 6.2 million programmers, log_bin_trust_function_creators aws just like you, helping each other. Join them; it only takes a minute: Sign up mysql error when adding function up vote 2 down vote favorite 5 The following error is returned when trying to use http://stackoverflow.com/questions/26015160/deterministic-no-sql-or-reads-sql-data-in-its-declaration-and-binary-logging-i a MySQL function.. #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) I couple of days ago I started using replication..? Don't know if this could have an incluence on it?! But I know one thing - it has worked :) If I try to override (add the function again) the http://stackoverflow.com/questions/11316008/mysql-error-when-adding-function same error occurs.. The function DELIMITER $$ DROP FUNCTION IF EXISTS `stock_in_stock_ids` $$ CREATE DEFINER=`dynaccount`@`localhost` FUNCTION `stock_in_stock_ids`(_running_total_limit INT, _product_id INT, _group_id INT) RETURNS TEXT BEGIN DECLARE done INT DEFAULT FALSE; DECLARE _running_count INT default 0; DECLARE _id INT; DECLARE _count INT; DECLARE _ids TEXT DEFAULT NULL; DECLARE _cur CURSOR FOR SELECT id, count FROM stock WHERE group_id=_group_id && type=2 && product_id=_product_id ORDER BY time DESC, id DESC; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; OPEN _cur; read_loop: LOOP FETCH _cur INTO _id, _count; IF done THEN SET _ids = '0'; LEAVE read_loop; END IF; SET _running_count = _running_count + _count; SET _ids = CONCAT_WS(',', _ids, _id); IF _running_count >= _running_total_limit THEN LEAVE read_loop; END IF; END LOOP read_loop; CLOSE _cur; RETURN _ids; END $$ DELIMITER ; mysql replication running-total share|improve this question edited Jul 3 '12 at 17:28 asked Jul 3 '12 at 17:23 clarkk 4,56727102175 add a comment| 3 Answers 3 active oldest votes up vote 6 down vote accepted here you have a post that saves me in the past about this error: http://forum.9kgames.com/default.aspx?g=posts&m=17 in my case I only had to specify the DETERMINISTIC in the create function: CREATE DEFINER=`dynaccount`@`localhost` FUNCTION `stock_in_stock_ids`(_running_total_limit INT, _product_id INT, _group_id INT) RETURNS TEXT DETERMINISTIC READS SQL DATA hope it helps share|improve this answer answered Jul 3 '12 at 17
and Logo DesignPortfolioBlogContact DETERMINISTIC, NO SQL, or READS SQL DATA in its declarationHome » Blog » DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration Written by Aldwin Galapon http://www.jamediasolutions.com/blog/deterministic-no-sql-or-reads-sql-data-in-its-declaration.html 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 https://forums.aws.amazon.com/message.jspa?messageID=158861 trying to alter one of my stored procedures remotely on a master server. After some research, I ended up getting information from "Binary Logging of Stored Programs".From MySQL mysql error 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 one of DETERMINISTIC, NO SQL, or READS SQL DATA must be specified explicitly. mysql error error 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 modify data), set the global log_bin_trust_function_creators system variable to 1. By default, this variable has a value of 0, but you can change it like