<p>Error 1844 is raised when STATEMENT-based binary logging tries to write to both replicated and non-replicated tables in the same statement.</p>
<p>MySQL Error 1844: ER_BINLOG_STMT_MODE_AND_NO_REPL_TABLES appears when STATEMENT binlog_format meets a query touching both replicated and filtered tables. Switch binlog_format to ROW or split the statement to fix the issue.</p>
Cannot execute statement: impossible to write to binary
Error 1844 fires when MySQL is running in STATEMENT binlog_format and a single DML statement targets tables that replication filters mark differently. Because the statement cannot be replayed safely on replicas, MySQL blocks execution.
The error text reads: "Cannot execute statement: impossible to write to binary log since BINLOG_FORMAT = STATEMENT, and both replicated and non replicated tables are written to."
The error often appears after enabling --replicate-do-table or --replicate-ignore-table on replicas. Any UPDATE, INSERT, DELETE, or MERGE touching both allowed and filtered tables in one statement will trigger the failure on the primary.
Multi-table statements, triggers referencing other schemas, and complex joins are common culprits because they silently mix table scopes.
With STATEMENT logging, the same SQL text is replayed on replicas. If replicas do not have all referenced tables or filters skip some, data divergence occurs. MySQL therefore refuses to log the statement to protect data integrity.
Most teams switch to ROW binlog_format, which records individual row events and ignores replication filters safely. Alternatively, split the offending statement into two transactions that each touch only replicated or only non-replicated tables.
Standardize on ROW or MIXED binlog_format in production, audit replication filter rules during schema reviews, and add CI checks to flag cross-schema DML. Galaxy’s linting engine can surface mixed-table writes before they hit production.
replicate-do-table or replicate-wild-do-table includes one table in the statement, while another table is excluded, creating a conflict.
The server is configured with BINLOG_FORMAT=STATEMENT, which cannot serialize safe mixed-table operations.
A trigger on a replicated table updates a non-replicated table, unintentionally mixing scopes.
Statements that join or update tables across databases, where one database is filtered out of replication, trigger the error.
Raised when ROW format meets a storage engine that only supports statement logging.
Occurs when a statement considered unsafe for STATEMENT logging is attempted.
Not replication specific but often appears after aborted mixed-table operations.
Yes. Issue SET sql_log_bin = 0 before the statement and SET sql_log_bin = 1 afterward. This suppresses the binary log but should be used only for non-critical local data.
MIXED chooses STATEMENT when safe and ROW when needed. In nearly all mixed-table scenarios it will switch to ROW and prevent the error.
If the table is meant to replicate, adding it and removing exclude filters can resolve the error. Otherwise switch to ROW or split the statement.
Galaxy’s SQL editor highlights replication filters and lint rules. It flags queries that join filtered and unfiltered tables, helping teams fix issues before deployment.
.avif)
.avif){kind=logo}
