Orphaned Threads

[ibaker]

I have done a check of my database and have found several hundred threads that belong to Node's that have been removed over time.

To clean these threads and posts out I thought the best way was to create a "Temp" Node and run an sql update query on the Thread's table changing their NodeID to that of the Temp Node. Then simply through the XF UI delete the threads and then delete the Temp Node. By doing it this way, any attachments in the posts would also be deleted.

SQL I would run on the xf_thread table would be:

UPDATE xf_thread SET `node_id`= x WHERE `node_id`= y

x=NodeID of Temp Node
y=NodeID of the Node that no longer exists

My question is...Is this the best way at the moment to clear out the orphaned threads and posts left over from the past...Would doing it this way get everything like attachments...Would anything be missed relating to those orphaned threads/posts?

 

[AndyB]

The above will not work to remove attachments because attachments are associated with the post_id which is referred to content_id in the attachments table.

Sounds like you would be better off creating a new node and changing the node_id. Also make sure to change the corresponding node_id in the xf_forum table.

Be sure to backup your database first.

 

[ibaker]

Thanks Andy
But if the threads are updated to the Temp NodeID, their associated posts would be displayed in the threads of the Temp Node as the Posts are linked to the ThreadID. There is no need to touch the posts.

So when viewing the Temp Node on my site wouldn't I see each of the now non-orphaned threads with their respective posts. I would then delete each of the threads manually using the XF interface which would delete their related posts and any attachments related to those posts...Once the Temp Node is empty, then delete the Temp Node in the Admin panel.

The other option is as you say, recreate the deleted Nodes, manually adjusting their NodeID in the Forum and Node tables, simply opening and then saving permissions to get the adjusted nodeID's active and then manually delete the threads using the XF interface etc

 

[AndyB]

So when viewing the Temp Node on my site wouldn't I see each of the now non-orphaned threads with their respective posts.

Whoops, your correct.

Although it still might be better to restore the deleted node_id so that in case we're overlooking anything there's a better chance of all data being properly deleted.

 

[Jake Bunce]

Yes that will work.

Note that attachments are not deleted immediately. They are processed by an hourly cron.

 

[ibaker]

Thanks Guys...just did a test run on a local installed copy of my site and seemed to work ok...so is there anything else that may be left around?

 

[AndyB]

so is there anything else that may be left around?

You can always use phpmyadmin to search for a particular id number. Use the feature that searches all the tables.