SymmetricDS

Initial load failing when replication is done across schemas (non-public) in PostgreSQL. Data changes however are replicated successfully

Details

  • Type: Bug Bug
  • Status: Closed Closed
  • Priority: Blocker Blocker
  • Resolution: Fixed
  • Affects Version/s: 1.7.3
  • Fix Version/s: 1.7.4
  • Component/s: None
  • Labels:
    None
  • Environment:
    RHEL 5.0
  • Number of attachments :
    0

Description

1. Configured SymmetricDS on server
2. Created triggers on server
3. Started SymmetricDS server
4. Configured SymmetricDS on client
5. Started SymmetricDS client
6. Sent initial load to client
At this point in time when I check the client database, I DO NOT see the data for tables that were configured for replication (triggers on server).

7. Modified table data on server
The data is replicated successfully on client.

===========================
SymmetricDS server table dump: |
===========================

sym_data
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1;"sym_node_security";"U";""2","e078da5bc9a42ad55b85365a26fa68","0","2009-08-28 19:15:23","1",,"001"";""2"";"";6;"2009-08-28 19:16:23.117985"
2;"pem_module";"R";"";"";"";2;"2009-08-28 19:16:23.152001"
3;"sym_node_security";"U";""2","e078da5bc9a42ad55b85365a26fa68","0","2009-08-28 19:15:23","0","2009-08-28 19:16:23","001"";""2"";"";6;"2009-08-28 19:16:23.22814"
4;"pem_module";"U";""mymodule","My Module 123","123",";""mymodule"";"";2;"2009-08-28 19:19:57.971722"
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

sym_data_event
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1;"2";"reload";"";4;1
2;"2";"reload";"";5;1
3;"2";"reload";"";5;1
4;"2";"SM";"";6;1
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

sym_outgoing_batch
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1;"2";"config";"EV";"OK";"2009-08-28 19:16:22.995998"
2;"2";"reload";"EV";"OK";"2009-08-28 19:16:23.04699"
3;"2";"SM";"EV";"OK";"2009-08-28 19:16:23.060989"
4;"2";"reload";"EV";"OK";"2009-08-28 19:16:23.17899"
5;"2";"reload";"EV";"OK";"2009-08-28 19:16:23.245993"
6;"2";"SM";"EV";"OK";"2009-08-28 19:20:24.426593"
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

sym_outgoing_batch_hist
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1;"2";"NE";"2009-08-28 19:16:23.032";"2009-08-28 19:16:23.032";0;0;259;"pdev4vm1.platform.avaya.com";0;0;0;"";0;""
2;"2";"NE";"2009-08-28 19:16:23.055";"2009-08-28 19:16:23.055";0;0;10;"pdev4vm1.platform.avaya.com";0;0;0;"";0;""
3;"2";"NE";"2009-08-28 19:16:23.067";"2009-08-28 19:16:23.067";0;0;7;"pdev4vm1.platform.avaya.com";0;0;0;"";0;""
4;"2";"NE";"2009-08-28 19:16:23.20";"2009-08-28 19:16:23.208";0;0;7;"pdev4vm1.platform.avaya.com";0;1;0;"";0;""
5;"2";"NE";"2009-08-28 19:16:23.247";"2009-08-28 19:16:23.25";0;0;3;"pdev4vm1.platform.avaya.com";0;2;0;"";0;""
4;"2";"SE";"2009-08-28 19:16:23.258";"2009-08-28 19:16:23.279";0;0;21;"pdev4vm1.platform.avaya.com";0;0;0;"";0;""
5;"2";"SE";"2009-08-28 19:16:23.281";"2009-08-28 19:16:23.353";0;0;72;"pdev4vm1.platform.avaya.com";0;0;0;"";0;""
4;"2";"OK";"2009-08-28 19:16:23.56";"2009-08-28 19:16:23.563";686;2;1;"pdev4vm1.platform.avaya.com";259;0;0;"";0;""
5;"2";"OK";"2009-08-28 19:16:23.566";"2009-08-28 19:16:23.567";919;1;2;"pdev4vm1.platform.avaya.com";0;0;0;"";0;""
6;"2";"NE";"2009-08-28 19:20:24.435";"2009-08-28 19:20:24.435";0;0;11;"pdev4vm1.platform.avaya.com";0;1;0;"";0;""
6;"2";"SE";"2009-08-28 19:20:24.444";"2009-08-28 19:20:24.643";0;0;199;"pdev4vm1.platform.avaya.com";0;0;0;"";0;""
6;"2";"OK";"2009-08-28 19:20:24.749";"2009-08-28 19:20:24.752";1431;0;1;"pdev4vm1.platform.avaya.com";0;0;0;"";0;""
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

