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
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