View Issue Details
| ID | Project | Category | View Status | Date Submitted | Last Update |
|---|---|---|---|---|---|
| 0006839 | SymmetricDS | Bug | public | 2025-04-20 19:51 | 2025-04-23 17:45 |
| Reporter | ingpibe | Assigned To | |||
| Priority | high | ||||
| Status | closed | Resolution | no change required | ||
| 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) |
|
|
This is not a bug with SymmetricDS. You are deleting from both City and Employee before the Routing job gets the chance to run. When the Routing job runs, the servidor_cliente_emp router's subselect query does not get any results because the corresponding City row has been deleted. So the deleted Employee row does not get sent anywhere, and then the deleted City row gets sent to the slave node and causes an error because the child Employee row still exists. The solution is to specify an external_select query on the t_Employee trigger to select the Node from the corresponding City row before it gets deleted. You can then use this value in the servidor_cliente_emp router to determine the target node. Here is a link to our 3.16 documentation on External Select: https://symmetricds.sourceforge.net/doc/3.16/html/user-guide.html#_external_select |
| 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 | |
| 2025-04-23 17:27 | emiller | Status | new => feedback |
| 2025-04-23 17:45 | emiller | Status | feedback => closed |
| 2025-04-23 17:45 | emiller | Resolution | open => no change required |
| 2025-04-23 17:45 | emiller | Note Added: 0002837 |