View Issue Details

Related ChangesetsJump to NotesJump to History
IDProjectCategoryView StatusDate SubmittedLast Update
0005597SymmetricDSBugpublic2022-11-27 19:132022-12-07 23:00
Reporternacyolsa Assigned Topmarzullo  
Priorityhigh 
Status closedResolutionfixed 
Product Version3.14.3 
Target Version3.14.4Fixed in Version3.14.4 
Summary0005597: SymDS is not syncing specific record during initial load
DescriptionLooks like it's a some edge case so take a look at steps to reproduce.

At the end this record:
INSERT INTO AttributeNames (ChainId, AttributeNameId, AttributeName) VALUES (null, 2, 'test 2');
is not loaded. Batch for this record is not created.

However duplicated batch is created for this record:
INSERT INTO AttributeNames (ChainId, AttributeNameId, AttributeName) VALUES (22, 1, 'test 1');

This issue occurs only when AttributeNames.ChainId = null.

As a result FK violations is thrown and SymDS is not able to continue:

[store-003] - ManageIncomingBatchListener - Failed to load batch 000-30
org.jumpmind.db.sql.SqlException: Cannot add or update a child row: a foreign key constraint fails (`store003`.`ChainsAttributes`, CONSTRAINT `AttributeNamesFKey` FOREIGN KEY (`AttributeNameId`) REFERENCES `AttributeNames` (`AttributeNameId`))...
Steps To ReproduceSource node configuration:

create table AttributeNames
(
    ChainId int(10),
    AttributeNameId int(10) not null
        primary key,
    AttributeName varchar(80) not null
);

create table ChainsAttributes
(
    ChainId int(10) not null,
    AttributeNameId int(10) not null
        primary key,
    AttributeValue varchar(200)
);

create table sym_lookup
(
    external_id varchar(10) not null,
    ChainId int(10) not null,
    SiteId int(10) not null
);

INSERT INTO sym_lookup (external_id, ChainId, SiteId) VALUES ('003', 22, 1307);


INSERT INTO AttributeNames (ChainId, AttributeNameId, AttributeName)
VALUES (22, 1, 'test 1');
INSERT INTO AttributeNames (ChainId, AttributeNameId, AttributeName)
VALUES (null, 2, 'test 2');
INSERT INTO ChainsAttributes (ChainId, AttributeNameId, AttributeValue)
VALUES (22, 1, 'attr 1');
INSERT INTO ChainsAttributes (ChainId, AttributeNameId, AttributeValue)
VALUES (22, 2, 'attr 2');

INSERT INTO sym_channel (channel_id, processing_order, max_batch_size, max_batch_to_send, max_data_to_route, extract_period_millis, enabled, use_old_data_to_route, use_row_data_to_route, use_pk_data_to_route, reload_flag, file_sync_flag, contains_big_lob, batch_algorithm, data_loader_type, description, queue, max_network_kbps, data_event_action, create_time, last_update_by, last_update_time) VALUES ('chain-config', 100, 1000, 60, 100000, 0, 1, 1, 1, 1, 0, 0, 0, 'default', 'default', 'Chain-level config', 'default', 0.000, null, null, null, null);

INSERT INTO sym_node_group (node_group_id, description) VALUES ('corp', null);
INSERT INTO sym_node_group (node_group_id, description) VALUES ('store', null);


INSERT INTO sym_node_group_link (source_node_group_id, target_node_group_id, data_event_action, sync_config_enabled, is_reversible, create_time, last_update_by, last_update_time) VALUES ('corp', 'store', 'W', 1, 0, null, null, null);
INSERT INTO sym_node_group_link (source_node_group_id, target_node_group_id, data_event_action, sync_config_enabled, is_reversible, create_time, last_update_by, last_update_time) VALUES ('store', 'corp', 'P', 1, 0, null, null, null);


INSERT INTO sym_router (router_id, target_catalog_name, target_schema_name, target_table_name, source_node_group_id, target_node_group_id, router_type, router_expression, sync_on_update, sync_on_insert, sync_on_delete, use_source_catalog_schema, create_time, last_update_by, last_update_time, description) VALUES ('above-store_to_CHAINNULLABLE_in-store', null, null, null, 'corp', 'store', 'subselect', 'c.external_id in (select ST.external_id from sym_lookup ST where COALESCE(:CHAINID,ST.ChainId)=ST.ChainId )', 1, 1, 1, 1, '2022-11-26 23:55:08.0', null, '2022-11-26 23:55:14.0', null);
INSERT INTO sym_router (router_id, target_catalog_name, target_schema_name, target_table_name, source_node_group_id, target_node_group_id, router_type, router_expression, sync_on_update, sync_on_insert, sync_on_delete, use_source_catalog_schema, create_time, last_update_by, last_update_time, description) VALUES ('above-store_to_CHAIN_in-store', null, null, null, 'corp', 'store', 'lookuptable', 'LOOKUP_TABLE=sym_lookup KEY_COLUMN=CHAINID LOOKUP_KEY_COLUMN=CHAINID EXTERNAL_ID_COLUMN=external_id ', 1, 1, 1, 1, '2022-11-27 00:12:39.0', null, '2022-11-27 00:12:41.0', null);


