Sign in Register
Go to solution
Copied to clipboard

Flag this post as spam?

This post will be reported to the moderators as potential spam to be looked at


  • Alex Skrypnyk 6175 posts 24186 karma points MVP 8x admin c-trib
    Jul 25, 2019 @ 20:18
    Alex Skrypnyk
    0

    Clean up recycle bin with 75000 nodes

    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 7103 karma points c-trib
    Jul 25, 2019 @ 20:46
    Nicholas Westby
    100

    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 6175 posts 24186 karma points MVP 8x admin c-trib
    Jul 26, 2019 @ 19:53
    Alex Skrypnyk
    0

    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 6175 posts 24186 karma points MVP 8x admin c-trib
    Jul 27, 2019 @ 14:01
    Alex Skrypnyk
    1

    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
    Saif Obeidat
    0

    Hi Alex,

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

    Copy Link
  • Alex Skrypnyk 6175 posts 24186 karma points MVP 8x admin c-trib
    Aug 26, 2019 @ 21:47
    Alex Skrypnyk
    0

    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
    Saif Obeidat
    0

    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 6175 posts 24186 karma points MVP 8x admin c-trib
    Nov 22, 2019 @ 00:33
    Alex Skrypnyk
    0

    Hi Saif

    Did you find a solution to this?

    Alex

    Copy Link
  • Alex Skrypnyk 6175 posts 24186 karma points MVP 8x admin c-trib
    Aug 27, 2019 @ 20:50
    Alex Skrypnyk
    1

    Hi Saif

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

    Alex

    Copy Link
Please Sign in or register to post replies

Write your reply to:

Draft