clean up recycle bin with 75000 nodes

Alex Skrypnyk 6132 posts 23951 karma points MVP 7x admin c-trib

Jul 25, 2019 @ 20:18

Umbraco Cloud

Umbraco 7

Hello Our Community

I have a problem with Umbraco Cloud website that has 75000 nodes in the recycling bin, basically, it's just one node with 75000 child nodes, the problem is that I'm not able to clean up the database, I can't remove it from the recycling bin.

We are using Umbraco v 7.14, should we go with SQL query or what to do?

Umbraco Cloud support didn't help with this question.

Thanks, Alex

Copy Link

Nicholas Westby 2054 posts 7100 karma points c-trib

Jul 25, 2019 @ 20:46

Yep, you seem to be familiar with this one (you posted some replies with a link to the SQL script): https://our.umbraco.com/forum/using-umbraco-and-getting-started/87181-unable-to-empty-recycle-bin

FYI, Umbraco Cloud has a section that shows the connection string so you can connect to the database from SQL Server Management Studio.

Be sure to whitelist your IP.

Be sure to also enter the database name, as explained here: https://our.umbraco.com/documentation/Umbraco-Cloud/Databases/#backup-with-sql-server-management-studio

Copy Link

Alex Skrypnyk 6132 posts 23951 karma points MVP 7x admin c-trib

Jul 26, 2019 @ 19:53

Thanks, Nicholas, I tried this query:

DELETE FROM cmsPreviewXml WHERE  nodeId IN (SELECT id FROM umbracoNode WHERE (path LIKE '%-21%' AND id!=-21) OR (path LIKE '%-20%' AND id!=-20));
DELETE FROM cmsContentVersion WHERE  ContentId IN (SELECT id FROM umbracoNode WHERE (path LIKE '%-21%' AND id!=-21) OR (path LIKE '%-20%' AND id!=-20));
DELETE FROM cmsDocument WHERE nodeId IN (SELECT id FROM umbracoNode WHERE (path LIKE '%-21%' AND id!=-21) OR (path LIKE '%-20%' AND id!=-20));
DELETE FROM cmsTagRelationship WHERE nodeId IN (SELECT id FROM umbracoNode WHERE (path LIKE '%-21%' AND id!=-21) OR (path LIKE '%-20%' AND id!=-20));
DELETE FROM cmsContentXML WHERE nodeId IN (SELECT id FROM umbracoNode WHERE (path LIKE '%-21%' AND id!=-21) OR (path LIKE '%-20%' AND id!=-20));
DELETE FROM cmsContent WHERE nodeId IN (SELECT id FROM umbracoNode WHERE (path LIKE '%-21%' AND id!=-21) OR (path LIKE '%-20%' AND id!=-20));
DELETE FROM cmsPropertyData WHERE contentNodeId IN (SELECT id FROM umbracoNode WHERE (path LIKE '%-21%' AND id!=-21) OR (path LIKE '%-20%' AND id!=-20));
DELETE FROM umbracoDomains WHERE domainRootStructureID IN (SELECT id FROM umbracoNode WHERE (path LIKE '%-21%' AND id!=-21) OR (path LIKE '%-20%' AND id!=-20));
DELETE FROM umbracoUser2NodePermission WHERE nodeId IN (SELECT id FROM umbracoNode WHERE (path LIKE '%-21%' AND id!=-21) OR (path LIKE '%-20%' AND id!=-20));
DELETE FROM umbracorelation WHERE parentId IN (SELECT id FROM umbracoNode WHERE (path LIKE '%-21%' AND id!=-21) OR (path LIKE '%-20%' AND id!=-20));
DELETE FROM umbracorelation WHERE childId IN (SELECT id FROM umbracoNode WHERE (path LIKE '%-21%' AND id!=-21) OR (path LIKE '%-20%' AND id!=-20));
DELETE FROM umbracoredirecturl WHERE contentKey IN (SELECT uniqueID FROM umbracoNode WHERE (path LIKE '%-21%' AND id!=-21) OR (path LIKE '%-20%' AND id!=-20));
ALTER TABLE `umbraconode` DROP FOREIGN KEY `umbraconode_ibfk_1`; 
DELETE FROM umbracoNode WHERE (path LIKE '%-21%' AND id!=-21) OR (path LIKE '%-20%' AND id!=-20);
ALTER TABLE `umbraconode` ADD CONSTRAINT `umbraconode_ibfk_1` FOREIGN KEY (`parentID`) REFERENCES `umbraconode`(`id`);

Almost all tables are cleaned up, only "umbraconode" still isn't cleaned. This query didn't work for me:

