Unique Constraint violation during staging deployment in BCC

[loka reddy]

Hi,

We Created the Root Category and child categories using custom code and the project was created and deployed successfully in BCC, the root category is assigned to the catalog for the corresponding site. After that, we created a project to unlink the root category from the catalog and site, and approved the project to deploy into staging.while deploying into staging it throws the unique constraint exception while trying to insert a record in dcs_cat_chldcat table as shown below.

2014-02-07 18:28:43,118 INFO  [nucleusNamespace.atg.deployment.DeploymentManager] (Thread-7331) Database: Oracle
2014-02-07 18:28:43,135 INFO  [nucleusNamespace.atg.epub.DeploymentServer] (Thread-7331) DEBUG DistributedDeploymentAdapter:Polling Until Data Appplied
2014-02-07 18:28:43,253 INFO  [nucleusNamespace.atg.deployment.DeploymentManager] (Main deployment thread: ) Starting deployment with id: 39100021
2014-02-07 18:28:43,262 INFO  [nucleusNamespace.atg.deployment.DeploymentManager] (Main deployment thread: ) Executing deployment in LOCAL mode
2014-02-07 18:28:55,043 WARN  [nucleusNamespace.atg.epub.PublishingRepository] (http-0.0.0.0-8070-18) Using default JDBC type for: project:tar332 could not find this column in the table's meta data
2014-02-07 18:29:36,127 ERROR [nucleusNamespace.atg.commerce.catalog.ProductCatalog_staging] (RepositoryWorkerThread-2(Reference Update Phase)) Error reading list or array index from the database. Expected: "0", got "3". The following property was not read: "
{fixedChildCategories,pType=List,IDesc=[ItemDesc: category],table=dcs_cat_chldcat,cols=child_cat_id
  ,pBI=atg.beans.MergedDynamicBeanInfo@437eabcf,pIDesc=null
  ,cType=interface atg.repository.RepositoryItem,cBI=[ItemDesc: category],cIDesc=[ItemDesc: category],colHandle=null}", for item id: "DIY581083". This means the database table holding this property does not have sequential integers starting with 0 in its multi-column. This should only happen if the database table was modified directly (outside of Dynamo).
java.lang.Exception
        at atg.adapter.gsa.ListTable.loadHelper(ListTable.java:190)
        at atg.adapter.gsa.ListTable.loadProperties(ListTable.java:97)
        at atg.adapter.gsa.GSAItemDescriptor.loadProperties(GSAItemDescriptor.java:5874)
        at atg.adapter.gsa.GSAItemDescriptor.loadProperty(GSAItemDescriptor.java:5986)
        at atg.adapter.gsa.GSAItem.getPersistentPropertyValue(GSAItem.java:1315)
        at atg.adapter.gsa.GSAItem.getPropertyValue(GSAItem.java:1208)
        at atg.adapter.gsa.GSAItem.getPropertyValue(GSAItem.java:1486)
        at atg.repository.RepositoryItemImpl.getPropertyValue(RepositoryItemImpl.java:151)
        at kf.repository.CategoryDynamicBlockOverridesPropertyDescriptor.getChildCategories(CategoryDynamicBlockOverridesPropertyDescriptor.java:73)
        at kf.repository.CategoryDynamicBlockOverridesPropertyDescriptor.getPropertyValue(CategoryDynamicBlockOverridesPropertyDescriptor.java:29)
        at atg.adapter.gsa.GSAItem.getPropertyValue(GSAItem.java:1219)
        at atg.adapter.gsa.GSAItem.getPropertyValue(GSAItem.java:1486)
        at atg.adapter.gsa.GSAPropertyDescriptor.setPropertyValue(GSAPropertyDescriptor.java:406)
        at atg.repository.RepositoryItemImpl.setPropertyValue(RepositoryItemImpl.java:249)
        at atg.adapter.gsa.GSAItem.setPropertyValue(GSAItem.java:1545)
        at atg.deployment.repository.RepositoryWorkerThread.copyRepositoryItemProperties(RepositoryWorkerThread.java:1229)
        at atg.deployment.repository.RepositoryWorkerThread.deployItem(RepositoryWorkerThread.java:1120)
        at atg.deployment.repository.RepositoryWorkerThread.processMarkerForReferenceUpdatePhase(RepositoryWorkerThread.java:362)
        at atg.deployment.DeploymentWorkerThread.processMarkerPhase(DeploymentWorkerThread.java:568)
        at atg.deployment.DeploymentWorkerThread.run(DeploymentWorkerThread.java:335)
