Updating from 2105 to 2205, unique index violation

[stefanweis]

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?

[neilhp]

I have not seen this issue myself, but I have some thoughts:

CmsItem is an abstract type without a deployment table, the child types are concrete types that each do have their own table. As CmsItem does not have a defined deployment table, it therefore inherits the one from the parent type: GenericItem. The index is defined at the CmsItem level so that it applies to all of the child types, but in doing so it is also apply to the generic items table that is inherited from GenericItem.

One of the data modelling principals in SAP Commerce is that all non-abstract types inheriting from GenericItem should define their own deployment table. Similarly with the abstract types inheriting from GenericItem, then the concrete child types should define a deployment table. There should not therefore be anything really using the generic items table that has actual concrete instances that become a row in the table.

However in this case there are rows there & those rows are probably for types that don't know about mid/catalogversion because they are non-CMS types. At the DB level it doesn't know about different types of objects sitting in a single table, it just applies the index. I'd guess that you have at least two rows sitting in that genericitems table that are non-CMS & therefore have uid=null, catalogversion=null. In Azure SQL (as in CCv2) nulls are considered unique - so only 1 record with null in both fields can be there if a unique index exists.

Usually this sort of situation suggests a historic data modelling issue, where custom types have not been given a deployment table & so the records for that type sit in GenericItems. Solving that can be tricky as you both need to change the data modelling in a project release + also move records to the new table at the same time as allocating them a new PK (deployment tables are per type code, so a new typecode will be needed ... and typecodes are embedded in the PKs)

[stefanweis]

Hi Neil,

I think, unfortunately, you are right. Is there any possibility to find out which table/item would cause this situation?

[stefanweis]

Im not sure, but maybe setting the property

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

would be a possibility for us. As migrating all of our data which extends GenericItem without a deployment table would be very risky and time consuming.

[felix_k1]

Hi Stefan,

were you able to remove / avoid creating the index by setting the property you mentioned?

[stefanweis]

Hi Felix,

yes, I was able to prevent the creation of the index by setting these two properties:

bootstrap.init.type.system.ignore.indices=true

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

[danielspasovski_kps]

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

[viktor_rudomyotkin]

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]

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

[b_jojo]

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...

[viktor_rudomyotkin]

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]

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