Vulnerability in EPiServer.Forms
For a little over a week has the job "Remove Unrelated Content Assets" failed with error (the job has been set to run once daily):
The jobs seems to end virtually directly when executed.
The DELETE statement conflicted with the REFERENCE constraint "FK_tblContentProperty_tblContent2". The conflict occurred in database "dbEpiCmsSF", table "dbo.tblContentProperty", column 'ContentLink'.
The EPiServerErrors.log contains the following:
2014-08-14 16:35:15,153  ERROR EPiServer.DataAbstraction.ScheduledJob: Job EPiServer.Util.CleanUnusedAssetsFoldersJob failedSystem.Data.SqlClient.SqlException (0x80131904): The DELETE statement conflicted with the REFERENCE constraint "FK_tblContentProperty_tblContent2". The conflict occurred in database "dbEpiCmsSF", table "dbo.tblContentProperty", column 'ContentLink'. 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 EPiServer.DataAccess.ContentSaveDB.<>c__DisplayClass4.b__3() at EPiServer.Data.Providers.SqlDatabaseHandler.<>c__DisplayClass4.b__3() at EPiServer.Data.Providers.SqlDatabaseHandler.<>c__DisplayClass7`1.b__6() at EPiServer.Data.Providers.SqlTransientErrorsRetryPolicy.Execute[TResult](Func`1 method) at EPiServer.Core.DefaultContentRepository.Delete(ContentReference contentLink, Boolean forceDelete, AccessLevel access) at EPiServer.Util.CleanUnusedAssetsFoldersJob.Execute() at EPiServer.DataAbstraction.ScheduledJob.<>c__DisplayClass9.b__5()ClientConnectionId:e845f241-2acf-46ae-b5ba-250915bcb3e8Error Number:547,State:0,Class:16>>>>
How do I figure out whats wrong?
This is a dated reply, but I wanted to check if you were able to get around the error.
I checked your stack trace and did the following to grab a little more information about the issue.
I decompiled the EPiServer.dll assembly (Version=18.104.22.168) in the bin folder of one of my local installations. I checked the CleanUnusedAssetsFoldersJob type in the EPiServer.Util namespace. It's got an Execute() method as listed in your stack trace.
If you take a look at the body of the method, there's an IList<IChangeLogItem> called "changes" that we iterate over. Each IChangeLogItem is cast as type ChangeLogContentDeletedItems. Then the ListOwnedContentAssetReferences() method is used to return a collection further cast as IEnumerable<ContentReference>. We iterate over the associated ContentReference instances using the following foreach body.
foreach (ContentReference contentLink in (IEnumerable<ContentReference>) this._contentListDb().ListOwnedContentAssetReferences(((ChangeLogContentDeletedItems) changes[index]).DeletedIdentities))
this._contentRepository.Delete(contentLink, false, AccessLevel.NoAccess);
return "Stop of job was called. Number of deleted asset folders: " + (object) num;
The call to Delete() is not surrounded by any exception handling so that would explain why you're getting an immediate stoppage.
On the sql side of things, I looked at the [tblContentProperty] table and scripted out the foreign key constraint that is mentioned in your stack trace, see below.
ALTER TABLE [dbo].[tblContentProperty] WITH CHECK
ADD CONSTRAINT [FK_tblContentProperty_tblContent2] FOREIGN KEY([ContentLink])
REFERENCES [dbo].[tblContent] ([pkID])
ALTER TABLE [dbo].[tblContentProperty] CHECK CONSTRAINT [FK_tblContentProperty_tblContent2]
The foreign key constraint basically says that values in the foreign key column [ContentLink] of table [tblContentProperty] will refer to the primary key column [pkID] of table [tblContent]. So, it looks like the crux of the problem is that the job is attempting to delete rows in [tblContent] for which there are corresponding referring rows in [tblContentProperty].
You could try isolating the offending [tblContentProperty] records by doing a sql profiler trace and seeing which [pkID] values are being used to identify the rows to be deleted when the SqlException is happening. You could also try taking a look at the definition of the ListOwnedContentAssetReferences() method in EPiServer.DataAccess.ContentListDB to be clear about the how the collection of ContentReference instances is being retreived.
Thanks for your reply. The problem has been resolved a while ago, so I've problem remember how I managed to get around it :-S Maybe something about clearing items in the trash(?)
Glad to hear you were able to resolve the issue! Lingering items in the trash makes sense. Thank you for sharing.
I know that I'm late to the party, but I just encountered the same issue.
You can replicate the issue following these steps:
From what I can tell, the problem is that we have Blobs (added in step 1) that need to be deleted (step 2), but then, the same Blobs (the same IDs) are being added again (step 3).
If I run the "Remove Unrelated Content Assets" job before step 3, everything works fine.
Would verifying if the ID is already used, be a valid solution for this? (and if it's already used either throw an error message or generate a new ID)
Any other ideas?