Bug #38355 Delete fails in Blackhole table on slave when using row-based replication

• Submitted: 24 Jul 2008 20:59
• Modified: 25 Jul 2008 10:26
• Reporter: Gordon Shannon
• Status: Duplicate
• Category: MySQL Server: Row Based Replication ( RBR )
• Severity: S2 (Serious)
• Version: 5.1.24-rc-community-log
• OS: Any
• CPU Architecture: Any

[24 Jul 2008 20:59] Gordon Shannon

Description:
When using row-based replication, inserts into a blackhole table on the slave work properly -- no data is inserted, and no errors are thrown.  But when deletes come from the master, an error is thrown on the slave, just as if it were a MyIsam or Innodb table:  

Last_Error: Could not execute Delete_rows event on table ci_sentiment_db.author_stats; Can't find record in 'author_stats', Error_code: 1032; handler error HA_ERR_END_OF_FILE; the event's master log mysql-bin.000411, end_log_pos 485772605

This is surprising, and seems like a bug. It seems to make blackhole not a viable option in 5.1 unless you force statement-based replication.

How to repeat:
-- Master:
create table t (id int) engine=myisam;

-- Slave:
alter table t engine=blackhole;

-- Master:
set session binlog_format='ROW'
insert into t values (1), (2);

-- Slave:
select count(*) from t:  -- yields 0 rows

-- Master:
set session binlog_format='ROW'
delete from t where id=1;

-- Slave:
Error!

Suggested fix:
Fix or document the limitations in the Blackhole section and Replication section.

[25 Jul 2008 10:26] Susanne Ebrecht

Many thanks for writing a bug report.

I will set this bug as duplicate of bug #38360, because there are already discussions on the newer bug report.