View Revisions: Issue #6597
| Summary | 0006597: Registration batch fails with foreign key error | ||
|---|---|---|---|
| Revision | 2024-11-11 17:31 by pmarzullo | ||
| Description | The 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) |
||
| Revision | 2024-09-26 13:13 by elong | ||
| Description | The 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) |
||
| Revision | 2024-09-26 13:09 by elong | ||
| Description | The 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] [DefaultDatabaseWriter] [client-1-dataloader-149] Failed to run the following sql: delete from sym_node where created_at_node_id = 'server' 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 != '1' [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) |
||