Error
Error Code:
1592
MySQL Error 1592: Unsafe Statement Binary Log
Description
This error occurs when MySQL attempts to write a SQL statement to the binary log that is deemed 'unsafe' for statement-based replication, while `BINLOG_FORMAT` is set to `STATEMENT`. Unsafe statements are those that might produce different results when replayed on a replica, leading to data inconsistency.
Error Message
Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. %s
Known Causes
3 known causesNon-Deterministic Functions
Executing SQL statements that include non-deterministic functions such as `UUID()`, `NOW()`, `RAND()`, or `GET_LOCK()`.
Session-Dependent Operations
Statements that rely on session variables, user-defined variables, or other context-dependent data that may not be identical on a replica server.
Missing Primary Keys
Performing DML operations (e.g., `UPDATE`, `DELETE`) on tables that do not have a primary key defined, making it difficult for statement-based replication to guarantee consistency.
Solutions
3 solutions available1. Change BINLOG_FORMAT to MIXED or ROW easy
The most straightforward fix is to alter the binary log format to one that handles unsafe statements better.
1
Connect to your MySQL server as a user with appropriate privileges (e.g., root).
2
Check the current binary log format.
SHOW VARIABLES LIKE 'binlog_format';3
If the format is 'STATEMENT', change it to 'MIXED' or 'ROW'. 'MIXED' is generally a good balance. 'ROW' is the safest but can increase binary log size.
SET GLOBAL binlog_format = 'MIXED';4
For the change to persist across server restarts, you need to update your MySQL configuration file (my.cnf or my.ini). Locate the file and add or modify the following line under the [mysqld] section:
[mysqld]
binlog_format = MIXED5
Restart your MySQL server for the configuration file changes to take effect.
2. Rewrite Unsafe Statements medium
Identify and modify the specific SQL statements causing the issue to be deterministic.
1
Identify the unsafe statement by examining the MySQL error log or the binary log itself. The error message often contains a snippet of the problematic SQL.
2
Common causes of 'unsafe' statements include:
- Using functions that are not deterministic (e.g., `NOW()`, `UUID()`, `RAND()`) in WHERE clauses or UPDATE/DELETE statements.
- Using LIMIT without an ORDER BY clause.
- Relying on the order of rows when it's not guaranteed.3
Rewrite the statement to be deterministic. For example, if you're using `NOW()` in a WHERE clause, consider passing a fixed timestamp or using it only in contexts where statement-based replication is safe (which is rare). If you're using `LIMIT` without `ORDER BY`, add an `ORDER BY` clause that uniquely identifies the rows.
Example of rewriting an unsafe statement:
Unsafe: UPDATE users SET status = 'inactive' WHERE last_login < NOW() - INTERVAL 30 DAY LIMIT 100;
Safer (if you want to update the oldest inactive users): UPDATE users SET status = 'inactive' WHERE last_login < NOW() - INTERVAL 30 DAY ORDER BY last_login ASC LIMIT 100;4
Test the rewritten statement thoroughly in a non-production environment to ensure it behaves as expected.
3. Disable Binary Logging for Specific Statements (Use with Caution) medium
Prevent specific queries from being written to the binary log. This is a workaround and should be used judiciously.
1
Connect to your MySQL server.
2
You can disable binary logging for a specific session using the `SET SESSION sql_log_bin = 0;` command. This will prevent all subsequent statements in that session from being written to the binary log.
SET SESSION sql_log_bin = 0;
-- Execute your potentially unsafe statement here
-- For example: UPDATE some_table SET some_column = RAND();
SET SESSION sql_log_bin = 1; -- Re-enable binary logging for the session3
Alternatively, you can use the `@@SESSION.sql_log_bin` variable. This method is generally preferred for its clarity.
SET SESSION sql_log_bin = 0;
-- Your statement here
SET SESSION sql_log_bin = 1;4
This approach is session-specific. If you need to disable it globally or for a longer period, consider other solutions. This is best for isolated, known problematic queries that don't affect replication consistency.