Can't empty recycle bin due to Foreign Key constraints

[PeteDuncanson]

This seems to be this old issue https://issues.umbraco.org/issue/U4-2165 rearing its ugly head again. This is happening on a V7 build (originally 7.4 but upgraded to 7.9 I believe).

I could remove the constraints and try to empty the recycle bin and then re-add them but I'm concerned as to why this might still be happening, clearly something is being allowed to get into an unknown state hence the need for this issue.

Reproduction

Difficult to do, I'll need to do some more digging into what is causing it.

The XHR call when you try to empty the recycle bin is returning a 500 error with this message:

)]}',
{"Message":"An error has occurred.","ExceptionMessage":"The DELETE statement conflicted with the SAME TABLE REFERENCE constraint \"FK_umbracoNode_umbracoNode_id\". The conflict occurred in database \"Umbraco771\", table \"dbo.umbracoNode\", column 'parentID'.\r\nThe statement has been terminated.","ExceptionType":"System.Data.SqlClient.SqlException","StackTrace":"   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)\r\n   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)\r\n   at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)\r\n   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)\r\n   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite)\r\n   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite)\r\n   at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite)\r\n   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()\r\n   at StackExchange.Profiling.Data.ProfiledDbCommand.ExecuteNonQuery()\r\n   at Umbraco.Core.Persistence.PetaPocoCommandExtensions.<>c__DisplayClass2_0.<ExecuteNonQueryWithRetry>b__0()\r\n   at Umbraco.Core.Persistence.FaultHandling.RetryPolicy.ExecuteAction[TResult](Func`1 func)\r\n   at Umbraco.Core.Persistence.Database.Execute(String sql, Object[] args)\r\n   at Umbraco.Core.Persistence.Repositories.RecycleBinRepository`2.EmptyRecycleBin()\r\n   at Umbraco.Core.Services.ContentService.EmptyRecycleBin()\r\n   at Umbraco.Web.Editors.ContentController.EmptyRecycleBin()\r\n   at lambda_method(Closure , Object , Object[] )\r\n   at System.Web.Http.Controllers.ReflectedHttpActionDescriptor.ActionExecutor.<>c__DisplayClass10.<GetExecutor>b__9(Object instance, Object[] methodParameters)\r\n   at System.Web.Http.Controllers.ReflectedHttpActionDescriptor.ExecuteAsync(HttpControllerContext controllerContext, IDictionary`2 arguments, CancellationToken cancellationToken)\r\n--- End of stack trace from previous location where exception was thrown ---\r\n   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)\r\n   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)\r\n   at System.Runtime.CompilerServices.TaskAwaiter`1.GetResult()\r\n   at System.Web.Http.Controllers.ApiControllerActionInvoker.<InvokeActionAsyncCore>d__0.MoveNext()\r\n--- End of stack trace from previous location where exception was thrown ---\r\n   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)\r\n   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)\r\n   at System.Runtime.CompilerServices.TaskAwaiter`1.GetResult()\r\n   at System.Web.Http.Filters.ActionFilterAttribute.<CallOnActionExecutedAsync>d__5.MoveNext()\r\n--- End of stack trace from previous location where exception was thrown ---\r\n   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()\r\n   at System.Web.Http.Filters.ActionFilterAttribute.<CallOnActionExecutedAsync>d__5.MoveNext()\r\n--- End of stack trace from previous location where exception was thrown ---\r\n   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)\r\n   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)\r\n   at System.Web.Http.Filters.ActionFilterAttribute.<ExecuteActionFilterAsyncCore>d__0.MoveNext()\r\n--- End of stack trace from previous location where exception was thrown ---\r\n   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)\r\n   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)\r\n   at System.Runtime.CompilerServices.TaskAwaiter`1.GetResult()\r\n   at System.Web.Http.Filters.ActionFilterAttribute.<CallOnActionExecutedAsync>d__5.MoveNext()\r\n--- End of stack trace from previous location where exception was thrown ---\r\n   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()\r\n   at System.Web.Http.Filters.ActionFilterAttribute.<CallOnActionExecutedAsync>d__5.MoveNext()\r\n--- End of stack trace from previous location where exception was thrown ---\r\n   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)\r\n   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)\r\n   at System.Web.Http.Filters.ActionFilterAttribute.<ExecuteActionFilterAsyncCore>d__0.MoveNext()\r\n--- End of stack trace from previous location where exception was thrown ---\r\n   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)\r\n   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)\r\n   at System.Runtime.CompilerServices.TaskAwaiter`1.GetResult()\r\n   at System.Web.Http.Filters.ActionFilterAttribute.<CallOnActionExecutedAsync>d__5.MoveNext()\r\n--- End of stack trace from previous location where exception was thrown ---\r\n   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()\r\n   at System.Web.Http.Filters.ActionFilterAttribute.<CallOnActionExecutedAsync>d__5.MoveNext()\r\n--- End of stack trace from previous location where exception was thrown ---\r\n   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)\r\n   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)\r\n   at System.Web.Http.Filters.ActionFilterAttribute.<ExecuteActionFilterAsyncCore>d__0.MoveNext()\r\n--- End of stack trace from previous location where exception was thrown ---\r\n   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)\r\n   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)\r\n   at System.Runtime.CompilerServices.TaskAwaiter`1.GetResult()\r\n   at System.Web.Http.Filters.ActionFilterAttribute.<CallOnActionExecutedAsync>d__5.MoveNext()\r\n--- End of stack trace from previous location where exception was thrown ---\r\n   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()\r\n   at System.Web.Http.Filters.ActionFilterAttribute.<CallOnActionExecutedAsync>d__5.MoveNext()\r\n--- End of stack trace from previous location where exception was thrown ---\r\n   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)\r\n   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)\r\n   at System.Web.Http.Filters.ActionFilterAttribute.<ExecuteActionFilterAsyncCore>d__0.MoveNext()\r\n--- End of stack trace from previous location where exception was thrown ---\r\n   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)\r\n   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)\r\n   at System.Runtime.CompilerServices.TaskAwaiter`1.GetResult()\r\n   at System.Web.Http.Filters.ActionFilterAttribute.<CallOnActionExecutedAsync>d__5.MoveNext()\r\n--- End of stack trace from previous location where exception was thrown ---\r\n   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()\r\n   at System.Web.Http.Filters.ActionFilterAttribute.<CallOnActionExecutedAsync>d__5.MoveNext()\r\n--- End of stack trace from previous location where exception was thrown ---\r\n   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)\r\n   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)\r\n   at System.Web.Http.Filters.ActionFilterAttribute.<ExecuteActionFilterAsyncCore>d__0.MoveNext()\r\n--- End of stack trace from previous location where exception was thrown ---\r\n   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)\r\n   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)\r\n   at System.Runtime.CompilerServices.TaskAwaiter`1.GetResult()\r\n   at System.Web.Http.Filters.ActionFilterAttribute.<CallOnActionExecutedAsync>d__5.MoveNext()\r\n--- End of stack trace from previous location where exception was thrown ---\r\n   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()\r\n   at System.Web.Http.Filters.ActionFilterAttribute.<CallOnActionExecutedAsync>d__5.MoveNext()\r\n--- End of stack trace from previous location where exception was thrown ---\r\n   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)\r\n   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)\r\n   at System.Web.Http.Filters.ActionFilterAttribute.<ExecuteActionFilterAsyncCore>d__0.MoveNext()\r\n--- End of stack trace from previous location where exception was thrown ---\r\n   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)\r\n   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)\r\n   at System.Runtime.CompilerServices.TaskAwaiter`1.GetResult()\r\n   at System.Web.Http.Filters.ActionFilterAttribute.<CallOnActionExecutedAsync>d__5.MoveNext()\r\n--- End of stack trace from previous location where exception was thrown ---\r\n   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()\r\n   at System.Web.Http.Filters.ActionFilterAttribute.<CallOnActionExecutedAsync>d__5.MoveNext()\r\n--- End of stack trace from previous location where exception was thrown ---\r\n   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)\r\n   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)\r\n   at System.Web.Http.Filters.ActionFilterAttribute.<ExecuteActionFilterAsyncCore>d__0.MoveNext()\r\n--- End of stack trace from previous location where exception was thrown ---\r\n   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)\r\n   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)\r\n   at System.Runtime.CompilerServices.TaskAwaiter`1.GetResult()\r\n   at System.Web.Http.Controllers.ActionFilterResult.<ExecuteAsync>d__2.MoveNext()\r\n--- End of stack trace from previous location where exception was thrown ---\r\n   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)\r\n   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)\r\n   at System.Web.Http.Filters.AuthorizationFilterAttribute.<ExecuteAuthorizationFilterAsyncCore>d__2.MoveNext()\r\n--- End of stack trace from previous location where exception was thrown ---\r\n   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)\r\n   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)\r\n   at System.Web.Http.Filters.AuthorizationFilterAttribute.<ExecuteAuthorizationFilterAsyncCore>d__2.MoveNext()\r\n--- End of stack trace from previous location where exception was thrown ---\r\n   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)\r\n   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)\r\n   at System.Web.Http.Filters.AuthorizationFilterAttribute.<ExecuteAuthorizationFilterAsyncCore>d__2.MoveNext()\r\n--- End of stack trace from previous location where exception was thrown ---\r\n   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)\r\n   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)\r\n   at System.Runtime.CompilerServices.TaskAwaiter`1.GetResult()\r\n   at System.Web.Http.Controllers.ExceptionFilterResult.<ExecuteAsync>d__0.MoveNext()\r\n--- End of stack trace from previous location where exception was thrown ---\r\n   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()\r\n   at System.Web.Http.Controllers.ExceptionFilterResult.<ExecuteAsync>d__0.MoveNext()\r\n--- End of stack trace from previous location where exception was thrown ---\r\n   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)\r\n   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)\r\n   at System.Runtime.CompilerServices.TaskAwaiter`1.GetResult()\r\n   at System.Web.Http.Dispatcher.HttpControllerDispatcher.<SendAsync>d__1.MoveNext()"}

