I have a node which has been spammed and has 300,000 comments. Whenever I try to delete it I get a 500 internal server error.
Deleting all comments via database is not an option because I need to retain a large number of legitimate comments on OTHER nodes. However, this specific node has been spammed and I wish to delete the node and all associated comments, But I can't. I have disabled commenting on the node, but how can I remove these comments? Any ideas?
I think you could delete the comments via database, limiting the deletion to those attached to the involved node.
It might be a simple as this:
DELETE FROM comment
WHERE nid = <the involved node id>
To ensure avoiding any issue (or in case there is something subtle I missed) you can first execute the equivalent SELECT and control its results:
SELECT * FROM comment
WHERE nid = <the involved node id>
And obviously you shouldn't execute the DELETE query without previously getting a backup.
EDIT: as a non-expert, I only proposed what seemed obvious when examining the comment table.
But thanks to @MPD comment, I realize that there are some linked data to also consider.
Regarding fields, after looking at the tables definition I think that an additional DELETE query should be executed first (before deleting comments themselves), something like:
DELETE FROM field_data_comment_body
WHERE entity_type = "comment"
AND entity_id IN (
SELECT cid
FROM comment
WHERE nid = <the involved node id>
)
If unfortunately supplemental comments had been added, a similar query should be executed also for each of them.
Regarding "The node won't be reindexed." and "Node statistics won't be updated.", I'm not expert enough to understand what is involved.
So I hope @MPD will be able to bring its own complementary information.
Likely you are running out of time or memory in your PHP setup. If you can temporarily allow PHP to have a great deal more of both you might be able to solve the problem just by changing values in php.ini and then trying again to delete the node.
To tell which is your limiting factor review the web server error log if you have access.
If you are comfortable writing a script, you could also write a simple drush script that would delete the one node using node_delete(). PHP Cli is not time limited so if that's the limit you're fighting you will not run into a time out.
If you have drush access to your server, you can use the drush php-script command (or drush scr for short) to run a script on top of a full Drupal bootstrap. This allows you to run any Drupal functions or access information you'd customarily have available in a Drupal module on your server, without the overhead of writing an entire custom module for a one-time operation.
Using comment_delete_multiple() to delete comments will ensure that additional field data is removed and statistics get updated during the deletion process. This script should get the job done. Back up your database before running!
<?php
// Change this to the nid you want to clear comments from
$nid = 12345;
// Queries the database for all comment IDs associated with the nid, max 1000
$cids = db_select('comment', 'c')
->fields('c', array('cid'))
->condition('c.nid', $nid)
->range(0, 1000)
->execute()
->fetchCol();
if (!count($cids)) {
drush_print("No more comments found. Aborting.");
// Abort with an error code (so a bash script can key off that)
exit(1);
}
// Delete the comments with comment IDs we got from the DB
comment_delete_multiple($cids);
drush_print(count($cids) . " comments deleted");
Upload this script to the web server and you can execute it from your local machine (with a properly configured drush alias), or the server itself, using drush @your-server-alias scr. Since you'll need to run it multiple times (it only deletes 1000 comments at a time to avoid timeouts or out-of-memory errors), you'll probably want to use a cron job, or a bash script with a while loop, to keep running it until all the comments are deleted. Here's a bash script that should do it:
#!/bin/bash
while drush @your-server-alias scr /path/to/script/on/server/so.php; do
sleep 1
done
The bash script can be run on the server itself, or your local machine if you have drush configured to talk to your server from your local. If you get 500 errors or other failures while the script is running, you may need to decrease the second value on the ->range(0, 1000) line. (You will see one error at the end when there are no comments left, by the design of the PHP script, so don't worry about that)
