Mysql Error This Function Has None Of Deterministic
Contents |
Connectors More MySQL.com Downloads Developer Zone Section Menu: Documentation Home MySQL 5.7 Reference Manual Preface and Legal Notices General Information Installing and Upgrading MySQL Using MySQL as a Document mysql error 1419 Store Tutorial MySQL Programs MySQL Server Administration Security Backup and Recovery Optimization mysql reads sql data Language Structure Globalization Data Types Functions and Operators SQL Statement Syntax The InnoDB Storage Engine Alternative Storage Engines log_bin_trust_function_creators rds High Availability and Scalability Replication MySQL Cluster NDB 7.5 Partitioning Stored Programs and Views Defining Stored Programs Using Stored Routines (Procedures and Functions) Stored Routine Syntax Stored Routines and MySQL mysql function deterministic Privileges Stored Routine Metadata Stored Procedures, Functions, Triggers, and LAST_INSERT_ID() Using Triggers Trigger Syntax and Examples Trigger Metadata Using the Event Scheduler Event Scheduler Overview Event Scheduler Configuration Event Syntax Event Metadata Event Scheduler Status The Event Scheduler and MySQL Privileges Using Views View Syntax View Processing Algorithms Updatable and Insertable Views The View WITH CHECK OPTION Clause View Metadata Access
Log_bin_trust_function_creators Aws
Control for Stored Programs and Views Binary Logging of Stored Programs INFORMATION_SCHEMA Tables MySQL Performance Schema MySQL sys Schema Connectors and APIs Extending MySQL MySQL Enterprise Edition MySQL Workbench MySQL 5.7 Frequently Asked Questions Errors, Error Codes, and Common Problems Restrictions and Limits Indexes MySQL Glossary Related Documentation MySQL 5.7 Release Notes Download this Manual PDF (US Ltr) - 35.6Mb PDF (A4) - 35.6Mb PDF (RPM) - 34.6Mb EPUB - 8.7Mb HTML Download (TGZ) - 8.4Mb HTML Download (Zip) - 8.5Mb HTML Download (RPM) - 7.3Mb Eclipse Doc Plugin (TGZ) - 9.3Mb Eclipse Doc Plugin (Zip) - 11.4Mb Man Pages (TGZ) - 202.2Kb Man Pages (Zip) - 307.4Kb Info (Gzip) - 3.3Mb Info (Zip) - 3.3Mb Excerpts from this Manual MySQL Backup and Recovery MySQL Globalization MySQL Information Schema MySQL Installation Guide MySQL and Linux/Unix MySQL and OS X MySQL Partitioning MySQL Performance Schema MySQL Replication Using the MySQL Yum Repository MySQL Restrictions and Limitations Security in MySQL MySQL and Solaris Building MySQL from Source Starting and Stopping MySQL MySQL Tutorial MySQL and Windows MySQL Cluster NDB 7.5 v
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and
Log_bin_trust_function_creators Mariadb
policies of this site About Us Learn more about Stack Overflow the amazon rds super privilege company Business Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags log-bin-trust-function-creators Users Badges 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 https://dev.mysql.com/doc/refman/5.7/en/stored-programs-logging.html takes a minute: Sign up CREATE 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 http://stackoverflow.com/questions/4920386/create-function-error-this-function-has-none-of-deterministic-no-sql-or-reads DETERMINISTIC BEGIN DECLARE number INTEGER UNSIGNED; UPDATE Settings 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-funct
log in tour help Tour Start here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings http://dba.stackexchange.com/questions/108316/use-of-log-bin-trust-function-creators-in-mysql and policies of this site About Us Learn more about Stack Overflow the company Business Learn more about hiring developers or posting ads with us Database Administrators Questions Tags Users Badges Unanswered Ask Question _ Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. mysql error Join them; it only takes a minute: Sign up Here's how it works: Anybody can ask a question Anybody can answer The best answers are voted up and rise to the top Use of log_bin_trust_function_creators in MySQL up vote 0 down vote favorite What is the use of log_bin_trust_function_creators? Also, Is the value of the parameter dynamic? Will it change if mysql error this I restart my MySQL service? mysql mysql-5.5 share|improve this question edited Jul 28 '15 at 13:08 Hector 824215 asked Jul 28 '15 at 11:47 tesla747 645526 add a comment| 1 Answer 1 active oldest votes up vote 1 down vote accepted This variable controls whether binary logging should trust the stored function creators for not to create stored functions that will cause unsafe events. Eg. having UUID functions. This has been explained well in documentation: 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. 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) To relax the preceding conditions on function creation (that you must have the SUPER pri