7

I am trying to publish a Taxonmy, but getting following error:

Violation of UNIQUE KEY constraint 'UIX_TAXFACETS'. Cannot insert duplicate key in object 'dbo.TAXFACETS'. The duplicate key value is (40, 306, 132).

I verified that one Keyword exists in the TAXFACETS table of Broker DB which is causing this issue.

Is there a way to resolve this, either by removing this unnessesary Keyword from Broker DB or (may be) by forcing publishing for this Taxonomy?

10

yes this can indeed be the case. We had same issue and contacted SDL support for fix. Why this indeed is the case, we have no answer. I suggest you contact them just in case to verify.

Hello Marko,

Thank you for contacting SDL Customer Support.

From the error message, it seems that there is already a reference to the record on the broker database that doesn't allow the committing to go through successfully.

This can happen when, somehow the record is incomplete (where the data has been entered for some tables but not for all tables) causing this exception to happen.

To be sure that this is the case, please run the below scripts and send the results to us:

SELECT ITEM_REFERENCE_ID, PUBLICATION_ID, ITEM_TYPE FROM ITEMS itm wherenotexists(select 1 from COMPONENT comp where itm.ITEM_REFERENCE_ID = comp.ITEM_REFERENCE_ID and itm.PUBLICATION_ID = comp.PUBLICATION_ID and itm.ITEM_TYPE = 16) and itm.ITEM_TYPE = 16

SELECT ITEM_REFERENCE_ID, PUBLICATION_ID, ITEM_TYPE FROM ITEMS itm wherenotexists(select 1 from PAGE page where itm.ITEM_REFERENCE_ID = page.ITEM_REFERENCE_ID and itm.PUBLICATION_ID = page.PUBLICATION_ID and itm.ITEM_TYPE = 64) and itm.ITEM_TYPE = 64

SELECT c.ITEM_REFERENCE_ID, c.PUBLICATION_ID, c.IS_MULTIMEDIA FROM COMPONENT c where not EXISTS(select 1 from BINARY b where b.ITEM_REFERENCE_ID = c.ITEM_REFERENCE_ID and b.PUBLICATION_ID = c.PUBLICATION_ID) and c.IS_MULTIMEDIA = 1

This is what they provided to us to delete items from broker:

Hello Marko,

The output shows that indeed there are some records that are incomplete. It is not clear why this happens. To be able to publish these successfully, we will need to remove these references. I enclose below the scripts that will delete the records that were output in the select statements. Please ensure to first take a backup of the broker database before you run these. You might need to revert to the backup, if issues occur. If you face any issues running these statements, please let me know.

DELETE itm FROM Items itm INNER JOIN COMPONENT c ON c.ITEM_REFERENCE_ID = itm.ITEM_REFERENCE_ID AND c.PUBLICATION_ID = itm.PUBLICATION_ID AND itm.ITEM_TYPE = 16 where not exists (select 1 from BINARY b where b.ITEM_REFERENCE_ID = c.ITEM_REFERENCE_ID and b.PUBLICATION_ID = c.PUBLICATION_ID) and c.IS_MULTIMEDIA = 1 DELETE c FROM COMPONENT c where not EXISTS(select 1 from BINARY b where b.ITEM_REFERENCE_ID = c.ITEM_REFERENCE_ID and b.PUBLICATION_ID = c.PUBLICATION_ID) and c.IS_MULTIMEDIA = 1

DELETE itm FROM ITEMS itm where not exists(select 1 from COMPONENT comp where itm.ITEM_REFERENCE_ID = comp.ITEM_REFERENCE_ID and itm.PUBLICATION_ID = comp.PUBLICATION_ID and itm.ITEM_TYPE = 16) and itm.ITEM_TYPE = 16

Please don't execute them until you verify with customer support.

UPDATE:

I just want to add update in response to @Adarsh comment. These queries are related to our issue where we couldn't publish component presentations. Since you have issue with TAXFACETS table, these queries should be somewhat changed. In any case, please contact customer support to provide you correct set of queries. Also, for debugging purpose, we observed issue in our deployer log stating:

    Caused by: com.tridion.broker.StorageException: Unable to persist data entity
    at com.tridion.storage.persistence.JPABaseDAO.create(JPABaseDAO.java:104) ~[cd_datalayer-0.0.1.jar:na]
    at com.tridion.storage.persistence.JPAItemDAO.store(JPAItemDAO.java:262) ~[cd_datalayer-0.0.1.jar:na]
    at com.tridion.storage.caching.CachedItemDAO.store(CachedItemDAO.java:144) ~[cd_datalayer-0.0.1.jar:na]
    at com.tridion.storage.deploy.workers.ComponentWorker.doDeploy(ComponentWorker.java:46) ~[cd_datalayer-0.0.1.jar:na]
    at com.tridion.deployer.model.transaction.TransactionLogItemWorker.doWork(TransactionLogItemWorker.java:27) ~[cd_model-0.0.1.jar:na]
    at com.tridion.deployer.phases.PreCommitPhase.runPrepare(PreCommitPhase.java:155) ~[cd_deployer-0.0.1.jar:na]
    ... 12 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 (76995, 119).
    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]
5
  • Although you can guess from the response - I would ask for more analysis of what didn't complete , maybe you can then track that down to a specific publish event and see if there were any event errors etc. around that time (maybe some sort of file error on the server, a connection drop, a firewall restart/change etc.) – Dylan .. Mark Saunders Oct 11 '17 at 8:37
  • Thanks Markko , above mentioned select query not returning any data. – Adarsh Kumar Oct 11 '17 at 8:37
  • please see my updated coment @AdarshKumar – Marko Milic Oct 11 '17 at 8:44
  • Thanks for the warning on using the scripts as-is. Indeed even if the symptoms were exactly the same, database scripts from Support are specific to a given installation, configuration, and product version. Though reading database tables aren't supported, making database updates directly without Support can potentially render a system unsupported. – Alvin Reyes Oct 12 '17 at 22:23
  • @AdarshKumar, if this is acceptable, can you please mark it as accepted answer to your question? – Marko Milic Oct 16 '17 at 7:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.