📋

Description

This error occurs when MySQL attempts to write to the binary log using `BINLOG_FORMAT = STATEMENT`, but the current operation involves both tables configured for replication and tables that are not. Statement-based replication has limitations when mixing these table types within the same transaction, which can lead to data inconsistency on replica servers.

💬

Error Message

Cannot execute statement: impossible to write to binary log since BINLOG_FORMAT = STATEMENT, and both replicated and non replicated tables are written to.

🔍

Known Causes

3 known causes

⚠️

Mixed Table Operations

An SQL statement or transaction attempts to modify both tables that are included in replication and tables that are explicitly ignored by replication filters.

⚠️

Statement-Based Replication (SBR)

The MySQL server is configured with `BINLOG_FORMAT = STATEMENT`, which is restrictive when operations involve a mix of replicated and non-replicated tables.

⚠️

Inconsistent Replication Filters

Replication filters (`replicate_do_db`, `replicate_ignore_db`, etc.) on the source are set up to selectively include or exclude tables, leading to the conflict during mixed operations.

🛠️

Solutions

3 solutions available

1. Switch BINLOG_FORMAT to MIXED or ROW medium

Change the binary log format to avoid statement-based replication issues with mixed table types.

1

Connect to your MySQL server as a user with sufficient privileges (e.g., root).

2

Check the current binary log format.

SHOW VARIABLES LIKE 'binlog_format';

3

If the format is STATEMENT, you need to change it. This requires a server restart. Edit your MySQL configuration file (e.g., `my.cnf` or `my.ini`).

4

Locate the `[mysqld]` section and add or modify the `binlog_format` directive to `MIXED` or `ROW`.

[mysqld]
binlog_format = MIXED

5

Save the configuration file and restart the MySQL server.

sudo systemctl restart mysql  # Or your specific service command

6

After the server restarts, verify the change.

SHOW VARIABLES LIKE 'binlog_format';

2. Ensure All Tables Use a Replicable Storage Engine advanced

Identify and convert non-replicable tables to a storage engine compatible with statement-based replication.

1

Connect to your MySQL server.

2

Query to identify tables that might not be compatible with statement-based replication (e.g., MyISAM tables if replication is intended for InnoDB).

SELECT table_schema, table_name, engine FROM information_schema.tables WHERE engine NOT IN ('InnoDB', 'NDB');

3

For each identified table, consider converting its storage engine to InnoDB. This can be done online for most versions of MySQL.

ALTER TABLE your_table_name ENGINE=InnoDB;

4

If you absolutely cannot convert certain tables, you must use `BINLOG_FORMAT = MIXED` or `BINLOG_FORMAT = ROW` as described in the previous solution.

3. Isolate Non-Replicated Tables advanced

Move non-replicated tables to a separate database or server that does not have binary logging enabled.

1

Identify tables that are intentionally not meant for replication.

2

Create a new database that will house these non-replicated tables.

CREATE DATABASE non_replicated_db;

3

Move the identified tables to this new database. This typically involves `ALTER TABLE ... MOVE TO DATABASE` or a combination of `CREATE TABLE ... LIKE` and `INSERT INTO ... SELECT` followed by `DROP TABLE`.

ALTER TABLE your_non_replicated_table MOVE TO DATABASE non_replicated_db;

4

Ensure that binary logging is disabled for the server or database instance where these non-replicated tables reside. If they are on a separate server, ensure binary logging is not enabled on that server.

5

If they are on the same server, you can sometimes achieve separation by configuring `replicate_ignore_db` in your replication setup, but this error specifically points to writing to the binlog, so moving them to a non-logged database or server is the most direct approach.

🔗

Related Errors

5 related errors

MySQL Error 1844: Binary Log Statement Format Conflict