Why Does My MySQL Function Throw a \'1418 (HY000)\' Error Regarding Deterministic Declarations?

DETERMINISTIC, NO SQL, or READS SQL DATA in Function Declaration with Binary Logging Enabled

While attempting to import a database into MySQL, an error message indicating that a function lacks one of the following declarations in its declaration and binary logging is enabled may occur:

1418 (HY000) at line 10185: 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)

Resolving the Issue

There are two methods to resolve this:

1. Execute the following in the MySQL console:

SET GLOBAL log_bin_trust_function_creators = 1;

2. Add the following to the mysql.ini configuration file:

log_bin_trust_function_creators = 1;

This relaxes the check for non-deterministic functions, which are functions that modify data.

Understanding Deterministic Declarations

Deterministic functions always produce the same result for the same input parameters, while non-deterministic functions do not. MySQL uses these declarations during replication optimization.

Types of Deterministic Declarations

Several types of deterministic declarations are available:

• DETERMINISTIC: Declares the function to be deterministic.
• NOT DETERMINISTIC: Declares the function to be non-deterministic.
• READS SQL DATA: Declares that the function only reads data from databases.
• NO SQL: Declares that the function contains no SQL statements.
• CONTAINS SQL: Declares that the function contains SQL instructions, but does not read or write data.

Best Practice

It is recommended to properly specify deterministic declarations for stored functions to ensure optimal replication and data integrity. By carefully considering the nature of the function and using the appropriate declarations, you can prevent errors and optimize database performance.

The above is the detailed content of Why Does My MySQL Function Throw a \'1418 (HY000)\' Error Regarding Deterministic Declarations?. For more information, please follow other related articles on the PHP Chinese website!