Issue description

The following exception is quite popular and can be found in the log files when the processing speed is low:

165164 14:04:56 WARN  Failed to process interaction with Id 2e58c56c-d6c7-452b-a1d1-2b068c15372c and ContactId {F9BE0608-E18C-4A35-B2C2-EA3C5226C107}. Processing will be retried later.
Exception: System.InvalidOperationException
Message: Definition not found: itemId: '{38AAA41B-E509-4083-B876-799B809F13BA}' culture: 'Invariant Language (Invariant Country)' type: 'Sitecore.Marketing.Definitions.Goals.IGoalDefinition'
Source: Sitecore.ExperienceAnalytics.Aggregation
   at Sitecore.ExperienceAnalytics.Aggregation.Pipeline.SegmentProcessor.ProcessSegments(AggregationPipelineArgs args, IEnumerable`1 segments)
   at Sitecore.ExperienceAnalytics.Aggregation.Pipeline.SegmentProcessor.OnProcess(AggregationPipelineArgs args)
   at Sitecore.Analytics.Aggregation.Pipeline.AggregationProcessor.Process(AggregationPipelineArgs args)
   at (Object , Object )
   at Sitecore.Pipelines.CorePipeline.Run(PipelineArgs args)
   at Sitecore.Pipelines.DefaultCorePipelineManager.Run(String pipelineName, PipelineArgs args, String pipelineDomain, Boolean failIfNotExists)
   at Sitecore.Pipelines.DefaultCorePipelineManager.Run(String pipelineName, PipelineArgs args, String pipelineDomain)
   at Sitecore.Analytics.Aggregation.Pipeline.AggregationPipeline.Run(AggregationPipelineArgs args)
   at Sitecore.Analytics.Aggregation.InteractionBatchAggregator.Aggregate(IVisitAggregationContext interaction, InteractionAggregationType interactionAggregationType, RebuildTargets targets)
   at Sitecore.Analytics.Aggregation.InteractionBatchAggregator.Aggregate(ItemBatch`1 batch, InteractionAggregationType interactionAggregationType, RebuildTargets targets)

Nested Exception

Exception: System.InvalidOperationException
Message: Definition not found: itemId: '{38AAA41B-E509-4083-B876-799B809F13BA}' culture: 'Invariant Language (Invariant Country)' type: 'Sitecore.Marketing.Definitions.Goals.IGoalDefinition'
Source: Sitecore.ExperienceAnalytics.Core
...

As it is seen from the stacktrace and the message, the exception is thrown during aggregation when converted goal can’t be found.
The processing pool will contain the records with attempts > 0 in this case:
pool


As a result, each record with missing goal will be tried to be processed 10 times. Even with small amount of data, this will slow down the aggregation process significantly.
There are two main sources of the marketing definition items: 1. Master database is the main marketing definitions storage (source of trust). Definitions are retrieved from the master database when standolone instance is used. 2. Reporting database is the definitions storage, which is used for aggregation if dedicated processing server is in place.

To address the issue, the following actions can be performed:

  1. Try to find the item which corresponds to the missing item ID in the master database. If the item is present in the master database, the issue is most likely happens due to the fact that the definition is missing in the reporting database. Redeploying the definitions using the Control Panel must solve the issue.
  2. In case if the item is missing, everything becomes a bit more difficult. The situation is the following: there is data in the collection database, which was collected for a goal which was removed.

What to do if the definition is missing in the master database?

The collection database stores the id of the goal item and during the aggregation, it should be retrieved from the definitions storage to get addition info (like assets).
Thus, to overcome the exceptions, it is possible to recreate the goals with the same ids as stored in the collection database. Here is the approach which I used:

  1. Backup the solution.
  2. For each problematic id from the log files like 38AAA41B-E509-4083-B876-799B809F13BA here:
    Definition not found: itemId: ‘**{38AAA41B-E509-4083-B876-799B809F13BA}’* culture: ‘Invariant Language (Invariant Country)’ type: ‘Sitecore.Marketing.Definitions.Goals.IGoalDefinition’*

    Ensure that the item does not exist and create a new goal item under /sitecore/system/Marketing Control Panel/Goals item. Do not deploy it. You can name it the way you want.
  3. Copy the created goal id.
  4. Now, we need to replace this id in the master database with the one from the log files. It can be done using the following SQL code executed against Master database:
    declare @itemId uniqueidentifier = '{F451B31C-71D2-4433-B449-C9886E9A09EC}' -- Your created goal id goes here.
    declare @oldItemId uniqueidentifier = '{38AAA41B-E509-4083-B876-799B809F13BA}' -- id from the log files goes here.

    UPDATE [dbo].SharedFields
    SET ItemId = @oldItemId
    WHERE ItemId = @itemId

    UPDATE [dbo].VersionedFields
    SET ItemId = @oldItemId
    WHERE ItemId = @itemId

    UPDATE [dbo].Items
    SET ID = @oldItemId
    WHERE ID = @itemId

Before running the update scripts, it is better to ensure that the item does not exist in DB:

SELECT *
FROM [dbo].Items where ID = @oldItemId

SELECT *
FROM [dbo].SharedFields where ItemId = @oldItemId

SELECT *
FROM [dbo].VersionedFields where ItemId = @oldItemId*


Since Sitecore has number of caching layers, to have the relevant data in the Content Editor, I recommend stopping the Sitecore instance when performing the SQL operation.
5. Start Sitecore instance and check that the created goals have correct IDs now (if you do not stop the process during running the scripts, you may get old IDs since they will be taken from cache).
6. Deploy the goals.
7. Check if the exceptions disappeared from the log files for the recreated goal ids.


The approach was checked for 8.2.7, should work for 9.x in the same way. The same behavior may be experienced for other marketing definitions (like campaigns). The approach is the same, the only difference is the definition item which needs to be created on the 2nd step.


P.S. When completing this article, I though about using ItemManager to create the item with corresponding ID. Did not check it, though I believe it should work as well.


P.P.S Do not remove the marketing definitions once created!