Friday, September 6, 2013

Failed to purge nodes - DeletedNodeCleanupWorker

"21:00:40,348 WARN [node.db.DeletedNodeCleanupWorker] Failed to purge nodes. If the purgable set is too large for the available DB resources then the nodes can be purged manually as well.
...................
### Error updating database. Cause: java.sql.SQLException: The DELETE statement conflicted with the REFERENCE constraint "fk_alf_cass_pnode". The conflict occurred in database "alfrescoDB", table "dbo.alf_child_assoc", column 'parent_node_id"

Reason of this error is - Alfresco is referencing to deleted node such that purge is failing because of the constraint on db
This error may affect lucene indexing. Indexes can be out-of-sync and search might not return all results.

To solve this error, We followed below steps.Basically we need to delete corrupted nodes manually from Database.

1)First we need to find out problematic node, For that Run full indexing. You may enable below logger for detail info.

3) We would consider this node 1260437 and we need to delete this node manually from DB. Before we delete this node, we need to make sure it doesn't affect any other contents and it doesn't have any child.

4)Need to execute below queries. For example, I got 2 corrupted nodes - 1260437,467222. I have also provided query result so you can know which nodes we needs to consider again from result and how we can go bottom of the tree.

From below query, Mainly we need to consider :SELECT * FROM alf_child_assoc WHERE parent_node_id IN(1260437,467222)

SELECT * FROM alf_node_assoc WHERE source_node_id IN (1260437,467222); - No Records

SELECT * FROM alf_node_assoc WHERE target_node_id IN (1260437,467222); - No Records
SELECT * FROM alf_usage_delta WHERE node_id IN (1260437,467222); - No Records
SELECT * FROM alf_node_aspects WHERE node_id IN (1260437,467222); - No Records
SELECT * FROM alf_node_properties WHERE node_id IN (1260437,467222); - No Records
SELECT * FROM alf_child_assoc WHERE child_node_id IN (1260437,467222); - No Records

Above query result, we can identify that node 1260437 has child with id: 1260445.

So again we need to execute query for node: 1260445 to make sure it doesn't have any child.

SELECT * FROM alf_child_assoc WHERE parent_node_id IN (1260445); - No Result

From query result we can confirm node 1260445 doesn't have any child. So we are good to delete three nodes - 1260437, 467222, 1260445

5) Need to execute below DELETE query.

DELETE * FROM alf_node_assoc WHERE source_node_id IN (1260437,467222,1260445);

DELETE * FROM alf_node_assoc WHERE target_node_id IN (1260437,467222,1260445);

DELETE * FROM alf_usage_delta WHERE node_id IN (1260437,467222,1260445);

DELETE * FROM alf_node_aspects WHERE node_id IN (1260437,467222,1260445);

DELETE * FROM alf_node_properties WHERE node_id IN (1260437,467222,1260445);

DELETE * FROM alf_child_assoc WHERE child_node_id IN (1260437,467222,1260445);

DELETE * FROM alf_child_assoc WHERE parent_node_id IN (1260437,467222,1260445);

DELETE * FROM alf_node WHERE alf_node.id IN (1260437,467222,1260445);

NOTE: Must need to take care of below things, before you delete nodes from DB.

1) Need to execute delete in given order
2) Must need to take DB backup. Once this nodes are deleted and if any issue then there is no way we can recover it.
3) Also take lucene backup if in case any issue.