Error
Error Code:
1664
MariaDB Error 1664: Binary Log Format Incompatibility
Description
This error signifies a conflict when MariaDB attempts to write an SQL statement to the binary log in row format, but at least one table involved in the statement uses a storage engine that is limited to statement-based logging. It typically occurs in environments configured for replication where the `binlog_format` setting does not align with the capabilities of all active storage engines.
Error Message
Cannot execute statement: impossible to write to binary log since statement is in row format and at least one table uses a storage engine limited to statement-based logging.
Known Causes
3 known causesServer Binary Log Format Mismatch
The MariaDB server's global `binlog_format` is configured for `ROW` or `MIXED` logging, which conflicts with a table's storage engine that only supports `STATEMENT` logging.
Storage Engine Limitation
One or more tables involved in the executed statement utilize a storage engine (e.g., NDB Cluster in specific configurations) that is inherently limited to statement-based logging for replication purposes.
Statement Requires Row-Based Logging
The nature of the SQL statement being executed (e.g., operations involving non-deterministic functions or temporary tables) implicitly requires row-based logging, clashing with an affected table's statement-only logging engine.
Solutions
3 solutions available1. Change Binary Log Format to MIXED easy
Temporarily allows row-based statements to be logged by falling back to statement-based logging when necessary.
1
Connect to your MariaDB server using the command-line client or a GUI tool.
2
Set the `binlog_format` to `MIXED`. This is a dynamic setting and can be changed without restarting the server.
SET GLOBAL binlog_format = 'MIXED';3
Verify the change by checking the current global variable.
SHOW GLOBAL VARIABLES LIKE 'binlog_format';4
Retry the statement that caused the error. If it succeeds, you can consider making this change permanent by adding `binlog_format = MIXED` to your `my.cnf` or `mariadb.conf.d` configuration file and restarting the server.
2. Convert Incompatible Tables to InnoDB medium
Ensures all tables support row-based logging by migrating them to the InnoDB storage engine.
1
Identify tables that are not using the InnoDB storage engine. These are typically MyISAM or other older engines that do not support row-based logging.
SHOW TABLE STATUS WHERE Engine NOT LIKE 'InnoDB';2
For each identified table, convert it to InnoDB. It's highly recommended to do this during a maintenance window as it can lock the table for the duration of the conversion.
ALTER TABLE your_table_name ENGINE=InnoDB;3
After converting all incompatible tables, the binary log should be able to handle row-based statements. You can then set `binlog_format` to `ROW` if desired for better consistency.
SET GLOBAL binlog_format = 'ROW';4
Verify the change.
SHOW GLOBAL VARIABLES LIKE 'binlog_format';3. Disable Binary Logging (Temporary/Development) easy
Disables binary logging entirely, which bypasses the error but sacrifices replication and point-in-time recovery capabilities.
1
Connect to your MariaDB server.
2
Stop the binary log writing process. This is a dynamic setting.
SET GLOBAL sql_log_bin = 0;3
Retry the statement that caused the error. It should now execute without binary log-related issues.
4
To re-enable binary logging, execute `SET GLOBAL sql_log_bin = 1;`. For a permanent change, modify your `my.cnf` or `mariadb.conf.d` file by commenting out or removing the `log_bin` directive and restarting the server.
SET GLOBAL sql_log_bin = 1;