database violations error while starting workflow through the job scheduling

kranthi — Tue, 18 Oct 2016 06:28:08 GMT

While I am trying to start the workflow from job scheduling getting database violation error: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.

Re: database violations error while starting workflow through the job scheduling

afaust — Tue, 18 Oct 2016 07:53:26 GMT

I do not believe the error in DeletedNodeCleanupWorker is in any way related to your attempt to start a workflow. Both are independant jobs which do not call / trigger each other. I cannot determine any relation via the log excerpts you posted. If you could set the log level for org.alfresco.repo.node.db.DeletedNodeCleanupWorker to WARN (as the message states) you will get more detail about this particular error.

Re: database violations error while starting workflow through the job scheduling

kranthi — Tue, 18 Oct 2016 09:05:41 GMT

added that to log statements after that I got like this:

2017-03-08 16:15:17,085 WARN [node.db.DeletedNodeCleanupWorker] [DefaultScheduler_Worker-3] 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: 1486377910675

Error:

### Error updating database. Cause: org.postgresql.util.PSQLException: ERROR: update or delete on table "alf_node" violates foreign key constraint "fk_alf_nprop_n" on table "alf_node_properties"

Detail: Key (id)=(1584) is still referenced from table "alf_node_properties".

### The error may involve alfresco.node.delete_NodesByTxnCommitTime-Inline

### The error occurred while setting parameters

### Cause: org.postgresql.util.PSQLException: ERROR: update or delete on table "alf_node" violates foreign key constraint "fk_alf_nprop_n" on table "alf_node_properties"

Detail: Key (id)=(1584) is still referenced from table "alf_node_properties".

; SQL []; ERROR: update or delete on table "alf_node" violates foreign key constraint "fk_alf_nprop_n" on table "alf_node_properties"

Detail: Key (id)=(1584) is still referenced from table "alf_node_properties".; nested exception is org.postgresql.util.PSQLException: ERROR: update or delete on table "alf_node" violates foreign key constraint "fk_alf_nprop_n" on table "alf_node_properties"

Detail: Key (id)=(1584) is still referenced from table "alf_node_properties".

org.springframework.dao.DataIntegrityViolationException:

### Error updating database. Cause: org.postgresql.util.PSQLException: ERROR: update or delete on table "alf_node" violates foreign key constraint "fk_alf_nprop_n" on table "alf_node_properties"

Detail: Key (id)=(1584) is still referenced from table "alf_node_properties".

### The error may involve alfresco.node.delete_NodesByTxnCommitTime-Inline

### The error occurred while setting parameters

### Cause: org.postgresql.util.PSQLException: ERROR: update or delete on table "alf_node" violates foreign key constraint "fk_alf_nprop_n" on table "alf_node_properties"

Detail: Key (id)=(1584) is still referenced from table "alf_node_properties".

; SQL []; ERROR: update or delete on table "alf_node" violates foreign key constraint "fk_alf_nprop_n" on table "alf_node_properties"

Detail: Key (id)=(1584) is still referenced from table "alf_node_properties".

at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:245)

at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)

at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:71)

at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:346)

at com.sun.proxy.$Proxy9.delete(Unknown Source)

at org.mybatis.spring.SqlSessionTemplate.delete(SqlSessionTemplate.java:259)

at org.alfresco.repo.domain.node.ibatis.NodeDAOImpl.deleteNodesByCommitTime(NodeDAOImpl.java:393)

at org.alfresco.repo.domain.node.AbstractNodeDAOImpl.purgeNodes(AbstractNodeDAOImpl.java:2044)

at org.alfresco.repo.node.db.DeletedNodeCleanupWorker$1.execute(DeletedNodeCleanupWorker.java:110)

at org.alfresco.repo.node.db.DeletedNodeCleanupWorker$1.execute(DeletedNodeCleanupWorker.java:107)

at org.alfresco.repo.transaction.RetryingTransactionHelper.doInTransaction(RetryingTransactionHelper.java:450)

at org.alfresco.repo.node.db.DeletedNodeCleanupWorker.purgeOldDeletedNodes(DeletedNodeCleanupWorker.java:122)

at org.alfresco.repo.node.db.DeletedNodeCleanupWorker.doCleanInternal(DeletedNodeCleanupWorker.java:62)

