Error
Error Code:
1665
MySQL Error 1665: Binary Log Format Conflict
Description
This error occurs when MySQL attempts to write to the binary log using statement-based logging (`BINLOG_FORMAT = STATEMENT`), but the executed statement involves a table that uses a storage engine requiring row-based logging. This conflict prevents the statement from being safely recorded in the binary log, typically in a replication environment, leading to execution failure.
Error Message
Cannot execute statement: impossible to write to binary log since BINLOG_FORMAT = STATEMENT and at least one table uses a storage engine limited to row-based logging.
Known Causes
3 known causesServer Configured for Statement Logging
The MySQL server's `BINLOG_FORMAT` system variable is explicitly set to `STATEMENT`, which attempts to record SQL statements directly in the binary log.
Storage Engine Demands Row Logging
One or more tables affected by the statement utilize a storage engine (e.g., NDB Cluster's `NDB` engine or specific `InnoDB` setups) that inherently requires row-based logging for data integrity or replication safety.
Operation Unsafe for Statement Log
The specific SQL statement being executed performs an operation that MySQL deems unsafe or non-deterministic for statement-based logging, implicitly requiring row-based logging which conflicts with the table's engine and current `BINLOG_FORMAT`.
Solutions
3 solutions available1. Change BINLOG_FORMAT to ROW easy
Temporarily or permanently switch the binary log format to ROW to accommodate row-based storage engines.
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 format is 'STATEMENT', you can change it dynamically for the current session (this change is lost on server restart).
SET GLOBAL binlog_format = 'ROW';4
To make this change permanent, you need to edit your MySQL configuration file (my.cnf or my.ini). Locate the `[mysqld]` section and add or modify the `binlog_format` directive.
[mysqld]
binlog_format = ROW5
After editing the configuration file, restart your MySQL server for the changes to take effect.
sudo systemctl restart mysql # On systems using systemd
# or
sudo service mysql restart # On older systems2. Change BINLOG_FORMAT to MIXED easy
Switch the binary log format to MIXED, which uses statement-based logging by default but falls back to row-based logging when necessary.
1
Connect to your MySQL server as a user with sufficient privileges.
2
Check the current binary log format.
SHOW VARIABLES LIKE 'binlog_format';3
If the format is 'STATEMENT', you can change it dynamically for the current session.
SET GLOBAL binlog_format = 'MIXED';4
To make this change permanent, edit your MySQL configuration file (my.cnf or my.ini) and add or modify the `binlog_format` directive within the `[mysqld]` section.
[mysqld]
binlog_format = MIXED5
Restart your MySQL server for the permanent change to take effect.
sudo systemctl restart mysql # On systems using systemd
# or
sudo service mysql restart # On older systems3. Convert Tables to a Compatible Storage Engine medium
Alter tables using row-based logging-only engines to use a compatible engine like InnoDB.
1
Identify tables that are causing the conflict. These are typically tables using storage engines that only support row-based logging (e.g., NDBCLUSTER, ARCHIVE, BLACKHOLE).
SHOW TABLE STATUS WHERE Engine NOT IN ('InnoDB', 'MyISAM');2
For each identified table, you can convert it to a compatible storage engine. InnoDB is generally recommended.
ALTER TABLE your_table_name ENGINE=InnoDB;3
After converting the tables, you can revert your `binlog_format` back to 'STATEMENT' if that was your preferred setting, or keep it as 'ROW' or 'MIXED'.
SET GLOBAL binlog_format = 'STATEMENT';4
If you made the `binlog_format` change permanent, remember to update your configuration file and restart the server.