This is a symptom though and not the cause. How did we end up with a node that doesn't have a FK reference in the database in the first place? How can we write code that just allows for this to "work" and fix itself. Why does the frontend just hang and not handle this for us better?

Expected result

Recycle bin should empty itself (obviously) and if it can't it should at least return a friendly error to the user or better still attempt to fix itself and force clear out the bin regardless of FK constraints.

Better still this fall back code shouldn't be needed and we should find the cause of how the DB got into this state in the first place. There was a comment right at the end of the linked to issue which said it might be because its trying to empty folders before they are empty which could indeed cause this error. If that is the case then we should try to fix that up with some logic to do children first or a Cascade delete.

Actual result

Front end hangs with a hidden error that you can only see through dev tools, recycle bin stays as it was, still full.

[nul800sebastiaan]

Yeah, that sounds super annoying.

Better still this fall back code shouldn't be needed and we should find the cause of how the DB got into this state in the first place.

Agreed. This IS an exception, in the true sense of the word. Most people don't have this problem. It would be absolutely great if you could send over at least the database schema so that we can see what is wrong with it.

which said it might be because its trying to empty folders before they are empty which could indeed cause this error.

You didn't specify, are you emptying the media recycle bin?

[zspasojevic]

I had the same issue and the problem was that few items in Recycle bin were not marked as Trashed (4 out of more than 300) and than I had a workaround to find those where dirty was false, delete them and then tried EmptyRecycleBin() again and it worked.

