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:
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.
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.
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!