View Issue Details
| ID | Project | Category | View Status | Date Submitted | Last Update |
|---|---|---|---|---|---|
| 0006839 | SymmetricDS | Bug | public | 2025-04-20 19:51 | 2025-04-21 01:52 |
| Reporter | ingpibe | Assigned To | |||
| Priority | high | ||||
| Status | new | Resolution | open | ||
| Product Version | 3.13.11 | ||||
| Summary | 0006839: Synchronization is truncated (stopped) after executing the delete command on 2 tables | ||||
| Description | I have 3 nodes: One Master Node and two Slave Nodes. On the Master Node, there are two tables: Parent Table (City) and Child Table (Employee). On the Slave Nodes, there are also the same two tables: Parent Table (City) and Child Table (Employee). Both tables are related by the "City_Id" column. The SQL version is: Microsoft SQL Server 2022 **************** ** Master Node ** **************** Table "City": ------------------------------------------------ City_Id (PK) | Description | Node ------------------------------------------------ 1 | Lima | NOD1 2 | Trujillo | NOD2 Table "Employee": -------------------------------------------------------- Employee_Id | Employee | City_Id (FK) -------------------------------------------------------- 1 | Juan P | 1 2 | Luis Ch | 1 3 | Ben Hur | 2 **************** ** Slave Node 1 ** **************** Table "City": ------------------------------------------------ City_Id (PK) | Description | Node ------------------------------------------------ 1 | Lima | NOD1 Table "Employee": -------------------------------------------------------- Employee_Id | Employee | City_Id (FK) -------------------------------------------------------- 1 | Juan P | 1 2 | Luis Ch | 1 **************** ** Slave Node 2 ** **************** Table "City": ------------------------------------------------ City_Id (PK) | Description | Node ------------------------------------------------ 2 | Trujillo | NOD2 Table "Employee": -------------------------------------------------------- Employee_Id | Employee | City_Id (FK) -------------------------------------------------------- 3 | Ben Hur | 2 So when I run the following 2 commands on the Master Node: DELETE FROM Employee WHERE City_Id = 2; DELETE FROM City WHERE City_Id = 2; The following error appears in the Symmetric Log of Slave Node 2: - Failed to load batch 000-1290 because: The DELETE statement conflicted with the REFERENCE constraint "FK_Employee_City". The conflict occurred in database "MyBD", table "dbo.Employee", column 'City_Id'. - org.jumpmind.db.sql.SqlException: The DELETE statement conflicted with the REFERENCE constraint "FK_Employee_City". The conflict occurred in database "MyBD", table "dbo.Employee", column 'City_Id'. - Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The DELETE statement conflicted with the REFERENCE constraint "FK_Employee_City". The conflict occurred in database "MyBD", table "dbo.Employee", column 'City_Id'. | ||||
| Steps To Reproduce | 1. Have 3 nodes: 1 master and 2 slaves. 2. For each node, have 2 tables: City and Employee. 3. Populate with records (as indicated in the description). 4. Delete the City with code 2, first from the Employee table and then from the City table: DELETE FROM Employee WHERE City_Id = 2; DELETE FROM City WHERE City_Id = 2; 5. An error is detected during the deletion synchronization for Node 2. However, if I wait at least 5 seconds between both DELETE commands, everything works correctly. - DELETE FROM Employee WHERE City_Id = 2; - Wait 5 seconds - DELETE FROM City WHERE City_Id = 2; It first synchronizes the deletion of the Employee table, and then synchronizes the deletion of the City table. | ||||
| Additional Information | Table "sym_node": ------------------------------------------------------------------------------------- node_id | node_group_id | external_id | sync_enabled | ... ------------------------------------------------------------------------------------- 000 | servidor | 000 | 1 | ... NOD1 | cliente | NOD1 | 1 | ... NOD2 | cliente | NOD2 | 1 | ... Table "sym_node_group_link": ---------------------------------------------------------------------------------------------------------------------------------------- source_node_group_id | target_node_group_id | data_event_action | sync_config_enabled | ... ---------------------------------------------------------------------------------------------------------------------------------------- cliente | servidor | P | 1 | ... servidor | cliente | W | 1 | ... Table "sym_trigger": ------------------------------------------------------------------------- trigger_id | source_table_name | channel_id | ... ------------------------------------------------------------------------- t_Employee | Employee | Canal01 | ... t_City | City | Canal01 | ... Table "sym_router": --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- router_id | source_node_group_id | target_node_group_id | router_type | router_expression | ... --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- servidor_cliente_ciud | servidor | cliente | colum | NODE=:EXTERNAL_ID | ... servidor_cliente_emp | servidor | cliente | subselect | c.external_id in (select NODE from City where City_Id=:CITY_ID) | ... Table "sym_trigger_router": ---------------------------------------------------------------------------------------------------- trigger_id | router_id | enabled | initial_load_order | ... ---------------------------------------------------------------------------------------------------- t_Employee | servidor_cliente_ciud | 1 | 1 | ... t_City | servidor_cliente_emp | 1 | 2 | … | ||||
| Tags | data sync, routing, trigger | ||||
|
|
Version 3.13.11 is really old. The newest version is SymmetricDS 3.16.1, which makes all versions 3.14 and older obsolete. Please upgrade to 3.16.1 and re-test (after 1 month this ticket will be closed). https://symmetricds.org/news/symmetricds-3-16-1-released/ |
|
|
Also, try reversing the order of the initial_load_order column values in the sym_trigger_router to ensure the t_City has initial_load_order=1 (not 2 as listed in the Additional Information section) |
| Date Modified | Username | Field | Change |
|---|---|---|---|
| 2025-04-20 19:51 | ingpibe | New Issue | |
| 2025-04-20 19:51 | ingpibe | Tag Attached: data sync | |
| 2025-04-20 19:51 | ingpibe | Tag Attached: routing | |
| 2025-04-20 19:51 | ingpibe | Tag Attached: trigger | |
| 2025-04-20 22:15 | pbelov | Note Added: 0002829 | |
| 2025-04-21 01:52 | pbelov | Note Added: 0002830 |