2014-02-07 18:29:36,221 ERROR [nucleusNamespace.atg.commerce.catalog.ProductCatalog_staging] (RepositoryWorkerThread-2(Reference Update Phase)) SQL Statement Failed: [++SQLInsert++]
 INSERT INTO dcs_cat_chldcat(category_id,sequence_num,child_cat_id)
 VALUES(?,?,?)
-- Parameters --
p[1] = {pd} DIY581083 (java.lang.String)
p[2] = {pd} 3 (java.lang.Integer)
p[3] = {pd: fixedChildCategories} DIY581087 (atg.adapter.gsa.SingleValueGSAId)
[--SQLInsert--]

2014-02-07 18:29:36,244 ERROR [nucleusNamespace.atg.deployment.DeploymentManager] (RepositoryWorkerThread-2(Reference Update Phase))  item = repositoryMarker:mark7611228 cause = CONTAINER:atg.deployment.DistributedDeploymentException; SOURCE:CONTAINER:atg.repository.RepositoryException; SOURCE:java.sql.SQLIntegrityConstraintViolationException: ORA-00001: unique constraint (VM_STAGING.DCS_CAT_CHLDCAT_P) violated

        at atg.deployment.repository.RepositoryWorkerThread.processMarkerForReferenceUpdatePhase(RepositoryWorkerThread.java:374)
        at atg.deployment.DeploymentWorkerThread.processMarkerPhase(DeploymentWorkerThread.java:568)
        at atg.deployment.DeploymentWorkerThread.run(DeploymentWorkerThread.java:335)
 message = Deployment Failed time = Fri Feb 07 18:29:36 EET 2014  atg.deployment.DeploymentFailure@5af453b3
CAUGHT AT:
CONTAINER:atg.deployment.DistributedDeploymentException; SOURCE:CONTAINER:atg.repository.RepositoryException; SOURCE:java.sql.SQLIntegrityConstraintViolationException: ORA-00001: unique constraint (VM_STAGING.DCS_CAT_CHLDCAT_P) violated

        at atg.deployment.repository.RepositoryWorkerThread.processMarkerForReferenceUpdatePhase(RepositoryWorkerThread.java:374)
        at atg.deployment.DeploymentWorkerThread.processMarkerPhase(DeploymentWorkerThread.java:568)
        at atg.deployment.DeploymentWorkerThread.run(DeploymentWorkerThread.java:335)
Caused by: CONTAINER:atg.repository.RepositoryException; SOURCE:java.sql.SQLIntegrityConstraintViolationException: ORA-00001: unique constraint (VM_STAGING.DCS_CAT_CHLDCAT_P) violated

        at atg.adapter.gsa.GSAItemDescriptor.updateItem(GSAItemDescriptor.java:7541)
        at atg.adapter.gsa.GSARepository.updateItem(GSARepository.java:1075)
        at atg.deployment.repository.RepositoryWorkerThread.deployItem(RepositoryWorkerThread.java:1135)
        at atg.deployment.repository.RepositoryWorkerThread.processMarkerForReferenceUpdatePhase(RepositoryWorkerThread.java:362)
        ... 2 more
Caused by: java.sql.SQLIntegrityConstraintViolationException: ORA-00001: unique constraint (VM_STAGING.DCS_CAT_CHLDCAT_P) violated

        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:440)
        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)
        at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:837)
        at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:445)
        at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:191)
        at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:523)
        at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:207)
        at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:1010)
        at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1315)
        at oracle.jdbc.driver.OracleStatement.doScrollExecuteCommon(OracleStatement.java:4644)
        at oracle.jdbc.driver.OraclePreparedStatement.doScrollPstmtExecuteUpdate(OraclePreparedStatement.java:10410)
        at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3574)
        at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:3657)
        at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeUpdate(OraclePreparedStatementWrapper.java:1350)
        at org.jboss.resource.adapter.jdbc.WrappedPreparedStatement.executeUpdate(WrappedPreparedStatement.java:365)
        at atg.adapter.gsa.SQLStatement.executeUpdate(SQLStatement.java:886)
        at atg.adapter.gsa.ListTable.insertHelper(ListTable.java:424)
        at atg.adapter.gsa.ListTable.addAndIncrement(ListTable.java:599)
        at atg.adapter.gsa.ListTable.update(ListTable.java:516)
        at atg.adapter.gsa.GSAItemDescriptor.updateItem(GSAItemDescriptor.java:7530)
        ... 5 more
