Bug #39841 update trigger on blackhole engine table

• Submitted: 3 Oct 2008 14:43
• Modified: 3 Oct 2008 20:03
• Reporter: Zhang Lu
• Status: Not a Bug
• Category: MySQL Server: Stored Routines
• Severity: S2 (Serious)
• Version: 5.0.38 +
• OS: Linux (RHEL 4)
• CPU Architecture: Any
• Tags: blackhole, update trigger

[3 Oct 2008 14:43] Zhang Lu

Description:
Refer to BUG #18309

After #18390 been claimed fixed, the update trigger on blackhole engine nolonger fire up on update to the base table.
No error message

How to repeat:
create database if not exists test;
use test ;

drop table if exists t2b;
create table t2b (
    i int not null primary key,
    c char(10)
) engine = blackhole;

drop table if exists t2m;
create table t2m (
    i int not null primary key,
    c char(10)
) engine = myisam;

create trigger t2b_bi before insert on t2b
for each row
insert into t2m values (new.i, new.c);

create trigger t2b_bu before update on t2b
for each row
update t2m set c = new.c where i=new.i;

insert into t2b values (1, 'a');
insert into t2b values (2, 'b');

select * from t2m;

update t2b set c = 'bb' where i = 2;

select * from t2m;

[3 Oct 2008 20:03] Sveta Smirnova

Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Please read at http://dev.mysql.com/doc/refman/5.0/en/blackhole-storage-engine.html:

INSERT triggers for BLACKHOLE  tables work as expected. However, because the BLACKHOLE table does not actually store any data, UPDATE and DELETE triggers are not activated: The FOR EACH ROW clause in the trigger definition does not apply because there are no rows.