U4-522 - Can't Empty Recycle Bin When Nodes Have Custom Permissions

Created by Sebastiaan Janssen 19 Aug 2012, 14:53:46 Updated by Mark Mars 29 Apr 2013, 08:56:43

We just found an issue with Umbraco emptying recycle bin. In a project we had an issue where the recycle bin would not empty, it would just hang and not do anything.

When I went to delete it through SQL it also would not let me do it due to a foreign key constraint to the table “umbracoUser2NodePermission”.

I think the issue can be replicated if you go into the users section and start assigning custom permissions for specific users to specific nodes. Then when you go into the Content section and delete a node that has custom permissions assigned to it, it won’t allow you to delete items in the recycle bin due to the foreign key constraint.

It is on a 4.7.1 installation.

''Originally created on CodePlex by [jbeauchamp|http://www.codeplex.com/site/users/view/jbeauchamp]'' on 1/11/2012 5:38:17 PM [Codeplex ID: 30674 - Codeplex Votes: 1]

Comments

Simon Steed 20 Aug 2012, 10:16:22

I've had this problem for ages now, the fix is to do the following:

umbracoUser2NodePermission table, set the FK_umbracoUser2NodePermission_umbracoNode Delete Rule to cascade and save umbracoRelation table, set the FK_umbracoRelation_umbracoNode Delete Rule to cascade and save

You should then be able to empty the recycle bin. These are DB changes so you run at your own risk but this works fine for the 3/4 sites i've had this on. Really these db script changes need applying to the core db upgrade process.


Anthony 20 Aug 2012, 10:21:41

You dont really need to play with the cascading delete rules...

Run this and your problems go away:

-- Delete all 'related' nodes and table contents... DELETE FROM cmsPreviewXml WHERE nodeId IN (SELECT id FROM umbracoNode WHERE path LIKE '%-20%' AND id != -20) DELETE FROM cmsContentVersion WHERE contentId IN (SELECT id FROM umbracoNode WHERE path LIKE '%-20%' AND id != -20) DELETE FROM cmsDocument WHERE nodeId IN (SELECT id FROM umbracoNode WHERE path LIKE '%-20%' AND id != -20) DELETE FROM cmsContentXML WHERE nodeId IN (SELECT id FROM umbracoNode WHERE path LIKE '%-20%' AND id != -20) DELETE FROM cmsContent WHERE nodeId IN (SELECT id FROM umbracoNode WHERE path LIKE '%-20%' AND id != -20) DELETE FROM cmsPropertyData WHERE contentNodeId IN (SELECT id FROM umbracoNode WHERE path LIKE '%-20%' AND id != -20) DELETE FROM umbracoDomains WHERE domainRootStructureID IN (SELECT id FROM umbracoNode WHERE path LIKE '%-20%' AND id != -20) DELETE FROM umbracoUser2NodePermission WHERE nodeId IN (SELECT id FROM umbracoNode WHERE path LIKE '%-20%' AND id != -20) DELETE FROM umbracoNode WHERE path LIKE '%-20%' AND id != -20


Simon Steed 20 Aug 2012, 10:27:31

This is the script I currently run:

-- clear out recycle bin files -- Uncomment below to verify the number of nodes returned is the -- same as the number of nodes that is in the Recycle Bin -- select * from umbracoNode where path like '%-20%' and id!=-20 -- Delete all 'related' nodes and table contents... delete from cmsPreviewXml where nodeId in (select id from umbracoNode where path like '%-20%' and id!=-20) delete from cmsContentVersion where contentId in (select id from umbracoNode where path like '%-20%' and id!=-20) delete from cmsDocument where nodeId in (select id from umbracoNode where path like '%-20%' and id!=-20) delete from cmsContentXML where nodeId in (select id from umbracoNode where path like '%-20%' and id!=-20) delete from cmsContent where nodeId in (select id from umbracoNode where path like '%-20%' and id!=-20) delete from cmsPropertyData where contentNodeId in (select id from umbracoNode where path like '%-20%' and id!=-20) -- delete the XML nodes.... delete from umbracoNode where path like '%,-20,%' and id not in (select parentID from umbracoNode where path not like '%,-20,%')

OBviously got a couple of statements missing - mind you the problem still persists that you have to drop down to SQL Manager to empty the recycle bin - thanks for the script update though :-)


Sebastiaan Janssen 01 Apr 2013, 16:56:31

This has been fixed in 6.0+, which uses the new API to delete content in the recycle bin. Considering this fixed.


Mark Mars 11 Apr 2013, 13:53:56

We are using 6.0.3 and still have this problem. Even worse the above script no longer works either. When trying to execute the last delete statement (DELETE FROM umbracoNode WHERE path LIKE '%-20%' AND id != -20) we get the following:

Msg 547, Level 16, State 0, Line 3 The DELETE statement conflicted with the SAME TABLE REFERENCE constraint "FK_umbracoNode_umbracoNode_id". The conflict occurred in database "db-name", table "dbo.umbracoNode", column 'parentID'.

I'm not much of a SQL man so despite my efforts I could not work round this. Any ideas anyone?


nicky 23 Apr 2013, 11:08:53

Same problem with version 4.11. Does anyone has a recursive SQL script to delete the recycle bin?


Mark Mars 27 Apr 2013, 06:45:58

I've not solved this yet Nicky. I'm hoping Sebastiaan will come good. I've still a week or 2 before the e-commerce site goes live...


Sebastiaan Janssen 27 Apr 2013, 07:30:03

@Mark We've made more improvements to the order in which things get deleted in 6.0.4, this should solve your problem. Please upgrade. :)


Mark Mars 27 Apr 2013, 08:31:48

Thanks Sebastiaan. I'll give it a go and report back.


Mark Mars 29 Apr 2013, 07:59:06

Hi Sebastiaan. Upgraded our dev server to 6.0.4 this morning. Tried to empty recycle bin. Nothing got deleted. Problem still remains.

I'd be happy with a workable recursive script at this stage but obviously a solid recycle bin longer term.

Thanks, Mark


Sebastiaan Janssen 29 Apr 2013, 08:04:56

@Mark Do you have custom permissions on the nodes?


Mark Mars 29 Apr 2013, 08:13:19

Hi Sebastiaan. No, we don't have custom permissions. The nodes are however mostly Tea Commerce nodes.


Sebastiaan Janssen 29 Apr 2013, 08:19:11

@Mark Okay, could you please open a new issue with more details then (javascript errors, log files, etc), it's a different problem then.


Mark Mars 29 Apr 2013, 08:56:43

Thanks Sebastiaan.

Opened here: http://issues.umbraco.org/issue/U4-2165

Mark


Priority: Normal

Type: Bug

State: Fixed

Assignee:

Difficulty: Normal

Category:

Backwards Compatible: True

Fix Submitted:

Affected versions:

Due in version: 6.0.0

Sprint:

Story Points:

Cycle: