I got thousends of spam comments on my drupal 7 site. The mysql database file is grown to 1.9GB and the site is broken. How can i delete all comments (or even better: all unapproved comments) from the database dump file?
1 Answer
From your dump file, I would cut the part that insert the data to the comment table. Note: Leave the part that create the table.
In another file paste the part that you cut from the original dump file, using a text editor that can handle to search using regular expressions (as Sublime Text 2). Create the regular expression that would give you as a result the complete row insert if that row has the status column value set to one. Once that the regular expression is working, now use the search an replace. Search all the matches and replace them with ''
When the search and replace is done, you can copy and paste to the sql tab if you are using phpMyAdmin, or even save the resulting file as .sql and import it.
Pretty much the laborious part would be to find the regular expression to use.
Hope this help!
commenttable, you can use thestatuscolumn to determine whether they're approved or not). Drupal can't help you with actually making the change, since it has no tools for manipulating mysql dumps. Neither does MySQL, to manipulate the data with SQL it needs to be in a database. There may be tools out there to help (I don't know of any), or the other option is a complex regular expression. To the best of my knowledge there's nothing Drupal can do to help you with this