The MySQL function cannot be created by a workaround

MySQL function can not be created, is a very troublesome problem, the following gives you a solution to this problem, if you have encountered similar problems, may wish to see.

Http://database.51cto.com/art/201010/229918.htm

When using a MySQL database, you can sometimes encounter situations where MySQL functions cannot be created. Here is a solution to the MySQL function can not create a problem, for your reference.

The error message is roughly similar:

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)
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)

The MySQL function cannot be created and is not enabled:

2. MySQL> Show variables like '%func% ';
4. +---------------------------------+-------+
6. | variable_name | Value |
8. +---------------------------------+-------+
10. | log_bin_trust_function_creators | OFF |
12. +---------------------------------+-------+
14. 1 row in Set (0.00 sec)
18. MySQL> Set global log_bin_trust_function_creators=1;
20. Query OK, 0 rows Affected (0.00 sec)
24. MySQL> Show variables like '%func% ';
26. +---------------------------------+-------+
28. | variable_name | Value |
30. +---------------------------------+-------+
32. | log_bin_trust_function_creators | On |
34. +---------------------------------+-------+
36. 1 row in Set (0.00 sec) MySQL>

==================

http://blog.csdn.net/ciwei007/article/details/15635151

1. Create a custom function in MySQL error message as follows:

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)

Workaround:

Mysql>set Global Log_bin_trust_function_creators=1;

Source Document

2. When creating function

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)

Reason:

This is when we open the bin-log and we have to specify whether our function is

1 Deterministic indeterminate

2 No SQL does not have SQL statements and of course does not modify the data

3 READS SQL Data just reads and of course does not modify the data

4 modifies SQL data to modify

5 CONTAINS SQL contains SQL statements

Within the function, only deterministic, NO SQL, and READS SQL DATA are supported. If we open the Bin-log, we must specify a parameter for our function.

The workaround for this error occurs when creating a function in MySQL:

Set global log_bin_trust_function_creators=true;

Source Document

3.

Error when importing data to MySQL

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)

Reason:

This is when we open the bin-log and we have to specify whether our function is

1 Deterministic indeterminate

2 No SQL does not have SQL statements and of course does not modify the data

3 READS SQL Data just reads and of course does not modify the data

4 modifies SQL data to modify

5 CONTAINS SQL contains SQL statements

Within the function, only deterministic, NO SQL, and READS SQL DATA are supported. If we open the Bin-log, we must specify a parameter for our function.

Workaround:

SQL Code

Mysql> Show variables like '%func% ';

+---------------------------------+-------+

| variable_name | Value |

+---------------------------------+-------+

| log_bin_trust_function_creators | OFF |

+---------------------------------+-------+

1 row in Set (0.00 sec)

mysql> set global Log_bin_trust_function_creators=1;

Query OK, 0 rows Affected (0.00 sec)

Mysql> Show variables like '%func% ';

+---------------------------------+-------+

| variable_name | Value |

+---------------------------------+-------+

| log_bin_trust_function_creators | On |

+---------------------------------+-------+

1 row in Set (0.00 sec)

Source Document

4.

Today we are going to write a function. But there is no way to build a hint error as follows:

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)

Solution: (Edit my.cnf, add the following)

[Mysqld]

Log_bin_trust_routine_creators = 1

It's good to restart MySQL.

Humen1 Tech

Source Document

The MySQL function cannot be created by a workaround