Custom index in AEMaaCS environment appeared like it might be causing problem with OOTB query, but updating the index didn't fix it

[Preston]

We have a custom index we created to address some specific needs for searching Content Fragments. We noticed a side effect where the Content Fragment browser stopped showing fragments on the initial screen.

Digging in to Splunk when the browser attempts to load this screen it makes the following OOTB query that fails on the backend. 

This is that query. 

SELECT asset.* FROM [dam:Asset] AS asset WHERE asset.[jcr:content/contentFragment] = true AND asset.[jcr:content/data/cq:model] IS NOT NULL AND ISDESCENDANTNODE(asset, '/content/dam') ORDER BY COALESCE(asset.[jcr:content/jcr:lastModified], asset.[jcr:created]) DESC, asset.[jcr:uuid] OPTION (TRAVERSAL FAIL, INDEX TAG[fragments])

Now I'm suspecting this may be caused by a custom index that is a customization of "fragments". We used the "fragments" tag and we believe that could be the cause. AEM attempts to use our custom index with the tag[fragments]" clause and our custom index isn't setup to work for that query". So we updated the index on a lower environment to no longer use the "fragments" tag as part of its definition, but the query is still failing. Is there something else we need to do here? Do we need to dummy out that custom index? Can we recreate a custom index after dummying it out? Do we need to trigger a reindex of "fragments" (currently "fragments-11"). 

[konstantyn_diachenko]

Hi @Preston ,

Official doc says:

---

Introducing new indexes on the dam:Asset nodetype (particularly fulltext indexes) is strongly discouraged as these can conflict with OOTB product features leading to functional and performance issues. In general, adding additional properties to the current damAssetLucene-* index version is the most appropriate way to index queries on the dam:Asset nodetype (these changes will automatically be merged into a new product version of the index if it is subseqently released). If in doubt, contact Adobe Support for advice.

---

So, I would suggest to extend OOTB index damAssetLucene-11 (this is latest version). Steps how to do this: https://experienceleague.adobe.com/en/docs/experience-manager-cloud-service/content/operations/index... 

[Preston]

This is a custom index meant for Content Fragments, though, and as far as we could tell the underlying search was using the "fragments" index if we didn't create our own index. 

[konstantyn_diachenko]

I think your custom index is designed for dam:Asset and probably contradicts OOTB damAssetLucene-11.

[Preston]

It's not. We based it on "fragments" and it's literally called "fragments-10-custom-2" (numbers vary, obviously). 

[konstantyn_diachenko]

Can you please share your index definition (mask any client-related info)?

[Preston]