sym_trigger_hist
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1;1976586007;"sym_parameter";"";"";"ON_U_FOR_1976586007";"ON_I_FOR_1976586007";"ON_D_FOR_1976586007";1446208826;1859103676;"external_id,node_group_id,param_key,param_value";"external_id,node_group_id,param_key";"N";"2009-08-28 19:14:19.604";""
2;1;"pem_module";"";"avaya_system_data";"AV_U_PEM_MODULE_1";"AV_I_PEM_MODULE_1";"AV_D_PEM_MODULE_1";-1916852273;-1372624149;"module_db_name,module_name,module_version,module_update_date_time";"module_db_name";"N";"2009-08-28 19:14:19.638";""
3;1993863269;"sym_node_identity";"";"";"ON_U_FOR_1993863269";"ON_I_FOR_1993863269";"ON_D_FOR_1993863269";-316145662;1859054644;"node_id";"node_id";"N";"2009-08-28 19:14:19.663";""
4;1464939314;"sym_node_group";"";"";"ON_U_FOR_1464939314";"ON_I_FOR_1464939314";"ON_D_FOR_1464939314";-358377918;494015008;"node_group_id,description";"node_group_id";"N";"2009-08-28 19:14:19.674";""
5;1208748370;"sym_node_channel_ctl";"";"";"ON_U_FOR_1208748370";"ON_I_FOR_1208748370";"ON_D_FOR_1208748370";-913698557;-1417949952;"node_id,channel_id,suspend_enabled,ignore_enabled";"node_id,channel_id";"N";"2009-08-28 19:14:19.695";""
6;908978659;"sym_node_security";"";"";"ON_U_FOR_908978659";"ON_I_FOR_908978659";"ON_D_FOR_908978659";-677430451;-1447447101;"node_id,node_password,registration_enabled,registration_time,initial_load_enabled,initial_load_time,created_at_node_id";"node_id";"N";"2009-08-28 19:14:19.705";""
7;1415275214;"sym_node";"";"";"ON_U_FOR_1415275214";"ON_I_FOR_1415275214";"ON_D_FOR_1415275214";1160122347;3806544922;"node_id,node_group_id,external_id,sync_enabled,sync_url,schema_version,symmetric_version,database_type,database_version,heartbeat_time,timezone_offset,created_at_node_id";"node_id";"N";"2009-08-28 19:14:19.714";""
8;154522872;"sym_trigger";"";"";"ON_U_FOR_154522872";"ON_I_FOR_154522872";"ON_D_FOR_154522872";-796038111;-2126817876;"trigger_id,source_catalog_name,source_schema_name,source_table_name,target_catalog_name,target_schema_name,target_table_name,source_node_group_id,target_node_group_id,channel_id,sync_on_update,sync_on_insert,sync_on_delete,sync_on_incoming_batch,sync_column_level,name_for_update_trigger,name_for_insert_trigger,name_for_delete_trigger,sync_on_update_condition,sync_on_insert_condition,sync_on_delete_condition,initial_load_select,node_select,tx_id_expression,excluded_column_names,initial_load_order,create_time,inactive_time,last_updated_by,last_updated_time";"trigger_id";"N";"2009-08-28 19:14:19.727";""
9;1953365059;"sym_channel";"";"";"ON_U_FOR_1953365059";"ON_I_FOR_1953365059";"ON_D_FOR_1953365059";-1161298402;1470866498;"channel_id,processing_order,max_batch_size,max_batch_to_send,enabled,description";"channel_id";"N";"2009-08-28 19:14:19.772";""
10;399355049;"sym_node_group_link";"";"";"ON_U_FOR_399355049";"ON_I_FOR_399355049";"ON_D_FOR_399355049";-1687995919;-2435863620;"source_node_group_id,target_node_group_id,data_event_action";"source_node_group_id,target_node_group_id";"N";"2009-08-28 19:14:19.807";""
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

==========================
SymmetricDS client table dump: |
==========================

sym_incoming_batch
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
4;"001";"reload";"OK";"2009-08-28 09:46:23.795529"
5;"001";"reload";"OK";"2009-08-28 09:46:23.814572"
6;"001";"SM";"OK";"2009-08-28 09:50:25.436639"
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

sym_trigger
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
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-28 19:13:48";"";"av_drs01";"2009-08-28 19:13:48"
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

sym_node_security
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
"2";"e078da5bc9a42ad55b85365a26fa68";0;"2009-08-28 19:15:23";0;"2009-08-28 19:16:23";"001"
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Activity

Ascending order - Click to sort in descending order
Hide
Permalink
Prashanto Chatterjee added a comment -

I tried debugging through the SymmetricDS code for this. It seems that the trigger information is not available for data changes that come on the 'reload' channel. As such it does not have the target-schema and target-table information.

