📋

Description

This error occurs when attempting to create or modify a stored function in MariaDB while binary logging is enabled. MariaDB requires stored functions to explicitly declare their data access characteristics (such as `DETERMINISTIC`, `NO SQL`, or `READS SQL DATA`) to ensure safe and consistent replication across servers. Without these declarations, the function is considered 'unsafe' for binary logging, preventing its creation.

💬

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 stored function's definition does not explicitly state whether it is `DETERMINISTIC`, `NO SQL`, or `READS SQL DATA`, which is necessary for binary logging.

⚠️

Binary Logging Enabled

The MariaDB server is configured with binary logging active, which imposes strict requirements on stored function declarations to maintain data integrity across replicas.

⚠️

Untrusted Function Creators (Default)

The `log_bin_trust_function_creators` system variable is set to `OFF` (the default), indicating that the server does not implicitly trust function creators to write safe functions for replication.

🛠️

Solutions

3 solutions available

1. Declare Function Characteristics Explicitly medium

Modify the function definition to include required characteristics like DETERMINISTIC, NO SQL, or READS SQL DATA.

1

Identify the stored function causing the error. You'll need to edit its `CREATE FUNCTION` or `ALTER FUNCTION` statement.

2

Determine the appropriate characteristic for your function. Common choices are:

  - `DETERMINISTIC`: The function always produces the same output for the same input values.
  - `NO SQL`: The function contains no SQL statements.
  - `READS SQL DATA`: The function reads data from tables but does not modify it.

3

Add the chosen characteristic to the function declaration. For example, if your function is deterministic:

CREATE FUNCTION my_function(arg INT)
RETURNS INT
DETERMINISTIC
BEGIN
  -- function body
END;

4

If your function modifies data (e.g., `INSERT`, `UPDATE`, `DELETE`), you might need to declare it as `MODIFIES SQL DATA` (though this is less common for the 1418 error which usually flags functions that *don't* have a safe declaration). If it's truly unsafe and you understand the risks, you might need to consider the `log_bin_trust_function_creators` option.

5

Re-create or alter the function with the updated declaration.

ALTER FUNCTION my_function DETERMINISTIC;

2. Temporarily Disable Binary Logging for Function Creators easy

Allow the creation of functions without explicit characteristics by setting a server variable.

1

Connect to your MariaDB server as a user with sufficient privileges (e.g., `root`).

2

Execute the following SQL command to temporarily enable the trust for function creators. This variable is session-specific, so it only affects the current connection.

SET GLOBAL log_bin_trust_function_creators = 1;

3

Now, create or alter your stored function. The error should no longer occur.

CREATE FUNCTION my_unsafe_function(arg INT)
RETURNS INT
BEGIN
  -- function body that doesn't declare characteristics
  RETURN arg * 2;
END;

4

Once you have created your function, it is highly recommended to disable this setting for security reasons, especially in production environments.

SET GLOBAL log_bin_trust_function_creators = 0;

3. Configure `log_bin_trust_function_creators` Permanently medium

Modify the MariaDB configuration file to permanently allow function creation without explicit characteristics.

1

Locate your MariaDB configuration file. Common locations include `/etc/my.cnf`, `/etc/mysql/my.cnf`, or files within `/etc/mysql/conf.d/`.

2

Open the configuration file in a text editor with administrative privileges (e.g., `sudo nano /etc/my.cnf`).

3

Find the `[mysqld]` section. If it doesn't exist, create it.

[mysqld]

4

Add or modify the `log_bin_trust_function_creators` option within the `[mysqld]` section. Setting it to `1` will permanently enable the trust.

log_bin_trust_function_creators = 1

5

Save the configuration file and exit the editor.

6

Restart the MariaDB service for the changes to take effect. The command may vary depending on your operating system.

sudo systemctl restart mariadb

7

After restarting, you can create functions without explicitly declaring `DETERMINISTIC`, `NO SQL`, or `READS SQL DATA`. However, be aware of the security implications of this setting.

🔗

Related Errors

5 related errors

MariaDB Error 1418: Unsafe Function Declaration