[umbrabot]

Hiya @PeteDuncanson,

Just wanted to let you know that we noticed that this issue got a bit stale and might not be relevant any more.

We will close this issue for now but we're happy to open it up again if you think it's still relevant (for example: it's a feature request that's not yet implemented, or it's a bug that's not yet been fixed).

To open it this issue up again, you can write @umbrabot still relevant in a new comment as the first line. It would be super helpful for us if on the next line you could let us know why you think it's still relevant.

For example:

@umbrabot still relevant This bug can still be reproduced in version x.y.z

This will reopen the issue in the next few hours.

Thanks, from your friendly Umbraco GitHub bot :robot: :slightly_smiling_face:

[el-slowmo]

@umbrabot still relevant Had the exact same issue when trying to empty a 200+ item recycling bin. This was on an Umbraco v8.6.1

[el-slowmo]

When checking in the db with this query

SELECT [id]
      ,[uniqueId]
      ,[parentId]
      ,[level]
      ,[path]
      ,[sortOrder]
      ,[trashed]
      ,[nodeUser]
      ,[text]
      ,[nodeObjectType]
      ,[createDate] FROM umbracoNode WHERE (path LIKE '%-21%' AND id!=-21) OR (path LIKE '%-20%' AND id!=-20) 

I found all entries in the bin had the bit 'trashed' set to 1 - unlike what @zspasojevic encountered.

