📋

Description

MariaDB Error 1661 signifies that a statement cannot be safely written to the binary log because the transaction involves both storage engines that are incompatible with row-based logging and those incompatible with statement-based logging. This conflict typically arises in replication environments when diverse storage engines are used within a single transaction, making it impossible for MariaDB to determine a consistent and safe binary log format.

💬

Error Message

Cannot execute statement: impossible to write to binary log since both row-incapable engines and statement-incapable engines are involved.

🔍

Known Causes

3 known causes

⚠️

Transaction Across Diverse Engines

A single SQL transaction attempts to modify data in tables that are managed by different storage engines, where each engine has conflicting requirements for binary logging.

⚠️

Incompatible Binary Log Format

The global or session `binlog_format` setting (e.g., STATEMENT, ROW, or MIXED) is unable to accommodate the logging requirements of all involved storage engines simultaneously for the current operation.

⚠️

Engine-Specific Logging Limitations

Certain storage engines involved in the transaction possess inherent restrictions or preferences for binary logging, which clash when trying to find a common logging strategy.

🛠️

Solutions

3 solutions available

1. Migrate Tables to a Binlog-Compatible Engine medium

Convert tables using incompatible storage engines to InnoDB.

1

Identify tables using non-row-based or statement-based engines that are causing the conflict. Common culprits are MyISAM.

SELECT TABLE_SCHEMA, TABLE_NAME, ENGINE FROM information_schema.TABLES WHERE ENGINE NOT IN ('InnoDB', 'TokuDB', 'XtraDB') OR (ENGINE = 'MyISAM' AND BINARY_LOG_FORMAT != 'ROW');

2

For each identified table, alter it to use the InnoDB engine. This is the most robust solution as InnoDB is fully compatible with row-based binary logging.

ALTER TABLE your_table_name ENGINE=InnoDB;

3

If you have a large number of tables or complex dependencies, consider using a tool like `pt-online-schema-change` from Percona Toolkit to perform the migration with minimal downtime.

pt-online-schema-change --alter 'ENGINE=InnoDB' --execute h=localhost,u=root,p=your_password D=your_database,t=your_table_name

2. Enforce Row-Based Binary Logging easy

Configure MariaDB to exclusively use row-based binary logging.

1

Edit the MariaDB configuration file (e.g., `my.cnf` or `mariadb.conf.d/50-server.cnf`). The exact location varies by OS and installation method.

2

Add or modify the following lines in the `[mariadb]` or `[mysqld]` section to enforce row-based logging. This is the recommended setting for most modern applications.

[mariadb]
binlog_format=ROW

3

Restart the MariaDB service for the changes to take effect.

sudo systemctl restart mariadb

3. Temporarily Disable Binary Logging (for Development/Testing) easy

Disable binary logging if it's not strictly required, e.g., in development environments.

1

Edit the MariaDB configuration file (e.g., `my.cnf` or `mariadb.conf.d/50-server.cnf`).

2

Comment out or remove the `log_bin` directive in the `[mariadb]` or `[mysqld]` section.

# log_bin = /var/log/mysql/mysql-bin.log

3

Restart the MariaDB service.

sudo systemctl restart mariadb

🔗

Related Errors

5 related errors

MariaDB Error 1661: Mixed Engine Binlog Conflict