Error
Error Code:
1672
MariaDB Error 1672: Unsafe UDF Replication Statement
Description
This error indicates that a SQL statement using a User-Defined Function (UDF) is considered unsafe for statement-based replication. It occurs when MariaDB's binary logging mechanism cannot guarantee that the UDF will produce the exact same result on a replica server as it did on the primary, potentially leading to data divergence.
Error Message
Statement is unsafe because it uses a UDF which may not return the same value on the slave.
Known Causes
3 known causesNon-Deterministic UDF Usage
The UDF used in the statement is not declared as DETERMINISTIC or inherently relies on external factors (like system time, file system, or network) that can vary between primary and replica servers.
UDF Installation Inconsistency
The UDF might not be installed, configured, or have the same version on the replica server as it does on the primary, leading to different behaviors or failures during replication.
Incompatible Binary Log Format
When using statement-based binary logging (binlog_format=STATEMENT), MariaDB cannot reliably replicate statements involving certain UDFs without potential inconsistencies.
Solutions
3 solutions available1. Disable Unsafe UDF Replication for Specific Statements medium
Mark the UDF as safe for replication if you've verified its behavior.
1
Identify the UDF causing the replication error. This is usually evident from the error message or by reviewing the slow query log on the master if it's a general performance issue leading to replication lag.
2
Connect to your MariaDB master server using a client like `mysql` or MariaDB Workbench.
3
Execute the `SET GLOBAL` statement to mark the UDF as safe for replication. Replace `your_udf_name` with the actual name of your User-Defined Function. This tells MariaDB that you have assessed the UDF and deemed it safe for replication, meaning it should produce the same results on the slave.
SET GLOBAL log_bin_trust_function_creators = 1;
-- After executing this, you may need to re-run the statement that caused the error or restart the slave threads.4
Alternatively, if you want to be more granular, you can mark individual UDFs as safe. However, this requires the UDF to be registered with `CREATE FUNCTION` and then potentially altered or re-registered with a specific attribute if the UDF mechanism supports it. The `log_bin_trust_function_creators` approach is more common for general UDFs.
5
Verify that replication is now working without the error. If the error persists, re-evaluate the UDF's behavior or consider other solutions.
2. Replicate UDF Logic in SQL advanced
Rewrite the UDF's functionality using standard SQL that is inherently safe for replication.
1
Analyze the logic of the User-Defined Function (UDF) that is causing the replication error. Understand exactly what calculations or operations it performs.
2
Rewrite the UDF's functionality using standard SQL statements. This might involve using built-in MariaDB functions, `CASE` statements, or other SQL constructs. The goal is to achieve the same output as the UDF but using only SQL that is guaranteed to be replicated safely.
Example: If your UDF `my_complex_calc(col1, col2)` returns `col1 * col2 + 10`, you would replace calls to `my_complex_calc(col1, col2)` with `(col1 * col2 + 10)` in your SQL statements.3
Replace all occurrences of the UDF call in your application code or SQL scripts with the equivalent SQL logic.
4
Test the modified SQL statements thoroughly on a development or staging environment to ensure they produce the same results as the original UDF and that replication is functioning correctly.
3. Use Slave-Side UDFs or Event Schedulers advanced
Execute the UDF logic on the slave independently or via a scheduled event.
1
Determine if the UDF's output is strictly required for data consistency on the master or if it can be calculated independently on the slave.
2
If the UDF can be run on the slave, ensure the UDF is installed and available on the slave server.
3
Modify your application logic to *not* call the UDF on the master when the statement is being replicated. Instead, have the slave execute the UDF or a similar logic independently. This often involves creating a stored procedure or an event on the slave that performs the UDF's task based on data that has been replicated.
Example on Slave:
CREATE EVENT update_slave_data ON SCHEDULE EVERY 1 MINUTE DO
UPDATE your_table SET replicated_column = your_udf_on_slave(original_column);
4
Ensure that the data dependencies for the UDF are met on the slave before it executes. This might involve waiting for specific transactions to be applied.
5
Thoroughly test this approach to ensure that the slave's state remains consistent with the master's intended state, even with the independent UDF execution.