Solution to the 1418 error code returned when MySQL creates a Function

The following is a reference clip:
ErrorCode: 1418
ThisfunctionhasnoneofDETERMINISTIC, NOSQL, orREADSSQLDATAinitsdeclarationandbinaryloggingisenabled (you * might * wanttousethelesssafelog_bin_trust_function_creatorsvariable)
(0 mstaken)

The solution is as follows:

1. mysql> set global log_bin_trust_function_creators = 1;

2. When the system is started -- log-bin-trust-function-creators = 1

3. Add a line of log-bin-trust-function-creators after marking [mysqld] In my. ini (my. conf in linux ).

If there is a 1418 error in the create function: you only need to execute set global log_bin_trust_routine_creators = 1;

Then there will be no problem with how to create a function (of course, your function must be correct). This is a bug in mysql. If you don't understand why, it will be okay to do so anyway.

In addition, you can add the following line [mysqld] log_bin_trust_routine_creators = 1 in the configuration file my. cnf;

This command can be run in the SQL editor environment without restarting the service.

MySQL has supported SP (Store Procedures) Since 5.0 ),
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) Assessment of the nature of a function is based on the "honesty" of the creator: MySQL does not check that a function declared DETERMINISTIC is free of statements that produce non-deterministic results.

To relax the preceding conditions on function creation (that you must have the SUPER privilege and that a function must be declared deterministic or to not modify data), set the global log_bin_trust_function_creators system variable to 1. by default, this variable has a value of 0, but you can change it like this:

Mysql> set global log_bin_trust_function_creators = 1;
You can also set this variable by using the -- log-bin-trust-function-creators = 1 option when starting the server.
1. mysql> set global log_bin_trust_function_creators = 1;
2. When the system is active, the value of -- log-bin-trust-function-creators is 1.
3. Add log-bin-trust-function-creators = 1 directly in the [mysqld] section of my. ini.
Of course, I think most people will use the third method,
OK... Thanks for learning my SP, good luck ..

Ii. mysql ERROR 1418 (HY000)

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)

We must specify whether our function is
1 DETERMINISTIC
2 no SQL does not have SQL statements, and of course data will not be modified
3 reads SQL DATA only READS DATA, and certainly does not modify the DATA
4 modifies SQL DATA to modify DATA
5 contains SQL statements

In function, only DETERMINISTIC, no SQL, and reads SQL DATA are supported. If bin-log is enabled, we must specify a parameter for our function.

We can disable this by setting the following parameters.

Set global log_bin_trust_function_creators = 1;

(You * might * want to use the less safe log_bin_trust_function_creators
Variable)