at org.alfresco.repo.node.cleanup.AbstractNodeCleanupWorker$1.doWork(AbstractNodeCleanupWorker.java:173)

at org.alfresco.repo.node.cleanup.AbstractNodeCleanupWorker$1.doWork(AbstractNodeCleanupWorker.java:168)

at org.alfresco.repo.security.authentication.AuthenticationUtil.runAs(AuthenticationUtil.java:548)

at org.alfresco.repo.node.cleanup.AbstractNodeCleanupWorker.doCleanAsSystem(AbstractNodeCleanupWorker.java:182)

at org.alfresco.repo.node.cleanup.AbstractNodeCleanupWorker.doClean(AbstractNodeCleanupWorker.java:124)

at org.alfresco.repo.node.cleanup.NodeCleanupRegistry.doClean(NodeCleanupRegistry.java:62)

at org.alfresco.repo.node.cleanup.NodeCleanupJob.execute(NodeCleanupJob.java:54)

at org.quartz.core.JobRunShell.run(JobRunShell.java:216)

at org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:563)

Caused by: org.postgresql.util.PSQLException: ERROR: update or delete on table "alf_node" violates foreign key constraint "fk_alf_nprop_n" on table "alf_node_properties"

Detail: Key (id)=(1584) is still referenced from table "alf_node_properties".

at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2102)

at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1835)

at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257)

at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:512)

at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:388)

at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:381)

at org.apache.commons.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:172)

at org.apache.ibatis.executor.statement.PreparedStatementHandler.update(PreparedStatementHandler.java:22)

at org.apache.ibatis.executor.statement.RoutingStatementHandler.update(RoutingStatementHandler.java:51)

at org.apache.ibatis.executor.SimpleExecutor.doUpdate(SimpleExecutor.java:29)

at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:87)

at org.apache.ibatis.executor.CachingExecutor.update(CachingExecutor.java:46)

at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:118)

at org.apache.ibatis.session.defaults.DefaultSqlSession.delete(DefaultSqlSession.java:131)

at sun.reflect.GeneratedMethodAccessor436.invoke(Unknown Source)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

at java.lang.reflect.Method.invoke(Method.java:606)

at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:338)

... 18 more

Re: database violations error while starting workflow through the job scheduling

afaust — Tue, 18 Oct 2016 09:09:40 GMT

This stacktrace confirms that the code causing the issue is run in an isolated job and not caused by your workflow start job.

The real cause of the problem may be completely unrelated. Apparently a node has been marked as deleted but not all of its properties have been removed at the time they should have been. This looks like a potential error / bug in the NodeService / NodeDAO area of responsibility.

Which Alfresco version are you running?

Re: database violations error while starting workflow through the job scheduling

kranthi — Tue, 18 Oct 2016 09:18:38 GMT

I am using alfresco enterprise 4.2.2 version

Re: database violations error while starting workflow through the job scheduling

douglascrp — Tue, 25 Jul 2017 02:53:25 GMT

I have just faced and fixed 101 different registres in a PostgreSQL database, in which an Alfresco 5.2.f is connected.

I had to take every single ID printed in the log file and execute the following SQL statements:

DELETE
FROM alf_node_properties
WHERE node_id = <ID>;

DELETE FROM alf_node
WHERE ID = <ID>;

After every execution, I had to re-execute the nodeServiceCleanupJobDetail job, as for each execution, I could get only 2 IDs.

I used the OOTB Support Tools to manually execute the job.

The problem with this is that I am not sure what exactly caused such inconsistencies.

I hope this information is useful for others facing the same issue.

Re: database violations error while starting workflow through the job scheduling

kgastaldo — Wed, 26 Jul 2017 12:45:07 GMT

For anyone looking, the OOTBee Support Tools can be found here - GitHub - OrderOfTheBee/ootbee-support-tools: OOTBee Support Tools addon to extend set of administrative tools on Reposit…

topic Re: database violations error while starting workflow through the job scheduling in Alfresco Archive

database violations error while starting workflow through the job scheduling

Re: database violations error while starting workflow through the job scheduling

Re: database violations error while starting workflow through the job scheduling

Re: database violations error while starting workflow through the job scheduling

Re: database violations error while starting workflow through the job scheduling

Re: database violations error while starting workflow through the job scheduling

Re: database violations error while starting workflow through the job scheduling