Failed to purge nodes @ 21.00 with mysql

[loftux]

I have seen this error with 2 clients, one is running 3.4.d, the other 4.0.c.

21:00:07,073 ERROR [org.alfresco.repo.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. 
  Set log level to WARN for this class to get exception log: 
   Max commit time: 1319745600050
   Error:       
### Error updating database.  Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Cannot delete or update a parent row: a foreign key constraint fails (`alfresco`.`alf_node_aspects`, CONSTRAINT `fk_alf_nasp_n` FOREIGN KEY (`node_id`) REFERENCES `alf_node` (`id`))
### The error may involve alfresco.node.delete_NodesByTxnCommitTime-Inline
### The error occurred while setting parameters
### Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Cannot delete or update a parent row: a foreign key constraint fails (`alfresco`.`alf_node_aspects`, CONSTRAINT `fk_alf_nasp_n` FOREIGN KEY (`node_id`) REFERENCES `alf_node` (`id`))
; SQL []; Cannot delete or update a parent row: a foreign key constraint fails (`alfresco`.`alf_node_aspects`, CONSTRAINT `fk_alf_nasp_n` FOREIGN KEY (`node_id`) REFERENCES `alf_node` (`id`)); nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Cannot delete or update a parent row: a foreign key constraint fails (`alfresco`.`alf_node_aspects`, CONSTRAINT `fk_alf_nasp_n` FOREIGN KEY (`node_id`) REFERENCES `alf_node` (`id`))‍‍‍‍‍‍‍‍‍‍

The version of mysql in this case is 5.1.52.
Anyone knows what could cause this error? I have tuned my.cnf to increase resources/memory for mysql, but maybe there is a specific setting that could help in this case as it complains about resources.
The job that runs at 21 is nodeServiceCleanupJobDetail, but what does it do and what effects will it have when it fails? I haven't got any reports of any effects from this, but it may pop up later.

[openpj]

Be sure to use the InnoDB engine for the MySQL database instance.
Hope this helps.

[loftux]

I checked using this

SELECT table_schema, table_name, table_collation FROM INFORMATION_SCHEMA.TABLES WHERE engine = 'innodb';‍

(and the negated where clause to see that no Alfresco tables were listed).
All tables are innodb, but table_collation is a mix of utf8_general_ci, utf8_bin and latin1_swedish_ci. This while db.url=jdbc:mysql://${db.host}:${db.port}/${db.name}?useUnicode=yes&characterEncoding=UTF-8, so that may be the reason for this error. Next step will be to find out how to convert all tables to same collation. Will report back when I find out how, or maybe someone already has the answer?

[glaenen]

Hello,

Did you found the solution for this problem? I'm experiencing the same problem when using the rendition engine on alfresco 4.0c with mysql 5.1.

Thanks,

Glenn

[janmulder]

This happens to me at the 21:00 cleanup:

com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Cannot delete or update a parent row: a foreign key constraint fails (`alfresco`.`alf_node_properties`, CONSTRAINT `fk_alf_nprop_n` FOREIGN KEY (`node_id`) REFERENCES `alf_node` (`id`))

I am running the svn 22617, I do not know how to relate that to any 3.x or 4.x version.

[janmulder]

This error aborted the 21:00 cleanup, so it was getting urgent to solve this.
I figured out which record had the foreign key pointing to the deleted node.
I removed that record manually.
Now the 21:00 cleanup works again.

The question remains:
Why was there a foreign key reference to a deleted node?

[ashwini]

Hello janmuldar,

Could you please tell me, how have you identified the right record ?

I am also getting identical exception while cleanup work at 21:00 .

Thanks in advance!!

Ashwini

[tssupremacy]

janmuldar,

Can you provide the steps you took to locate the problem record?

Sounds like your resolution details would help a few people!

Thanks

[ashwini]

Hello tssupremacy,

I too resolved this issue by doing Manually removing entries from database. I followed below steps:

1. I tried to do Full Indexing, and got exception for some nodes something like below :

 ERROR [index.AbstractReindexComponent.threads] [indexTrackerThread5] Reindexer   345 failed with error: Possible cache integrity issue during reindexing; nested exception is org.alfresco.service.cmr.repository.InvalidNodeRefException: Node does not exist: workspace://SpacesStore/332f578a-c80a-4b0d-b3fb-c076bfab334f(Status[changeTxnId=a96ba91d-b42b-48f8-bf7d-c2829bc9fe69, dbTxnId=253912, deleted=true]‍

Here I found uuid of the node which is in this case is "332f578a-c80a-4b0d-b3fb-c076bfab334f"
2. I checked in node browser existence of this node.
3. As this node was not available , I checked entry in alf_node table based on UUID   and found id of the respective row.
4. Then I deleted entry from alf_child_assoc where parent_node_id is id from alf_node
5. Committed the Transaction

Thats all and Alfresco stopped throwing exception . It worked for me.

Regard,
Ashwini