View Issue Details

IDProjectCategoryView StatusLast Update
0006597SymmetricDSBugpublic2024-11-11 17:31
Reporterelong Assigned Toelong  
Prioritynormal 
Status closedResolutionfixed 
Product Version3.15.0 
Target Version3.15.9Fixed in Version3.15.9 
Summary0006597: Registration batch fails with foreign key error
DescriptionThe registration batch can fail with a foreign key violation of a child row in sym_node_security when deleting sym_node. The registration batch will clear the SYM tables in reverse foreign key order before loading them with the current configuration. It deletes from sym_node and sym_node_security with a where clause of created_at_node_id equal to the registration server. The historical reason for this isn't clear, but it appears to be avoiding removal of any registrations that are owned by the target node in a multi-tier setup.

For example, a 3-tier setup of server, client, and pos, where client registers with server and pos registers with client. The registration batch for client would delete from sym_node where created_at_node_Id = 'server'. However, if the created_at_node_id is set inconsistently in sym_node versus sym_node_security, a foreign key violation can occur. For example, sym_node has a created_at_node_id of server while sym_node_security has null.

Instead of created_at_node_id equal to registration server, it could use created_at_node_id not equal to target node or equal to null. For example, created_at_node_id != 'client' or created_at_node_id is null. This includes removal of more rows, including nulls and nodes that were created on another tier (although routing should prevent sending of nodes from other tiers). It might also clear up the intention of removing everything except for nodes that were registered on the target. Using a correlated join to delete would be more correct and guarantee avoidance of foreign key errors, but not every platform supports it, and this fix is trying to be somewhat careful.

ERROR [client-1] [ManageIncomingBatchListener] [client-1-dataloader-149] Failed to load batch server--9999 org.jumpmind.db.sql.SqlException: Referential integrity constraint violation: "SYM_FK_SEC_2_NODE: PUBLIC.SYM_NODE_SECURITY FOREIGN KEY(NODE_ID) REFERENCES PUBLIC.SYM_NODE(NODE_ID) ('omni')"; SQL statement:
delete from sym_node where created_at_node_id = 'server' [23503-214]
    at org.jumpmind.db.sql.AbstractSqlTemplate.translate(AbstractSqlTemplate.java:308)
    at org.jumpmind.db.sql.AbstractSqlTemplate.translate(AbstractSqlTemplate.java:297)
    at org.jumpmind.db.sql.JdbcSqlTransaction.executeCallback(JdbcSqlTransaction.java:377)
    at org.jumpmind.db.sql.JdbcSqlTransaction.prepareAndExecute(JdbcSqlTransaction.java:344)
    at org.jumpmind.symmetric.io.data.writer.DefaultDatabaseWriter.prepareAndExecute(DefaultDatabaseWriter.java:1274)
    at org.jumpmind.symmetric.io.data.writer.DefaultDatabaseWriter.sql(DefaultDatabaseWriter.java:741)
    at org.jumpmind.symmetric.io.data.writer.AbstractDatabaseWriter.write(AbstractDatabaseWriter.java:194)
    at org.jumpmind.symmetric.io.data.writer.NestedDataWriter.write(NestedDataWriter.java:62)
    at org.jumpmind.symmetric.model.ProcessInfoDataWriter.write(ProcessInfoDataWriter.java:98)
    at org.jumpmind.symmetric.io.data.writer.NestedDataWriter.write(NestedDataWriter.java:62)
    at org.jumpmind.symmetric.io.data.writer.TransformWriter.write(TransformWriter.java:187)
    at org.jumpmind.symmetric.io.data.DataProcessor.forEachDataInTable(DataProcessor.java:194)
    at org.jumpmind.symmetric.io.data.DataProcessor.forEachTableInBatch(DataProcessor.java:160)
    at org.jumpmind.symmetric.io.data.DataProcessor.process(DataProcessor.java:108)
    at org.jumpmind.symmetric.service.impl.DataLoaderService$LoadIntoDatabaseOnArrivalListener$2.call(DataLoaderService.java:1076)
    at org.jumpmind.symmetric.service.impl.DataLoaderService$LoadIntoDatabaseOnArrivalListener$2.call(DataLoaderService.java:1)
    at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:264)
    at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1136)
    at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:635)
    at java.base/java.lang.Thread.run(Thread.java:840)
Tagsregistration

Activities

There are no notes attached to this issue.

Related Changesets

SymmetricDS: 3.15 8ed1768a

2024-09-26 13:11:03

admin

Details Diff
0006597: Registration batch fails with foreign key error Affected Issues
0006597
mod - symmetric-core/src/main/java/org/jumpmind/symmetric/service/impl/DataExtractorService.java Diff File

SymmetricDS: 3.16 4aa55c8c

2024-09-26 13:11:03

admin

Details Diff
0006597: Registration batch fails with foreign key error Affected Issues
0006597
mod - symmetric-core/src/main/java/org/jumpmind/symmetric/service/impl/DataExtractorService.java Diff File

Issue History

Date Modified Username Field Change
2024-09-26 13:09 elong New Issue
2024-09-26 13:09 elong Status new => assigned
2024-09-26 13:09 elong Assigned To => elong
2024-09-26 13:09 elong Tag Attached: registration
2024-09-26 13:13 elong Description Updated View Revisions
2024-09-26 13:13 elong Status assigned => resolved
2024-09-26 13:13 elong Resolution open => fixed
2024-09-26 13:13 elong Fixed in Version => 3.15.9
2024-09-26 14:00 admin Changeset attached => SymmetricDS 3.15 8ed1768a
2024-09-26 14:00 admin Changeset attached => SymmetricDS 3.16 4aa55c8c
2024-10-01 19:48 admin Status resolved => closed
2024-11-11 17:31 pmarzullo Description Updated View Revisions