Error
Error Code:
1665
MariaDB Error 1665: Binary Log Format Mismatch
Description
This error indicates a conflict between the server's binary log format, which is set to STATEMENT, and a storage engine used by at least one table involved in the transaction. The affected storage engine requires or is limited to row-based logging, making statement-based logging impossible for the current operation.
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.%s
Known Causes
3 known causesIncompatible Storage Engine
A table involved in the statement uses a storage engine (e.g., NDB Cluster) that fundamentally requires row-based logging for accurate and consistent replication, conflicting with the current statement-based log format.
Explicit Statement-Based Logging
The `BINLOG_FORMAT` system variable is explicitly set to `STATEMENT` globally or for the current session, overriding any automatic determination or preference for row-based logging by an involved table's engine.
Mixed Engine Database
The database schema contains a mix of tables where some use engines compatible with statement-based logging and others use engines (like NDB) that are not, leading to conflicts during operations.
Solutions
3 solutions available1. Switch BINLOG_FORMAT to MIXED or ROW easy
Change the binary log format to one that supports row-based logging for all storage engines.
1
Connect to your MariaDB server using a client like `mysql` or `mariadb`.
2
Check the current binary log format.
SHOW VARIABLES LIKE 'binlog_format';3
If the format is 'STATEMENT', modify your MariaDB configuration file (e.g., `my.cnf` or `mariadb.conf.d/50-server.cnf`) to set `binlog_format` to 'MIXED' or 'ROW'.
[mariadb]
binlog_format = MIXED4
Restart the MariaDB service for the changes to take effect.
sudo systemctl restart mariadb5
Alternatively, you can set it dynamically (though this is not persistent across restarts):
SET GLOBAL binlog_format = 'MIXED';2. Convert Tables to Compatible Storage Engines medium
Change the storage engine of tables that are incompatible with STATEMENT-based logging.
1
Identify tables that use storage engines limited to row-based logging. Common examples include `CSV` and `ARCHIVE`.
SHOW TABLE STATUS WHERE Engine NOT IN ('InnoDB', 'MyISAM', 'XtraDB');2
For each identified table, alter its storage engine to a compatible one, such as `InnoDB`.
ALTER TABLE your_table_name ENGINE=InnoDB;3
After converting all problematic tables, you can continue using `BINLOG_FORMAT = STATEMENT` if desired, or switch to `MIXED` or `ROW` for broader compatibility.
3. Disable Binary Logging Temporarily easy
Turn off binary logging if it's not strictly required for replication or point-in-time recovery.
1
Connect to your MariaDB server.
2
Edit your MariaDB configuration file (e.g., `my.cnf` or `mariadb.conf.d/50-server.cnf`) and comment out or remove the `log_bin` directive.
#log_bin = /var/log/mysql/mysql-bin.log3
Restart the MariaDB service.
sudo systemctl restart mariadb4
Alternatively, you can disable it dynamically (not persistent):
SET GLOBAL sql_log_bin = 0;