Categories publishing failing

Question

I am having problems publishing Categories from our Dev CMS. I am able to publish Pages and Components in Dev though.

The error that I am receiving is:

2018-07-12 05:03:05,721 ERROR DeployPipelineExecutor - Final attempt in Phase: Deployment Prepare Commit Phase failed for transaction: tcm:0-1069503-66560
2018-07-12 05:03:05,721 ERROR DeployPipelineExecutor - Original stack trace for transaction: tcm:0-1069503-66560
com.tridion.deployer.ProcessingException: Unable to prepare transaction: tcm:0-1069503-66560
    at com.tridion.deployer.phases.PreCommitPhase.handleFailure(PreCommitPhase.java:131) ~[cd_deployer.jar:na]
    at com.tridion.deployer.phases.PreCommitPhase.execute(PreCommitPhase.java:109) ~[cd_deployer.jar:na]
    at com.tridion.deployer.phases.DeployPipelineExecutor.runMainExecutePhase(DeployPipelineExecutor.java:198) [cd_deployer.jar:na]
    at com.tridion.deployer.phases.DeployPipelineExecutor.doExecute(DeployPipelineExecutor.java:100) [cd_deployer.jar:na]
    at com.tridion.deployer.phases.DeployPipelineExecutor.execute(DeployPipelineExecutor.java:64) [cd_deployer.jar:na]
    at com.tridion.deployer.TransactionManager.handleDeployPackage(TransactionManager.java:82) [cd_deployer.jar:na]
    at com.tridion.deployer.queue.QueueLocationHandler$1.run(QueueLocationHandler.java:180) [cd_deployer.jar:na]
    at java.util.concurrent.Executors$RunnableAdapter.call(Unknown Source) [na:1.7.0_79]
    at java.util.concurrent.FutureTask.run(Unknown Source) [na:1.7.0_79]
    at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source) [na:1.7.0_79]
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source) [na:1.7.0_79]
    at java.lang.Thread.run(Unknown Source) [na:1.7.0_79]
Caused by: com.tridion.deployer.ProcessingException: Unable to store item inside current transaction
    at com.tridion.deployer.phases.PreCommitPhase.runPrepare(PreCommitPhase.java:161) ~[cd_deployer.jar:na]
    at com.tridion.deployer.phases.PreCommitPhase.execute(PreCommitPhase.java:101) ~[cd_deployer.jar:na]
    ... 10 common frames omitted
Caused by: com.tridion.broker.StorageException: Unable to persist data entity
    at com.tridion.storage.persistence.JPABaseDAO.create(JPABaseDAO.java:104) ~[cd_datalayer.jar:na]
    at com.tridion.storage.persistence.JPAItemDAO.store(JPAItemDAO.java:262) ~[cd_datalayer.jar:na]
    at com.tridion.storage.services.TaxonomyService.create(TaxonomyService.java:287) ~[cd_datalayer.jar:na]
    at com.tridion.storage.services.TaxonomyService.store(TaxonomyService.java:211) ~[cd_datalayer.jar:na]
    at com.tridion.storage.deploy.workers.TaxonomyWorker.doDeploy(TaxonomyWorker.java:105) ~[cd_datalayer.jar:na]
    at com.tridion.deployer.model.transaction.TransactionLogItemWorker.doWork(TransactionLogItemWorker.java:27) ~[cd_model.jar:na]
    at com.tridion.deployer.phases.PreCommitPhase.runPrepare(PreCommitPhase.java:155) ~[cd_deployer.jar:na]
    ... 11 common frames omitted
Caused by: javax.persistence.PersistenceException: org.hibernate.exception.ConstraintViolationException: Violation of PRIMARY KEY constraint 'PK_ITEMS'. Cannot insert duplicate key in object 'dbo.ITEMS'. The duplicate key value is (211272, 247).
    at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1361) ~[hibernate-entitymanager.jar:4.0.1.Final]
    at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1289) ~[hibernate-entitymanager.jar:4.0.1.Final]
    at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1295) ~[hibernate-entitymanager.jar:4.0.1.Final]
    at org.hibernate.ejb.AbstractEntityManagerImpl.persist(AbstractEntityManagerImpl.java:859) ~[hibernate-entitymanager.jar:4.0.1.Final]
    at sun.reflect.GeneratedMethodAccessor108.invoke(Unknown Source) ~[na:na]
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source) ~[na:1.7.0_79]
    at java.lang.reflect.Method.invoke(Unknown Source) ~[na:1.7.0_79]
    at org.springframework.orm.jpa.ExtendedEntityManagerCreator$ExtendedEntityManagerInvocationHandler.invoke(ExtendedEntityManagerCreator.java:365) ~[spring-orm.jar:3.1.0.RELEASE]
    at com.sun.proxy.$Proxy47.persist(Unknown Source) ~[na:na]
    at com.tridion.storage.persistence.JPABaseDAO.create(JPABaseDAO.java:82) ~[cd_datalayer.jar:na]

I have unpublished the Category and checked in Tridion Broker DB and still that item exist in dbo.items table.

Answer 1

This line in the stack trace clearly shows the problem:

Violation of PRIMARY KEY constraint 'PK_ITEMS'. Cannot insert duplicate key in object 'dbo.ITEMS'. The duplicate key value is (211272, 247).

Somehow the item remained in (some parts of) the Broker and now when it tries to deploy it to the DB, a DB constraint fails. You have two choices to solve this:

1. Contact SDL support who will provide you with a SQL script for deleting the problematic item. Do not try and delete it on your own, you will lose support if you tamper with the Broker on your own
2. (Try and create an unpublish package with that exact same item and see if that helps. BUT errors like this usually indicate that the item was removed from some of the tables, but not all, so this approach will most likely not work. To create the unpublish package, turn off the cleanup of the transport packages, can be done on either CM or Deployer side, unpublish a category and examine the package. Create your own package with the same structure but overriding all the relevant IDs (of the Category, Transaction, etc.). Then zip it and just drop it in the Deployers work folder. Since this is 2013sp1 it will pick it up immediately.)

So in conclusion go with 1.