find and delete orphans

[wot-Mike]

In short, some forums were deleted through the Admin Control Panel, but the stuff timed out. The result is that the forums are indeed gone, but the threads and posts they contained are still inside the database.

How can I get rid of those "forumless" orphans?
Yes, I've deleted a lot of them manually, but geez...

[JulianD]

Uhmmm. Try to run from your Admin CP:

Update Counters > Remove dupe threads.

If that doesn't work, you will have to run a query like this:

[sql]DELETE FROM thread WHERE forumid='XX'[/sql]

And replace XX with the forumid of the deleted forum.

[filburt1]

That won't get rid of posts though.

Check out Scott's script at http://www.vbulletin.com/forum/showt...light=orphaned .

[wot-Mike]

I think I've got all the posts. Cleaned up 11,000 of them via a SSH session.

And DELETE FROM thread WHERE forumid='XX' worked. Geez, I can't imagine why I didn't think of this

Anyway, it was the site map hack thingie that woke me up. Suddenly I saw a whole lot of stuff I was busy cleaning out ages ago, and umm, got fed up with.


Thanks guys

[sparky2]

The following is PARTIALLY
code excerpted from v2.2.9 admin/misc.php
and
PARTIALLY code I added (remove EMPTY thread routine)
based on a suggestion offered by "Sadler"
which has apparently been ignored
by the vBulletin developers
----------------------------------v

PHP Code:

// ###################### Start remove EMPTY threads #######################
        // (EMPTY threads  are the buggered thread that show header/footer table cells w/ just a slice of whitespace b/t them)
if ($action=="removeemptythreads") {
  if (isset($perpage)==0 or $perpage=="") {
    $perpage=50;
  }
  if (isset($startat)==0 or $startat=="") {
    $startat=0;
  }
  $finishat=$startat+$perpage;

  $threads = $DB_site->query("SELECT thread.threadid FROM thread AS thread LEFT JOIN post AS post USING(threadid) WHERE post.threadid IS NULL AND thread.open<>10 LIMIT $startat, $perpage");
  while ($thread = $DB_site->fetch_array($threads)) {
    $deleting++;
    deletethread($thread['threadid']);
    echo "<p>Deleting thread $thread[threadid]</p>\n";
    flush();
  }
  if($deleting) {
    cpredirect("misc.php?s=$session[sessionhash]&action=removeemptythreads&startat=$finishat&perpage=$perpage");
  } else {
    echo '<p>No -=EMPTY=- threads were found</p>';
    cpredirect("misc.php?s=$session[sessionhash]",1);
  }

}


// ###################### Start remove orphan threads #######################
if ($action=="removeorphanthreads") {
  if (isset($perpage)==0 or $perpage=="") {
    $perpage=50;
  }
  if (isset($startat)==0 or $startat=="") {
    $startat=0;
  }
  $finishat=$startat+$perpage;
  $threads = $DB_site->query("SELECT thread.threadid FROM thread AS thread LEFT JOIN forum AS forum USING(forumid) WHERE forum.forumid IS NULL LIMIT $startat, $perpage");
  while ($thread = $DB_site->fetch_array($threads)) {
    $deleting++;
    deletethread($thread['threadid']);
    echo "<p>Deleting thread $thread[threadid]</p>\n";
    flush();
  }
  if($deleting) {
    cpredirect("misc.php?s=$session[sessionhash]&action=removeorphanthreads&startat=$finishat&perpage=$perpage");
  } else {
    echo '<p>No orphan threads were found</p>';
    cpredirect("misc.php?s=$session[sessionhash]",1);
  }

}
// ###################### Start remove orphan posts #######################
if ($action=="removeorphanposts") {
  if (isset($perpage)==0 or $perpage=="") {
    $perpage=50;
  }
  if (isset($startat)==0 or $startat=="") {
    $startat=0;
  }
  $finishat=$startat+$perpage;

  $posts = $DB_site->query("SELECT post.threadid, post.postid FROM post AS post LEFT JOIN thread AS thread USING(threadid) WHERE thread.threadid IS NULL LIMIT $startat, $perpage");
  while ($post = $DB_site->fetch_array($posts)) {
    $deleting++;
    deletepost($post['postid']);
    echo "<p>Deleting post $post[postid]</p>\n";
    flush();
  }
  if($deleting) {
    cpredirect("misc.php?s=$session[sessionhash]&action=removeorphanposts&startat=$finishat&perpage=$perpage");
  } else {
    echo '<p>No orphan posts were found</p>';
    cpredirect("misc.php?s=$session[sessionhash]",1);
  } 


I'm still not convinced that the "remove orphaned posts" routine is quite right, though.
Heck of a lot faster to do it from the commandline
(DELETE instead of SELECT in the query and leave out the limit clause)
...and when I ran it from the v2.2.9 AdminCP (misc.php) several times consecutively, it "keep finding" (?) additional orphans with each pass. (Shouldn't be so ~~ it should have whacked all the orphans in one run.)



I'm open to additional thoughts on handling orphans.
A related problem is that a number of posts mysteriously
(continually) have post.visible='0' ~~ mysterious because users have no way of choosing this & it seems to happen at random.

[Scott MacVicar]

If you use Delete instead of the select it will not get the ids to remove the appropriate information.

It wont un-index the words from the search engine, or remove attachments. Leaving some more unclaimed stuff in your database.

I've not had a problem with the remove orpahn posts / threads on any of my test boards though this could be caused by hacks or any number of things.

If your still have problems try running the select query in phpmyadmin and see what it returns.