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?
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.
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
i use this command on ubuntu:
mysql -u root -p
and enter mysql server error and execute this command in mysql:
SET GLOBAL log_bin_trust_function_creators = 1;
When your function is deterministic, you are safe to declare it to be deterministic. The location of "DETERMINISTIC" keyword is as follows.
following Donald's comment:
This variable applies when binary logging is enabled.
All I had to do was:
That step out that import problem.
(Then I'll review the programmer's code to suggest an improvement)
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
MySQL auto read the configuration from conf.d and mysql.conf.d directory. So create custom.cnf in the directory /etc/mysql/conf.d/ and add the following configs.
[mysqld]
log_bin_trust_function_creators=1
The root user permission is required to create and save the file. Then restart the MySQL server.
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.
