4

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?

3 Answers 3

3

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.

3
  • The node won't be reindexed. Fields on the comment (if added) won't be garbage collected. Node statistics won't be updated. hook_comment_delete() won't fire.
    – mpdonadio
    May 3, 2016 at 13:02
  • @MPD Thanks for pointing that. Please look at my edited answer.
    – cFreed
    May 3, 2016 at 14:30
  • @cFreed - You saved me. God will reserve a space for your in heaven, haha.
    – caustic
    Aug 18, 2016 at 15:49
1

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.

1
  • Increased max execution time and memory limit to insanely high numbers, and also increased my.cnf max_allowed_packet to the limit of 1GB, and all that still wasn't enough for my 8GB server to handle deleting 300,000 comments, annoyingly enough, lol
    – caustic
    Aug 18, 2016 at 15:52
1

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)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.