Troubleshooting : Validation pipeline error after triggering reverse pipeline on Auth

After triggering a reverse pipeline operation in the Authoring environment, a Severity E pipeline error is returned.

Problem

When using HCL Commerce Search Version 9.1.15.2, you receive a Severity E error after triggering a reverse pipeline operation in the Authoring environment.

A typical scenario that can trigger this error in this release is as follows:
  1. You log in to the Management Center, then select an Emerald or Emerald Catalog Asset Store.
  2. Create a new product, update Existing Products and then delete the product.
  3. Wait for Near Real-Time (NRT) indexing to complete.
  4. Trigger the reverse pipeline using the endpoint 
    http://elasticsearch-server:port/connectors/auth.validate/run?storeId=11
  5. Using the following API, check the run status:
    http://elasticsearch-server:port/connectors/auth.validate/runs/runid/status?detailedRunningStatus=false
  6. Use the runid to check the logs:
    http://elasticsearch-server:port/connectors/auth.validate/runs/runid?logSeverity=V
The log shows one or more Severity E errors.

This behavior occurs in Version 9.1.15.2 because in this version, only facetable attributes have their values indexed into the attribute index. This is done to optimize the overall ingest time. The attribute is expected to have not values in a non-facetable atttribute document. Attributes are managed differently in HCL Commerce 9.1.16 and subsequent versions.

Solution

For Catalog Asset stores
  1. Open the NiFi canvas and navigate to NiFi Flow > auth.validate.cas - Attribute Index Validation > Get Attribute List > Execute SQL. Open Find Attribute processor.
  2. Stop and configure the processor, setting Use Custom SQL to yes. Update the Custom SQL value with the following SQL code. 
    SELECT A.ATTR_ID
	      FROM LANGUAGE L, ATTR A
	      LEFT JOIN FACET F ON (A.ATTR_ID = F.ATTR_ID AND
	    	 					     F.STOREENT_ID IN (${param.storeId}))
	      LEFT JOIN ATTRDESC AD ON (A.ATTR_ID = AD.ATTR_ID AND AD.LANGUAGE_ID = L.LANGUAGE_ID)
	     WHERE A.STOREENT_ID IN (${param.storeId}) AND A.FACETABLE <> 0 AND L.LANGUAGE_ID = ${param.langId} ${excludeObjectIdsAndSQL1}
         UNION
        SELECT S.SRCHATTR_ID
          FROM SRCHATTRPROP S, SRCHATTR A, FACET F, FACETDESC D, LANGUAGE L
         WHERE S.PROPERTYNAME = 'facet' AND S.PROPERTYVALUE NOT LIKE 'ad%'
           AND S.SRCHATTR_ID = A.SRCHATTR_ID AND A.SRCHATTR_ID = F.SRCHATTR_ID
           AND (A.INDEXSCOPE IN   
		       (SELECT C.CATALOG_ID FROM STORECAT C
		         WHERE C.MASTERCATALOG = 1 AND C.STOREENT_ID IN (${param.storeId}))
		    OR  A.INDEXSCOPE = 0)
		   AND L.LANGUAGE_ID = ${param.langId} AND D.LANGUAGE_ID = L.LANGUAGE_ID AND F.FACET_ID = D.FACET_ID ${excludeObjectIdsAndSQL2}
${paging.prefix} ${param.offset} ${paging.link} ${param.pageSize} ${paging.suffix}
  3. Restart the processor.
For Esite stores
  1. Open the NiFi canvas and navigate to NiFi Flow > auth.validate.cas - Attribute Index Validation > Get Attribute List > Execute SQL. Open Find Attribute processor.
  2. Stop and configure the processor, setting Use Custom SQL to yes. Update the Custom SQL value with the following SQL code. 
    SELECT A.ATTR_ID
	      FROM LANGUAGE L, ATTR A
	      LEFT JOIN FACET F ON (A.ATTR_ID = F.ATTR_ID AND
	    	 					     F.STOREENT_ID IN (SELECT RELATEDSTORE_ID FROM STOREREL
	    	 					   					    WHERE STATE = 1 AND STRELTYP_ID = -4 AND STORE_ID = ${param.storeId}))
	      LEFT JOIN ATTRDESC AD ON (A.ATTR_ID = AD.ATTR_ID AND AD.LANGUAGE_ID = L.LANGUAGE_ID)
	     WHERE A.STOREENT_ID IN (SELECT RELATEDSTORE_ID FROM STOREREL
	    	 					    WHERE STATE = 1 AND STRELTYP_ID = -4 AND STORE_ID = ${param.storeId}) AND A.FACETABLE <> 0 AND L.LANGUAGE_ID = ${param.langId} ${excludeObjectIdsAndSQL1}
         UNION
        SELECT S.SRCHATTR_ID
          FROM SRCHATTRPROP S, SRCHATTR A, FACET F, FACETDESC D, LANGUAGE L
         WHERE S.PROPERTYNAME = 'facet' AND S.PROPERTYVALUE NOT LIKE 'ad%' AND S.PROPERTYVALUE NOT LIKE 'price_%'
           AND S.SRCHATTR_ID = A.SRCHATTR_ID AND A.SRCHATTR_ID = F.SRCHATTR_ID
           AND (A.INDEXSCOPE IN   
		       (SELECT C.CATALOG_ID FROM STORECAT C
		         WHERE C.MASTERCATALOG = 1 AND C.STOREENT_ID IN
		               (SELECT RELATEDSTORE_ID FROM STOREREL
		                 WHERE STATE = 1 AND STRELTYP_ID = -4 AND STORE_ID = ${param.storeId}))
		    OR  A.INDEXSCOPE = 0)
		   AND L.LANGUAGE_ID = ${param.langId} AND D.LANGUAGE_ID = L.LANGUAGE_ID AND F.FACET_ID = D.FACET_ID ${excludeObjectIdsAndSQL2}
${paging.prefix} ${param.offset} ${paging.link} ${param.pageSize} ${paging.suffix}
  3. Restart the processor.

Result

Reverse pipeline operations in the Authoring environemnt no longer cause Severity E errors.