Previous Thread
Next Thread
Print Thread
Rate Thread
Joined: Feb 2007
Posts: 329
Yarp™
Yarp™
Offline
Joined: Feb 2007
Posts: 329
If this query:

Code
SELECT count( DISTINCT TOPIC_ID )
FROM ubbt_POSTS
WHERE TOPIC_ID NOT
IN (
SELECT TOPIC_ID
FROM ubbt_TOPICS
)

Gives you a result >0 you have posts in your database that are not referenced anymore by any existing topic. Attached scripts will salvage those topics to a predefined forum (YOU HAVE TO SET THIS MANUALLY IN THE SCRIPT BEFORE USING IT).

This script and above query require MySQL >= v4.10

Enjoy your cleaned up database smile
Attachments
salvage.zip (1.91 KB, 12 downloads)
Topic salvage tool v1.00
SHA1: a7c09c4649645d4903820d9e5285ccc743aaa7e2

Sponsored Links
Joined: Feb 2007
Posts: 329
Yarp™
Yarp™
Offline
Joined: Feb 2007
Posts: 329
A note that didn't really fit in the first post of this topic. The inline moderation does not always want to cooperate with topics salvaged by above tool. Not all salvaged topics are a-okay. Some topics you have to delete by going into them and deleting them with the manage topic stuff.

When using the tool, I discovered some complete topics, but also some loose messages.

Joined: Feb 2002
Posts: 2,286
Veteran
Veteran
Joined: Feb 2002
Posts: 2,286
Thanks - gave it a go as I had 19 posts that were not linked.

It recovered 5 threads. Not all can now be deleted, which I am not too worried about.

One says it can't find the post in the database to view the post.

The other where it merged two threads together (making a 4 post thread), I could delete the last 2 posts, but the 2nd post you can't delete as it says it is the first post in the thread (which it isn't now) and has dependant posts beneath it (which it doesn't)

Anyway I am not too worried, but just reporting back my findings.

HOWEVER! - it found the very first post I made with threads back in 2001 - meaning that my forums are at least 7 years old. Although they go back a couple of years further in a smaller incarnation. So I am happy smile


Fans Focus - Focusing on Fans of Sport

(Okay - mainly football (the British variety wink at the moment - but expanding all the time....)
Joined: Feb 2007
Posts: 329
Yarp™
Yarp™
Offline
Joined: Feb 2007
Posts: 329
For some posts, I just went into the post, and deleted it from the edit post screen.

I also had one problem with a post I could not click on, but clicking on the date on the left did open the topic.

You finding your very first post kinda makes the time spend on this all worth it wink

Joined: Apr 2001
Posts: 96
Power User
Power User
Joined: Apr 2001
Posts: 96
Quote

$salvageforum = #;

Just to be clear, would I have to run this for each forum? Can the new private forum be used for each run of this?

Edit: Never mind, I read that wrong...

Last edited by jgeoff; 05/19/2008 11:20 AM.

GangsterBB.NET (Ver. 7.3)
2007 Content Rulez Contest - Honorable Mention
UBB.classic 6.7.2 - RIP
Browser: Firefox 2.0
Sponsored Links
Joined: Feb 2007
Posts: 329
Yarp™
Yarp™
Offline
Joined: Feb 2007
Posts: 329
No, the forum you enter there is the forum the lost posts are put into.

You should make a new empty forum for that to see exactly what is salvaged.

Joined: Apr 2001
Posts: 96
Power User
Power User
Joined: Apr 2001
Posts: 96

It worked... YOU RULE!!! cool

Now to figure out how long thread posts get orphaned to begin with...



GangsterBB.NET (Ver. 7.3)
2007 Content Rulez Contest - Honorable Mention
UBB.classic 6.7.2 - RIP
Browser: Firefox 2.0
Joined: Feb 2007
Posts: 329
Yarp™
Yarp™
Offline
Joined: Feb 2007
Posts: 329
Great to hear!

Joined: Nov 2003
Posts: 482
Enthusiast
Enthusiast
Offline
Joined: Nov 2003
Posts: 482
sometimes Yarp™ does good work... laugh

it's akin to a Blind squirrel getting an acorn every now and then..


Joined: Mar 2000
Posts: 21,079
Likes: 3
I type Like navaho
I type Like navaho
Joined: Mar 2000
Posts: 21,079
Likes: 3
Cool, my personal site lists 2, I need to try and and run the script now.

I know we could probably use it here - seems like most of the announcements we imported from threadsdev have issues - like ubbt_topics has them, but ubbt_posts doesn't. Except our mysl is 4.0.something smash


- Allen wavey
- What Drives You?
Sponsored Links
Joined: Feb 2007
Posts: 329
Yarp™
Yarp™
Offline
Joined: Feb 2007
Posts: 329
Ah, is ubbdev the reason threads still insists on supporting MySQL <4.1 wink

You would only need to redo the part that finds out about the lost posts and their topic id's since that one uses a query in a query. The rest would work fine.

Joined: Mar 2000
Posts: 21,079
Likes: 3
I type Like navaho
I type Like navaho
Joined: Mar 2000
Posts: 21,079
Likes: 3
You may be right there wink

I may talk to Navaho and see about transferring the site to our server... he'd probably prefer to get it out of his hair too smile


- Allen wavey
- What Drives You?
Joined: Nov 2003
Posts: 482
Enthusiast
Enthusiast
Offline
Joined: Nov 2003
Posts: 482
you guys should be on mysql 5 and php 5 and apache 2, for being a ubbDEV site, imho :2c:

i have a dedicated server that can handle your needs wink

Joined: Mar 2000
Posts: 21,079
Likes: 3
I type Like navaho
I type Like navaho
Joined: Mar 2000
Posts: 21,079
Likes: 3
We should... I have lots of space on mine too - I may ask him soon.


- Allen wavey
- What Drives You?
Joined: Oct 2010
Posts: 9
Lurker
Lurker
Offline
Joined: Oct 2010
Posts: 9
The script worked perfectly. Many thanks!


Link Copied to Clipboard
Donate Today!
Donate via PayPal

Donate to UBBDev today to help aid in Operational, Server and Script Maintenance, and Development costs.

Please also see our parent organization VNC Web Services if you're in the need of a new UBB.threads Install or Upgrade, Site/Server Migrations, or Security and Coding Services.
Recommended Hosts
We have personally worked with and recommend the following Web Hosts:
Stable Host
bluehost
InterServer
Visit us on Facebook
Member Spotlight
Posts: 70
Joined: January 2007
Forum Statistics
Forums63
Topics37,573
Posts293,925
Members13,849
Most Online5,166
Sep 15th, 2019
Today's Statistics
Currently Online
Topics Created
Posts Made
Users Online
Birthdays
Top Posters
AllenAyres 21,079
JoshPet 10,369
LK 7,394
Lord Dexter 6,708
Gizmo 5,833
Greg Hard 4,625
Top Posters(30 Days)
Top Likes Received
isaac 82
Gizmo 20
Brett 7
Morgan 2
Top Likes Received (30 Days)
None yet
The UBB.Developers Network (UBB.Dev/Threads.Dev) is ©2000-2024 VNC Web Services

 
Powered by UBB.threads™ PHP Forum Software 8.0.0
(Preview build 20221218)