how can I delete all comments at once? I have seen how to do it with nodes and bulkdelete module, How to delete all nodes of a given content type? but am looking at a similar solution for comments.
I am looking for a solution that does not leave any artifacts in the database. Is Views Bulk Operations the best solution?
AFAIK, VBO should be able to do the job, but I haven't tried it myself to delete comments.
Another way is to run some code (in a module or in Devel module's 'execute php' block) that gets a list of all queries from the db, builds an array of the comment IDs and passes that array to the comment_delete_multiple function. Note that this can take a while to complete (depending on server performance and the number of comments), so you may have work your way around that, for instance by using set_time_limit (http://php.net/manual/en/function.set-time-limit.php) or Drupal's batch API.
[update: see Chris Cohen's answer for example code based on this approach.]
This is not an alternative answer, more an elaboration on marcvangend's, but I couldn't comment on his answer and leave example code. So the manual approach, using devel's execute code block, would look a bit like:
$cids = db_select('comment', 'c')
->fields('c', array('cid'))
->execute()
->fetchCol();
comment_delete_multiple($cids);
As marcvangend points out, this is going to be limited to the execution time on your server, so you will need to temporarily raise it if you have a lot of comments. Thanks to manarth and instanceofjamie for the dbtng assistance.
comment, you need an alias and the raw object results can not be fed directly into comment_delte_multiple
Personally, I'd go for the Views Bulk Operations module.
This module augments Views by allowing bulk operations to be executed on the displayed rows. It does so by showing a checkbox in front of each node, and adding a select box containing operations that can be applied. Drupal Core or Rules actions can be used.
Honestly when I needed to do this I just did it in the database. You delete the comments, fix the comment statistics and poof, all the comments are gone. I'd avoid doing this if you have modules interacting with the comments in some odd fashion, otherwise its what I'd suggest.
TRUNCATE TABLE comments
UPDATE node_comment_statistics SET comment_count = 0
comment_delete_multiple is that it can call additional drupal hooks for you, leaving your DB potentially cleaner.
Some UI Tools You may install the modules below
Views - drupal.org/project/views
Every Drupal site needs, I believe... It generates SQL at the backend and displays results with configurable settings, filters, sorting, paging...etc
VBO - http://drupal.org/project/views_bulk_operations To allow bulk operations (i.e. delete comments for this thread)
Administration Views - http://drupal.org/project/admin_views Take advantages of Views and VBO, replace original contnet, comment, user admin pages by menu_alter...
Achieved deleting about 45,000 comments by using the Backup and Migrate module. In the advanced set up in 'Exclude the data from the following tables' - first press Ctrl key then select comment in the list - (be careful because here the unnecessary cache tables are already selected) - backup - restore from this backed up file. Hurrah!
This answer is similar to an already listed answer but I modified it to prevent an 'out of memory error' for about 27,000 comments. This will take a while to execute depending on the number of comments. Simply truncating the comments table may not be a good idea; it's best to let Drupal handle the deleting of content.
I created a PHP script:
$cids = db_select('comment', 'c')
->fields('c', array('cid'))
->execute()
->fetchCol();
foreach($cids as $cid)
{
comment_delete($cid);
}
...then ran the script with Drush
drush @my_alias php-script my_script.php
I would usually recommend to use VBO to delete comments or nodes in bulk but if you are in a situation when you have hundreds of thousands of comments and you don't have too much time, here is a sql query which will delete all unapproved comments along with all the revisions and data related to those comments which in my case was taking 1.2Gb of space in DB
DELETE c, rcb, dcb
FROM
comment AS c
JOIN field_revision_comment_body AS rcb ON (c.cid = rcb.entity_id)
JOIN field_data_comment_body AS dcb ON (rcb.entity_id = dcb.entity_id)
WHERE
c. STATUS = 0
For anyone who interested in Drupal 8/9 solution you can use built-in drush entity:delete command.
# delete comments only for specific comment type
drush entity:delete comment --bundle=COMMENT_TYPE
# delete all comments
drush entity:delete comment
Enable the PHP filter, and create a basic page with the following code:
<?php
db_query("TRUNCATE TABLE {comment}");
db_query("UPDATE {node_comment_statistics} SET comment_count = 0");
?>
