Copied to clipboard

Flag this post as spam?

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


  • Jan Skovgaard 11280 posts 23678 karma points MVP 11x admin c-trib
    Oct 10, 2012 @ 21:26
    Jan Skovgaard
    0

    Remove ghost nodes from database

    Hi guys

    I'm currently having an odd issue with Umbraco 4.9.

    I had an issue when creating nodes of a certain document type in the content area. Due to some datatypes being corrupt I got a yellow screen when creating a node. I set the document type to temporarily use another datatype and now it creates just fine.

    However all the failed creations seems to have been created but are not visible in the content tree somehow. But if I go to the root node and choose "sort" the nodes appear in the sort window.

    Therefore I would like to know how I can remove these nodes directly in the database - I only know the name of the nodes.

    Using CE SQL.

    Lookking forward to hearing form you guys.

    /Jan

  • Jeroen Breuer 4908 posts 12265 karma points MVP 5x admin c-trib
    Oct 10, 2012 @ 21:37
  • Mads Krohn 211 posts 504 karma points c-trib
    Oct 10, 2012 @ 21:42
    Mads Krohn
    0

    I don't know if it's of any help, but if you inspect the sort window (in Firebug, Chrome Developer Tools, "View frame source" etc), where you can see your nodes, you can find the id's of the nodes on the table-tr-tags. Now you have the id's and names of your nodes when venturing into the database.

  • Sébastien Richer 194 posts 430 karma points
    Oct 10, 2012 @ 22:23
    Sébastien Richer
    1

    Hi Jan,

    I've gotten this exact problem before. I ran these queries. I took this script from another post, I hate not to be able to give credit, but I completely forgot where. These are based on a more general query to empty the recycle bin. I'll include that one also after these.

    -- ############################
    -- ### Clear all their kids ###
    -- ############################
    
    DECLARE @nodeId1 int
    
    DECLARE node_cursor CURSOR FOR 
    SELECT Id
    FROM umbracoNode
    WHERE parentID = 1329 OR parentID = 1337 OR parentID = 1321
    
    OPEN node_cursor;
    
    FETCH NEXT FROM node_cursor 
    INTO @nodeId1;
    
    WHILE @@FETCH_STATUS = 0
    BEGIN
        delete from [dbo].cmsDocument where nodeId = @nodeId1
        delete from [dbo].cmsContentXml where nodeId = @nodeId1
        delete from [dbo].cmsPreviewXml where nodeId = @nodeId1
        delete from [dbo].cmsPropertyData where contentNodeId = @nodeId1
        delete from [dbo].cmsContentVersion where ContentId = @nodeId1
        delete from [dbo].cmsContent where nodeId = @nodeId1
        delete from [dbo].umbracoNode where id = @nodeId1
    
         FETCH NEXT FROM node_cursor 
        INTO @nodeId1
    END
    CLOSE node_cursor;
    DEALLOCATE node_cursor;
    
    -- #######################
    -- ### Now delete them ###
    -- #######################
    
    DECLARE @nodeId int
    
    DECLARE node_cursor CURSOR FOR 
    SELECT Id
    FROM umbracoNode
    where id = 1321 or id = 1329 or id = 1337
    
    OPEN node_cursor;
    
    FETCH NEXT FROM node_cursor 
    INTO @nodeId;
    
    WHILE @@FETCH_STATUS = 0
    BEGIN
        delete from [dbo].cmsDocument where nodeId = @nodeId
        delete from [dbo].cmsContentXml where nodeId = @nodeId
        delete from [dbo].cmsPreviewXml where nodeId = @nodeId
        delete from [dbo].cmsPropertyData where contentNodeId = @nodeId
        delete from [dbo].cmsContentVersion where ContentId = @nodeId
        delete from [dbo].cmsContent where nodeId = @nodeId
        delete from [dbo].umbracoNode where id = @nodeId
    
         FETCH NEXT FROM node_cursor 
        INTO @nodeId
    END
    CLOSE node_cursor;
    DEALLOCATE node_cursor;

    I had this project with a gigantic recycle bin that was impossible to empty through the UI so I used this.

    DECLARE @nodeId int
    
    DECLARE node_cursor CURSOR FOR 
    SELECT Id
    FROM umbracoNode
    WHERE ParentId = -20
    
    OPEN node_cursor;
    
    FETCH NEXT FROM node_cursor 
    INTO @nodeId;
    
    WHILE @@FETCH_STATUS = 0
    BEGIN
        delete from [dbo].cmsDocument where nodeId = @nodeId
        delete from [dbo].cmsContentXml where nodeId = @nodeId
        delete from [dbo].cmsPreviewXml where nodeId = @nodeId
        delete from [dbo].cmsPropertyData where contentNodeId = @nodeId
        delete from [dbo].cmsContentVersion where ContentId = @nodeId
        delete from [dbo].cmsContent where nodeId = @nodeId
        delete from [dbo].umbracoNode where id = @nodeId
    
         FETCH NEXT FROM node_cursor 
        INTO @nodeId
    END
    CLOSE node_cursor;
    DEALLOCATE node_cursor;

    Hope this helps.

    When I was digging around this issue I noticed that the problem was that one of these tables had no records for the ghosts nodes. Don'T remember which ones, but a safe bet would be to say that it's those the tree uses to display (cmdDocument I think). Of course this caused loss of data, but this was fine for me.

    Seb

  • Jan Skovgaard 11280 posts 23678 karma points MVP 11x admin c-trib
    Oct 11, 2012 @ 08:04
    Jan Skovgaard
    0

    Hi guys

    Thanks for the suggestions.

    @Sébastian - When I'm trying to run the above script in WebMatrix I get this error: ---------------------------

    Database Manager

    ---------------------------

    There was an error parsing the query. [ Token line number = 5,Token line offset = 1,Token in error = DECLARE ]

    ---------------------------

    OK

    ---------------------------

     

    ---------------------------

    System.Data.SqlServerCe.SqlCeException (0x80004005): There was an error parsing the query. [ Token line number = 5,Token line offset = 1,Token in error = DECLARE ]

       at System.Data.SqlServerCe.SqlCeCommand.ProcessResults(Int32 hr)

       at System.Data.SqlServerCe.SqlCeCommand.CompileQueryPlan()

       at System.Data.SqlServerCe.SqlCeCommand.ExecuteCommand(CommandBehavior behavior, String method, ResultSetOptions options)

       at System.Data.SqlServerCe.SqlCeCommand.ExecuteDbDataReader(CommandBehavior behavior)

       at System.Data.Common.DbCommand.ExecuteReader()

       at Microsoft.WebMatrix.DatabaseManager.SqlCeDatabase.SqlCeDatabaseProvider.ExecuteQuery(String connectionString, Query query)

       at Microsoft.WebMatrix.DatabaseManager.IisDbManagerModuleService.ExecuteQuery(Object databaseConnectionObject, Object query, String configPathState)

    ---------------------------

    Any idea how I can fix that?

  • Jeroen Breuer 4908 posts 12265 karma points MVP 5x admin c-trib
    Oct 11, 2012 @ 09:43
    Jeroen Breuer
    1

    Found another topic with a solution. You could try that. The order of delete queries is different which might help: http://our.umbraco.org/forum/core/general/17462-Missing-nodes-in-backend?p=1#comment68851.

    Jeroen

  • Lennart Stoop 304 posts 842 karma points
    Oct 11, 2012 @ 10:12
    Lennart Stoop
    1

    The TSQL script won't run properly as SQL CE dialect simply doesn't support it (declaring variables for example).

    Instead of using a cursor, you can rewrite the SQL queries using subqueries though, like:

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

     

    Grtz

    L

  • Sébastien Richer 194 posts 430 karma points
    Oct 11, 2012 @ 14:32
    Sébastien Richer
    0

    Lennart is right, sorry I missed it was SQLCE! Jeroen's link has an example to basically the same scripts, just not in a cursor loop.

    Seb

  • Jan Skovgaard 11280 posts 23678 karma points MVP 11x admin c-trib
    Oct 11, 2012 @ 20:14
    Jan Skovgaard
    0

    Hi guys

    Thanks a lot for all your comments and solution suggestions. I chose to use the solution in the post Jeroen linked to. All works like a charm now.

    /Jan

  • Anders Burla 2560 posts 8256 karma points
    Mar 14, 2013 @ 09:02
    Anders Burla
    0

    I used Jeroen's solution as well.

    I used this SQL to find the node id's to delete - just replace the parent id of the node that you cant sort childs for:

    SELECT id from umbracoNode
    WHERE parentID = 1170
    AND id NOT IN (
    SELECT DISTINCT doc.nodeId
    FROM cmsDocument AS doc
    LEFT OUTER JOIN umbracoNode AS node ON doc.nodeId = node.id
    WHERE node.parentID = 1170)

Please Sign in or register to post replies

Write your reply to:

Draft