By tsk1979 on

I did not install the spam module(my foolishness!) and therefore have lots and lots of spam comments. Is there a way to delete all the comments on my drupal installation. 99.9% of the comments are spam, so individually deleting is not an option.

⋅ Categories: Drupal 4.6.x

Comments

sillygwailo’s picture

sillygwailo
Location Toronto, ON
commented

The spam module, at least the one available at kerneltrap.org, should let you scan for spam and do mass deletion.

(Username formerly my full name, Richard Eriksson.)

tsk1979’s picture

tsk1979 commented

This module will let me do that for any new spam which comes in, but not for old spam which is already there. I am looking for a solution which lets me delete comments in bulk. one by one is too too slow

sillygwailo’s picture

sillygwailo
Location Toronto, ON
commented

Actually it will. If you create some custom filters under administer » spam (the module comes with some good filters, but you'll no doubt want to create some more or add to the existing ones) you can scan for spam and delete old comments in bulk.

(Username formerly my full name, Richard Eriksson.)

dmitrig01’s picture

dmitrig01 commented

To delete all comments, execute this SQL query.

DROP TABLE comments;
CREATE TABLE comments (
  cid int(10) NOT NULL auto_increment,
  pid int(10) NOT NULL default '0',
  nid int(10) NOT NULL default '0',
  uid int(10) NOT NULL default '0',
  subject varchar(64) NOT NULL default '',
  comment longtext NOT NULL,
  hostname varchar(128) NOT NULL default '',
  timestamp int(11) NOT NULL default '0',
  score mediumint(9) NOT NULL default '0',
  status tinyint(3) unsigned NOT NULL default '0',
  format int(4) NOT NULL default '0',
  thread varchar(255) NOT NULL,
  users longtext,
  name varchar(60) default NULL,
  mail varchar(64) default NULL,
  homepage varchar(255) default NULL,
  PRIMARY KEY (cid),
  KEY lid (nid)
) TYPE=MyISAM;

::::::::::::::::::::::::::
My opinion of drupal:
The best of the best
What I have to say about drupal:

Drupal, my hero

::::::::::::::::::::::::::

sillygwailo’s picture

sillygwailo
Location Toronto, ON
commented

Note that this will delete legitimate comments as well. I don't recommend this option, but if you do go with it, back up your database first.

(Username formerly my full name, Richard Eriksson.)

Aleet’s picture

Aleet commented

I was victim of spam and there are many hundreds of comments in my approval queue. They are ALL spam. Can you tell me what would be the code for deleting all comments in approval queue?

ericatkins’s picture

ericatkins commented

I just had to delete all comments in my approval queue. The code:

delete from comments where status = '1'

My results: Deleted rows: 56779 (Query took 4.2604 sec)

Comments database table went from being 85MB to 320KB in filesize after the deletion and optimization.

Yeah. Things got a little out of hand. Good luck with your spam.

lazycat’s picture

lazycat commented

Where do I run this code?

ericatkins’s picture

ericatkins commented

I ran this code from myPHPadmin. I could have also run it from terminal.

Unsure how to write the PHP code needed to run this from within Drupal.

---
Eric Atkins | Hushed Casket | Sojourn Huntsville

mediarevolutionary’s picture

mediarevolutionary commented

scary, but worked, and got me to get a mind bout poking around MySql. Thnx ';'

Aleet’s picture

Aleet commented

This worked for me great. I had to delete 60,000 PUBLISHED spam comments which were all the comments on a site.

delete from comments where status = '0'

michaelfavia’s picture

michaelfavia commented

For the future, a very simple way to empty a table without recreating it is:
TRUNCATE table comments;

nicl’s picture

nicl commented

For non-mysql people like myself, phpMyAdmin has the option to 'empty' a table in your database, so simply empty the comments table.

This is equivalent to running the TRUNCATE command directly.

Anonymous’s picture

Anonymous (not verified) commented

I just had to delete all
Posted by ericatkins on September 6, 2006 at 3:43am
delete from comments where status = '1'

I could delete all my spam with above help. Thanks.

polgard’s picture

polgard commented

I deleted hundreds of spam comments with SQL in phpmyadmin as above, but then noticed all nodes that had been afflicted with comment spam still showed the number of comments even though they were no longer there. I fixed this with the following SQL

UPDATE `node_comment_statistics` SET `comment_count` =0

I don't know if there are any other issues left hanging, but after a quick look, my site still seems intact!

mErilainen’s picture

mErilainen commented

There is a modu... function for proper deletion of comments. comment_delete() and comment_delete_multiple() (for Drupal7). If these functions are fed with proper cid's, then there won't be any trash left begind in the database.

Take a look at http://drupal.stackexchange.com/questions/796/deleting-comments-in-bulk

My problem is that there are 1,5million spam comments at the database I wish to delete, but still save some of them. And that script deletes "only" about 120 000 comments per hour on my MacbookPro...

guruling’s picture

guruling commented

run below sql qury in phpMyAdmin > sql

DELETE FROM users WHERE `status` = 0 AND `uid` != 0;

anandkp’s picture

anandkp commented

this sql query works for me
guruling commented February 15, 2015 at 5:06am

run below sql qury in phpMyAdmin > sql

DELETE FROM users WHERE `status` = 0 AND `uid` != 0;

For non-developer users, please beware, the SQL code above (to my not-so-very-SQL-trained eye) is supposed to delete all users, including User 1 (the master admin account). Note the "FROM users" part of the statement? That's the "Users" table.

Could someone from the greater community more sure about SQL than I please confirm? If so, the site moderator needs to delete the comment. I'm not sure if it was a typo or not but really, if it is a typo and @guruling meant to say, "FROM comments" then yikes, what a weird and awful (and specific) typo!

Word to the wary, whenever you're about to perform any action directly upon your database, please be sure to back up the DB (I usually make multiple copies and keep them in multiple places - both on my laptop and on a cloud service like Google Drive).

NEVER execute SQL code on your DB unless you're absolutely sure you know what it will do.

mmjvb’s picture

mmjvb commented

DELETE FROM users WHERE `status` = 0 AND `uid` != 0;

It doesn't remove user 1 because that shouldn't have status:0 !
Maybe `uid` > 1 instead of != 0 puts your mind at ease.
Also this only applies to D7, in D8 the user is stored in multiple tables.

Indeed it is not removing comments, but removing those users might be what you need as well.

There is no reason to be afraid of working on the database using phpMyAdmin. Export the data when you are afraid you are going to loose data. You can import the exported data when something didn't work out as expected.

There is no referential integrity implemented in the database. That means you need to use Drupal to clean your database. Assuming it takes care of it for you.

anandkp’s picture

anandkp commented

Thank you for the clarity @mmjvb, your explanation is much appreciated!

I'm not too sure that the focus of this thread requires the deletion of Users as it was started in order to understand how to remove Comments specifically - this is what gave rise to an alarm in my head. That said, again, thank you for putting my worries to rest.

It is sorta odd that @guruling suggested the deletion of any user account (active or otherwise) in the context of this thread.

Regarding the integrity of the DB, as you said, I know that I'll be relying on Drupal (D6 in my case) to delete comments - there are thousands of them but I agree with the consensus in this thread - it's safer to let Drupal do the deletion for you.

To future visitors, I'd like to add that I ran a full text search on all "*.info" files followed by all "*.module" and "*.php" files to discover whether any modules in my D6 site made mention of "comments" and/or "comment" to be on the safe side and to check for modules declaring Comments as a dependency (which might indicate additional database linkages). To my relief, the text searches revealed no such references (it's an inherited site). Doing a search like this may help point us developers in the right direction.

One other suggestion (not sure if this will work for D6) that I came across in another "delete comments" thread here was to install the VBO module, build a view that allows you to load all comments and then to use VBO's functionality to delete everything.

I'm investigating (briefly) the possibility of using an anti-spam module to scrub all Comments to remove spam and thus salvage as many bonafide Comments as possible. My client has said that it's okay to just delete them all but it would be useful to see if this sort of clean up can be done after the fact (after Comments are enabled and before an anti-spam module is activated) on a live site.

Fingers crossed but not much optimism for that...

Hope all this info helps in the future! :-)