SOURCE EXCEPTION:
CAUGHT AT:
CONTAINER:atg.repository.RepositoryException; SOURCE:java.sql.SQLIntegrityConstraintViolationException: ORA-00001: unique constraint (VM_STAGING.DCS_CAT_CHLDCAT_P) violated

        at atg.adapter.gsa.GSAItemDescriptor.updateItem(GSAItemDescriptor.java:7541)
        at atg.adapter.gsa.GSARepository.updateItem(GSARepository.java:1075)
        at atg.deployment.repository.RepositoryWorkerThread.deployItem(RepositoryWorkerThread.java:1135)
        at atg.deployment.repository.RepositoryWorkerThread.processMarkerForReferenceUpdatePhase(RepositoryWorkerThread.java:362)
        at atg.deployment.DeploymentWorkerThread.processMarkerPhase(DeploymentWorkerThread.java:568)
        at atg.deployment.DeploymentWorkerThread.run(DeploymentWorkerThread.java:335)
Caused by: java.sql.SQLIntegrityConstraintViolationException: ORA-00001: unique constraint (VM_STAGING.DCS_CAT_CHLDCAT_P) violated

        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:440)
        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)
        at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:837)
        at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:445)
        at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:191)
        at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:523)
        at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:207)
        at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:1010)
        at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1315)
        at oracle.jdbc.driver.OracleStatement.doScrollExecuteCommon(OracleStatement.java:4644)
        at oracle.jdbc.driver.OraclePreparedStatement.doScrollPstmtExecuteUpdate(OraclePreparedStatement.java:10410)
        at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3574)
        at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:3657)
        at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeUpdate(OraclePreparedStatementWrapper.java:1350)
        at org.jboss.resource.adapter.jdbc.WrappedPreparedStatement.executeUpdate(WrappedPreparedStatement.java:365)
        at atg.adapter.gsa.SQLStatement.executeUpdate(SQLStatement.java:886)
        at atg.adapter.gsa.ListTable.insertHelper(ListTable.java:424)
        at atg.adapter.gsa.ListTable.addAndIncrement(ListTable.java:599)
        at atg.adapter.gsa.ListTable.update(ListTable.java:516)
        at atg.adapter.gsa.GSAItemDescriptor.updateItem(GSAItemDescriptor.java:7530)
        ... 5 more
SOURCE EXCEPTION:
java.sql.SQLIntegrityConstraintViolationException: ORA-00001: unique constraint (VM_STAGING.DCS_CAT_CHLDCAT_P) violated

        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:440)
        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)
        at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:837)
        at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:445)
        at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:191)
        at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:523)
        at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:207)
        at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:1010)
        at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1315)
        at oracle.jdbc.driver.OracleStatement.doScrollExecuteCommon(OracleStatement.java:4644)
        at oracle.jdbc.driver.OraclePreparedStatement.doScrollPstmtExecuteUpdate(OraclePreparedStatement.java:10410)
        at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3574)
        at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:3657)
        at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeUpdate(OraclePreparedStatementWrapper.java:1350)
        at org.jboss.resource.adapter.jdbc.WrappedPreparedStatement.executeUpdate(WrappedPreparedStatement.java:365)
        at atg.adapter.gsa.SQLStatement.executeUpdate(SQLStatement.java:886)
        at atg.adapter.gsa.ListTable.insertHelper(ListTable.java:424)
        at atg.adapter.gsa.ListTable.addAndIncrement(ListTable.java:599)
        at atg.adapter.gsa.ListTable.update(ListTable.java:516)
        at atg.adapter.gsa.GSAItemDescriptor.updateItem(GSAItemDescriptor.java:7530)
        at atg.adapter.gsa.GSARepository.updateItem(GSARepository.java:1075)
        at atg.deployment.repository.RepositoryWorkerThread.deployItem(RepositoryWorkerThread.java:1135)
        at atg.deployment.repository.RepositoryWorkerThread.processMarkerForReferenceUpdatePhase(RepositoryWorkerThread.java:362)
        at atg.deployment.DeploymentWorkerThread.processMarkerPhase(DeploymentWorkerThread.java:568)
        at atg.deployment.DeploymentWorkerThread.run(DeploymentWorkerThread.java:335)
