📋

Description

This error occurs when MySQL attempts to write a statement in row-based format to the binary log, but one or more tables involved in the statement use a storage engine that only supports statement-based logging. This inconsistency prevents the statement from being safely replicated or recovered, halting execution.

💬

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 causes

⚠️

Mixed Storage Engines

The database contains tables using different storage engines, where some are compatible with row-based logging (e.g., InnoDB) and others are restricted to statement-based logging (e.g., MyISAM in certain contexts or older versions).

⚠️

Binary Log Format Setting

The `binlog_format` system variable is set to `ROW` or `MIXED`, but the executed statement interacts with a table whose storage engine can only be safely logged using `STATEMENT` format.

⚠️

Incompatible Statement Execution

A specific statement or DDL operation, even in `MIXED` logging mode, is attempted in a row-based format when an underlying table's storage engine only supports statement-based logging.

🛠️

Solutions

3 solutions available

1. Set Binary Log Format to MIXED easy

Temporarily allows both statement and row-based logging to resolve the immediate conflict.

1

Connect to your MySQL server using a client like `mysql`.

mysql -u your_user -p

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.

SHOW GLOBAL VARIABLES LIKE 'binlog_format';

4

Attempt to execute the statement that caused the error. This will likely succeed now.

YOUR_STATEMENT_HERE;

2. Alter Table to Use InnoDB medium

Converts tables using statement-based logging engines to InnoDB, which supports row-based logging.

1

Identify tables that are not using InnoDB and are causing the issue. You can check storage engines with:

SHOW TABLE STATUS FROM your_database_name WHERE Engine != 'InnoDB';

2

For each identified table, alter its storage engine to InnoDB. Replace `your_table_name` and `your_database_name` accordingly.

ALTER TABLE your_database_name.your_table_name ENGINE=InnoDB;

3

After converting all relevant tables, ensure your `binlog_format` is set to `ROW` (or `MIXED` if preferred).

SET GLOBAL binlog_format = 'ROW';

4

Attempt to execute the statement that caused the error.

YOUR_STATEMENT_HERE;

3. Change Binary Log Format to STATEMENT medium

Forces the entire binary log to use statement-based logging, avoiding the row format mismatch.

1

Connect to your MySQL server.

mysql -u your_user -p

2

Set the `binlog_format` to `STATEMENT`. This requires a server restart.

SET GLOBAL binlog_format = 'STATEMENT';

3

Find the MySQL configuration file (e.g., `my.cnf` or `my.ini`).

text

4

Add or modify the `binlog_format` setting in the `[mysqld]` section of your configuration file.

[mysqld]
binlog_format = STATEMENT

5

Restart your MySQL server for the configuration change to take effect.

sudo systemctl restart mysql  # Or your specific service command

6

Verify the change after restart.

SHOW GLOBAL VARIABLES LIKE 'binlog_format';

7