INSERT INTO sym_trigger (trigger_id, source_catalog_name, source_schema_name, source_table_name, channel_id, reload_channel_id, sync_on_update, sync_on_insert, sync_on_delete, sync_on_incoming_batch, name_for_update_trigger, name_for_insert_trigger, name_for_delete_trigger, sync_on_update_condition, sync_on_insert_condition, sync_on_delete_condition, custom_before_update_text, custom_before_insert_text, custom_before_delete_text, custom_on_update_text, custom_on_insert_text, custom_on_delete_text, external_select, tx_id_expression, channel_expression, excluded_column_names, included_column_names, sync_key_names, use_stream_lobs, use_capture_lobs, use_capture_old_data, use_handle_key_updates, stream_row, create_time, last_update_by, last_update_time, description) VALUES ('trg-AttributeNames', null, null, 'AttributeNames', 'chain-config', 'reload', 1, 1, 1, 0, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, 0, 0, 1, 1, 0, '2022-11-27 00:27:50.0', null, '2022-11-27 00:27:54.0', null);
INSERT INTO sym_trigger (trigger_id, source_catalog_name, source_schema_name, source_table_name, channel_id, reload_channel_id, sync_on_update, sync_on_insert, sync_on_delete, sync_on_incoming_batch, name_for_update_trigger, name_for_insert_trigger, name_for_delete_trigger, sync_on_update_condition, sync_on_insert_condition, sync_on_delete_condition, custom_before_update_text, custom_before_insert_text, custom_before_delete_text, custom_on_update_text, custom_on_insert_text, custom_on_delete_text, external_select, tx_id_expression, channel_expression, excluded_column_names, included_column_names, sync_key_names, use_stream_lobs, use_capture_lobs, use_capture_old_data, use_handle_key_updates, stream_row, create_time, last_update_by, last_update_time, description) VALUES ('trg-ChainsAttributes', null, null, 'ChainsAttributes', 'chain-config', 'reload', 1, 1, 1, 0, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, 0, 0, 1, 1, 0, '2022-11-27 00:29:09.0', null, '2022-11-27 00:29:10.0', null);

INSERT INTO sym_trigger_router (trigger_id, router_id, enabled, initial_load_order, initial_load_select, initial_load_delete_stmt, ping_back_enabled, create_time, last_update_by, last_update_time, description) VALUES ('trg-AttributeNames', 'corp_to_CHAINNULLABLE_in-store', 1, 3, null, null, 0, '2022-11-27 00:31:41.0', null, '2022-11-27 00:31:45.0', null);
INSERT INTO sym_trigger_router (trigger_id, router_id, enabled, initial_load_order, initial_load_select, initial_load_delete_stmt, ping_back_enabled, create_time, last_update_by, last_update_time, description) VALUES ('trg-AttributeNames', 'corp_to_CHAIN_in-store', 1, 3, null, null, 0, '2022-11-27 00:32:34.0', null, '2022-11-27 00:32:36.0', null);
INSERT INTO sym_trigger_router (trigger_id, router_id, enabled, initial_load_order, initial_load_select, initial_load_delete_stmt, ping_back_enabled, create_time, last_update_by, last_update_time, description) VALUES ('trg-ChainsAttributes', 'corp_to_CHAIN_in-store', 1, 4, null, null, 0, '2022-11-27 00:33:16.0', null, '2022-11-27 00:33:17.0', null);
==========================================================

Target node:

create table AttributeNames
(
    ChainId int(10),
    AttributeNameId int(10) not null
        primary key,
    AttributeName varchar(80) not null
);

create table ChainsAttributes
(
    ChainId int(10) not null,
    AttributeNameId int(10) not null,
    AttributeValue varchar(200),
    CONSTRAINT AttributeNamesFKey foreign key (AttributeNameId) references AttributeNames (AttributeNameId)
);
Additional InformationI checked this issue with the following versions:

source (mariadb) -> target (mariadb)
3.14.2 -> 3.9.8 -- issue occurs
3.14.2 -> 3.14.2 -- issue occurs
3.13.8 -> 3.9.8 -- issue occurs
3.12.19 -> 3.9.8 -- issue occurs
3.11.14 -> 3.9.8 -- issue occurs
3.10.14 -> 3.9.8 -- issue not occurs
3.9.8 -> 3.9.8 -- issue not occurs

Issue occurs for Sybase and MariaDB.
Tagsrouting

Activities

pmarzullo

2022-12-01 17:59

developer   ~0002222

