Bug #19783 triggers are triggered on just single node in clustered table
Submitted: 12 May 2006 19:27 Modified: 12 May 2006 21:31
Reporter: johnny slakva Email Updates:
Status: Verified Impact on me:
Category:MySQL Cluster: Cluster (NDB) storage engine Severity:S4 (Feature request)
Version:mysql-5.0 OS:Linux (RHEL3)
Assigned to: CPU Architecture:Any
Tags: 5.0.21

[12 May 2006 19:27] johnny slakva
Overview: When i set triggers on a table with storage engine=ndbcluster, the trigger is only activated on node where triggering sql statement occured, not on all nodes.

Description: i tried to use triggers to maintain a copy of clustered table to make fulltext search index to be up to date. To achieve this, on each mysql node where i supposed to use fulltext, i created myisam table with same structure as i've had in clustered table, and made triggers on each mysql node that were supposed to update this myisam table on each node when something occurs in clustered table.

Expected behavior: i expected all updates in clustered table to be repeated in myisam tables on each node.

Actual behavior: it updates myisam table only on the node where clustered table update occurs.

How to repeat:
on any node in cluster: 
create table tst (id int(11) auto_increment, txt varchar(20), primary key (id)) engine=ndbcluster;

on each mysql node in cluster:
create table tst1 (id int(11) auto_increment, txt varchar(20), primary key (id)) engine=myisam;
create trigger test after insert on tst
for each row insert into tst1 select * from tst where id=NEW.id

after this, do on any mysql node
insert into tst (tst) values ("test");

expected behavior:
record with "test" should appear on each node in tst1

actual behavior:
record with "test" appears only on mysql where insert was issued.

Suggested fix:
i think when triggers are manually created on each node in cluster for clustered tables they should be invoked for all nodes.
[12 May 2006 21:31] Hartmut Holzgraefe
This is expected behavior, triggers act on the SQL level and the SQL
part of the query is executed in the mysqld server the client issuing
the query is connected to. Only the actual data is replicated to all
connected mysqld api nodes but not the query changing the data.

This could possibly be solved by a NDBAPI application using the
clusters internal notification event mechanism.

I'm keeping this bug report as a feature request as it might make
sense to implement this on the SQL level at a later stage, too,
but don't expect this to be implemented anytime soon ...