Introduction

This MySQL error usually appears when you create a stored function while binary logging is enabled, but the function declaration does not say whether it is deterministic or whether it reads SQL data. MySQL asks for that information because replication safety depends on how predictable the function is.

Why MySQL Cares

With binary logging enabled, MySQL may need to replay statements on replicas. If a stored function returns different results at different times for the same inputs, statement-based replication can become unsafe.

That is why MySQL wants the function definition to declare characteristics such as:

• 'DETERMINISTIC'
• 'NO SQL'
• 'READS SQL DATA'

If you omit them, MySQL may reject the function with the error you saw.

A Simple Deterministic Function

Here is a function that is deterministic and does not read tables:

1CREATE FUNCTION circle_area(radius DOUBLE)
2RETURNS DOUBLE
3DETERMINISTIC
4NO SQL
5RETURN PI() * radius * radius;

Given the same radius, this function returns the same result every time and does not query database tables. That is exactly the kind of declaration MySQL is asking you to make explicit.

A Function That Reads Data

If the function queries a table, say so:

1CREATE FUNCTION tax_rate_for_state(state_code CHAR(2))
2RETURNS DECIMAL(5,2)
3READS SQL DATA
4BEGIN
5    DECLARE rate DECIMAL(5,2);
6
7    SELECT tax_rate
8    INTO rate
9    FROM tax_rates
10    WHERE code = state_code;
11
12    RETURN rate;
13END;

This function is not NO SQL, because it does read table data. Declaring READS SQL DATA tells MySQL what kind of function it is.

DETERMINISTIC Has a Real Meaning

Do not mark a function as deterministic just to silence the error. A deterministic function must truly return the same result for the same inputs.

For example, a function that uses RAND(), NOW(), or other non-deterministic inputs is not honestly deterministic. Declaring it incorrectly can create replication problems later.

So the real fix is:

1. classify the function honestly
2. declare the right characteristics

The log_bin_trust_function_creators Escape Hatch

MySQL also offers another path: allowing trusted function creators by enabling log_bin_trust_function_creators.

SET GLOBAL log_bin_trust_function_creators = 1;

This tells MySQL to stop enforcing the same safety check so strictly. It can be useful in controlled environments, but it shifts responsibility to the developer or DBA. If function definitions are inaccurate, replication safety is now your problem instead of MySQL's.

That is why this option should be a deliberate administrative choice, not a reflex.

A Good Mental Model

Think of the error as MySQL asking:

• does this function always return the same output for the same input
• does it touch SQL data
• is it safe enough to replicate

Once you answer those questions honestly in the declaration, the error usually goes away.

Common Pitfalls

The biggest pitfall is marking a function DETERMINISTIC when it uses non-deterministic behavior such as timestamps or random values.

Another issue is using log_bin_trust_function_creators = 1 as a blanket workaround without understanding the replication implications.

Developers also confuse stored functions with procedures. The specific error is about function declarations under binary logging, not about every stored program in general.

Summary

• MySQL raises this error because binary logging needs stored-function behavior to be declared clearly.
• Add characteristics such as DETERMINISTIC, NO SQL, or READS SQL DATA to the function definition as appropriate.
• Declare the function honestly rather than using DETERMINISTIC as a random fix.
• 'log_bin_trust_function_creators = 1 can bypass the safety check, but it shifts the risk to you.'
• The safest solution is to describe the function's real behavior accurately in its declaration.