ALTER TABLE `umbraconode` DROP FOREIGN KEY `umbraconode_ibfk_1`; 
DELETE FROM umbracoNode WHERE (path LIKE '%-21%' AND id!=-21) OR (path LIKE '%-20%' AND id!=-20);
ALTER TABLE `umbraconode` ADD CONSTRAINT `umbraconode_ibfk_1` FOREIGN KEY (`parentID`) REFERENCES `umbraconode`(`id`);

Alex

Copy Link

Alex Skrypnyk 6132 posts 23951 karma points MVP 7x admin c-trib

Jul 27, 2019 @ 14:01

This script worked for me in Umbaco 7.14:

DELETE FROM cmsPreviewXml WHERE  nodeId IN (SELECT id FROM umbracoNode WHERE (path LIKE '%-21%' AND id!=-21) OR (path LIKE '%-20%' AND id!=-20));
DELETE FROM cmsContentVersion WHERE  ContentId IN (SELECT id FROM umbracoNode WHERE (path LIKE '%-21%' AND id!=-21) OR (path LIKE '%-20%' AND id!=-20));
DELETE FROM cmsDocument WHERE nodeId IN (SELECT id FROM umbracoNode WHERE (path LIKE '%-21%' AND id!=-21) OR (path LIKE '%-20%' AND id!=-20));
DELETE FROM cmsTagRelationship WHERE nodeId IN (SELECT id FROM umbracoNode WHERE (path LIKE '%-21%' AND id!=-21) OR (path LIKE '%-20%' AND id!=-20));
DELETE FROM cmsContentXML WHERE nodeId IN (SELECT id FROM umbracoNode WHERE (path LIKE '%-21%' AND id!=-21) OR (path LIKE '%-20%' AND id!=-20));
DELETE FROM cmsContent WHERE nodeId IN (SELECT id FROM umbracoNode WHERE (path LIKE '%-21%' AND id!=-21) OR (path LIKE '%-20%' AND id!=-20));
DELETE FROM cmsPropertyData WHERE contentNodeId IN (SELECT id FROM umbracoNode WHERE (path LIKE '%-21%' AND id!=-21) OR (path LIKE '%-20%' AND id!=-20));
DELETE FROM umbracoDomains WHERE domainRootStructureID IN (SELECT id FROM umbracoNode WHERE (path LIKE '%-21%' AND id!=-21) OR (path LIKE '%-20%' AND id!=-20));
DELETE FROM umbracoUserGroup2NodePermission WHERE nodeId IN (SELECT id FROM umbracoNode WHERE (path LIKE '%-21%' AND id!=-21) OR (path LIKE '%-20%' AND id!=-20));
DELETE FROM umbracorelation WHERE parentId IN (SELECT id FROM umbracoNode WHERE (path LIKE '%-21%' AND id!=-21) OR (path LIKE '%-20%' AND id!=-20));
DELETE FROM umbracorelation WHERE childId IN (SELECT id FROM umbracoNode WHERE (path LIKE '%-21%' AND id!=-21) OR (path LIKE '%-20%' AND id!=-20));
DELETE FROM umbracoredirecturl WHERE contentKey IN (SELECT uniqueID FROM umbracoNode WHERE (path LIKE '%-21%' AND id!=-21) OR (path LIKE '%-20%' AND id!=-20));

DELETE FROM umbracoNode WHERE (path LIKE '%-21%' AND id!=-21) OR (path LIKE '%-20%' AND id!=-20);

Copy Link

Saif Obeidat 79 posts 296 karma points

Aug 24, 2019 @ 15:37

Hi Alex,

Do you know how that can be done on Umbraco 8.1 ?

Copy Link

Alex Skrypnyk 6132 posts 23951 karma points MVP 7x admin c-trib

Aug 26, 2019 @ 21:47

Hi Saif

I don't know, but what is the issue in Umbraco 8.1?

Copy Link

Saif Obeidat 79 posts 296 karma points

Aug 27, 2019 @ 07:38

Hi Alex,

I just want to apply the same thing in Umbraco 8.. I want to clean up a recycle bin with large number of nodes by script not through Umbraco backoffice because it takes long time and sometime it crashes.

Copy Link

Alex Skrypnyk 6132 posts 23951 karma points MVP 7x admin c-trib

Nov 22, 2019 @ 00:33

Hi Saif

Did you find a solution to this?

Alex

Copy Link

Alex Skrypnyk 6132 posts 23951 karma points MVP 7x admin c-trib

Aug 27, 2019 @ 20:50

Hi Saif

I think it makes sense to create a topic for Umbraco 8. Database scheme is really different in v8.

Alex