Why Does MySQL Throw an Error About Deterministic Functions When Binary Logging is Enabled?

MySQL Error: "This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled"

When attempting to import a database into MySQL, an error might occur indicating that a function lacks one of the following declarations in its declaration: DETERMINISTIC, NO SQL, or READS SQL DATA. This error arises when binary logging is activated.

Resolving the Error:

This error can be addressed in two ways:

1. Configure MySQL Console: Execute the following command in the MySQL console:

   SET GLOBAL log_bin_trust_function_creators = 1;

2. Edit Configuration File: Append the following line to the mysql.ini configuration file:

   log_bin_trust_function_creators = 1;

These modifications allow non-deterministic functions to be used with binary logging. However, if binary logging is disabled, these settings do not affect the error.

Understanding Function Declarations:

• DETERMINISTIC: Indicates that a function always produces the same result for the same input parameters.
• NOT DETERMINISTIC: Indicates the opposite, that a function's result can vary for the same input parameters.
• READS SQL DATA: Explicitly specifies that a function will only read data from databases.
• NO SQL: Indicates that a function does not contain any SQL statements.
• CONTAINS SQL: Indicates that a function contains SQL instructions but does not modify data.

It is important to note that functions that contain non-deterministic instructions, such as NOW() or UUID(), should be declared as NOT DETERMINISTIC. Additionally, functions that read data from an unreplicated schema are also non-deterministic.

Best Practices:

The ideal approach is to understand and utilize deterministic declarations for stored functions as they aid in replication optimization. MySQL recommends explicitly specifying one of the mentioned declarations in the definition of stored functions to avoid potential errors.

The above is the detailed content of Why Does MySQL Throw an Error About Deterministic Functions When Binary Logging is Enabled?. For more information, please follow other related articles on the PHP Chinese website!