XF 1.2 How to clean deleted post, forums, and threads

[Brandon Sheley]

I thought there was suppose to be a fix in 1.2 but it's not working ATM or I don't know how to use it.
I had merged my site with another one then removed a ton of threads.

My site has just under 80,000 post but whenever I run any updated via admin.php?tools/run-deferred it's showing approximately 500,000 post and threads that were removed when I split the forums apart again. (The DB is showing the old post as well.. 423,000)
Also my member list is jacked up, it's showing the top poster with 14,000 post when she actually has a dozen or so post and threads.

What do I need to do to clean up the old data that isn't on the site anymore?

Thanks
-Brandon

 

[Chris D]

For you to be having problems like this, it seems you might have slightly more deep-seated issues than can be solved by simple cache rebuilds.

"Merging sites" doesn't sound like a good idea. Neither does "removed a ton of threads" because presumably you just removed them from the database, right?

Seems to me like you have a lot of orphaned records. And perhaps some crossover where user_ids are concerned. Not sure the best course of action, going forward.

 

[Brandon Sheley]

So are you suggesting that there is no solution?

Merging sites is a very common thing to do, also I removed them from the forum itself by manually selecting all the threads and deleting or deleting the whole forum. There is a thread somewhere that others were complain about old threads and post being in the database still even after deleting forums or "nodes" as they're called here.

I want to remove everything from the database that isn't visible on the front end. It doesn't seem like it's that tough of a request. I know other script will do this with ease and I'd hate to think that the only way to clean up my database of deleted threads is to convert it to another script then back.

thx

This is the thread I was thinking of, I'll try @Jake Bunce suggested in it.
http://xenforo.com/community/threads/orphan-threads-and-posts.9741/

 

[Mike]

1.2 doesn't retroactively fix the orphan issue - it just deletes all the content if you delete a forum after upgrading.

There isn't a script to clean up orphan data. I suppose you could try creating a forum and changing the node ID in the xf_node and xf_forum tables to one of the forums with orphaned data and then deleting the forum. I'd strongly recommend taking a backup before you do this. It's not something I've tried (and I wouldn't say it's recommended).

 

[Brandon Sheley]

1.2 doesn't retroactively fix the orphan issue - it just deletes all the content if you delete a forum after upgrading.

There isn't a script to clean up orphan data. I suppose you could try creating a forum and changing the node ID in the xf_node and xf_forum tables to one of the forums with orphaned data and then deleting the forum. I'd strongly recommend taking a backup before you do this. It's not something I've tried (and I wouldn't say it's recommended).

Thanks, ya that's what was suggested above.
I also tried cleaning up old threads via the batch thread cleanup and according to the backend, I deleted something like 30,000 records but I still see 430,000 post in my database xf_post table

I'll try the method mentioned above as soon as I get some more time.

 

[Paul B]

An alternative method might be to create a new temp forum and then update the node ID for the orphaned data to that new ID.
For example:

"UPDATE xf_thread
SET node_id = 4
WHERE node_id = 2"

Then you should be able to delete the temp forum and the threads and posts will be removed.

Untested so I can't guarantee the outcome - again take a backup first and test it on a dev site if possible.

 

[Brandon Sheley]

Thanks @Brogan, I think this is the route I was checking out last night after the batch thread cleanup didn't do what I was expecting. I have a "trash" forum that I was going to move everything into then delete the threads from there.. Or at least I'm going to try that, hopefully it helps.

 

[Paul B]

Deleting a forum now deletes the threads so if there are thousands of them it would be quicker to just delete the forum from the ACP.

 

[Brandon Sheley]

Deleting a forum now deletes the threads so if there are thousands of them it would be quicker to just delete the forum from the ACP.

good to know

There doesn't happen to be a query that I could run to find all orphaned threads is there?

 

[Paul B]

It might be possible to compare the node IDs with the node table and identify any which aren't listed there but that's beyond my capabilities.

 

[Brandon Sheley]

Ya I'll just have to dig around and compare, how about a query that would give me a list of all threads before month/day/year?
I wouldn't mind just removing all threads before say 2009 if possible.

thanks for the help

 

[Marcel]

Couldn't you just run a query that selects all the threads with thread.node_id that don't exist in node.node_id (where node_type_id = forum), and thread.post_date < 1230768000

I'll be buggered if I can work out the SQL statement from that though.

 

[Adam Howard]

An alternative method might be to create a new temp forum and then update the node ID for the orphaned data to that new ID.
For example:

"UPDATE xf_thread
SET node_id = 4
WHERE node_id = 2"

Then you should be able to delete the temp forum and the threads and posts will be removed.

Untested so I can't guarantee the outcome - again take a backup first and test it on a dev site if possible.

Dude.

Do you have any idea how much I love XenForo right now? Or how useful your post is to me this very moment?

Long ago... We had an evil staff member who deleted a few forums (and all their content). We tried something like what you suggested on the forum software we were using, but it caused a lot of problems doing it. And so we had to accept the fact that these threads were beyond hope.

Fast forward to today.... Guess what... XenForo is beautify product. I XenForo.