Posted by Steel Rat on October 16, 2007 at 7:44pm
When deleting spammers, if I haven't gotten rid of all their comments/postings, the nodes, mostly comments, become orphaned. They'll show up in something like the Admin block module, but not on the comments listing or moderation queue.
How can I clean up the database so things like Recent Comments block and Admin block don't think they're still there?
Comments
Re: deleting orphaned comments
I don't have orphaned comments on my test site, thus I haven't been able to test the stuff below thoroughly, thus test before you really use it.
The following query should produce all orphaned comments:
select * from comments where nid not in (select nid from node)If you have verified that these are the actual orphaned comments, you can delete them by:
delete from comments where nid not in (select nid from node)Note that this query uses a subquery which was not supported in older versions of mysql.
Hope this helps!
--------
F.J. Jungen
www.boria.nl
--------
Karna, a.k.a. F.J. Jungen
www.boria.nl
Thanks for the reply
Unfortunately that returned zero results.
In looking at the comments table, and finding one of the orphaned comments, they all seem to have a UID of 0, I'm assuming that means user Id. Since there is no zero UID, I should be able to safely delete them?
Steel Rat
My Drupal Sites:
RPGMapShare.com
Infinite Ordnance
Malvern Rouge Valley Youth Center
Steel Rat
Some of My Drupal Sites:
Helm's Deep RPG Network
RPGMapShare.com
Infinite Ordnance
The uid of 0 signifies
The uid of 0 signifies anonymous users, so deleting all comments by the user id, every comment by someone who wasn't logged in will be deleted. Probably not what you want.
What I've done before is to delete the comments from the spammer's hostname. You should still do a select on that column first to make sure you don't delete legitimate comments, but if you're using phpMyAdmin or something, you should be able to pick out the spam.
-----
Übercart -- One cart to rule them all.
Thanks, folks.
That's basically what I did. there was only one other comment with a zero UID and it was a legit one from a user who was purged for inactivity. The spam was amazingly consistent and easy to spot when browsing the table.
My biggest concern was failing to account for dependencies in other tables, but I guess there aren't any.
Steel Rat
My Drupal Sites:
RPGMapShare.com
Infinite Ordnance
Malvern Rouge Valley Youth Center
Steel Rat
Some of My Drupal Sites:
Helm's Deep RPG Network
RPGMapShare.com
Infinite Ordnance