ERROR 1418 (HY000): This function has none of DETERMINISTIC, no SQL, or reads SQL DATA..., hy000deterministic

ERROR 1418 (HY000): This function has none of DETERMINISTIC, no SQL, or reads SQL DATA..., hy000deterministic

Create function today

CREATE FUNCTION `func_get_split_string_total`(f_string varchar(1000),f_delimiter varchar(5))

 RETURNS int(11)BEGIN 

 return 1+(length(f_string) - length(replace(f_string,f_delimiter,'')));

END

Error message:

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 checking some information, the solution is to set log_bin_trust_function_creators = 1.

But what does the log_bin_trust_function_creators parameter do? What is the impact of enabling it?

Next, we will introduce the related functions and impacts:

The features of the binary log function for storing subroutine statements are described in the list below. Some entries indicate the issues you should be aware.

Create procedure, create function, alter procedure, and alter function statements are written into binary logs, CALL, drop procedure, and drop function.

However, there is a security implication for replication: to CREATE a sub-program, the user must have the create routine permission, however, users with this permission cannot write a subroutine to perform any operations on the slave server. Because the SQL thread on the slave server runs with full permissions. For example, if the master server and slave server respectively have server ID values 1 and 2, the user on the master server may create and call the following program:

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 binary logs, so the slave server will execute them. Because the SQL thread has full permissions, it will remove the accounting database.

To avoid this risk, MySQL 5.1 requires that the creator of the storage program and function must have the SUPER permission in addition to the create routine permission. Similarly, to use alter procedure or alter function, you must have the SUPER permission in addition to the alter routine permission. If you do not have the SUPER permission, an error will occur:
ERROR 1419 (HY000): You do not have the SUPER privilege and binary logging is enabled (you * might * want to use the less safe log_bin_trust_routine_creators variable)

You may not want to force the subprogram creator to have the SUPER permission. For example, all users on your system with the create routine permission may be experienced application developers. To disable the SUPER permission, set the global system variable log_bin_trust_routine_creators to 1. By default, the value of this variable is 0.

If the binary log function is not allowed, log_bin_trust_routine_creators is not used. The SUPER permission is required for subprogram creation.
I. An undefined subroutine that executes updates cannot be repeated. It has two unsatisfactory effects:
Ii. It will make the slave server different from the master server.
Iii. The recovered data is different from the original data.

To solve these problems, MySQL enforces the following requirements: On the master server, unless the subroutine is declared as deterministic or data is not changed, the creation or replacement subroutine will be rejected. This means that when you create a child program, you must either declare that it is deterministic or that it does not change data. Two sets of sub-program features are applicable here:
I. 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 NOTDETERMINISTIC. Therefore, you must specify a terministic statement to declare that a subprogram is DETERMINISTIC.

Ii. Using the NOW () function (or its synonym) or RAND () function is not necessary to make it a subprogram non-deterministic. For NOW (), binary logs include timestamps and are correctly copied. RAND () can be correctly copied as long as it is called once in a subroutine. (You can think of the subprogram execution Timestamp and random number seed as input without any doubt. They are the same on the master server and slave server .)
Iii. contains SQL, no SQL, reads SQL data, and modifies SQL DATA provide information about whether the subroutine READS or writes DATA. Both no SQL and reads SQL DATA indicate that the subroutine does not change the DATA, but you must clearly specify one of them, because if any of these features are not given, the default feature is contains SQL.

When binlog is enabled, a create procedure or create function statement must be accepted. either DETERMINISTIC or no SQL or reads SQL DATA must be explicitly specified. Otherwise, the following error occurs:
ERROR 1418 (HY000): This routine 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_routine_creators variable ).

Note that the subroutine nature is evaluated based on the Creator's "honesty": MySQL does not check whether declared deterministic subroutines do not contain statements that generate non-deterministic results.

If no error is returned for the subroutine, the CALL statement is written into the binary log; otherwise, the CALL statement is not written. When a sub-program fails to modify data, you will receive the following warning:
ERROR 1417 (HY000): A routine failed and has neither no SQL nor reads SQL DATA in its declaration and binary logging is enabled; if non-transactional tables were updated, the binary log will miss their changes.

This logging behavior may cause problems. If a sub-program 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 situation, you should use the interactive table in the subroutine and modify the table within the interactive action.

In a sub-program, if you use the IGNORE keyword in INSERT, DELETE, or UPDATE to IGNORE the error, a partial UPDATE may occur, but no error is generated. Such statements are logged and copied normally.

If a stored function is called in a statement such as SELECT without modifying data, function execution will not be written into binary logs even if the function itself changes data. This log recorded behavior may cause problems. Assume that the function myfunc () is defined as follows:

CREATE FUNCTION myfunc () 

RETURNS INTBEGININSERT INTO t (i) VALUES(1);

RETURN 0;

END;

According to the above definition, the following statement modifies table t because myfunc () modifies table t, but the statement is not written into binary logs because it is a SELECT statement:
SELECT myfunc ();

Statements executed in a sub-program are not written into binary logs. If you publish the following statements:
Create procedure mysp insert into t VALUES (1 );
CALL mysp;

In this example, the create procedure and CALL statements appear in the binary log, but the INSERT statements do not. On the slave server, when deciding which event to copy to the autonomous server, the following restrictions are applied: -- replicate-*-table rules are not applicable to CALL statements or subprograms: in these cases, "Copy!" is always returned!"

The TRIGGER program is similar to a storage function, so the comment above applies to the TRIGGER program. Except for the following situations: create trigger has no optional DETERMINISTIC features, so the TRIGGER program is assumed to be always DETERMINISTIC. However, this assumption is invalid in some cases. For example, the UUID () function is non-deterministic (cannot be copied ). You should be careful to use this function in the trigger program.

The trigger program cannot update the table currently, but it will be supported in the future. For this reason, if you do not have the SUPER permission and log_bin_trust_routine_creators is set to 0, the error message is similar to the error message generated by the storage subroutine and create trigger.

This is the end of the Introduction. To sum up, log_bin_trust_function_creators is used to define create procedure or create function, to block super permissions and remove them, you 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, not restricted ).

During synchronization, if log_bin_trust_function_creators is enabled and the function for modifying table data is created, the master-slave synchronization may be inconsistent. When using binlog for data recovery, data recovery may be lost due to incomplete recovery.

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