2014-02-07 18:29:36,264 ERROR [nucleusNamespace.atg.deployment.DeploymentManager] (RepositoryWorkerThread-2(Reference Update Phase)) Unexpected error occurred. DeploymentWorkerThread terminated prematurely.
atg.deployment.DistributedDeploymentException: Exceeded allowable error count (0)
        at atg.deployment.DeploymentWorkerThread.processMarkerPhase(DeploymentWorkerThread.java:680)
        at atg.deployment.DeploymentWorkerThread.run(DeploymentWorkerThread.java:335)
2014-02-07 18:29:38,121 INFO  [nucleusNamespace.atg.epub.DeploymentServer] (Thread-7331) DEBUG DistributedDeploymentAdapter:found 1 failure messages
2014-02-07 18:29:38,133 INFO  [nucleusNamespace.atg.epub.DeploymentServer] (Thread-7331) DEBUG DistributedDeploymentAdapter:adding failure message Deployment Failed with severity Error
2014-02-07 18:29:43,142 INFO  [nucleusNamespace.atg.epub.DeploymentServer] (Thread-7331) DEBUG DistributedDeploymentAdapter:found 1 failure messages
2014-02-07 18:29:43,149 INFO  [nucleusNamespace.atg.epub.DeploymentServer] (Thread-7331) DEBUG DistributedDeploymentAdapter:adding failure message Deployment Failed with severity Error
2014-02-07 18:29:48,147 INFO  [nucleusNamespace.atg.epub.DeploymentServer] (Thread-7331) DEBUG DistributedDeploymentAdapter:found 1 failure messages
2014-02-07 18:29:48,156 INFO  [nucleusNamespace.atg.epub.DeploymentServer] (Thread-7331) DEBUG DistributedDeploymentAdapter:adding failure message Deployment Failed with severity Error
2014-02-07 18:29:53,155 INFO  [nucleusNamespace.atg.epub.DeploymentServer] (Thread-7331) DEBUG DistributedDeploymentAdapter:found 1 failure messages
2014-02-07 18:29:53,164 INFO  [nucleusNamespace.atg.epub.DeploymentServer] (Thread-7331) DEBUG DistributedDeploymentAdapter:adding failure message Deployment Failed with severity Error
2014-02-07 18:29:58,162 INFO  [nucleusNamespace.atg.epub.DeploymentServer] (Thread-7331) DEBUG DistributedDeploymentAdapter:found 1 failure messages
2014-02-07 18:29:58,170 INFO  [nucleusNamespace.atg.epub.DeploymentServer] (Thread-7331) DEBUG DistributedDeploymentAdapter:adding failure message Deployment Failed with severity Error
2014-02-07 18:29:58,491 INFO  [nucleusNamespace.atg.epub.DeploymentServer] (Main deployment thread: ) DEBUG DistributedDeploymentAdapter:DeploymentListener.deploymentFailed called for deployment with ID: 39100021
2014-02-07 18:29:58,491 INFO  [nucleusNamespace.atg.epub.DeploymentServer] (Main deployment thread: ) DEBUG DistributedDeploymentAdapter:DAFDeploymentID: 39100021 matches DeploymmentID: 39100021
2014-02-07 18:29:58,491 INFO  [nucleusNamespace.atg.epub.DeploymentServer] (Main deployment thread: ) DEBUG DistributedDeploymentAdapter:DeploymentListener.deploymentFailed
2014-02-07 18:29:58,492 INFO  [nucleusNamespace.atg.epub.DeploymentServer] (Thread-7331) DEBUG DistributedDeploymentAdapter:found 1 failure messages

Thanks in advance.

Regards,

Lokanath.

[Nag]

Hi

I am also getting same kind of error. How did you resolve it?


Thanks,