It has been a long time after my latest post. New job , new resposibilities. I still work as a Sitecore consultant, but with my new role as Consultant Manager, and a little disapointed of not getting the MVP status after a long time trying, I didn’t have the time and the motivation to write articles for this blog. I am glad to see that some of my articles are still read and shared
During the last weeks I have worked on an important bug that occured on all our environments (test, stage and prod). The resolution of this bug involved a lot of reseach in pipelines and Sitecore databases, and above all it was not documented or discussed anywhere. So I decided to write an article about this, in the case of someone in this community had the same problem.
So what happened ?
We have a Sitecore 10.2 instance with many JSS sites. Some of these sites has one or many Sitecore Commerce catalogs. We have a separate index for Sitecore Commerce items, so “sitecore_web_index” and “sitecore_master_index” manage only Sitecore items.
This new commerce index is very important to show all our products on our different websites.
Suddenly, the complete indexing of this commerce items couldn’t index all of the objects. It stopped without errors after for example 3993 items, or 2995 och 998, instead of 30000+ items. Always just before the end of a batch of 1000 items. The incremental index was still working without problem.
The only Exception we could find in the minion log was something that seemed at first irrelevant:
ERROR Pipeline completed with error
System.Exception: Error processing block: ApplyComposerTemplatesBlock
---> System.NullReferenceException: Object reference not set to an instance of an object.
at System.Object.GetType()
at Sitecore.Commerce.Plugin.Catalog.BaseApplyComposerTemplatesBlock1.<>c__DisplayClass0_0.<ApplyComposerTemplates>b__0(ComposerTemplate x) at System.Linq.Enumerable.WhereListIterator1.MoveNext()
at System.Collections.Generic.List1.InsertRange(Int32 index, IEnumerable1 collection)
at Sitecore.Commerce.Plugin.Catalog.BaseApplyComposerTemplatesBlock`1.ApplyComposerTemplates(CommerceEntity entity, CommercePipelineExecutionContext context)
at Sitecore.Commerce.Plugin.Catalog.ApplyComposerTemplatesBlock.Run(PipelineArgument arg, CommercePipelineExecutionContext context)Full Indexing 101
Reading code in the Pipeline FullIndexMinionPipeline, it is clear that the system is searching first all the items from Sitecore Commerce in batches of 1000 items (default value, it can be changed easily). Those items are then added to the relevant index in Solr. When the number of items returned by the intern database search is less than the number of the batch (1000), then the full index process is stopped.
For example if the pipeline is asking for 1000 items and gets only 993 items, the process is stopped without error, thinking that there is no more items to get from the database.
So how does this intern database search is working ? The logical choice would be that it retrieve rows from the intern database table CatalogEntities, but instead it calls a Stored Procedure (CatalogGetMappingsWeb). This Stored Procedure actually looks in the database table Mappings, list items from there and then retrieve these items from the table CatalogEntities.
In our case, we had commerce entities present in the table Mappings, but these entities were old and already deleted from CatalogEntities.
This is why the Stored Procedure returned for example 993 items instead of 1000, because we had 7 rows in the Mappings database with old entities.
The Full index pipeline then thought the search returned the latest entities from the database and stopped gracefully.
The solution
Open a connection to the Sitecore database and remove all rows in Mappings table with entites that are not present in CatalogEntities table. Here is a SQL query doing just this:
In the database SitecoreCommerce_SharedEnvironments:
DELETE from [sitecore_commerce_storage].[Mappings]
where Id in (
SELECT Id
FROM [sitecore_commerce_storage].[Mappings]
where entityid not in (
SELECT Id
FROM [sitecore_commerce_storage].[CatalogEntities]
)
)
jesuissitecore.com 