ERROR 1418 (HY000): This function has none of deterministic, NO SQL, or READS SQL DATA ...

Create function today

CREATE FUNCTION ' func_get_split_string_total ' (f_string varchar (+), F_delimiter varchar (5)) RETURNS Int (one) BEGIN  return 1+ (Length (f_string)-Length (replace (F_string,f_delimiter, '))); END

Error message:

ERROR 1418 (HY000): This function has none of the deterministic, NO SQL, or READS SQL DATA in its declaration and binary Loggi NG is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)

Checked some information, the solution is to put log_bin_trust_function_creators = 1

But what does log_bin_trust_function_creators do with this parameter? What are the effects of opening?

Let's start by introducing the relevant effects and effects:

The features of the binary logging feature for stored subroutine statements are described in the following list. Some articles point out the problems you should be aware of.

Create PROCEDURE, create FUNCTION, Alter PROCEDURE, and ALTER function statements are written into the binary log, call, Drop PROCEDURE, and drop function also.

However, there is a security implication for replication: to create a subroutine, the user must have the Create routine permission, but a user with this permission cannot write a subroutine to perform any action from the server. Because the SQL thread on the slave server runs with full permissions. For example, if the primary server and the slave server have server ID values 1 and 2, the user on the primary server may create and invoke one of the following programs:

Mysql> delimiter//mysql> CREATE PROCEDURE mysp () begin-> IF @ @server_id =2 then DROP DATABASE accounting; END if;-> end;->//mysql> delimiter;mysql> call MYSP ();

The CREATE procedure and call statements will be written into the binary log, so they will be executed from the server. Because full permissions are threads from the SQL line, it will remove the accounting database.

    to make the server that allows binary logging to avoid this danger, MySQL 5.1 already requires the creator of the stored programs and functions to be in addition to the permissions that are normally required for create routine. You must also have super privileges. Similarly, to use ALTER procedure or ALTER FUNCTION, you must have super privileges in addition to alter routine permissions. Without SUPER privilege, an error will occur:
    Error 1419 (HY000): You do not have the Super privilege and binary logging are en abled (*might* want to use the less safe log_bin_trust_routine_creators variable)

You may not want to force the creator of the subroutine to have super privileges. For example, all users on your system who have create routine permissions may be experienced application developers. To disable the requirement for Super permissions, set the Log_bin_trust_routine_creators Global system variable to 1. By default, this variable has a value of 0.

    If the binary logging feature is not allowed, log_bin_ Trust_routine_creators is not used, subroutine creation requires Super permissions.
I.    a non-deterministic subroutine that performs updates is not repeatable, and it can have two unpleasant effects:
ii.  it makes the slave server different from the primary server.
III. The recovered data differs from the original data.

    To solve these problems, MySQL enforces the following requirements: On the primary server, the creation or replacement of a subroutine is rejected unless the subroutine is declared deterministic or does not change the data. This means that when you create a subroutine, you must either declare it to be deterministic, or it will not change the data. The two-sheath program features apply here:
I.  deterministic and NOT Deterministic indicates whether a subroutine always produces the same result for a given input. If no feature is given, the default is notdeterministic, so you must explicitly specify deterministic to declare a subroutine to be deterministic.

II. Using the Now () function (or its synonymous) or the rand () function is not necessary to make also a subroutine non-deterministic. For now (), the binary log includes a timestamp and is copied correctly. RAND () can be copied correctly as long as it is called within a subroutine. (You can assume that the subroutine performs timestamps and random number seeds as a no doubt input, they are the same on the primary server and from the server.) ）
Iii. CONTAINS sql, NO sql, READS SQL data, and modifies SQL provides the information that subroutines are read or write data. No SQL or reads SQL data indicates that the subroutine does not change the data, but you must clearly indicate one of these, because if any of these features are not given, the default feature is contains SQL.

When you turn on Binlog, want a create PROCEDURE or create FUNCTION statement to be accepted, deterministic or NO SQL and reads SQL DATA must be specified clearly, otherwise it will The following error is generated:
ERROR 1418 (HY000): This routine have none of deterministic, NO sql,or READS SQL DATA in its declaration and B Inary logging is enabled (you *might* want to use the less safe log_bin_trust_routine_creators variable).

     Note that the evaluation of the subroutine's nature is based on the creator's "honesty": MySQL does not check whether a subroutine declared as deterministic does not contain statements that produce non-deterministic results.

If the subroutine returns error-free, the call statement is written into the binary log, otherwise it is not written. When a subroutine fails to modify the data, you get a warning like this:
ERROR 1417 (HY000): A routine failed and has neither NO SQL nor READS SQL DATA in it declaration and binary logging is enabled; if non-transactional tables were updated, the binary log would miss their changes.

This logging behavior can potentially cause problems. If a subroutine partially modifies a non-interactive table (such as a MyISAM table able) and returns an error, the binary log will reflect these changes. To prevent this, you should use the interaction table in the subroutine and modify the table within the interactive action.

Within a subroutine, if you use the Ignore keyword in the insert, DELETE, or update to ignore the error, a partial update may occur, but no error occurs. Such statements are logged and copied normally.

If a stored function is called within a statement that does not modify the data, such as SELECT, even if the function itself changes the data, the execution of the function will not be written into the binary log. This logging behavior can potentially cause problems. Suppose the function myfunc () is defined as follows:

CREATE FUNCTION myfunc () RETURNS Intbegininsert into T (i) VALUES (1); RETURN 0; END;

As defined above, the following statement modifies table T because MyFunc () modifies table T, but the statement is not written into the binary log because it is a SELECT statement:
SELECT MyFunc ();

Statements executed within a subroutine are not written into a binary log. If you post the following statement:
CREATE PROCEDURE mysp INSERT into T VALUES (1);
Call MYSP;

For this example, the CREATE PROCEDURE and call statements appear in the binary log, but the INSERT statement does not appear. On the slave server, when deciding which event to replicate to the autonomic server, the following restrictions are applied: the--replicate-*-table rule does not apply to statements within a call statement or subroutine: In these cases, always return "Copy!" ”

The trigger is similar to a stored function, so the previous comment applies to the trigger, except for the following: CREATE Trigger has no optional deterministic feature, so the trigger is assumed to be always deterministic. However, this hypothesis is illegal in some cases. For example, the UUID () function is nondeterministic (cannot be copied). You should be careful to use this function in the triggering program.

The trigger is currently unable to update the table, but will be supported in the future. For this reason, if you do not have super privileges and log_bin_trust_routine_creators is set to 0, the error message you get is similar to the error message generated by the store subroutine and create trigger.

This is the end of the introduction, finally to summarize,the role of Log_bin_trust_function_creators is to define the CREATE procedure or create function, Masking super permissions and removal must clearly specify the requirements for deterministic or no SQL and reads SQL data (deterministic or no SQL and reads SQL data are only declared as Use, not constraints ).

In synchronization, if you enable log_bin_trust_function_creators, creating a function that modifies table data can result in inconsistent master-slave synchronization. When using Binlog to do data recovery, it may cause the recovery data incomplete and lost, the reason explained above.

Attached: Official Related introduction http://dev.mysql.com/doc/refman/5.7/en/stored-programs-logging.html

ERROR 1418 (HY000): This function has none of deterministic, NO SQL, or READS SQL DATA ...