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

MySQLINTERMEDIATEHIGHUpdated Dec 25, 2025· 222 views

When creating stored functions in MySQL with binary logging enabled, you must explicitly declare how the function interacts with data using DETERMINISTIC, NO SQL, or READS SQL DATA. Without one of these declarations, MySQL raises ERROR 1418 to ensure safe replication.

Quick fix

Fixes 1418

CREATE FUNCTION my_function(param INT)
RETURNS INT
BEGIN
  -- Review: Does this function read data?
  -- Does it call NOW(), RAND(), or other non-deterministic functions?
  -- Does it modify any data?
  RETURN param * 2;
END;
  1. Identify what your function actually does
  2. Add the appropriate declaration to your function
  3. Update your existing function definition
  4. Test your function works correctly
mysql — error
CREATE FUNCTION my_function(param INT)
RETURNS INT
BEGIN
  -- Review: Does this function read data?
  -- Does it call NOW(), RAND(), or other non-deterministic functions?
  -- Does it modify any data?
  RETURN param * 2;
END;
ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled

What this error means

MySQL uses binary logging to record all changes to the database for replication and recovery purposes. When binary logging is enabled (the default in MySQL 8.0+), stored functions must declare their characteristics to ensure they can be safely replicated across servers. The three main characteristics control how MySQL logs and replicates your function: - **DETERMINISTIC**: The function always returns the same result for the same input parameters - **NO SQL**: The function contains no SQL statements at all - **READS SQL DATA**: The function reads data from tables but never modifies it Without one of these declarations, MySQL cannot determine whether the function is safe for replication, so it rejects the CREATE FUNCTION statement with ERROR 1418.

Typical symptoms

  • ERROR 1418 (HY000) appears when executing CREATE FUNCTION statement
  • Error message: "This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration"
  • The error occurs even though the function code is syntactically correct
  • Error appears when binary logging is enabled on your MySQL server
  • Migration scripts fail when they try to create functions without these declarations

Common causes

  1. Missing or incomplete function declaration (most common cause)
  2. Binary logging is enabled (log_bin=ON) but the function lacks required characteristics
  3. Copy-pasting function definitions from older MySQL versions that didn't require these declarations
  4. Using functions that access data without properly declaring READS SQL DATA or DETERMINISTIC
  5. Defining functions that modify data without using MODIFIES SQL DATA (if allowed)

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

1Identify what your function actually does

Before declaring the function characteristics, you need to understand what it does:

1. Does it use ANY SQL statements? If no → use NO SQL
2. Does it modify data (INSERT, UPDATE, DELETE)? If yes → it cannot be DETERMINISTIC; use READS SQL DATA with appropriate caution
3. Does it only read data without modifying? → use READS SQL DATA
4. Does it use date/time functions like NOW(), UUID(), RAND()? → it's NOT DETERMINISTIC
5. Does it perform pure calculation without any SQL? → use NO SQL

Review your function code carefully:

sql
CREATE FUNCTION my_function(param INT)
RETURNS INT
BEGIN
  -- Review: Does this function read data?
  -- Does it call NOW(), RAND(), or other non-deterministic functions?
  -- Does it modify any data?
  RETURN param * 2;
END;
2Add the appropriate declaration to your function

Once you've identified what your function does, add the appropriate characteristic:

For pure calculation functions (no SQL access):

sql
CREATE FUNCTION calculate_tax(amount DECIMAL(10,2))
RETURNS DECIMAL(10,2)
DETERMINISTIC
NO SQL
BEGIN
  RETURN amount * 0.08;
END;

For functions that read data only:

sql
CREATE FUNCTION get_user_count()
RETURNS INT
READS SQL DATA
BEGIN
  DECLARE count INT;
  SELECT COUNT(*) INTO count FROM users;
  RETURN count;
END;

For functions without any SQL:

sql
CREATE FUNCTION format_name(first_name VARCHAR(100), last_name VARCHAR(100))
RETURNS VARCHAR(200)
DETERMINISTIC
NO SQL
BEGIN
  RETURN CONCAT(first_name, ' ', last_name);