I have reproduced the symptoms, but this was introduced when we removed the router ID from the sym_data_event table. The reason that this was removed from the sym_data_event table is because of performance reasons. We determined that there is another way to route a single data event with different logic and that way would be to use a bean shell router if necessary.

In your case, you can enhance the configuration of the lookup router and remove the subselect router. By specifying a catchall value for the lookup router, by setting ALL_NODES_VALUE=-1, or some other value that you may want to set it to, and adding a record to the sym_lookup table with values of external_id='003', chain_id='-1', then whenever the lookup router is used, the ALL_NODES_VALUE will match the chainid value of '-1' and will route to the node in question.

Right now, with two routers defined for the same piece of data, the same node is receiving two batches with the same data.

nacyolsa

2022-12-03 20:02

reporter   ~0002223

Unfortunately proposed solution with ALL_NODES_VALUE=-1 doesn't work. Nodes with ChainId = null are not synced and the same error is throw. Created batch for record with ChainId = null looks like below:

nodeid,000
binary,HEX
channel,reload
batch,890

pmarzullo

2022-12-03 21:02

developer   ~0002224

Can the application change the null value for chainid to a value of -1? Then it should work.

nacyolsa

2022-12-03 21:24

reporter   ~0002225

Changes to AttributeNames table are not the way I would like to go. I think I will just stay with subselect router and remove lookup router.

Except performance and potential issues with no routing when data is removed. Is there some another reason why lookup routers should be used instead of subselect routers?

Do you have plans to fix this edge case with nulls for lookup routers?

nacyolsa

2022-12-07 15:58

reporter   ~0002229

Can be closed.

pmarzullo

2022-12-07 22:07

developer   ~0002231

The lookup router now allows the specification of a null value for the ALL_NODES_VAUE parameter. Just set the value to the string value of null, and it will be interpreted as a null value.
Documentation has also been updated.

pmarzullo

2022-12-07 22:08

developer   ~0002232

By adding the ALL_NODES_VALUE=null to the lookup table router, the correct rows will now be sent to the target.

Related Changesets

SymmetricDS: 3.14 9f7eb4a7

2022-12-07 22:02:53

pmarzullo

Details Diff 		0005597: SymDS is not syncing specific record during initial load

The lookup router now allows the specification of a null value for the
ALL_NODES_VAUE parameter. Just set the value to the string value of
null, and it will be interpreted as a null value.		 Affected Issues
0005597
mod - symmetric-core/src/main/java/org/jumpmind/symmetric/route/LookupTableDataRouter.java Diff File

SymmetricDS: 3.14 61cf1033

2022-12-07 22:05:24

pmarzullo

Details Diff 		0005597: SymDS is not syncing specific record during initial load

The lookup router now allows the specification of a null value for the
ALL_NODES_VAUE parameter. Just set the value to the string value of
null, and it will be interpreted as a null value.		 Affected Issues
0005597
mod - symmetric-assemble/src/asciidoc/configuration/routers/lookuptable.ad Diff File

Issue History

Date Modified Username Field Change
2022-11-27 19:13 nacyolsa New Issue
2022-11-27 19:16 nacyolsa Tag Attached: initial/partial load
2022-11-27 19:16 nacyolsa Tag Attached: initial
2022-11-27 19:16 nacyolsa Tag Detached: initial
2022-12-01 17:59 pmarzullo Note Added: 0002222
2022-12-02 18:39 pmarzullo Assigned To => pmarzullo
2022-12-02 18:39 pmarzullo Status new => feedback
2022-12-03 20:02 nacyolsa Note Added: 0002223
2022-12-03 20:02 nacyolsa Status feedback => assigned
2022-12-03 21:02 pmarzullo Status assigned => feedback
2022-12-03 21:02 pmarzullo Note Added: 0002224
2022-12-03 21:24 nacyolsa Note Added: 0002225
2022-12-03 21:24 nacyolsa Status feedback => assigned
2022-12-07 15:58 nacyolsa Note Added: 0002229
2022-12-07 21:59 pmarzullo Product Version => 3.14.3
2022-12-07 21:59 pmarzullo Target Version => 3.14.4
2022-12-07 22:06 pmarzullo Tag Attached: routing
2022-12-07 22:06 pmarzullo Tag Detached: initial/partial load
2022-12-07 22:07 pmarzullo Status assigned => resolved
2022-12-07 22:07 pmarzullo Resolution open => fixed
2022-12-07 22:07 pmarzullo Fixed in Version => 3.14.4
2022-12-07 22:07 pmarzullo Note Added: 0002231
2022-12-07 22:08 pmarzullo Note Added: 0002232
2022-12-07 23:00 pmarzullo Changeset attached => SymmetricDS 3.14 61cf1033
2022-12-07 23:00 pmarzullo Changeset attached => SymmetricDS 3.14 9f7eb4a7
2023-01-16 20:59 admin Status resolved => closed