Error
Error Code:
1592
MariaDB Error 1592: Unsafe Statement in Binary Log
Description
This error indicates that an SQL statement considered potentially non-deterministic or unsafe for replication was executed while the MariaDB server's `BINLOG_FORMAT` was set to `STATEMENT`. It prevents such statements from being written to the binary log, which can lead to data inconsistency on replication replicas.
Error Message
Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. %s
Known Causes
4 known causesUse of Non-Deterministic Functions
Statements containing functions like `UUID()`, `RAND()`, `NOW()` (without specific context), or `GET_LOCK()` can yield different results each time they are executed, making them unsafe for statement-based replication.
Unordered `UPDATE`/`DELETE` with `LIMIT`
Executing `UPDATE` or `DELETE` statements that lack an `ORDER BY` clause, especially when combined with a `LIMIT`, can result in different rows being affected on the replica server.
Complex Operations with Temp Tables/Variables
Statements that involve temporary tables or user-defined variables in ways that are difficult to reproduce exactly on a replica can be flagged as unsafe for statement-based logging.
Implicit Dependencies or Side Effects
Certain DDL/DML statements might have implicit side effects or rely on server state that is not consistently captured or replicated via the statement format.
Solutions
3 solutions available1. Change BINLOG_FORMAT to ROW or MIXED medium
Switching the binary log format to ROW or MIXED prevents statement-based replication of unsafe statements.
1
Connect to your MariaDB server.
2
Check the current BINLOG_FORMAT setting.
SHOW VARIABLES LIKE 'binlog_format';3
If the format is 'STATEMENT', modify the MariaDB configuration file (e.g., `my.cnf` or `my.ini`). Locate the `[mysqld]` section and add or modify the `binlog_format` line.
[mysqld]
binlog_format = ROW4
Alternatively, you can set it dynamically for the current session (this change will be lost on restart unless persisted in the configuration file).
SET GLOBAL binlog_format = 'ROW';5
Restart the MariaDB server for the configuration file changes to take effect.
6
Verify the change after restart.
SHOW VARIABLES LIKE 'binlog_format';2. Identify and Rewrite Unsafe Statements advanced
Locate the specific statement causing the error and rewrite it to be safe for statement-based replication.
1
Examine the MariaDB error log for the exact statement that triggered error 1592. The error message itself often contains a placeholder (`%s`) for the problematic statement.
2
Common unsafe statements include those using non-deterministic functions (like `NOW()`, `RAND()`), `LIMIT` clauses without an `ORDER BY` clause, or user-defined variables that might have different values on the master and replica.
3
Rewrite the statement. For example, if the issue is with `LIMIT` without `ORDER BY`, add a stable `ORDER BY` clause. If it's a non-deterministic function, consider if the logic can be made deterministic or if ROW-based replication is a better fit.
/* Example: Unsafe statement */
UPDATE my_table SET column = 'value' LIMIT 1;
/* Safer alternative if you want to update any row */
UPDATE my_table SET column = 'value' WHERE id = (SELECT id FROM my_table LIMIT 1);
/* Safer alternative if you want to update a specific row based on some criteria */
UPDATE my_table SET column = 'value' WHERE some_condition LIMIT 1;4
Apply the corrected statement to the master database. Ensure it executes without errors. Monitor the binary logs to confirm it's no longer being logged as unsafe.
3. Disable Binary Logging for Specific Operations (Use with Caution) medium
Temporarily disable binary logging for operations known to be safe but might trigger this error.
1
Identify the specific SQL statement that is causing the error.
2
Wrap the unsafe statement within `SET sql_log_bin = 0;` and `SET sql_log_bin = 1;` commands. This will prevent the statement from being written to the binary log.
SET sql_log_bin = 0;
-- Your unsafe SQL statement here
SET sql_log_bin = 1;3
Execute this block of SQL on the master server. This is a quick fix but should be used judiciously as it bypasses replication for that specific statement. Ensure your replica is otherwise consistent.