Copied to clipboard

Flag this post as spam?

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


  • Nik Wahlberg 639 posts 1237 karma points MVP
    Aug 15, 2009 @ 15:33
    Nik Wahlberg
    0

    Delete items in Recycle Bin

    Hi all,

    This topic was already raised in the following thread. I am starting a new one to see if it can generate some interest. I have a problem in my currentinstallation were everytime I try to empty the recycle bin it ends up ADDING nodes instead of deleting. I think I have an issue where the recycle bin hung, I cancelled the process, and now it's messed up.

    Does anyone have some tips on how I might be able to manually delete these nodes from the DB? I feel like that is my only opion at this point (other than starting fresh, which is not really an option any longer.

    Thank you all.

    -- Nik

  • Douglas Robar 3570 posts 4711 karma points MVP ∞ admin c-trib
    Aug 15, 2009 @ 16:21
    Douglas Robar
    102

    You know the drill... backup before playing with the database since this is not the "approved" method.

    But, since you asked... look for nodes that have -20 in the @path. -20 is the recycle bin.

    cheers,
    doug.

  • Nik Wahlberg 639 posts 1237 karma points MVP
    Aug 17, 2009 @ 15:09
    Nik Wahlberg
    0

    Ah, ok. Should have thought of the 'path'. THanks Doug.

    -- Nik

  • Nik Wahlberg 639 posts 1237 karma points MVP
    Aug 21, 2009 @ 02:20
    Nik Wahlberg
    2

    Ok, so if anyone else runs into this issue, herre's the script that I have created to 'delete' everything in the recycle bin. As Doug states above, this is not a sanctioned method so pleae backup all of your datasource(s) before attempting this:

    -- 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 cmsContent 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 cmsDocument 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!=-20
  • Wojciech Tengler 95 posts 198 karma points
    Mar 30, 2010 @ 16:22
    Wojciech Tengler
    0

    I think that recursive T-SQL will be more safe because I have just had situation in which path contains "-20" value but the parent of this node  isn't in recycle bin.

    So how to update path column? :)

    Best regards

  • Chris Houston 535 posts 980 karma points MVP admin c-trib
    Aug 19, 2010 @ 17:55
    Chris Houston
    1

    @Nik, thank for posting this SQL, it's just solved my issue and saved me having to write it :)

    Cheers,

    Chris

  • Manos Gatsios 21 posts 41 karma points
    Oct 21, 2010 @ 15:52
    Manos Gatsios
    0

    Maybe a bit too late but isn't it safer and simpler to look for nodes with a parentID = -20 ?

    Or am I missing something...

    Regards,

    Manos

     

  • Dave Rollins 35 posts 48 karma points
    Nov 09, 2010 @ 11:59
    Dave Rollins
    0

    Querying on ParentID = -20 vs path like '%-20%' and id!=-20 came back with different counts for me ???

    I also needed to add a delete of the CMSTagRelationship table to the script above:

    So mine looks like this:

    -- 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 cmsContent 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 cmsDocument 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 cmsTagRelationship where nodeId 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!=-20 

     

  • Jorge Ruiz Caro 9 posts 29 karma points
    Dec 28, 2010 @ 00:21
    Jorge Ruiz Caro
    0

    Greetings everyone, i had the same problem because i was migrating data from another database to umbraco, but i forgot to delete it permanently. Then in the Recycled bin I had like 6000 items. I tried the script that left Nik, but i had to do some changes, a i leave it :



    delete from cmsPreviewXml where versionID in (select versionid from cmsContentVersion where ContentId in (select nodeId from cmsContent where nodeId in (select id from umbracoNode where path like '%-20%' and id!=-20)))

    delete from cmsContentVersion where ContentId in (select nodeid 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 cmsContentXML where nodeId in (select nodeid from cmsContent where nodeId 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 cmsContent where nodeId in (select id from umbracoNode where path like '%-20%' and id!=-20)

    delete from umbracoNode where path like '%-20%' and id!=-20


    /*
    select * from cmsDocumentType where contentTypeNodeId in (select id from umbracoNode where path like '%-20%' and id!=-20)

    select * from cmsDataType where nodeid in (select id from umbracoNode where path like '%-20%' and id!=-20)

    select * from umbracoUser2NodePermission where nodeid in (select id from umbracoNode where path like '%-20%' and id!=-20)



    */

    By the way, it worked because i did not use tags,relations,members and permissions with the nodes.

    Jorge

  • Kim Grandgagnage 63 posts 87 karma points
    Sep 16, 2011 @ 18:01
    Kim Grandgagnage
    0

    I executed the script of Dave, everything executes well, but on the last line I get an error message saying:

    The DELETE statement conflicted with the SAME TABLE REFERENCE constraint "FK_umbracoNode_umbracoNode".

    Any ideas?

    My recycle bin is still not empty, because the last line won't execute I suppose?...

    grts,

    Kim

  • Tom 713 posts 954 karma points
    Oct 12, 2011 @ 04:39
    Tom
    0

    I have the same issue as kim any help?

  • Kim Grandgagnage 63 posts 87 karma points
    Oct 12, 2011 @ 09:57
    Kim Grandgagnage
    0

    Hi Tom,

    I solved the issue by first removing the parent of the umbraco Node (=ParentId of current node) that had a relationship with the node I tried to remove using the SQL statement. When that node is removed, the foreign key conflict no longer occurs.

     

    grts,

    Kim

     

  • Tom 713 posts 954 karma points
    Oct 12, 2011 @ 23:08
    Tom
    0

    Thanks Kim!

    I ended up doing the same! but unfortunately now when I add things to the recycle bin and try and delete im getting an error response from the web service call to remove a node:

     

    1. ScriptResource.axd:6934Uncaught Sys.Net.WebServiceFailedException: Sys.Net.WebServiceFailedException: The server method 'Delete' failed with the following error: System.NullReferenceException-- Object reference not set to an instance of an object.
      1. onCompleteScriptResource.axd:6934
      2. (anonymous function)ScriptResource.axd:3484
      3. Sys$Net$WebRequest$completedScriptResource.axd:6364
      4. Sys$Net$XMLHttpExecutor._onReadyStateChange
  • Tom 713 posts 954 karma points
    Oct 12, 2011 @ 23:21
    Tom
    0

    Now when i try and delete an element from the recycle bin when debugging:

     

     public static void Delete(string NodeType, int NodeId, string Text)

            {

                // Load task settings

                XmlDocument createDef = GetXmlDoc();

     

                // Create an instance of the type by loading it from the assembly

                XmlNode def = createDef.SelectSingleNode("//nodeType [@alias = '" + NodeType + "']");

                string taskAssembly = def.SelectSingleNode("./tasks/delete").Attributes.GetNamedItem("assembly").Value;

                string taskType = def.SelectSingleNode("./tasks/delete").Attributes.GetNamedItem("type").Value;

     

                Assembly assembly = Assembly.LoadFrom(System.Web.HttpContext.Current.Server.MapPath(GlobalSettings.Path + "/../bin/" + taskAssembly + ".dll"));

                Type type = assembly.GetType(taskAssembly + "." + taskType);

                interfaces.ITask typeInstance = Activator.CreateInstance(type) as interfaces.ITask;

                if (typeInstance != null)

                {

                    typeInstance.ParentID = NodeId;

                    typeInstance.Alias = Text;

                    typeInstance.Delete();

                }

            }

     

     

    the XmlNode def is null for the node type "contentRecycleBin" when there is clearly some content still in the bin im trying to delete!

    Any help would be most appreciated.

    Thanks,

    Tom

  • Blair Garrett 2 posts 22 karma points
    Feb 17, 2012 @ 12:17
    Blair Garrett
    0

    Is functionality exposed through the API in version 5 for empyting or deleting items by their id from the recycle bin?

  • Denford 132 posts 323 karma points
    Apr 12, 2013 @ 15:18
    Denford
    1

    I can confirm the script bellow will delete all the bin items, got the error above 1st by using @nik script then tried @jorge script and it worked fine. this worked on an umbraco 4.11.4 installation;

    delete from cmsPreviewXml where versionID in (select versionid from cmsContentVersion where ContentId in (select nodeId from cmsContent where nodeId in (select id from umbracoNode where path like '%-20%' and id!=-20)))

    delete from cmsContentVersion where ContentId in (select nodeid 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 cmsContentXML where nodeId in (select nodeid from cmsContent where nodeId 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 cmsContent 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 Dingley 1470 posts 3427 karma points c-trib
    Apr 16, 2013 @ 10:33
    Simon Dingley
    0

    Thanks @denford this worked for me. I'd say that 9 out of 10 times I am unable to empty the recycle bin. The UI offers no feedback and the underlying error you can see in your Firebug console provides nothing useful. The solution is a bit of an extreme measure but unfortauntely seems to be the only viable solution at the moment as running PathFix doesn't seem to overcome the error.

    Cheers, Simon

  • Shahid Hafiz 9 posts 29 karma points
    Jul 18, 2013 @ 16:17
    Shahid Hafiz
    0

    in version 6.0.5, instead of deleting the contents in the bin, it deletes everything but, so the entire site has gone.
    mysql db has the nodes and content (maybe previous versions), but the umbraco interface says nodes dont exsist and so no publish occurs.

     

     

     

  • David Madi 3 posts 26 karma points
    Oct 30, 2013 @ 19:19
    David Madi
    3

    I've used the Nik Wahlberg post to create this SQL Server Job to clean the recycle bin.

    Hope its useful.

    --select * From umbracoNode where path like '%-20%' and id!=-20

    create procedure EmptyRecycleBin

    as 

    begin

     

    declare @nodesToDelete table(id int, lev int)

    declare @treeLevel int

    declare @previousLevel int;

    declare @rowsInserted int;

    set @rowsInserted = 1;

    set @treeLevel = 0;

    set @previousLevel = 0;

     

    insert into @nodesToDelete (id, lev)

    SELECT DISTINCT id, @treeLevel FROM umbracoNode (nolock) where path like '%-20%' and id!=-20 and parentID = '-20'

     

    set @treeLevel = @treeLevel + 1;

     

    while @rowsInserted > 0

    begin

    SET ROWCOUNT 0

    insert into @nodesToDelete (id, lev)

    SELECT DISTINCT id, @treeLevel FROM umbracoNode (nolock) where parentID in (select id from @nodesToDelete where lev = @previousLevel)

    set @rowsInserted = @@ROWCOUNT;

    print @rowsInserted;

    set @previousLevel = @treeLevel;

    set @treeLevel = @treeLevel + 1;

    end

     

    declare @currId int

    declare @currLevel int

    declare currentDeletionCursor cursor for select * from @nodesToDelete order by lev desc

    open currentDeletionCursor;

    FETCH NEXT FROM currentDeletionCursor INTO @currId, @currLevel;

    WHILE @@FETCH_STATUS = 0

    begin

    BEGIN TRY

    begin tran;

    delete from cmsPreviewXml where nodeId in (@currId)

    delete from cmsContentVersion where contentId in (@currId)

    delete from cmsDocument where nodeId in (@currId)

    delete from cmsContentXML where nodeId in (@currId)

    delete from cmsContent where nodeId in (@currId)

    delete from cmsPropertyData where contentNodeId in (@currId)

    delete from umbracoRelation where childId in (@currId)

    delete from umbracoRelation where parentId in (@currId)

    delete from umbracoUser2NodePermission where nodeId in (@currId)

    DELETE FROM umbracoNode WHERE id = @currId;

    commit;

    END TRY

    BEGIN CATCH

    rollback;

    END CATCH;

    FETCH NEXT FROM currentDeletionCursor INTO @currId, @currLevel;

    end

    CLOSE currentDeletionCursor;

    DEALLOCATE currentDeletionCursor;

    end

  • Aaron 22 posts 93 karma points
    Jun 30, 2017 @ 11:09
    Aaron
    0

    Many thanks David only your recursive approach worked in my case.

  • Matt Taylor 873 posts 2086 karma points
    Nov 30, 2018 @ 19:01
    Matt Taylor
    0

    Great, David's script helped me delete over 6,000 nodes in a v4 site.

  • Fergus Davidson 309 posts 588 karma points
    Jun 19, 2014 @ 11:26
    Fergus Davidson
    0

    @David - this seemed to work for me.

    Umbraco v4.11.3

    Database had appeared empty 'Empty recycle bin' said 1056 items.

    Ran script [after backing up, of course]. took circa 12 mins to complete. nodes are gone, deleting still functions, everything seems good.

    many thanks

  • Diogo Cunha 8 posts 69 karma points
    Feb 10, 2015 @ 21:08
    Diogo Cunha
    1

    From the first response I added a couple of other tables and it worked for me

    -- 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 cmsContent 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 cmsDocument 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 cmsTagRelationship where nodeId 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
    
Please Sign in or register to post replies

Write your reply to:

Draft