| 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 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 ...
Description: 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.