Ошибка 1418 mysql

There are two ways to fix this:

1. Execute the following in the MySQL console:

   SET GLOBAL log_bin_trust_function_creators = 1;

2. 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.

Specific error:

When using mysql to create, call stored procedures, functions and triggers, there will be an error symbol of 1418 error.

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)

After some Baidu, it is summarized as follows:

Because CREATE PROCEDURE, CREATE FUNCTION, ALTER PROCEDURE, ALTER FUNCTION, CALL, DROP PROCEDURE, DROP FUNCTION and other statements will be written into the binary log and then executed on the slave server. However, an indeterminate subroutine (storage procedure, function, trigger) that performs an update is not repeatable. Execution on the slave server (relative to the master server) may result in the recovery of the data different from the original data. The server is different from the primary server.

In order to solve this problem, MySQL mandates:

On the primary server, the create or replace subroutine will be rejected unless the subroutine is declared deterministic or does not change the data. This means that when creating a subroutine, you must either declare it to be deterministic or it does not change the data.

There are two ways to declare:

First: Whether the statement is deterministic

DETERMINISTIC and NOT DETERMINISTIC indicate whether a subroutine always produces the same result for a given input.

If no feature is given, the default is NOT DETERMINISTIC, so DETERMINISTIC must be explicitly specified to declare a subroutine to be deterministic.

The point here is that using the NOW() function (or its synonym) or the RAND() function does not make a subroutine non-deterministic. For NOW(), the binary log includes the timestamp and will be executed correctly. RAND() can be copied correctly as long as it is called once in a subroutine. Therefore, it can be considered that the timestamp and random number seed are deterministic inputs of the subroutine, which are the same on the primary server and the secondary server.

Second: Declare whether the data will change

CONTAINS SQL, NO SQL, READS SQL DATA, MODIFIES SQL is used to indicate whether a subroutine reads or writes data.

Regardless of NO SQL or READS SQL DATA, the subroutine does not change the data, but one must be explicitly specified, because if specified, the default specification is CONTAINS SQL.

By default, if a CREATE PROCEDURE or CREATE FUNCTION statement is allowed to be accepted, one of DETERMINISTIC or NO SQL and READS SQL DATA must be explicitly specified, otherwise a 1418 error will result.

Solution:

There are also two solutions:

The first is to declare one of DETERMINISTIC or NO SQL and READS SQL DATA when creating a subroutine (stored procedure, function, trigger), for example:

CREATE DEFINER = CURRENT_USER PROCEDURE `NewProc`()
    DETERMINISTIC
BEGIN
 #Routine body goes here...
END;

The second is the creator of the trusted subroutine. It prohibits the requirement of the SUPER permission when creating and modifying the subroutine. Set the log_bin_trust_routine_creators global system variable to 1. There are three ways to set it up:

1. Execute SET GLOBAL log_bin_trust_function_creators = 1 on the client.

2. When MySQL starts, add —log-bin-trust-function-creators to select the parameter and set the parameter to 1.

3. Add log-bin-trust-function-creators=1 to the [mysqld] section of the MySQL configuration file my.ini or my.cnf.

Article Source:

APACHE, PHP and MYSQL LOGOS

This error occurred when you have to execute SQL file that contains function. i am trying to import SQL file using terminal like this:

mysql -u root -p1234 my_database < dump.sql

So there’s many solution, when that error occurred. These 3 solutions for you solve this problem. I sometimes use the third one, because it do it permanently.

1. The first would be, when you dump into SQL file, please be always using command MYSQLDUMP, for example

mysqldump -u root -p1234 my_database > dump.sql

Always use that as the main dumping tool, using exporting tool for example from MySQL Workbench or Navicat, sometimes that dump file will not working in the importing process. Using MYSQLDUMP commands always work perfectly.

2. In the MySQL console, execute the following commands

SET GLOBAL log_bin_trust_function_creators = 1;

3. Edit the mysql.ini configuration file, and add this line, require MySQL restart

log_bin_trust_function_creators = 1;

That’s all the three solutions i can provide.