Description:
Updates on Blackhole tables are not supported (I could not find the relevant docs on this issue, but when I try it, I get an error saying that such action is not supported).
However, triggers associated with an UPDATE statement are executed, although an error is issued.
mysql>select version();
+----------------+
| version() |
+----------------+
| 5.0.19-max-log |
+----------------+
mysql>drop table if exists t2b;
mysql>create table t2b (
-> i int not null primary key,
-> c char(10)
-> ) engine = blackhole;
Query OK, 0 rows affected (0.02 sec)
mysql>drop table if exists t2m;
Query OK, 0 rows affected (0.00 sec)
mysql>create table t2m (
-> i int not null primary key,
-> c char(10)
-> ) engine = myisam;
Query OK, 0 rows affected (0.02 sec)
mysql>create trigger t2b_bi before insert on t2b
-> for each row
-> insert into t2m values (new.i, new.c);
Query OK, 0 rows affected (0.02 sec)
mysql>create trigger t2b_bu before update on t2b
-> for each row
-> update t2m set c = new.c where i=new.i;
Query OK, 0 rows affected (0.00 sec)
mysql>insert into t2b values (1, 'a');
Query OK, 1 row affected (0.00 sec)
mysql>insert into t2b values (2, 'b');
Query OK, 1 row affected (0.00 sec)
mysql>
mysql>select * from t2m;
+---+------+
| i | c |
+---+------+
| 1 | a |
| 2 | b |
+---+------+
2 rows in set (0.00 sec)
mysql>
mysql>update t2b set c = 'bb' where i = 2;
ERROR 1031 (HY000): Table storage engine for 't2b' doesn't have this option
mysql>
mysql>select * from t2m;
+---+------+
| i | c |
+---+------+
| 1 | a |
| 2 | bb |
+---+------+
2 rows in set (0.00 sec)
How to repeat:
select version();
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;
Suggested fix:
Given that BLACKHOLE tables are used only for their side effects, it should be convenient to remove the error completely.
Currently, to use UPDATE triggers in replication, I am inserting this option in my slave configuration file:
slave-skip-errors=1031