Home > Database > Mysql Tutorial > body text

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

Barbara Streisand
Release: 2024-11-03 20:18:29
Original
190 people have browsed it

Why Does MySQL Throw

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>
    Copy after login

    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
    Copy after login

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!

source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Latest Articles by Author
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template
About us Disclaimer Sitemap
php.cn:Public welfare online PHP training,Help PHP learners grow quickly!