📋

Description

MariaDB Error 1674 indicates that a SQL statement contains a system function that may produce different results on the master and slave servers during replication. This commonly occurs with statement-based replication (SBR) and can lead to data inconsistency between your primary and replica databases. It highlights a potential issue where the slave's data might diverge from the master's.

💬

Error Message

Statement is unsafe because it uses a system function that may return a different value on the slave.

🔍

Known Causes

3 known causes

⚠️

Using Non-Deterministic Functions

The SQL statement includes functions like UUID(), NOW(), RAND(), GET_LOCK(), or other system functions whose output is not guaranteed to be the same across different server executions or at different times.

⚠️

Statement-Based Replication (SBR)

This error primarily occurs when the server is configured to use statement-based replication (SBR), where SQL statements are re-executed on the slave, making non-deterministic functions problematic.

⚠️

Application-Generated Unsafe Queries

Applications or Object-Relational Mappers (ORMs) might inadvertently generate SQL queries that utilize unsafe system functions without explicit awareness of their replication implications.

🛠️

Solutions

4 solutions available

1. Disable Statement-Based Replication for Unsafe Statements easy

Temporarily allows unsafe statements by switching to row-based replication.

1

Log in to your MariaDB master server as a user with replication privileges.

2

Check the current binlog format. If it's 'STATEMENT', you'll need to change it.

SHOW GLOBAL VARIABLES LIKE 'binlog_format';

3

Temporarily change the binlog format to ROW. This is a quick fix that bypasses the error for the current session. For a permanent change, you'll need to modify the MariaDB configuration file.

SET GLOBAL binlog_format = 'ROW';

4

Execute the statement that was causing the error.

5

If you only needed to switch temporarily, you can revert the binlog format back to STATEMENT (if that was your original setting). However, it's generally recommended to keep it at ROW for better safety.

SET GLOBAL binlog_format = 'STATEMENT';

2. Modify Application Logic to Avoid System Functions medium

Rewrite the SQL query to use deterministic functions or fetch values from the master directly.

1

Identify the specific system function causing the error in your SQL statement. Common examples include NOW(), RAND(), UUID(), etc.

2

Rewrite the query to use a deterministic equivalent. For example, if you need a unique ID, consider using a sequence or a pre-generated ID from the application layer.

Example: Instead of `INSERT INTO my_table (id, name) VALUES (UUID(), 'Test');`, consider generating the UUID in your application code and then inserting it.

3

Alternatively, if the value is critical and must be consistent, fetch it on the master and then pass it as a parameter to the statement that is being replicated.

Example: `SELECT UUID() INTO @uuid; INSERT INTO my_table (id, name) VALUES (@uuid, 'Test');` (This would then be executed on the master, and the value of @uuid would be replicated.)

4

Test the modified query thoroughly on the master and then observe replication to ensure the error is resolved.

3. Configure Replication to Ignore Unsafe Statements advanced

Instruct the slave to skip specific unsafe statements, but use with extreme caution.

1

Log in to your MariaDB slave server.

2

Stop the replication process on the slave.

STOP SLAVE;

3

Use `SET GLOBAL SQL_SLAVE_SKIP_COUNTER` to skip the problematic statement. You will need to determine the exact number of statements to skip. This is often found by inspecting the binary log (`mysqlbinlog`).

SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; -- Replace '1' with the actual number of statements to skip

4

Start the replication process again.

START SLAVE;

5

Monitor replication closely. This is a risky solution as it can lead to data divergence if not handled correctly. It's generally preferable to fix the root cause rather than skipping statements.

4. Permanently Set Binlog Format to ROW in Configuration medium

Ensure the binlog format is always ROW to prevent future replication inconsistencies.

1

Locate your MariaDB configuration file. This is typically `my.cnf` or `my.ini` on Linux/macOS and `my.ini` on Windows. Common locations include `/etc/mysql/my.cnf`, `/etc/my.cnf`, or within the MariaDB installation directory.

2

Edit the configuration file with a text editor. You'll need root or administrator privileges.

3

Find the `[mysqld]` section and add or modify the `binlog_format` directive to `ROW`.

[mysqld]
binlog_format = ROW

4

Save the changes to the configuration file.

5

Restart the MariaDB server for the changes to take effect.

On Linux/macOS (using systemd):
sudo systemctl restart mariadb

On Windows:
Open Services, find MariaDB, and restart it.

6

Verify the binlog format is set to ROW after restarting.

SHOW GLOBAL VARIABLES LIKE 'binlog_format';

🔗

Related Errors

5 related errors

MariaDB Error 1674: Replication Inconsistency Risk