<?xml version="1.0" encoding="UTF-8"?>
<jcr:root xmlns:oak="http://jackrabbit.apache.org/oak/ns/1.0" xmlns:dam="http://www.day.com/dam/1.0" xmlns:jcr="http://www.jcp.org/jcr/1.0" xmlns:nt="http://www.jcp.org/jcr/nt/1.0" xmlns:rep="internal"
    jcr:primaryType="oak:QueryIndexDefinition"
    async="[async,nrt]"
    compatVersion="{Long}2"
    evaluatePathRestrictions="{Boolean}true"
    includedPaths="[/content/dam/theclient/en/content-fragments]"
    queryPaths="[/content/dam/theclient/en/content-fragments]"
    reindex="{Boolean}false"
    reindexCount="{Long}1"
    selectionPolicy="tag"
    tags="[theClientFragments]"
    type="lucene">
    <facets
        jcr:primaryType="nt:unstructured"
        secure="statistical"
        topChildren="100"/>
    <indexRules jcr:primaryType="nt:unstructured">
        <dam:Asset jcr:primaryType="nt:unstructured"
            includePropertyTypes="[all]"
            indexNodeName="{Boolean}true">
            <properties jcr:primaryType="nt:unstructured">
                <dcFormat
                    jcr:primaryType="nt:unstructured"
                    facets="{Boolean}true"
                    name="jcr:content/metadata/dc:format"
                    propertyIndex="{Boolean}true"/>
                <damStatus
                    jcr:primaryType="nt:unstructured"
                    facets="{Boolean}true"
                    name="jcr:content/metadata/dam:status"
                    propertyIndex="{Boolean}true"/>
                <contentFragment
                    jcr:primaryType="nt:unstructured"
                    name="jcr:content/contentFragment"
                    propertyIndex="{Boolean}true"
                    type="Boolean"/>
                <model
                    jcr:primaryType="nt:unstructured"
                    name="jcr:content/data/cq:model"
                    ordered="{Boolean}true"
                    propertyIndex="{Boolean}true"
                    type="String"/>
                <jcrUUID
                    jcr:primaryType="nt:unstructured"
                    name="jcr:uuid"
                    ordered="{Boolean}true"
                    propertyIndex="{Boolean}true"
                    type="String"/>
                <jcrTitle
                    jcr:primaryType="nt:unstructured"
                    analyzed="{Boolean}true"
                    name="jcr:content/jcr:title"
                    ordered="{Boolean}true"
                    propertyIndex="{Boolean}true"
                    type="String"/>
                <jcrCreated
                    jcr:primaryType="nt:unstructured"
                    name="jcr:created"
                    ordered="{Boolean}true"
                    propertyIndex="{Boolean}true"
                    type="Date"/>
                <jcrCreatedBy
                    jcr:primaryType="nt:unstructured"
                    name="jcr:createdBy"
                    ordered="{Boolean}true"
                    propertyIndex="{Boolean}true"
                    type="String"/>
                <jcrLastModified
                    jcr:primaryType="nt:unstructured"
                    name="jcr:content/jcr:lastModified"
                    ordered="{Boolean}true"
                    propertyIndex="{Boolean}true"
                    type="Date"/>
                <jcrLastModifiedBy
                    jcr:primaryType="nt:unstructured"
                    name="jcr:content/jcr:lastModifiedBy"
                    ordered="{Boolean}true"
                    propertyIndex="{Boolean}true"
                    type="String"/>
                <cqLastReplicated
                    jcr:primaryType="nt:unstructured"
                    name="jcr:content/cq:lastReplicated"
                    ordered="{Boolean}true"
                    propertyIndex="{Boolean}true"
                    type="Date"/>
                <cqLastReplicatedBy
                    jcr:primaryType="nt:unstructured"
                    name="jcr:content/cq:lastReplicatedBy"
                    ordered="{Boolean}true"
                    propertyIndex="{Boolean}true"
                    type="String"/>
                <cqLastReplicationAction
                    jcr:primaryType="nt:unstructured"
                    name="jcr:content/cq:lastReplicationAction"
                    propertyIndex="{Boolean}true"
                    type="Boolean"/>
                <createModifiedCombined
                    jcr:primaryType="nt:unstructured"
                    function="fn:coalesce(jcr:content/@jcr:lastModified, @jcr:created)"
                    ordered="{Boolean}true"
                    propertyIndex="{Boolean}true"/>
                <createModifiedByCombined
                    jcr:primaryType="nt:unstructured"
                    function="fn:coalesce(jcr:content/@jcr:lastModifiedBy, @jcr:createdBy)"
                    ordered="{Boolean}true"
                    propertyIndex="{Boolean}true"/>
                <cqTags
                    jcr:primaryType="nt:unstructured"
                    name="jcr:content/metadata/cq:tags"
                    propertyIndex="{Boolean}true"/>
                <tagsOnVariations
                    jcr:primaryType="nt:unstructured"
                    name="jcr:content/metadata/dam:cfVariations/*/cq:tags"
                    propertyIndex="{Boolean}true"/>
                <_strucVersion
                    jcr:primaryType="nt:unstructured"
                    name="jcr:content/data/_strucVersion"
                    nullCheckEnabled="{Boolean}true"
                    ordered="{Boolean}true"
                    propertyIndex="{Boolean}true"
                    type="Long"/>
                <nodeNameLowerCase
                    jcr:primaryType="nt:unstructured"
                    function="fn:lower-case(fn:name())"
                    ordered="{Boolean}true"
                    propertyIndex="{Boolean}true"/>
                <damActivationTarget
                    jcr:primaryType="nt:unstructured"
                    facets="{Boolean}true"
                    name="jcr:content/metadata/dam:activationTarget"
                    propertyIndex="{Boolean}true"/>

                <!-- Begin custom fields -->
                <!-- Useful for determining which fields to add to the current version of the fragments-11 index when updating -->
                <jcrNameLowerCase
                    jcr:primaryType="nt:unstructured"
                    function="fn:lower-case(jcr:content/data/master/@jcrName)"
                    ordered="{Boolean}true"
                    propertyIndex="{Boolean}true"/>
                <includeProps
                    jcr:primaryType="nt:unstructured"
                    isRegexp="{Boolean}true"
                    name="jcr:content/data/master/[^@]*"
                    ordered="{Boolean}true"
                    propertyIndex="{Boolean}true"/>
                <excludeProps
                    jcr:primaryType="nt:unstructured"
                    index="{Boolean}false"
                    isRegexp="{Boolean}true"
                    name="jcr:content/data/master/[^@]*@(LastModified|ContentType)$"/>
                <!-- End custom fields -->
            </properties>
        </dam:Asset>
        <dam:cfVariationNode jcr:primaryType="nt:unstructured">
            <properties jcr:primaryType="nt:unstructured">
                <values
                    jcr:primaryType="nt:unstructured"
                    isRegexp="{Boolean}true"
                    name="^[^\\/]*$"
                    propertyIndex="{Boolean}true"
                    type="String"/>
            </properties>
        </dam:cfVariationNode>
    </indexRules>
    <tika jcr:primaryType="nt:unstructured">
        <config.xml jcr:primaryType="nt:file">
            <jcr:content jcr:primaryType="nt:unstructured"/>
        </config.xml>
    </tika>
