MySQL error 1418 Cause Analysis and Solution, mysql1418

MySQL error 1418 Cause Analysis and Solution, mysql1418

Analysis and Solution to error 1418 in MySQL

Specific error:

When using mysql to create and call stored procedures, functions and triggers, the error code is 1418.

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 times, Baidu summarized as follows:

Because the statements such as create procedure, create function, alter procedure, alter function, CALL, drop procedure, and drop function are all written into binary logs and then executed on the slave server. However, an uncertain subroutine (stored procedures, functions, and triggers) that executes updates cannot be repeated and executed on the slave server (which is executed repeatedly with the master server) the recovered data may be different from the original data. The slave server is different from the master server.

To solve this problem, MySQL requires:

On the master server, unless the subroutine is declared deterministic or data is not changed, the creation or replacement subroutine will be rejected. This means that when creating a child program, you must either declare that it is deterministic or that it does not change the data.

There are two declaration methods:

First: whether the statement is deterministic

DETERMINISTIC and not deterministic indicate whether a subprogram always produces the same result for a given input.
If no feature is specified, the default value is NOT terministic. Therefore, DETERMINISTIC must be explicitly specified to declare that a subprogram is DETERMINISTIC.
It should be noted that the use of the NOW () function (or its synonym) or the RAND () function will not make a subprogram non-deterministic. For NOW (), binary logs include timestamps and are correctly executed. RAND () can be correctly replicated once called in a sub-program. Therefore, the timestamp and Random Seed are the deterministic input of subprograms, which are the same on the master server and slave server.

Type 2: whether the statement will change the data

Contains SQL, no SQL, reads SQL data, and modifies SQL are used to indicate whether the subprogram READS or writes DATA.
Both no SQL and reads SQL data indicate that the subroutine does not change the DATA, but one of them must be explicitly specified, Because if any one is specified, the default value is contains SQL.

By default, if you allow the create procedure or create function statement to be accepted, you must specify either DETERMINISTIC or no SQL or reads SQL DATA. Otherwise, a 1418 error will occur.

Solution:

There are two solutions,

The first type is declared as one of the DETERMINISTIC or no SQL and reads SQL DATA when creating a subroutine (stored procedure, function, trigger,

For example:

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

The second is the creator of the trust subroutine.To prohibit the SUPER permission when creating or modifying subprograms, set log_bin_trust_routine_creators to 1 as the global system variable. You can set three methods:

1. Execute set global log_bin_trust_function_creators = 1 on the client;
2. When MySQL is started, add the -- log-bin-trust-function-creators option 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.

Thank you for reading this article. I hope it will help you. Thank you for your support for this site!