END;

Important: Only declare DETERMINISTIC if your function truly is deterministic (same input always produces same output). Functions using NOW(), RAND(), UUID(), or other non-deterministic operations should NOT be marked DETERMINISTIC.

3Update your existing function definition

Drop and recreate the function with the proper declaration, or modify it if your MySQL version supports ALTER FUNCTION:

sql
DROP FUNCTION IF EXISTS my_function;

CREATE FUNCTION my_function(param INT)
RETURNS INT
READS SQL DATA
BEGIN
  -- Your function body here
  RETURN param;
END;

For migration files or seed scripts, ensure all CREATE FUNCTION statements include the required characteristics from the start.

4Test your function works correctly

After creating the function with proper declarations, verify it works:

sql
-- Test the function
SELECT my_function(100);

-- For reading data functions, ensure results are correct
SELECT * FROM users WHERE id = my_function(1);

-- Check function details
SHOW CREATE FUNCTION my_function;

Advanced notes

### When to Use Each Characteristic

DETERMINISTIC: Use only when your function truly produces identical output for identical input, with no side effects. Never use this for functions that call NOW(), UUID(), RAND(), or any function that returns different values on each call.

NO SQL: The safest declaration if your function doesn't use any SQL statements at all. This guarantees no data access or modification.

READS SQL DATA: Use this for functions that query tables but never modify data. This is commonly used for lookup functions, aggregations, and other read-only operations.

MODIFIES SQL DATA: If your function modifies data (INSERT, UPDATE, DELETE), declare MODIFIES SQL DATA. However, this limits use of the function in certain contexts (e.g., generated columns, indexes). Such functions should be carefully reviewed for replication safety.

### Understanding Binary Logging and Replication

When binary logging is enabled, MySQL records all data modifications to a binary log file. When that log is replicated to another server, the replica must execute the same functions and operations. If a function is non-deterministic and marked as DETERMINISTIC, it could produce different results on the replica, causing data inconsistency.

This is why MySQL enforces these declarations - to prevent silent data corruption across replicated environments.

### The log_bin_trust_function_creators Variable

You may see advice to set SET GLOBAL log_bin_trust_function_creators = 1; to bypass this error. While this works temporarily, it's not recommended for production systems because:

1. It disables the safety check entirely
2. Your function could still cause replication issues if it's actually non-deterministic
3. Other developers or deployments may not have this setting enabled
4. It creates inconsistency between environments

Always prefer properly declaring your function's characteristics over disabling the check.

### Functions vs Procedures and Triggers

This error only affects stored functions (CREATE FUNCTION). Stored procedures (CREATE PROCEDURE) and triggers do not have the same requirements, but any functions they call must still be properly declared.

Frequently asked questions

What does "1418" mean?
MySQL uses binary logging to record all changes to the database for replication and recovery purposes. 0+), stored functions must declare their characteristics to ensure they can be safely replicated across servers. The three main characteristics control how MySQL logs and replicates your function: - DETERMINISTIC: The function always returns the same result for the same input parameters - NO SQL: The function contains no SQL statements at all - READS SQL DATA: The function reads data from tables but never modifies it Without one of these declarations, MySQL cannot determine whether the function is safe for replication, so it rejects the CREATE FUNCTION statement with ERROR 1418.
How do I fix "1418"?
When creating stored functions in MySQL with binary logging enabled, you must explicitly declare how the function interacts with data using DETERMINISTIC, NO SQL, or READS SQL DATA. Without one of these declarations, MySQL raises ERROR 1418 to ensure safe replication.
What causes "1418"?
Missing or incomplete function declaration (most common cause) Binary logging is enabled (log_bin=ON) but the function lacks required characteristics Copy-pasting function definitions from older MySQL versions that didn't require these declarations
Is the "1418" error serious?
Yes. This is a high-severity error that usually blocks work until it is fixed, but the steps above resolve it.

Official resources & further reading