Why Does MySQL Throw \'DETERMINISTIC, NO SQL, or READS SQL DATA\' Error with Binary Logging Enabled?

Error: "DETERMINISTIC, NO SQL, or READS SQL DATA" Missing in Function Declaration with Binary Logging Enabled

When importing a database in MySQL, encountering the error "1418 (HY000): This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled" indicates that the function in question lacks an appropriate declaration.

Resolution:

There are two methods to resolve this issue:

1. Execute MySQL Console Command:

   <code class="mysql">SET GLOBAL log_bin_trust_function_creators = 1;</code>

   This command relaxes the checking for non-deterministic functions, allowing the import to proceed.

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

   log_bin_trust_function_creators = 1

Explanation:

Binary logging records all modifications made to the database. By default, MySQL requires functions that modify data to be declared as DETERMINISTIC, NO SQL, or READS SQL DATA in their declarations to ensure that they can be replicated accurately.

Deterministic Declarations for Stored Functions:

These declarations inform MySQL about the nature of the function, guiding optimization and replication decisions.

• DETERMINISTIC: Functions that always produce the same result for the same inputs, regardless of external factors.
• NOT DETERMINISTIC: Functions that can produce different results for the same inputs, such as those containing non-deterministic MySQL functions (e.g., NOW(), UUID()).
• READS SQL DATA: Functions that read data from the database, without modifying it.
• NO SQL: Functions that do not contain SQL statements.

Note: If binary logging is disabled, the log_bin_trust_function_creators setting does not apply. Hence, it is crucial to ensure that appropriate deterministic declarations are specified.

By implementing one of the provided solutions, you can resolve the error and continue with the database import.

The above is the detailed content of Why Does MySQL Throw \'DETERMINISTIC, NO SQL, or READS SQL DATA\' Error with Binary Logging Enabled?. For more information, please follow other related articles on the PHP Chinese website!