DETERMINISTIC NO SQL or READS SQL DATA in its declaration and binary logging is enabled

Question

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

User · Answer

following Donald s comment      This variable applies when binary logging is enabled    All I had to do was    disabled log bin in my cnf   log bin  restart mysql import DB enable log bin restart mysql   That step out that import problem    Then I ll review the programmer s code to suggest an improvement

User · Answer

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    To fix this issue add following lines After Return and Before Begin statement   READS SQL DATA DETERMINISTIC   For Example    CREATE FUNCTION f2   RETURNS CHAR 36  CHARACTER SET utf8   ADD HERE    READS SQL DATA DETERMINISTIC BEGIN   For more detail about this issue please read Here

User · Answer

When your function is deterministic  you are safe to declare it to be deterministic  The location of  DETERMINISTIC  keyword is as follows

User · Answer

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    Binary Logging of Stored Programs     If binary logging is not enabled  log bin trust function creators does   not apply    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 DETERMINISTIC  To declare that a function is deterministic  you must specify DETERMINISTIC explicitly    So it seems that if no statement is made  MySQl will treat the function as  NOT DETERMINISTIC   This statement from manual is in contradiction with other statement from another area of manual which tells that    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   I personally got error in MySQL 5 5 if there is no declaration  so i always put at least one declaration of  DETERMINISTIC    NOT DETERMINISTIC    NO SQL  or  READS SQL DATA  regardless other declarations i may have   READS SQL DATA This explicitly tells to MySQL that the function will ONLY read data from databases  thus  it does not contain instructions that modify data  but it contains SQL instructions that read data  e q  SELECT    MODIFIES SQL DATA  This indicates that the routine contains statements that may write data  for example  it contain UPDATE  INSERT  DELETE or ALTER instructions    NO SQL This indicates that the routine contains no SQL statements   CONTAINS SQL This indicates that the routine contains SQL instructions  but does not contain statements that read or write data  This is the default if none of these characteristics is given explicitly  Examples of such statements are SELECT NOW    SELECT 10  b  SET  x   1 or DO RELEASE LOCK  abc    which execute but neither read nor write data    Note that there are MySQL functions that are not deterministic safe  such as  NOW    UUID    etc  which are likely to produce different results on different machines  so a user function that contains such instructions must be declared as NOT DETERMINISTIC  Also  a function that reads data from an unreplicated schema is clearly NONDETERMINISTIC        Assessment of the nature of a routine is based on the    honesty    of the   creator  MySQL does not check that a routine declared DETERMINISTIC is   free of statements that produce nondeterministic results  However    misdeclaring a routine might affect results or affect performance    Declaring a nondeterministic routine as DETERMINISTIC might lead to   unexpected results by causing the optimizer to make incorrect   execution plan choices  Declaring a deterministic routine as   NONDETERMINISTIC might diminish performance by causing available   optimizations not to be used

User · Answer

On Windows 10   I just solved this issue by doing the following    Goto my ini and add these 2 lines under  mysqld   skip-log-bin log bin trust function creators   1  restart MySQL service

User · Answer

Try setting the definer for the function!

So instead of

CREATE FUNCTION get_pet_owner

you will write something akin to

CREATE DEFINER=procadmin@% FUNCTION get_pet_owner

which ought to work if the user prodacmin has rights to create functions/procedures.

In my case the function worked when generated through MySQL Workbench but did not work when run directly as an SQL script. Making the changes above fixed the problem.

[mysql] DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled

The answer is

Examples related to mysql

Tags