What to do when DeleteNodeClean job is failing everyday ?

[cesarista]

Hi:

I am working in an Alfresco instance that is having problems when executing the DeleteNodeClean Job at 21:00 everyday. Did you find it sometime ? What to do in these situations ? Any recommendation for a non-DBA?

The log mentions that "If the purgable set is too large for the available DB resources then the nodes can be purged manually as well". Is this safe ?

The log appears everyday at 21:00, this is a little extraction that is repeated 4 times each day.

2016-12-15 21:00:21,790 ERROR [node.db.DeletedNodeCleanupWorker] [DefaultScheduler_Worker-10] 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. 
Set log level to WARN for this class to get exception log: 
From commit time (ms): 1440757078845 
To commit time (ms) : 1440764278845 
Error:

### Error updating database. Cause: org.postgresql.util.PSQLException: ERROR: update or delete on table "alf_node" violates foreign key constraint "fk_alf_cass_pnode" on table "alf_child_assoc" 
Detail: Key (id)=(82010420) is still referenced from table "alf_child_assoc". 
### The error may involve alfresco.node.delete.delete_NodesByTxnCommitTime-Inline 
### The error occurred while setting parameters 
### SQL: delete from alf_node where type_qname_id = ? and transaction_id <= ( select max( txn.id ) from alf_transaction txn where txn.commit_time_ms >= ? and txn.commit_time_ms < ? ) 
### Cause: org.postgresql.util.PSQLException: ERROR: update or delete on table "alf_node" violates foreign key constraint "fk_alf_cass_pnode" on table "alf_child_assoc" 
Detail: Key (id)=(82010420) is still referenced from table "alf_child_assoc". 
; SQL []; ERROR: update or delete on table "alf_node" violates foreign key constraint "fk_alf_cass_pnode" on table "alf_child_assoc" 
Detail: Key (id)=(82010420) is still referenced from table "alf_child_assoc".; nested exception is org.postgresql.util.PSQLException: ERROR: update or delete on table "alf_node" violates foreign key constraint "fk_alf_cass_pnode" on table "alf_child_assoc" 
Detail: Key (id)=(82010420) is still referenced from table "alf_child_assoc". 
2016-12-15 21:00:49,846 ERROR [node.db.DeletedNodeCleanupWorker] [DefaultScheduler_Worker-10] Failed to purge nodes.

Regards.

--C.

[kaynezhang]

You error is caused by . Cause: org.postgresql.util.PSQLException: ERROR: update or delete on table "alf_node" violates foreign key constraint "fk_alf_cass_pnode" on table "alf_child_assoc" 

Detail: Key (id)=(82010420) is still referenced from table "alf_child_assoc". 

So you can execute following sql manully
delete FROM `alfresco`.`alf_child_assoc` where parent_node_id=82010420

[cesarista]

Thank you very much ‌, I'll try.

Regards.

--C.

[afaust]

Please note that this way you might end up with orphaned nodes in the DB that do not have any parent. You should identify the child associations first via a simple select instead of blindly deleting them. Normally when a node is deleted all child associations should be automatically deleted as well. This occurs days or weeks before the DeletedNodeCleanupWorker even considers the node for cleanup. It should not be possible to create new child associations for a deleted node, so it is very interesting to find out how this entry was created.

[cesarista]

Thanks for the clarification Axel Faust.

Regards.

--C.