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 5397 posts 19981 karma points MVP 3x 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

  • Nicholas Westby 1906 posts 6364 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

  • Alex Skrypnyk 5397 posts 19981 karma points MVP 3x 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

  • Alex Skrypnyk 5397 posts 19981 karma points MVP 3x 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);
    
  • Saif Obeidat 53 posts 200 karma points
    Aug 24, 2019 @ 15:37
    Saif Obeidat
    0

    Hi Alex,

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

  • Alex Skrypnyk 5397 posts 19981 karma points MVP 3x 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?

  • Saif Obeidat 53 posts 200 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.

  • Alex Skrypnyk 5397 posts 19981 karma points MVP 3x admin c-trib
    24 days ago
    Alex Skrypnyk
    0

    Hi Saif

    Did you find a solution to this?

    Alex

  • Alex Skrypnyk 5397 posts 19981 karma points MVP 3x admin c-trib
    Aug 27, 2019 @ 20:50
    Alex Skrypnyk
    0

    Hi Saif

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

    Alex

Please Sign in or register to post replies

Write your reply to:

Draft