In the CsvLoader class, inside function "protected void setTable(String tableName)", the trigger information obtained from configurationService is null in case of 'reload'. This is expected as there is no trigger with channel_id set to 'reload'.
But reload being a SymmetricDS created channel, I would expect that reload would use the data from other created triggers on that table.

Since we set the default search_path for the Postgres DB user to the schema in which we keep SymmetricDS, we do not see any issues in case of configuration sync.

I jotted down whatever little I understood during debug. Kindly correct me if I am wrong in any step.

Show
Prashanto Chatterjee added a comment - I tried debugging through the SymmetricDS code for this. It seems that the trigger information is not available for data changes that come on the 'reload' channel. As such it does not have the target-schema and target-table information. In the CsvLoader class, inside function "protected void setTable(String tableName)", the trigger information obtained from configurationService is null in case of 'reload'. This is expected as there is no trigger with channel_id set to 'reload'. But reload being a SymmetricDS created channel, I would expect that reload would use the data from other created triggers on that table. Since we set the default search_path for the Postgres DB user to the schema in which we keep SymmetricDS, we do not see any issues in case of configuration sync. I jotted down whatever little I understood during debug. Kindly correct me if I am wrong in any step.
Hide
Permalink
Prashanto Chatterjee added a comment -

Did a hack and changed the channel_id of the trigger on the server to 'reload'. Sent an initial load to the agent. The initial load was SUCCESSFUL in replicating data.

This makes a case for special handling of data that is received on the 'reload' channel. Kindly correct me if I am wrong.

Show
Prashanto Chatterjee added a comment - Did a hack and changed the channel_id of the trigger on the server to 'reload'. Sent an initial load to the agent. The initial load was SUCCESSFUL in replicating data. This makes a case for special handling of data that is received on the 'reload' channel. Kindly correct me if I am wrong.
Hide
Permalink
Chris Henson added a comment -

Can you try the 1.7.4 snapshot?

http://snapshots.repository.codehaus.org/org/jumpmind/symmetric/symmetric/1.7.4-SNAPSHOT/

I made a patch to lookup the trigger differently if the channel is reload.

Show
Chris Henson added a comment - Can you try the 1.7.4 snapshot? http://snapshots.repository.codehaus.org/org/jumpmind/symmetric/symmetric/1.7.4-SNAPSHOT/ I made a patch to lookup the trigger differently if the channel is reload.
Hide
Permalink
Prashanto Chatterjee added a comment -

Hi Chris,
The 1.7.4 snapshot works perfectly. Thanks for the quick fix.

Could you kindly update us with the tentative release date of 1.7.4?

Regards,
Prashanto

Show
Prashanto Chatterjee added a comment - Hi Chris, The 1.7.4 snapshot works perfectly. Thanks for the quick fix. Could you kindly update us with the tentative release date of 1.7.4? Regards, Prashanto
Hide
Permalink
Chris Henson added a comment -

I can release it whenever you need it. I don't have any other plans for it unless someone runs into something ...

Show
Chris Henson added a comment - I can release it whenever you need it. I don't have any other plans for it unless someone runs into something ...
Hide
Permalink
Prashanto Chatterjee added a comment -

Hi Chris,
If you do not have anything else planned for 1.7.4 (apart from this and the initial.load.delete.first JIRA), I request you to kindly release it so that we can start using it for our next rounds of POCs and development activities.

Regards,
Prashanto

Show
Prashanto Chatterjee added a comment - Hi Chris, If you do not have anything else planned for 1.7.4 (apart from this and the initial.load.delete.first JIRA), I request you to kindly release it so that we can start using it for our next rounds of POCs and development activities. Regards, Prashanto
Hide
Permalink
Chris Henson added a comment -

I'll release it today.

Show
Chris Henson added a comment - I'll release it today.
Hide
Permalink
Prashanto Chatterjee added a comment -

It would have been good to have had the fix for JIRA http://jira.codehaus.org/browse/SYMMETRICDS-138 in the 1.7.4 release. It is a vital functionality that our clients are looking for. Can you put the fix for this in the 1.7.5 release? If possible, can you also spell out the tentative release date?

Show
Prashanto Chatterjee added a comment - It would have been good to have had the fix for JIRA http://jira.codehaus.org/browse/SYMMETRICDS-138 in the 1.7.4 release. It is a vital functionality that our clients are looking for. Can you put the fix for this in the 1.7.5 release? If possible, can you also spell out the tentative release date?

People

  • Assignee:
    Chris Henson
    Reporter:
    Prashanto Chatterjee
Vote (0)
Watch (0)

Dates

  • Created:
    Updated:
    Resolved: