Sitecore WFFM Unofficial Hotfix: Form Data Fails to be Aggregated

On Sitecore 8.1, when rebuilding the Reporting database, the following error occurs and halts the rebuild process:

Exception when storing an aggregation result into reporting database.
Item will be postponed and retried later.
Failed to insert or update rows in the [Fact_FormSummary] table.

Here’s an excerpt from our Sitecore log:

Note that this error may potentially occur in other conditions as well. Rebuilding the Reporting DB was just the context where we saw the error first.

Solution

First, I am going to show how you can fix this issue.

1. Add the linked stored procedure to your Reporting database(s).

(corresponding connection strings are reporting and reporting.secondary)

Add_FormSummary Stored Procedure

2. Add the following configuration patch to all processing servers.

3. Restart processing servers.

You don’t need to rebuild the reporting DB after this fix. Once the processing servers have been restarted, they will proceed to successfully aggregate all form data. Even if you are in the process of rebuilding the Reporting DB, there’s no need to restart the rebuild from scratch.

What causes the error?

Below I’ll describe how me and Angel managed to discover the root of the problem. Hopefully, our thought process can be useful to others when debugging this sort of issues.

First of all, when we noticed that the rebuild process was stalled, we looked at Sitecore logs and saw dozens, if not hundreds, of errors I mentioned earlier:

Failed to insert or update rows in the [Fact_FormSummary] table.

To get a better idea about the state of the rebuild, we watched the amount of unprocessed items. This information can be retrieved from the collection ProcesslingPool in your MongoDB database with the connection string tracking.history. Here’s the query we used:

db.ProcessingPool.aggregate(
{
$group:
{
_id: "$Attempts",
count: { $sum: 1 }
}
})

Query results:

[
{
"_id" : 0, // 0 attempts
"count" : 1340
},
{
"_id" : 1, // 1 attempt
"count" : 8895
},
{
"_id" : 2, // 2 attempts
"count" : 8239
}
]

This showed us that there was more than one attempt to process many pool items. Also, these numbers are supposed to decrease over time, but that didn’t happen. This meant that the rebuild process wasn’t making any progress.

The errors in the Sitecore log did not contain details as to the underlying reasons of failures. Still, it was obvious that xDB couldn’t insert records into the table Fact_FormSummary. So we launched SQL Server Profiler and set it up to trace all errors. That helped us in finding this relevant SQL Server error:

Violation of PRIMARY KEY constraint 'PK_FormSummary'.
Cannot insert duplicate key in object 'dbo.Fact_FormSummary'.
The duplicate key value is (1150d612-8b0e-42f8-b48f-f1b68930a814, 6d4adeba-015b-4bb5-b109-f40a50248c6f, -9059201126804239676).

As it often is with failed inserts, the problem is a duplicate key. Let’s have a look at the structure of the table in question:

CREATE TABLE [dbo].[Fact_FormSummary]
(
[FormId] [uniqueidentifier] NOT NULL,
[FieldId] [uniqueidentifier] NOT NULL,
[FieldValueId] [bigint] NOT NULL,
[FieldName] [nvarchar](max) NOT NULL,
[Count] [int] NOT NULL,
CONSTRAINT [PK_FormSummary] PRIMARY KEY CLUSTERED
(
[FormId] ASC,
[FieldId] ASC,
[FieldValueId] ASC
)
)

OK, so the primary key consists of three fields: FormId, FieldId and FieldValueId. Now we know that INSERT statements that Sitecore issues will sometimes attempt to add a new record with values of this primary key already present in the table. To find out why this happens, we decompiled Sitecore.WFFM.Analytics.dll. The code that is the culprit for the error is located in the class FormSummaryProcessor:

// ...
FormSummaryKey formSummaryKey = new FormSummaryKey
{
FormId = current2.get_ItemId(),
FieldId = current3.FieldId,
FieldName = current3.FieldName,
FieldValueId = args.GetDimension<FormFieldValues>().Add(current4)
};
FormSummaryValue formSummaryValue = new FormSummaryValue
{
Count = 1
};
if (formSummary == null)
{
formSummary = args.GetFact<FormSummary>();
}
formSummary.Emit(formSummaryKey, formSummaryValue);

AHA! A FormSummaryKey object in the code consists of four fields, not three! The extra key field here is FieldName. This means that when xDB generates an SQL MERGE statement, it will look like this:

MERGE
[Fact_FormSummary] AS [target]
USING ( VALUES ( @p30, @p34, @p17, @p35, @p31 ) )
AS [source]
(
[FormId],
[FieldId],
[FieldValueId],
[FieldName],
[Count]
)
ON
([target].[FormId] = [source].[FormId]) AND
([target].[FieldId] = [source].[FieldId]) AND
([target].[FieldValueId] = [source].[FieldValueId]) AND
([target].[FieldName] = [source].[FieldName])
WHEN MATCHED THEN
UPDATE
SET
[target].[Count] = ([target].[Count] + [source].[Count])
WHEN NOT MATCHED THEN
INSERT
(
[FormId],
[FieldId],
[FieldValueId],
[FieldName],
[Count]
)
VALUES
(
[source].[FormId],
[source].[FieldId],
[source].[FieldValueId],
[source].[FieldName],
[source].[Count]
);

This is clearly a mistake. FieldId and FieldName both refer to a field and are both included as a part of the "key"!

This becomes a problem when you change the name of a form field after the form has been live for a while. When updating the aggregated record for the field, the MERGE statement will think that it should insert a new entry because the condition at line 16 (above) is not fulfilled anymore. The insert will fail as the key only includes three fields: FormId, FieldId and FieldValueId. We'll see the error Violation of PRIMARY KEY constraint 'PK_FormSummary' because a record with these exact form, field and value IDs already exists.

Conclusion

This issue is still present in the latest Web Forms for Marketers. The solution I provided above will fix the errors by overriding Sitecore’s MERGE statement with a custom stored procedure.

The most proper fix, though, should be applied in the WFFM source code: classes FormSummaryProcessor, FormSummaryKeyand FormSummaryValue need to be changed to remove FieldName from the logical key of the form summary. I have submitted this to Sitecore Support along with my suggestions; bug reference number is 441147.