</jcr:root>

Note that perviously the tags attribute was 

tags="[fragments,theClientFragments]"

[konstantyn_diachenko]

I see. Thank you for the index definition. If your custom index has name fragments-10-custom-5, it means that you did customization of OOTB fragments-10 index.

From the official doc:

---

If customizing an out-of-the-box index, for example, damAssetLucene-8, copy the latest out-of-the-box index definition from a Cloud Service environment using the CRX DE Package Manager (/crx/packmgr/) . Rename it to damAssetLucene-8-custom-1 (or higher), and add your customizations inside the XML file. This ensures that the required configurations are not being removed inadvertently. For example, the tika node under /oak:index/damAssetLucene-8/tika is required in the customized index deployed to an AEM Cloud Service environment but doesn’t exist on the local AEM SDK.

For customizations of an OOTB index, prepare a new package that contains the actual index definition that follows this naming pattern:

<indexName>-<productVersion>-custom-<customVersion>

I think in your case it makes sense to create fully customized index, because you customized queryPaths, includedPaths, tags, etc.

For a fully customized index, prepare a new index definition package that contains the index definition that follows this naming pattern:

<prefix>.<indexName>-<productVersion>-custom-<customVersion>

[Preston]

Adobe's documentation on custom indexes indicates that they expect you would use custom tags. I agree that the custom queryPaths and includedPaths might be a case for a fully custom index. If we were to do that, would we simply create a "dummy" version of the existing custom index? If that didn't work could we recreate the custom index after the dummy version? That's my concern about trying that. Once again keeping in mind how differently things behave on development environments vs actually in Cloud.

[konstantyn_diachenko]

In your case I would do the following:

1) Remove or undo your fragments-10-custom-5 to the initial state of fragments-10 in a new version fragments-10-custom-6. See more: https://experienceleague.adobe.com/en/docs/experience-manager-cloud-service/content/operations/index... 

2) Create new index like abc.clientFragments-1-custom-1 and take the definition from the fragments-10-custom-5.