📋

Description

Error 1418 indicates that a user-defined function (UDF) has been created or altered without explicitly specifying its characteristics regarding data modification or determinism. This occurs when MySQL's binary logging is active, as such undeclared functions are deemed unsafe for replication, potentially causing inconsistencies between master and replica servers.

💬

Error Message

This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)

🔍

Known Causes

3 known causes

⚠️

Missing Function Characteristics

The CREATE FUNCTION or ALTER FUNCTION statement did not explicitly include DETERMINISTIC, NO SQL, or READS SQL DATA clauses.

⚠️

Binary Logging Enabled

The MySQL server is configured with binary logging active, which enforces stricter rules for function safety to ensure replication integrity.

⚠️

`log_bin_trust_function_creators` Disabled

The `log_bin_trust_function_creators` system variable is set to OFF (its default), preventing the creation or modification of 'unsafe' functions.

🛠️

Solutions

3 solutions available

1. Declare Function Determinism medium

Explicitly define the function's behavior regarding data modification and SQL execution.

1

When creating or altering your stored function, add one of the following keywords to its declaration:

-- For functions that always produce the same output for the same input and don't modify data:
DETERMINISTIC

-- For functions that do not contain any SQL statements:
NO SQL

-- For functions that only read data but do not modify it:
READS SQL DATA

2

Choose the most appropriate keyword based on your function's logic. For example, if your function calculates a value based on its input parameters and doesn't interact with the database, `DETERMINISTIC` is usually the best choice. If it only reads from tables, use `READS SQL DATA`.

DELIMITER //

CREATE FUNCTION my_deterministic_function(input_value INT)
RETURNS INT
DETERMINISTIC
BEGIN
    RETURN input_value * 2;
END //

DELIMITER ;

-- Or for a function that reads data:
DELIMITER //

CREATE FUNCTION get_user_name(user_id INT)
RETURNS VARCHAR(255)
READS SQL DATA
BEGIN
    DECLARE user_name VARCHAR(255);
    SELECT name INTO user_name FROM users WHERE id = user_id;
    RETURN user_name;
END //

DELIMITER ;

2. Disable Binary Logging for Functions easy

Temporarily allow functions to be created without explicit determinism declarations by trusting their creators.

1

Set the `log_bin_trust_function_creators` server variable to `1`. This should be done with caution, as it bypasses safety checks. This setting can be applied globally or per session.

-- To set globally (requires SUPER privilege, will persist after restart if in my.cnf/my.ini):
SET GLOBAL log_bin_trust_function_creators = 1;

-- To set for the current session only (will be reset upon connection close):
SET SESSION log_bin_trust_function_creators = 1;

2

After setting this variable, you can create your function without adding `DETERMINISTIC`, `NO SQL`, or `READS SQL DATA` to its declaration.

DELIMITER //

CREATE FUNCTION my_unsafe_function(input_value INT)
RETURNS INT
BEGIN
    -- This function might have side effects or be non-deterministic
    RETURN input_value + FLOOR(RAND() * 10);
END //

DELIMITER ;

3

Consider reverting this setting to `0` after creating your function if you want to re-enable the safety checks. For a permanent solution, it's better to declare the function's determinism.

SET GLOBAL log_bin_trust_function_creators = 0;
SET SESSION log_bin_trust_function_creators = 0;

3. Review and Refactor Function Logic advanced

Modify the function to be deterministic or explicitly declare its SQL interaction.

1

Examine the internal logic of your stored function. Identify any operations that might produce different results for the same input, or any SQL statements that modify data.

Example of a non-deterministic operation: using `RAND()` or `NOW()` without proper handling.
Example of a data modification: `INSERT`, `UPDATE`, `DELETE` statements within the function.

2

If the function's purpose is purely computational and should be deterministic, remove or replace non-deterministic functions like `RAND()` with deterministic alternatives (e.g., using a seed or a different approach).

Instead of `RAND()`, consider passing a seed value as a parameter or using a pre-defined sequence.

3

If the function legitimately needs to read data, add the `READS SQL DATA` clause. If it modifies data, it's generally not recommended to use such functions in a way that requires binary logging safety (e.g., for replication). If it's absolutely necessary, consider the implications and potential data inconsistencies.

CREATE FUNCTION update_and_get_status(user_id INT, new_status VARCHAR(50))
RETURNS VARCHAR(50)
MODIFIES SQL DATA -- Note: MODIFIES SQL DATA is not a valid option for this error. You would typically avoid this or use log_bin_trust_function_creators.
BEGIN
    UPDATE users SET status = new_status WHERE id = user_id;
    SELECT status INTO @current_status FROM users WHERE id = user_id;
    RETURN @current_status;
END;

-- A more appropriate approach for replication would be to perform the UPDATE and then SELECT in separate statements outside the function if possible, or ensure it's truly deterministic if it only reads.

4

Once the function's behavior is understood and modified, re-create it with the appropriate determinism declaration (`DETERMINISTIC`, `NO SQL`, or `READS SQL DATA`).

See Solution 1 for examples of correct declarations.

🔗

Related Errors

5 related errors

MySQL Error 1418: Unsafe Function Declaration