📋

Description

This error occurs when MariaDB attempts to execute a statement while `BINLOG_FORMAT` is set to `ROW`, but one or more tables involved in the operation utilize a storage engine that is incompatible or limited to statement-based logging for that specific action. This prevents the server from reliably recording the changes in its binary log, which is crucial for replication and point-in-time recovery.

💬

Error Message

Cannot execute statement: impossible to write to binary log since BINLOG_FORMAT = ROW and at least one table uses a storage engine limited to statement-based logging.

🔍

Known Causes

3 known causes

⚠️

Incompatible Storage Engine

One or more tables accessed by the SQL statement are using a storage engine (e.g., MyISAM) that is not fully compatible with row-based binary logging for the specific operation being performed.

⚠️

Mixed Storage Engine Usage

Your database environment contains tables using different storage engines, where the current `BINLOG_FORMAT=ROW` setting conflicts with operations on engines limited to statement-based logging.

⚠️

Unsafe Statement for ROW Format

The specific SQL statement being executed is considered unsafe for row-based logging due to its nature (e.g., non-deterministic functions), requiring a statement-based log format which conflicts with the current server setting.

🛠️

Solutions

3 solutions available

1. Change BINLOG_FORMAT to MIXED easy

Temporarily or permanently switch the binary log format to MIXED to accommodate statement-based logging for certain tables.

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 `binlog_format` is `ROW`, you can temporarily change it for the current session by executing the following command. Note that this change will be lost upon server restart.

SET SESSION binlog_format = 'MIXED';

4

For a permanent change, edit your MariaDB configuration file (e.g., `my.cnf` or `mariadb.conf.d/50-server.cnf`). Locate or add the `binlog_format` setting under the `[mysqld]` section and set it to `MIXED`.

[mysqld]
binlog_format = MIXED

5

Restart the MariaDB server for the configuration change to take effect.

2. Alter Tables to Use a Compatible Storage Engine medium

Convert tables that are incompatible with ROW-based binlogging to a storage engine that supports it.

1

Identify tables that are using storage engines limited to statement-based logging. Common examples include `MyISAM`.

2

You can list tables and their storage engines with the following query:

SELECT TABLE_SCHEMA, TABLE_NAME, ENGINE FROM information_schema.TABLES WHERE ENGINE = 'MyISAM';

3

For each identified table, convert it to a storage engine compatible with ROW-based binlogging, such as `InnoDB`. This can be done using the `ALTER TABLE` statement. **WARNING: This operation can be time-consuming and lock tables. Perform this during a maintenance window.**

ALTER TABLE your_table_name ENGINE=InnoDB;

4

After altering the tables, ensure your `binlog_format` is set to `ROW` (or `MIXED` if preferred) in your MariaDB configuration and restart the server if necessary.

3. Disable Binary Logging Temporarily easy

If binary logging is not critical for your immediate operation, you can disable it to resolve the conflict quickly.

1

Connect to your MariaDB server.

2

Temporarily disable binary logging for the current session. This will allow statements to execute but will not be recorded in the binary log.

SET SESSION sql_log_bin = 0;

3

Execute your problematic statement.

4

For a permanent disable, edit your MariaDB configuration file (e.g., `my.cnf` or `mariadb.conf.d/50-server.cnf`). Comment out or remove the `log_bin` directive under the `[mysqld]` section.

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

5