• Not Answered

Problems with rebuilding reporting database


- Sitecore 8.0 Update-2 Dedicated Processing Server

- Mongo 1.10 client libraries (connecting to Mongo 3.0 db replica set)

I recently decided to rebuild my reporting database, and I think that doing so has highlighted some configuration problem in this version of Sitecore.

To elaborate:

  • Cleared all data from tables in Reporting.Secondary db - noticed there was a lot of data but not sure why
  • Restarted Sitecore processing server
  • Noticed many errors in the logs referring to procedure failures on Secondary database

I've removed all material parts of my configuration changes and I don't understand why there would be any activity from the aggregators against the secondary database at all?

Here's an example error:

7976 21:24:16 ERROR Error during aggregation.
Exception: System.Data.SqlClient.SqlException
Message: T-SQL ERROR 547, SEVERITY 16, STATE 0, PROCEDURE Add_Traffic, LINE 24, MESSAGE: The MERGE statement conflicted with the FOREIGN KEY constraint "FK_Fact_Traffic_Campaigns". The conflict occurred in database "SitecoreAnalyticsSecondary_Live", table "dbo.Campaigns", column 'CampaignId'.
T-SQL ERROR 547, SEVERITY 16, STATE 0, PROCEDURE Add_ValueBySource, LINE 21, MESSAGE: The MERGE statement conflicted with the FOREIGN KEY constraint "FK_Fact_ValueBySource_TrafficTypes". The conflict occurred in database "SitecoreAnalyticsSecondary_Live", table "dbo.TrafficTypes", column 'TrafficType'.
T-SQL ERROR 547, SEVERITY 16, STATE 0, PROCEDURE Add_VisitsByBusinessContactLocation, LINE 24, MESSAGE: The MERGE statement conflicted with the FOREIGN KEY constraint "FK_Fact_VisitsByBusinessContactLocation_TrafficTypes". The conflict occurred in database "SitecoreAnalyticsSecondary_Live", table "dbo.TrafficTypes", column 'TrafficType'.
Source: .Net SqlClient Data Provider
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite)
   at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite)
   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   at Sitecore.Data.DataProviders.Sql.DataProviderTransaction.Dispose()
   at Sitecore.Analytics.Aggregation.SqlReportingStorageProvider.<>c__DisplayClass5.<Store>b__2()
   at Sitecore.Data.DataProviders.NullRetryer.ExecuteNoResult(Action action, Action recover)
   at Sitecore.Analytics.Aggregation.SqlReportingStorageProvider.Store(Guid id, AggregationDataSet data)
   at Sitecore.ExperienceAnalytics.Client.Platform.SwitchingReportingStorageProvider.Store(Guid id, AggregationDataSet data)
   at Sitecore.Analytics.Aggregation.InteractionAggregator.Aggregate(Byte[] recordKey, IAggregationContext context)
   at Sitecore.Analytics.Aggregation.Aggregator.Execute()

Now I'm not at all surprised that "Add_Traffic" is failing here - I just deleted all the data from the secondary database after all.

What I'm surprised by is why the aggregator is executing a procedure on the secondary database at all?

I haven't started running the rebuild process yet, and I used config to set the live and contact processing pools to Enabled=false like so:


Going through all the config most of the references to the aggregation/reportingStorageProviders/secondary.xxx elements are either in a ReportingTargets/secondary element or in the historyTaskManager which I believe is only concerned with the process of rebuilding the reporting database.

While this is very confusing, the bigger problem is that the rebuild doesn't work at all, but fails with a NullReferenceException at:

3056 21:36:31 ERROR Exception when executing agent aggregation/rebuildAgent
Exception: System.NullReferenceException
Message: Object reference not set to an instance of an object.
Source: Sitecore.Analytics.MongoDB
   at Sitecore.Analytics.Aggregation.History.MongoDbHistoryTaskManager.InitializeScheduler(Nullable`1 cutoff)
   at Sitecore.Analytics.Aggregation.History.HistoryTaskManager.StartProcessingHistory(Nullable`1 cutoff, Boolean copyDefinition)
   at Sitecore.Analytics.Aggregation.History.InteractionHistoryAggregatorManager.Start(List`1 targetProvidersConfigPaths)
   at System.Linq.Enumerable.WhereSelectListIterator`2.MoveNext()
   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
   at Sitecore.Analytics.Aggregation.History.ReportingStorageManager.ProcessRebuild()
   at Sitecore.Analytics.Core.BackgroundService.Run()

I've looked at the code in InitializeScheduler and it appears to construct and populate a "HistoryRange" object in MongoDB - in fact I can see the output in MongoDB which looks to be populated properly so I really can't tell where it's going wrong.

I have a huge number of ProcessingPool objects in both tracking_live and tracking_contact that won't get processed and the whole system is a bit stuck.

Any help or advice appreciated at this point!

3 Replies