Error
Error Code:
1673
MariaDB Error 1673: Unsafe System Variable Replication
Description
This error occurs when a SQL statement uses a system variable whose value might not be consistent across master and slave servers in a replication setup. Such statements are deemed unsafe for statement-based replication because they can lead to data divergence or unexpected results on the slave. It signals a potential for replication breakdown or data inconsistency.
Error Message
Statement is unsafe because it uses a system variable that may have a different value on the slave.
Known Causes
4 known causesNon-Deterministic System Variables
Statements refer to system variables like `CURRENT_USER()`, `UUID()`, `LAST_INSERT_ID()`, or `RAND()` which can produce different results on master and slave.
Statement-Based Replication (SBR)
When `binlog_format` is set to `STATEMENT`, MariaDB must ensure that statements execute identically on the slave as they did on the master. System variables often violate this determinism.
Inconsistent Session Variables
Statements relying on session-specific system variables (e.g., `sql_mode`, `time_zone`) that are not synchronized or have different values between master and slave.
Mixed Replication Mode Fallback
Even in `MIXED` mode, MariaDB might fall back to statement-based logging for some operations, triggering this error if an unsafe system variable is used.
Solutions
3 solutions available1. Disable Replication of Unsafe System Variable Statements easy
Prevent replication of statements that modify system variables with potential replication inconsistencies.
1
On the primary server, locate and edit the MariaDB configuration file (e.g., `my.cnf` or `mariadb.conf.d/50-server.cnf`).
2
Add or modify the `slave_exec_mode` setting in the `[mariadb]` or `[mysqld]` section to `IDEMPOTENT`.
[mariadb]
...
slave_exec_mode = IDEMPOTENT
...3
Restart the MariaDB service on the primary server for the changes to take effect.
sudo systemctl restart mariadb4
On the replica server(s), ensure that `slave_exec_mode` is not explicitly set to a value that would override this. If it is, remove or comment out that setting.
5
Restart the MariaDB service on the replica server(s).
sudo systemctl restart mariadb2. Replicate System Variable Changes Directly on Replicas medium
Manually apply system variable changes on replicas to ensure consistency.
1
When a statement that modifies an unsafe system variable is executed on the primary, it will be logged and reported as an error on the replica. Instead of letting replication stop, identify the specific statement that caused the error.
2
On the replica server, manually execute the same `SET` statement that was attempted on the primary.
SET GLOBAL variable_name = 'new_value';3
Once the manual application is successful on the replica, you can resume replication.
STOP SLAVE;
START SLAVE;4
Consider creating a script or a trigger to automate this process if such variable changes are frequent and predictable.
3. Configure Replication to Ignore Unsafe Statements medium
Instruct the replica to ignore specific statements that trigger the unsafe system variable replication error.
1
Identify the exact SQL statement that is causing the error 1673. This can be found in the replica's error log or by observing the output when replication is halted.
2
On the replica server, configure the replication filter to ignore statements that match the problematic pattern. This can be done using `replicate-ignore-db` or `replicate-do-db`, or more granularly with `replicate-ignore-table` and `replicate-wild-ignore-table` if the variable change affects a specific table indirectly.
replicate-ignore-db = database_name3
Alternatively, for specific statements, you might need to use a more advanced filtering mechanism if available or consider custom replication filtering solutions.
4
Restart the replica's SQL thread.
STOP SLAVE;
START SLAVE;5
Note: This approach should be used with caution as it might lead to data inconsistency if the system variable change is critical for certain operations.