Fixing MySQL ERROR 1418: Missing DETERMINISTIC or READS SQL DATA

The Error ScenarioYou've just finished writing a custom MySQL function to automate your workflow. You hit execute, expecting a success message, but the database spits back this frustrating block of text instead:

ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled...

This error is common on production servers where binary logging (binlog) is active for replication or point-in-time recovery. If you are using a managed service like AWS RDS or Google Cloud SQL, you'll see this often because they enable binary logging by default to handle backups and high availability.

Why MySQL is Blocking YouThink of binary logging as a flight recorder. To keep a replica server perfectly in sync with the master, MySQL needs to ensure that every function call produces the same result on both machines. If a function is "unpredictable," the two servers will eventually drift apart, leading to data corruption.

For example, a function using UUID() or RAND() is non-deterministic; it generates a different value every time it runs. If MySQL allowed these functions without a specific declaration, the master might save 'ID-A' while the replica saves 'ID-B'. To prevent this nightmare, MySQL defaults log_bin_trust_function_creators to 0 (OFF), forcing you to prove your function is safe.

How to Fix ItYou have two paths: write better SQL by declaring your function's intent, or tell MySQL to trust you blindly. The first path is the professional standard for production environments.

Method 1: Declare Function Characteristics (Recommended)Adding a single keyword tells MySQL exactly how your function interacts with data. Place one of these keywords immediately after the RETURNS clause:

• DETERMINISTIC: The function always returns the same output for the same input (e.g., a tax calculation).- NO SQL: The function doesn't touch the database at all (e.g., string manipulation).- READS SQL DATA: The function uses SELECT but never modifies data.- MODIFIES SQL DATA: The function performs INSERT, UPDATE, or DELETE operations.Example of a fixed function:

DELIMITER //
CREATE FUNCTION get_discount_price(price DECIMAL(10,2))
RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGIN
    RETURN price * 0.90;
END //
DELIMITER ;

Method 2: The Global Variable "Trust" FixIf you are migrating hundreds of legacy functions or working in a local development sandbox, manually updating every script is tedious. You can disable the strict check by toggling a global variable. This requires SUPER privileges, which means it might not work on managed cloud DBs without changing a "Parameter Group" first.

Run this in your terminal:

SET GLOBAL log_bin_trust_function_creators = 1;

Note: This change is volatile. If the MySQL service restarts, the setting reverts to 0.

Method 3: Permanent ConfigurationTo make the trust setting stick after a reboot, add it to your server's configuration file (my.cnf on Linux or my.ini on Windows).

• Open your config file (usually in /etc/mysql/).- Find the [mysqld] header.- Add this line: log_bin_trust_function_creators = 1- Restart the service: sudo systemctl restart mysql## Verifying the SolutionCheck if the change took effect by querying the system variables:

SHOW GLOBAL VARIABLES LIKE 'log_bin_trust_function_creators';

If the value is ON, you're ready to go. Test it by creating a dummy function without any characteristics:

CREATE FUNCTION quick_test() RETURNS INT RETURN 1;

The bottom line? While Method 2 is faster, Method 1 is the superior engineering choice. It documents your code's behavior and ensures your replication logs remain predictable and clean.