However, the exception did tell me that the FK constraint prevented the delete, so, there had to be a node that still points to a recycled item.

Indeed, when querying like this: select * from umbracoNode where parentid in (SELECT [id] FROM umbracoNode WHERE (path LIKE '%-21%' AND id!=-21) OR (path LIKE '%-20%' AND id!=-20))

I got a single item that has a trashed parent item. It is level 4, and has a path like -1,1143,1149,2576,1325 -> and 2576 is indeed trashed and now underneath node -20!!

In my case that item needed to be deleted anyway, so I added it to the recycle bin by updating it:

update umbracoNode
set level=1, parentid = '-20', path = '-1,-20, 1325', trashed = 1
where id=1325

I would guess if you do not want it trashed, you would need to find a new parent for the orphaned node.

Any way, recycling the bin now no longer yields this error.

[el-slowmo]

Could the orphans be caused because Umbraco.Core.Services.Implement.ContentService.DeleteLocked is deleting the items in order of "Path" descending instead of "Level" descending? It would make a lot more sense to me that the deepest items be deleted first, and this is way more sure if we use the numeric Level instead of alfanumerical string sorting.

[nul800sebastiaan]

@el-slowmo I don't know, but you're a very rare person at the moment.. haven't seen this error in years! Let's chalk this up to a fluke or some kind of accident for now. If other people encounter it again, they now have some queries to run and we can reconsider opening the issue. If you can reproduce the issue now by sending stuff to the recycle bin that would be good too of course, then we can reproduce locally as well and figure out where the problem might be.

For now, I'll close this and keep my fingers crossed for you for no repeats 😄

[SRUC]

We are currently experiencing this same issue on Umbraco Cloud (Umbraco 8.6.8). We have hundreds of nodes, many with children going down 2-3 levels, in the recycle bin and cannot delete them.

[reneemhaas-diagram]

We are experiencing this. Umbraco Cloud hosted with Umbraco 8.9.3.

[hfloyd]

@umbrabot still relevant

I just came across this today working on a local v10 site.

What I noticed, was that the specific items in the bin which were not allowing themselves to be permanently deleted were nested items which had been trashed, then the schema had been updated to not allow the children type below the parent type (of the trashed nodes). So, perhaps during the deletion, the Path permissions are checked somehow, and these trashed nodes were no longer "valid"?

Anyway, I only had a few in the bin with the issue, and I was able to fix it through the UI by individually deleting them from the lowest-level descendants first, one at a time.

[bipin24x7]

-- This will identify orphan nodes and delete those. It wikk help with cleaning recycle bin.

DELETE FROM dbo.cmsPropertyData WHERE contentNodeId IN ( select items.id from umbracoNode as items left join umbracoNode as parent on items.parentID = parent.id WHERE parent.id is NULL );

DELETE FROM dbo.cmsPreviewXml WHERE nodeId IN ( select items.id from umbracoNode as items left join umbracoNode as parent on items.parentID = parent.id WHERE parent.id is NULL );

DELETE FROM dbo.cmsContentVersion WHERE ContentId IN ( select items.id from umbracoNode as items left join umbracoNode as parent on items.parentID = parent.id WHERE parent.id is NULL );

DELETE FROM dbo.cmsDocument WHERE nodeId IN ( select items.id from umbracoNode as items left join umbracoNode as parent on items.parentID = parent.id WHERE parent.id is NULL );

DELETE FROM dbo.cmsContent WHERE nodeId IN ( select items.id from umbracoNode as items left join umbracoNode as parent on items.parentID = parent.id WHERE parent.id is NULL );

ALTER TABLE umbracoNode NOCHECK CONSTRAINT FK_umbracoNode_umbracoNode_id; DELETE FROM umbracoNode WHERE id in ( select items.id from umbracoNode as items left join umbracoNode as parent on items.parentID = parent.id WHERE parent.id is NULL ); ALTER TABLE umbracoNode CHECK CONSTRAINT FK_umbracoNode_umbracoNode_id;