Triggers not getting synced on child node when created dynamically on the parent and calling 'syncTriggers' function

On the master does the sym_trigger table have database triggers installed on it? Do you have auto.sync.configuration set to true?

Looking at the 1.7.3 documentation, it seems that the 'auto.sync.configuration' property is set to true by default. We do not set this property to false. We create triggers dynamically when the master instance is running using SQL statements. We set the value for 'create_time' and 'last_updated_time' to the current_timestamp. After making these entries in the database, we call syncTrigger function on the master via JMX.

Kindly revert back if you need additional information.

In that case, there should be triggers created on the sym_trigger table. Do you see any triggers configured for sym_trigger? Is the table sym_trigger configured as a table to sync in sym_trigger (it can be, but shouldn't have to be)?

I do see triggers created on the sym_trigger table. I had a glance and it seemed it is doing what it is expected to do. What seems strange is the fact that triggers get synced properly on manual re-registration. Earlier we used to create triggers on all tables as part of our initial configuration (no dynamic entries). All triggers were properly synced on client registration. Since this generated a huge volume of data in the sym_data and sym_data_event table; we decided to go with trigger-on-demand architecture wherein we solicited table information from the client via JMX and created triggers on those tables only. It is these trigger information that is added dynamically that is not getting synced.

Below is the insert function for your reference:
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION av_drs01.fon_i_for_154522872()
RETURNS "trigger" AS
$BODY$
begin
if 1=1 and 1=1 then
insert into sym_data
(table_name, event_type, trigger_hist_id, row_data, create_time)
values(
'sym_trigger',
'I',
7,

case when new."trigger_id" is null then '' else '"' || cast(new."trigger_id" as varchar) || '"' end ||','||
case when new."source_catalog_name" is null then '' else '"' || replace(replace(new."source_catalog_name",E'\\',E'\\\\'),'"',E'
"') || '"' end ||','||
case when new."source_schema_name" is null then '' else '"' || replace(replace(new."source_schema_name",E'\\',E'\\\\'),'"',E'
"') || '"' end ||','||
case when new."source_table_name" is null then '' else '"' || replace(replace(new."source_table_name",E'\\',E'\\\\'),'"',E'
"') || '"' end ||','||
case when new."target_catalog_name" is null then '' else '"' || replace(replace(new."target_catalog_name",E'\\',E'\\\\'),'"',E'
"') || '"' end ||','||
case when new."target_schema_name" is null then '' else '"' || replace(replace(new."target_schema_name",E'\\',E'\\\\'),'"',E'
"') || '"' end ||','||
case when new."target_table_name" is null then '' else '"' || replace(replace(new."target_table_name",E'\\',E'\\\\'),'"',E'
"') || '"' end ||','||
case when new."source_node_group_id" is null then '' else '"' || replace(replace(new."source_node_group_id",E'\\',E'\\\\'),'"',E'
"') || '"' end ||','||
case when new."target_node_group_id" is null then '' else '"' || replace(replace(new."target_node_group_id",E'\\',E'\\\\'),'"',E'
"') || '"' end ||','||
case when new."channel_id" is null then '' else '"' || replace(replace(new."channel_id",E'\\',E'\\\\'),'"',E'
"') || '"' end ||','||
case when new."sync_on_update" is null then '' else '"' || cast(new."sync_on_update" as varchar) || '"' end ||','||
case when new."sync_on_insert" is null then '' else '"' || cast(new."sync_on_insert" as varchar) || '"' end ||','||
case when new."sync_on_delete" is null then '' else '"' || cast(new."sync_on_delete" as varchar) || '"' end ||','||
case when new."sync_on_incoming_batch" is null then '' else '"' || cast(new."sync_on_incoming_batch" as varchar) || '"' end ||','||
case when new."sync_column_level" is null then '' else '"' || cast(new."sync_column_level" as varchar) || '"' end ||','||
case when new."name_for_update_trigger" is null then '' else '"' || replace(replace(new."name_for_update_trigger",E'\\',E'\\\\'),'"',E'
"') || '"' end ||','||
case when new."name_for_insert_trigger" is null then '' else '"' || replace(replace(new."name_for_insert_trigger",E'\\',E'\\\\'),'"',E'
"') || '"' end ||','||
case when new."name_for_delete_trigger" is null then '' else '"' || replace(replace(new."name_for_delete_trigger",E'\\',E'\\\\'),'"',E'
"') || '"' end ||','||
case when new."sync_on_update_condition" is null then '' else '"' || replace(replace(new."sync_on_update_condition",E'\\',E'\\\\'),'"',E'
"') || '"' end ||','||
case when new."sync_on_insert_condition" is null then '' else '"' || replace(replace(new."sync_on_insert_condition",E'\\',E'\\\\'),'"',E'
"') || '"' end ||','||
case when new."sync_on_delete_condition" is null then '' else '"' || replace(replace(new."sync_on_delete_condition",E'\\',E'\\\\'),'"',E'
"') || '"' end ||','||
case when new."initial_load_select" is null then '' else '"' || replace(replace(new."initial_load_select",E'\\',E'\\\\'),'"',E'
"') || '"' end ||','||
case when new."node_select" is null then '' else '"' || replace(replace(new."node_select",E'\\',E'\\\\'),'"',E'
"') || '"' end ||','||
case when new."tx_id_expression" is null then '' else '"' || replace(replace(new."tx_id_expression",E'\\',E'\\\\'),'"',E'
"') || '"' end ||','||
case when new."excluded_column_names" is null then '' else '"' || replace(replace(new."excluded_column_names",E'\\',E'\\\\'),'"',E'
"') || '"' end ||','||
case when new."initial_load_order" is null then '' else '"' || cast(new."initial_load_order" as varchar) || '"' end ||','||
case when new."create_time" is null then '' else '"' || to_char(new."create_time", 'YYYY-MM-DD HH24:MI:SS') || '"' end ||','||
case when new."inactive_time" is null then '' else '"' || to_char(new."inactive_time", 'YYYY-MM-DD HH24:MI:SS') || '"' end ||','||
case when new."last_updated_by" is null then '' else '"' || replace(replace(new."last_updated_by",E'\\',E'\\\\'),'"',E'
"') || '"' end ||','||
case when new."last_updated_time" is null then '' else '"' || to_char(new."last_updated_time", 'YYYY-MM-DD HH24:MI:SS') || '"' end ,
CURRENT_TIMESTAMP
);
insert into sym_data_event (node_id, data_id, channel_id, transaction_id)
(select node_id, currval('sym_data_data_id_seq'), 'config', null
from sym_node c
where c.node_group_id='replica' and c.sync_enabled=1 and fn_sym_node_disabled() != c.node_id );
end if;
return null;
end;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION av_drs01.fon_i_for_154522872() OWNER TO av_drs01;

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

The sequence of events that we follow for configuration:

Server SymmetricDS instance
========================
1. Master Symmetric Configurations via scripts
2. Start SymmetricDS server

Client SymmetricDS instance
========================
1. Slave Symmetric Configurations via scripts
2. Start SymmetricDS server
3. Start process that sends table information to master via JMX

When the slave instance is started it is registered with the master as we have 'auto.registration' set to true on master (So registration occurs before trigger information is recorded). The slave then sends the tables it is interested in via JMX. The master instance recieves the table information and creates entries in sym_trigger table. After creating entries, it invokes syncTriggers function on the 'Node' JMX service.

At this point it is observed that the triggers are not sync'ed onto the slave instance as part of the configuration sync.

Can you post your configuration for node_groups and node_group_links?

HI Chris/Team,
Any updates on this issue? Pardon me for pushing you guys on this considering the fact that you guys must be very busy trying to give us those fabulous stuff in 2.0.

But this is turning out to be a BLOCKER issue for us. We are trying to deliver a replication framework where the basic replication itself is not happening as the triggers are not getting synced.
Please do let me know whatever information you require to debug this issue.

Regards,
Prashanto

After you insert the triggers on the master, what does sym_data and sym_data_event have in it?

sym_data
=========
1;"sym_trigger";"I";""1",,"avaya_system_data","pem_module",,"avaya_system_data","pem_module","master","replica","SM","1","1","1","1","0","av_u_pem_module_1","av_i_pem_module_1","av_d_pem_module_1",,,,,,,"2","100","2009-08-25 19:07:11",,"master","2009-08-25 19:07:11"";"";"";7;"2009-08-25 19:07:11.354091"

sym_data_event
=============
1;"2";"config";"";1;1
2;"2";"reload";"";4;1
3;"2";"reload";"";5;1
4;"2";"reload";"";5;1

We create triggers on the master node, call 'syncTriggers' function and then call 'reloadNode' function to open initial load for the slave node.

Forgot to add that we have upgraded to SymmetricDS version 1.7.3

It looks like the data was captured for the new trigger and sent to a node with the id of 2. I am guessing that is your client node?

You are saying that the sym_trigger row never shows up on the client node? Do the reloads successfully complete?

Thats correct. We configure client node with an externalId of '2'.

The row in sym_trigger DOES NOT show up on the client node. The reload DOES NOT happen presumably because it does not have the target information (as sym_trigger info is missing).

I do see the reload information in the incoming_batch table:
----------------------------------------------------------------------------------
4;"001";"reload";"OK";"2009-08-25 09:37:55.80172"
5;"001";"reload";"OK";"2009-08-25 09:37:55.808421"

Don't know how much this would help but we do all this via an MBean that we deploy on the master using SymmetricDS provided extension.

I am at a loss still. The change to sym_trigger was captured. It looked like it was sent to node 2 because batched=1 on sym_data_event. What is the content of sym_outgoing_batch on the root for batch_id=1?

sym_outgoing_batch
==================
1;"2";"config";"EV";"OK";"2009-08-25 19:43:50.217903"

It just occurred to me why this might be happening. On a reload, all previous batches are marked as sent, so I am guessing that the act of reloading is hijacking your trigger change. Are you making the call to syncTriggers and reloadNode from the client in separate JMX calls? If so, then can you force a pull from the client before calling reloadNode?

I did not get a chance to implement your suggestion. But most likely it seems that the issue is what you are pointing out. I will try this out tomorrow and get back to you.
The reason why we make a JMX call for syncTriggers is an altogether different discussion which I would start in a new thread.

I followed your suggestion and it did work. I changed my logic to move initial load out of the trigger creation process. I also forced a pull request from the client after the syncTriggers call. This solved the issue and the triggers were successfully replicated onto the client.
Do you see a case for assigning priority to events based on their type - config changes before reload? Let us know your thoughts on this.
Thanks for your inputs on this.

However we are facing another problem during initial load for which I have raised the following JIRA:
(SYMMETRICDS-144) Initial load failing when replication is done across schemas (non-public) in PostgreSQL. Data changes however are replicated successfully.