In case anyone was wondering, no: I do not want to try hemp products, your CBD oil, your business partnership, or offers for sexy meetings, just click here.
I would like to keep this site open for any real person wanting to post a comment! However, the thousands of - scripted, I am sure - weird comments I keep receiving are a bit of a pain.
The Drupal 10 admin interface has a means of deleting comments by multi selecting comments and choosing the "Delete comment' action for the selected comments. However, this is limited to the maximum number of comments shown per page - which is about 50. Last time I had 6,083 comments I needed to delete - which would be 122 pages.
Most of the comments were clearly script-created, many of them attempts at SQL-injection (see below) and some attempts to get a comment on the site that promotes a link to their own site.
Surely there is a better way of bulk-deleting all these comments, I thought, and consulted the Duck. I found recommended a VBO - bulk actions, but that advice seemed out of date. There was also mention of PHP coding - but that is outside of my comfort zone.
One user recommended a SQL approach, with the following statement:
I logged into the Drupal MariaDB instance, replaced the 'delete' with a 'select' to test this approach - and found out that these tables do not exist. I assume these instructions applied to a previous Drupal version - possibly Drupal 7.
But I am not one to give up easily. "SHOW TABLES" gave me a number of tables with the word 'comment' in their name, which seemed promising.
After examining the contents of the tables, I couldn't figure out what 'drupal_comment_entity_statistics' was used for - it had few rows. The other tables had the 6000+ rows I was expecting.
I crossed my fingers and executed the following statement:
DELETE drupal_comment, drupal_comment__comment_body, drupal_comment_field_data
FROM drupal_comment
INNER JOIN drupal_comment__comment_body ON drupal_comment.cid = drupal_comment__comment_body.entity_id
INNER JOIN drupal_comment_field_data on drupal_comment.cid = drupal_comment_field_data.cid
WHERE
drupal_comment_field_data.status = 0;
to delete from the 3 tables that seemed to be right ones, with "status = 0" restricting the delete to unapproved only.
I refreshed my page in the Drupal admin interface - and the 6083 comments were still there! To eliminate a possible caching issue, I selected two unapproved comments, and applied a 'Delete' action. Et Voila, all 6083 unapproved comments disappeared, and only the approved comments remained.
NOTE: I did make a full server backup before making any changes - you should always have a rollback plan!! This is a 'try at your own risk' post - I am by no means a Drupal admin or expert - but it seems to have worked.
Add new comment