cancel
Showing results for 
Search instead for 
Did you mean: 

Updating from 2105 to 2205, unique index violation

stefanweis
Participant
2,936

Hi,

we're trying to update from SAP Commerce 2105.11 to 2205.0. While performing the ant updatesystem task in our ccv2 deployment, we're getting the following error message:

problem executing sql [reason: StatementCallback; uncategorized SQLException for SQL [ CREATE UNIQUE INDEX cmsItemByUidCvIDX_99 ON genericitems (p_uid, p_catalogversion);  CREATE INDEX i4a4122c8_typecode_84 ON maptypes3 (InternalCode);  CREATE INDEX i4a4122c8_typecodelowercase_84 ON maptypes3 (InternalCodeLowerCase);  CREATE UNIQUE INDEX cmsItemByUidCvIDX_1076 ON pagetemplate (p_uid, p_catalogversion);  CREATE UNIQUE INDEX cmsItemByUidCvIDX_1074 ON restriction (p_uid, p_catalogversion);  CREATE INDEX SlotsForPagePageIdx_1071 ON slotsforpage (p_page);  CREATE UNIQUE INDEX cmsRelationByUidCvIdx_1071 ON slotsforpage (p_uid, p_catalogversion);  CREATE INDEX SlotsForTempTempIdx_1069 ON slotsfortemplate (p_pagetemplate);  CREATE UNIQUE INDEX cmsRelationByUidCvIdx_1069 ON slotsfortemplate (p_uid, p_catalogversion);  CREATE INDEX i4a4122c8_nameidx_typesystemprops3 ON typesystemprops3 (NAME);  CREATE INDEX i4a4122c8_itempk_typesystemprops3 ON typesystemprops3 (ITEMPK);  CREATE INDEX i4a4122c8_realnameidx_typesystemprops3 ON typesystemprops3 (REALNAME)]; SQL state [S0001]; error code [1505]; The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.genericitems' and the index name 'cmsItemByUidCvIDX_99'. The duplicate key value is (<NULL>, <NULL>).; nested exception is java.sql.BatchUpdateException: The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.genericitems' and the index name 'cmsItemByUidCvIDX_99'. The duplicate key value is (<NULL>, <NULL>).]

We're seeing that in the cms2-items.xml the new index cmsItemByUidCvIDX was introduced. Otherwise we are not seeing anything related to our custom implementation.

Is anyone else having this issue?

Accepted Solutions (0)

Answers (4)

Answers (4)

Hello,

The issue that's causing it's very well explained here.

We also had this issue in our projects and we were thinking to prevent the creation of the index by setting these two properties as mentioned by stefanweis here in this comment.

bootstrap.init.type.system.ignore.indices=true
bootstrap.init.type.system.model.index.ignore.names.1=cmsItemByUidCvIDX

But, it looks like re-creating the indexes with unique=false might be the safest option:

<!-- START Replace unique indexes causing 2205 upgrade issues --><br><itemtype code="CMSRelation" autocreate="false" generate="false"><br>    <indexes><br>        <index name="cmsRelationByUidCvIdx" replace="true" unique="false"><br>            <key attribute="uid"/><br>            <key attribute="catalogVersion"/><br>        </index><br>    </indexes><br></itemtype><br><br><itemtype code="CMSItem" autocreate="false" generate="false"><br>    <indexes><br>        <index name="cmsItemByUidCvIDX" replace="true" unique="false"><br>            <key attribute="uid"/><br>            <key attribute="catalogVersion"/><br>        </index><br>    </indexes><br></itemtype><br><br><itemtype code="ComponentTypeGroup" autocreate="false" generate="false"><br>    <indexes><br>        <index name="componentTypeGroupByCodeIDX" replace="true" unique="false"><br>            <key attribute="code"/><br>        </index><br>    </indexes><br></itemtype><br><!-- END Replace unique indexes causing 2205 upgrade issues -->

Btw. we were also not able to find any duplicates for these types:

SELECT count(*) as cc,{uid} as uid,{catalogVersion} as catalogVersion from {CMSItem} group by {uid},{catalogVersion} having count(*) > 1<br>SELECT count(*) as cc,{uid} as uid,{catalogVersion} as catalogVersion from {CMSRelation} group by {uid},{catalogVersion} having count(*) > 1<br>SELECT count(*) as cc,{code} as code from {ComponentTypeGroup} group by {code} having count(*) > 1

Regards,

Daniel

Try something similar to the following as SQL in hac:

select count(*), p_uid from dbo.cmsitem group by p_uid having count(*) >1;

stefanweis
Participant

Thank you! Im seeing several itemtypes which have p_uid=null. Like WorkflowTemplate for example.

b_jojo
Explorer
0 Kudos

Hi,

Our findings show that the genericitems database table contains WorkflowTemplate objects which are extending GenericItem. CMSItem is another extension of GenericItem but does not contain a separate deployment table for itself. Other cms items extending CMSItem may have their own tables, but the index defined in the OOTB cms2-item.xml file considers all CMSItems and that is why it also takes the genericitems table into account. The index depends on the uid attribute, which is not present in the GenericItem definition, all the more so in the WorkflowTemplate definition which depends on the code attribute as its unique identifier.

Might this be an error in ootb items definition?

Deleting the index seems a good workaround for the time being...

0 Kudos

Hi Stefan,

You will need to figure out the duplicate entries in the table corresponding to the index and then remove or modify them in the way that they conform to the new index.

Kind regards,

Viktor

stefanweis
Participant
0 Kudos

The index is being added for CMSItem. As the duplicate key values are NULL, NULL I tried running the following flex searches, I get no results so I dont see where the problem is:

select {pk} from {CMSItem} where {uid} is null

select {pk} from {CMSItem} where {catalogversion} is null