problems with ER & triggers
User on IDS 10 (RHEL4) chained Enterprise Replication: table1 on serv1 replicates to table2 on serv2, a trigger/stored procedure on table2 populates table3, and table3 was supposed to replicate back to table4 on serv1. The second replicate never fired, though manual inserts or load/unload into the tables worked fine. Answer: ER deliberately does not cascade — changes made by the replication apply thread, including rows written by triggers on the target, are not recaptured, to avoid replication loops. Behaviour is by design, not a bug; no workaround given.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
I have some problems with replicates using ER and triggers on IDS 10 FC4/Rhel 4 platform. So, I'm replicating the table1 on serv1 to another table2 on serv2. That it's ok. I have a trigger on target table table2 on serv2 that runs a stored procedure that insert some columns of table2 and a unique number allocated by me on table table3 on serv2. It's ok. I want to replicate this table, table3 on serv2 to table4 on serv1. I define replicate but it doesn't work. The chain is : table1/serv1-----replicate1----table2/serv2-----trigger(stored procedure)----table3/serv2-----replicate2----table4/serv1. Replicate2 doesn't work. But 1. If I insert manually a row in table3 on serv2 replicate2 works! Only if table3 on serv 2 is populated by the trigger replicate2 doesn't work ! 2. If I populated table2/serv2 with unload/load, not using replicate1 , the rest of chain .. table2/serv2-----trigger(stored procedure)----table3/serv2-----replicate2----table4/serv1 works, even replicate2 works !! What is wrong ? Few months ago I tried to define 2 replicates A--B and B--C....but B--C replicate doesn't work; it's necessary to define directly A--C. Could be the similar problem now, even if I intercalated triggers between replicates ?
cristizaharioiu wrote: > I have some problems with replicates using ER and triggers on IDS 10 > FC4/Rhel 4 platform. > > So, > > I'm replicating the table1 on serv1 to another table2 on serv2. That > it's ok. > > I have a trigger on target table table2 on serv2 that runs a stored > procedure that insert some columns of table2 and a unique number > allocated by me on table table3 on serv2. It's ok. > > I want to replicate this table, table3 on serv2 to table4 on serv1. I > define replicate but it doesn't work. > > The chain is : > > table1/serv1-----replicate1----table2/serv2-----trigger(stored > procedure)----table3/serv2-----replicate2----table4/serv1. > > Replicate2 doesn't work. > > But > > 1. If I insert manually a row in table3 on serv2 replicate2 works! Only > if table3 on serv 2 is > > populated by the trigger replicate2 doesn't work ! > > 2. If I populated table2/serv2 with unload/load, not using replicate1 > , the rest of chain .. table2/serv2-----trigger(stored > procedure)----table3/serv2-----replicate2----table4/serv1 works, even > replicate2 works !! > > What is wrong ? > > Few months ago I tried to define 2 replicates A--B and B--C....but > B--C replicate doesn't work; it's necessary to define directly A--C. > > Could be the similar problem now, even if I intercalated triggers > between replicates ? > If you could provide your replicate definitions and the schemas including triggers, that would help. It *may* just be a case of adding : -T --firetrigger fire triggers when replicating to the definition of replicate1 But then again, it may not :-)
I defined replicate1 with firetrigger. I don't define replicate2 with firetrigger because on table4/serv1 is not trigger. TBP wrote: > cristizaharioiu wrote: > > I have some problems with replicates using ER and triggers on IDS 10 > > FC4/Rhel 4 platform. > > > > So, > > > > I'm replicating the table1 on serv1 to another table2 on serv2. That > > it's ok. > > > > I have a trigger on target table table2 on serv2 that runs a stored > > procedure that insert some columns of table2 and a unique number > > allocated by me on table table3 on serv2. It's ok. > > > > I want to replicate this table, table3 on serv2 to table4 on serv1. I > > define replicate but it doesn't work. > > > > The chain is : > > > > table1/serv1-----replicate1----table2/serv2-----trigger(stored > > procedure)----table3/serv2-----replicate2----table4/serv1. > > > > Replicate2 doesn't work. > > > > But > > > > 1. If I insert manually a row in table3 on serv2 replicate2 works! Only > > if table3 on serv 2 is > > > > populated by the trigger replicate2 doesn't work ! > > > > 2. If I populated table2/serv2 with unload/load, not using replicate1 > > , the rest of chain .. table2/serv2-----trigger(stored > > procedure)----table3/serv2-----replicate2----table4/serv1 works, even > > replicate2 works !! > > > > What is wrong ? > > > > Few months ago I tried to define 2 replicates A--B and B--C....but > > B--C replicate doesn't work; it's necessary to define directly A--C. > > > > Could be the similar problem now, even if I intercalated triggers > > between replicates ? > > > If you could provide your replicate definitions and the schemas > including triggers, that would help. > > It *may* just be a case of adding : > > -T --firetrigger fire triggers when replicating > > to the definition of replicate1 > > But then again, it may not :-)
In short ... "Results of triggered actions from a replicate are not replicated" This is to avoid the potential of infinite replicate => trigger => replicate => trigger => replicate etc. :P
We do not cascade replicate. That means that we do not recapture the changes made by the replication apply thread because that would result in a 'loop-de-loop' echo problem. That would include rows changed by triggers on the target. cristizaharioiu wrote: > I defined replicate1 with firetrigger. I don't define replicate2 with > firetrigger because on table4/serv1 is not trigger. > > TBP wrote: > >>cristizaharioiu wrote: >> >>>I have some problems with replicates using ER and triggers on IDS 10 >>>FC4/Rhel 4 platform. >>> >>>So, >>> >>>I'm replicating the table1 on serv1 to another table2 on serv2. That >>>it's ok. >>> >>>I have a trigger on target table table2 on serv2 that runs a stored >>>procedure that insert some columns of table2 and a unique number >>>allocated by me on table table3 on serv2. It's ok. >>> >>>I want to replicate this table, table3 on serv2 to table4 on serv1. I >>>define replicate but it doesn't work. >>> >>>The chain is : >>> >>>table1/serv1-----replicate1----table2/serv2-----trigger(stored >>>procedure)----table3/serv2-----replicate2----table4/serv1. >>> >>>Replicate2 doesn't work. >>> >>>But >>> >>>1. If I insert manually a row in table3 on serv2 replicate2 works! Only >>>if table3 on serv 2 is >>> >>>populated by the trigger replicate2 doesn't work ! >>> >>>2. If I populated table2/serv2 with unload/load, not using replicate1 >>>, the rest of chain .. table2/serv2-----trigger(stored >>>procedure)----table3/serv2-----replicate2----table4/serv1 works, even >>>replicate2 works !! >>> >>>What is wrong ? >>> >>>Few months ago I tried to define 2 replicates A--B and B--C....but >>>B--C replicate doesn't work; it's necessary to define directly A--C. >>> >>>Could be the similar problem now, even if I intercalated triggers >>>between replicates ? >>> >> >>If you could provide your replicate definitions and the schemas >>including triggers, that would help. >> >>It *may* just be a case of adding : >> >> -T --firetrigger fire triggers when replicating >> >>to the definition of replicate1 >> >>But then again, it may not :-) > >
I understand. Thank you ! Madison Pruet wrote: > We do not cascade replicate. That means that we do not recapture the > changes made by the replication apply thread because that would result > in a 'loop-de-loop' echo problem. That would include rows changed by > triggers on the target. > > > cristizaharioiu wrote: > > I defined replicate1 with firetrigger. I don't define replicate2 with > > firetrigger because on table4/serv1 is not trigger. > > > > TBP wrote: > > > >>cristizaharioiu wrote: > >> > >>>I have some problems with replicates using ER and triggers on IDS 10 > >>>FC4/Rhel 4 platform. > >>> > >>>So, > >>> > >>>I'm replicating the table1 on serv1 to another table2 on serv2. That > >>>it's ok. > >>> > >>>I have a trigger on target table table2 on serv2 that runs a stored > >>>procedure that insert some columns of table2 and a unique number > >>>allocated by me on table table3 on serv2. It's ok. > >>> > >>>I want to replicate this table, table3 on serv2 to table4 on serv1. I > >>>define replicate but it doesn't work. > >>> > >>>The chain is : > >>> > >>>table1/serv1-----replicate1----table2/serv2-----trigger(stored > >>>procedure)----table3/serv2-----replicate2----table4/serv1. > >>> > >>>Replicate2 doesn't work. > >>> > >>>But > >>> > >>>1. If I insert manually a row in table3 on serv2 replicate2 works! Only > >>>if table3 on serv 2 is > >>> > >>>populated by the trigger replicate2 doesn't work ! > >>> > >>>2. If I populated table2/serv2 with unload/load, not using replicate1 > >>>, the rest of chain .. table2/serv2-----trigger(stored > >>>procedure)----table3/serv2-----replicate2----table4/serv1 works, even > >>>replicate2 works !! > >>> > >>>What is wrong ? > >>> > >>>Few months ago I tried to define 2 replicates A--B and B--C....but > >>>B--C replicate doesn't work; it's necessary to define directly A--C. > >>> > >>>Could be the similar problem now, even if I intercalated triggers > >>>between replicates ? > >>> > >> > >>If you could provide your replicate definitions and the schemas > >>including triggers, that would help. > >> > >>It *may* just be a case of adding : > >> > >> -T --firetrigger fire triggers when replicating > >> > >>to the definition of replicate1 > >> > >>But then again, it may not :-) > > > >