Error
Error Code:
1663
MySQL Error 1663: Unsafe Statement Binary Log Conflict
Description
This error indicates that MySQL cannot execute a statement because it's considered "unsafe" for statement-based replication, and the involved storage engine prevents a switch to row-based logging, even when `BINLOG_FORMAT` is set to `MIXED`. This prevents reliable binary log recording and ensures data consistency across replicas.
Error Message
Cannot execute statement: impossible to write to binary log since statement is unsafe, storage engine is limited to statement-based logging, and BINLOG_FORMAT = MIXED. %s
Known Causes
3 known causesUnsafe SQL Statements
Executing SQL statements that contain non-deterministic functions (e.g., `UUID()`, `RAND()`) or operations that cannot be reliably replicated using statement-based logging.
Storage Engine Restriction
The specific storage engine in use (e.g., NDB Cluster for certain operations) is constrained to statement-based logging and cannot use row-based logging for the identified unsafe statement.
Mixed Log Format Incompatibility
Although `BINLOG_FORMAT` is `MIXED` (designed to switch to row-based logging for unsafe statements), the storage engine's limitations override this fallback mechanism, leading to the error.
Solutions
3 solutions available1. Switch BINLOG_FORMAT to ROW medium
Change the binary log format to ROW-based logging to avoid statement-based conflicts.
1
Connect to your MySQL server as a user with sufficient privileges (e.g., root).
2
Check the current binary log format.
SHOW VARIABLES LIKE 'binlog_format';3
If the current format is MIXED, you need to change it. This requires modifying the MySQL configuration file.
4
Locate your MySQL configuration file. Common locations include `/etc/my.cnf`, `/etc/mysql/my.cnf`, or `/etc/mysql/mysql.conf.d/mysqld.cnf`.
5
Edit the configuration file and change the `binlog_format` directive to `ROW` within the `[mysqld]` section.
[mysqld]
binlog_format=ROW6
Save the configuration file.
7
Restart the MySQL server for the changes to take effect.
sudo systemctl restart mysql # or mysqld, depending on your OS8
After restarting, verify the binary log format has been updated.
SHOW VARIABLES LIKE 'binlog_format';2. Switch BINLOG_FORMAT to STATEMENT medium
Change the binary log format to STATEMENT-based logging, which might resolve the conflict if the unsafe statement is compatible with statement logging.
1
Connect to your MySQL server as a user with sufficient privileges (e.g., root).
2
Check the current binary log format.
SHOW VARIABLES LIKE 'binlog_format';3
If the current format is MIXED, you can try changing it to STATEMENT. This requires modifying the MySQL configuration file.
4
Locate your MySQL configuration file. Common locations include `/etc/my.cnf`, `/etc/mysql/my.cnf`, or `/etc/mysql/mysql.conf.d/mysqld.cnf`.
5
Edit the configuration file and change the `binlog_format` directive to `STATEMENT` within the `[mysqld]` section.
[mysqld]
binlog_format=STATEMENT6
Save the configuration file.
7
Restart the MySQL server for the changes to take effect.
sudo systemctl restart mysql # or mysqld, depending on your OS8
After restarting, verify the binary log format has been updated.
SHOW VARIABLES LIKE 'binlog_format';3. Modify the Unsafe Statement advanced
Rewrite the problematic SQL statement to be compatible with statement-based logging.
1
Identify the exact SQL statement that is causing the error. This is often logged in the MySQL error log or can be identified by the context in which the error occurs.
2
Analyze the statement for elements that are not safe for statement-based replication. Common culprits include:
3
Functions that return non-deterministic results (e.g., `NOW()`, `UUID()`, `RAND()`).
4
Statements that rely on the order of rows in a table without an explicit `ORDER BY` clause.
5
Statements that update a table without a `WHERE` clause affecting all rows.
6
Rewrite the statement to be deterministic and explicit. For example, if using `NOW()`, consider using a fixed timestamp or a more predictable method.
Example: Original (unsafe): UPDATE my_table SET created_at = NOW() WHERE id = 1;
Example: Rewritten (safer): UPDATE my_table SET created_at = '2023-10-27 10:00:00' WHERE id = 1;7
If the statement involves `LIMIT` without `ORDER BY`, add an `ORDER BY` clause to ensure deterministic row selection.
Example: Original (unsafe): DELETE FROM my_table LIMIT 10;
Example: Rewritten (safer): DELETE FROM my_table ORDER BY id LIMIT 10;8
Apply the rewritten statement and test its functionality and logging behavior.