UCommerce Vague scratch indexer errors, not quite what it seems

I hit a problem recently where I was working with Ucommerce and linking it up to Sitefinity as the e-commerce solution.

As part of this task due to the customer's requirements, we decided to manually set up Ucommerce and then export the Ucommerce_X SQL tables in the database. This was also spurred on with a bug in Ucommerce for Sitefinity, where it does not run the install scripts on installation as expected. This works well in practice as it means when this script is run against another database, the product structure, price groups, catalogs, categories, payment methods, order states, payment methods, shipping methods, email profiles/types and order series (plus more) are all set up. As SiteSync for Sitefinity does not implement uCommerce entities at this time it makes life much easier when replicating the uCommerce store structure across environments. The script will not contain things specific to an environment, such as products, orders, order lines, customers or addresses.

This in theory, works perfectly; until it doesn't.

When running this script onto a database for a new environment, I started getting issues with the website.

Naturally, as the database structure data for uCommerce has changed, the scratch indexer needs to be run first to localise everything on the hosting server.

I hit a brick wall when running the indexer and got no errors, but I found that the indexes were not getting created for 'Product'.

As this is the Sitefinity CMS, this manifests as showing all website widgets as an exception message instead of rendering, which makes the website unusable.

The error being thrown in the logs for these widgets is:

"Type : Ucommerce.Search.Lucene.Indexes.IndexMissingError, Ucommerce.Search.Lucene, Version=9.5.1.21265, Culture=neutral, PublicKeyToken=null
Message : Searching for Product in English, I did not find the index files at '\App_Data/Ucommerce/Indexes/B/Product/DefaultProductsIndexDefinition/24473202/en'. Please check the base path in ucommerce/Configuration/Search.config and index everything from scratch.
Source : Ucommerce.Search.Lucene
Help link :
Data : System.Collections.ListDictionaryInternal
TargetSite : Lucene.Net.Facet.Taxonomy.SearcherTaxonomyManager GetManager(Ucommerce.Search.Lucene.IndexLocation)
HResult : -2146233088
Stack Trace :    at Ucommerce.Search.Lucene.Indexes.DiskIndex`1.GetManager(IndexLocation location)
   at Ucommerce.Search.Lucene.Indexes.DiskIndex`1.Find[TProjection](CultureInfo culture)"

However, the solution given is to run the scratch indexer again, which reports no error messages when it has finished execution.

After some pondering, I realised that the message is not actually telling me that it can't find 'en' Product indexes that it is expecting to be there and that they have just not been generated. But they were not generated because no Products are defined in the database.

Thinking about the default install process that uCommerce ships with, this starts to make sense when, by Default UCommerce ships with a uCommerce store, several catalogs and default products (which no one ever asked for).

This then makes more sense as to why these are there when it is an unwritten requirement to have at least one product, price group and catalog.

Annoyingly the error message from the logs, the uCommerce documentation or in the uCommerce UI, nowhere does not inform you that there must be at least one product defined for the indexer to work. The uCommerce UI also does not stop you from deleting all products defined and having a website that is then broken when you next index. The only fix then is to manually trawl the SQL tables and create a placeholder product with a sql script. Another side note on this is that the product must also have 'DisplayOnSite' = True for the product to be indexed.

The main takeaway with this is that if you are using uCommerce and you are having head-scratching issues with uCommerce's scratch indexer missing index files that you are not sure about. Ensure you have at least one of the types of index defined before indexing.

It sounds obvious now, but if you don't know, you don't know.

If you're in this boat, you may find this script helpful for creating a default product to get you out of this jam:

-- Create a default product
	DECLARE @PriceGroupId int;
	SET @PriceGroupId = 
	DECLARE @ProductDefinitionId int;
	SET @ProductDefinitionId = 
--Just creates with a price of 0
 INSERT INTO  [dbo].[uCommerce_Price] VALUES(0, @PriceGroupId, NEWID())
	DECLARE @PriceId int;
	SET @PriceId = (SELECT TOP 1 PriceId From [dbo].[uCommerce_Price])
	--NOTE UCommerce MUST have at least one product defined and it must be Display On Site = 1 for the indexer to be able to run. Go Figure....
	INSERT INTO  [dbo].[uCommerce_Product] VALUES (NULL, 'DEFAULT', null, 'DEFAULT', 1, null, null, 0, @ProductDefinitionId, 0, 'System', GETUTCDATE(), GETUTCDATE(), 'System', null, NEWID());
		DECLARE @ProductId int;
	SET @ProductId = (SELECT TOP 1 ProductId From [dbo].[uCommerce_Product])
		INSERT INTO [dbo].[uCommerce_ProductPrice] VALUES (1, NEWID(), @ProductId, @PriceId)
-- Check what localisations you have defined in your languages table or if you have a category use that and add for each as below. You may not have these 10 cultures that i have..

INSERT INTO [dbo].[uCommerce_ProductDescription] 
SELECT @ProductId, 'DEFAULT', '','', d.CultureCode, NEWID() 

FROM (SELECT DISTINCT cd.[CultureCode] FROM uCommerce_CategoryDescription cd) d
 ===
-- Finally, query what productDefinitionFieldIds you have for the @ProductDefinitionId you have used above and set a default value for each ProductDefinitionId returned:
--  = SELECT  [ProductDefinitionFieldId]  FROM [dbo].[uCommerce_ProductDefinitionField] where ProductDefinitionId = @ProductDefinitionId and Deleted = 0
INSERT INTO [dbo].[uCommerce_ProductProperty] VALUES ('  ', , @ProductId, NEWID())