MySQL raises error 1870 when row-based replication tries to log changes to BLACKHOLE tables, because no row events can be written to the binary log.
MySQL error 1870 (WARN_ON_BLOCKHOLE_IN_RBR) appears when row-based replication attempts to log changes against BLACKHOLE tables, which discard data and cannot generate row events. Switch to STATEMENT logging, disable binlog for the session, or avoid modifying BLACKHOLE tables to resolve the warning.
WARN_ON_BLOCKHOLE_IN_RBR
MySQL throws error 1870 (SQLSTATE HY000) when the server runs in row-based replication (RBR) mode and a statement modifies a table that uses the BLACKHOLE storage engine. Because BLACKHOLE ignores all data, the server cannot write the required row images into the binary log, so it warns that no row events were recorded.
The warning protects replica consistency. If row events are missing from the binary log, downstream replicas could miss changes and drift. Addressing the issue is critical before relying on the binlog for replication, backups, or point-in-time recovery.
The immediate trigger is executing INSERT, UPDATE, DELETE, LOAD DATA, or ALTER TABLE … INSERT/DELETE operations on a BLACKHOLE table while binlog_format is set to ROW or MIXED (row path chosen). MySQL 5.7.2 and newer detect the situation and emit error 1870.
Sessions that temporarily enable row logging with SET SESSION binlog_format = 'ROW' also hit the warning when they touch BLACKHOLE tables. Replication filters do not help because the write happens on the source before filters apply.
Fixes revolve around preventing row logging for BLACKHOLE tables or avoiding writes to them on the source. The fastest remedy is to switch to STATEMENT format for the session or globally if safe.
Another option is to disable binary logging just for the statement by toggling sql_log_bin, ensuring no row or statement events are written. Finally, redesign workloads so that BLACKHOLE tables are used only on replicas or not modified at all.
Many users employ BLACKHOLE tables for asynchronous auditing or federation. When the primary switches from STATEMENT to ROW for finer replication control, legacy jobs that still insert into BLACKHOLE start generating error 1870. Isolate those jobs on a replica or rewrite them to use normal tables.
ETL pipelines sometimes bulk-insert into BLACKHOLE tables to trigger triggers or events. Convert the table to MEMORY or TEMPORARY, or wrap the load in SET sql_log_bin = 0 to avoid the warning while keeping pipeline logic intact.
Do not modify BLACKHOLE tables on any server that must write row events. Use them only on replicas that receive statement logs or no logs at all.
Standardize binlog_format across environments and document when STATEMENT is required. Monitor the error log for code 1870 and create alerts so you can react before replicas lose data.
Error 1236: Could not find first log file - occurs when replicas miss events after skipping 1870. Ensure full backups and binlog retention.
Error 1592: relay log read failure - often surfaces after 1870 warnings if row events go missing. Re-sync replicas or avoid BLACKHOLE writes.
Any DML on a BLACKHOLE table while binlog_format = ROW or MIXED triggers the warning because the server cannot store row images for a table that discards data.
In MIXED mode MySQL may switch to ROW automatically for non-deterministic statements, leading to unexpected 1870 warnings if those statements affect BLACKHOLE tables.
Developers sometimes set SESSION binlog_format = 'ROW' for debugging but forget to reset it, causing their DML on BLACKHOLE tables to raise 1870.
Replica fails when binlog gaps appear after skipping unsafe events like 1870.
Occurs when a replica cannot apply row events missing due to earlier 1870 warnings.
Warns about non-deterministic statements that force RBR, similar to 1870 context.
It is a warning on the source, but if ignored it can cause replicas to miss data and eventually fail, so treat it as critical.
If the server never uses binary logs for replication or recovery, you may ignore it, but best practice is still to avoid BLACKHOLE writes in RBR.
No. In MIXED mode MySQL switches to ROW for many statements, so 1870 can still appear. Use STATEMENT or disable logging for the statement.
Galaxy highlights server warnings in the results pane, lets you toggle session variables quickly, and shares vetted scripts that switch formats safely, reducing replication risks.
.avif)
.avif){kind=logo}
