Answers.Body: <p>Assuming the SPAM posts came through in a contiguous block, one semi-easy way would be to restore the 300k entry database locally on a Craft install.</p>
  
  <p>Then you could do a SQL query like:</p>
  
  <pre><code>DELETE FROM `craft_elements` WHERE `id` IN (
      SELECT `id` FROM `craft_entries` WHERE`dateCreated` BETWEEN '2017-02-01 00:00:00' AND '2017-02-02 00:00:00'
  )
  </code></pre>
  
  <p>Adjusting the <code>dateCreated</code> values for the beginning and ending of the SPAM posts.</p>
  
  <p>The cascading deletes on the foreign keys that Craft use will kick in and clean up all of the content in the related tables like <code>craft_content</code> and <code>craft_entryrevisions</code> (as well as others).</p>
  
  <p>Then you could restore that cleaned-up database back to production.</p>
  
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-02-15T01:53:53.950Z
Answers.Text: Thanks, that'd be solid except I nuked all the entries from craft_entries already and the client doesn't have backups enabled. I've been poking around the table structures a little, and I see there's a reference to 'sectionId' and 'entryId' in craft_entryversions.
  
  So I could clean up that table by removing any entry where the sectionId matches the channel that got spammed right? If that's right, and before I do that, can I use a query to grab the entryIds from craft_entryversions that match the sectionId I need and use those to delete the entries from craft_content?
Answers.UserDisplayName: hypind
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-02-15T02:04:36.847Z
Answers.Text: Also, I realize it was dumb as hell to nuke anything from the DB like that without dumping it first haha. Never again, dodged a bullet this time and didn't screw anything up irrevocably.
Answers.UserDisplayName: hypind
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-02-15T02:15:31.397Z
Answers.Text: Ahh... yeah, so you'll want to run a left join SQL query on craft_elements and craft_entries to grab any rows that exist in the former, but not the latter. Keep track of those IDs and delete them from elements, content, entryversions, etc.
Answers.UserDisplayName: Brad Bell
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-02-15T00:51:34.597Z
Answers.LastActivityDate: 2017-02-15T00:51:34.597Z
Answers.UserDisplayName: Brad Bell
Answers.Body: <p>Put this: <a href="https://github.com/aberkie/craft-recaptcha" rel="nofollow noreferrer">https://github.com/aberkie/craft-recaptcha</a></p>
  
  <p>(or something) on your forms going forward.</p>
  
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-02-15T02:47:21.880Z
Answers.LastActivityDate: 2017-02-15T02:47:21.880Z
Answers.UserDisplayName: andrew.welch
Body: <p>Long story short, a client had 300,000+ entries come in through a form, which caused the database size to swell, exceeding the storage capacity of the VPS and causing the site to crash. </p>
  
  <p>I was able to manually delete all the entries from that channel from the craft_entries table, which got them back within storage limits and got the site back online. Rebuilding the search index got that table back down to a normal size. However, both the craft_content and craft_entryversions tables are still measured in GB.</p>
  
  <p>I don't know much about the database architecture in Craft, and raw MySQL is not my strong suit. Is there anyway to cleanup those tables based on a channel? As in, I need to remove any stored content that's related to a specific channel and leave the rest of it in tact.</p>
  
  <p>Any help would be greatly appreciated. Thanks.</p>
  
Tags: <database>
Title: Looking for tips on cleaning up craft_content and craft_entryversions tables after spam attack
ThreadText: # Looking for tips on cleaning up craft_content and craft_entryversions tables after spam attack
  
  (Asked by: username_0 on 2017-02-15)
  
  Long story short, a client had 300,000+ entries come in through a form, which caused the database size to swell, exceeding the storage capacity of the VPS and causing the site to crash.
  
  I was able to manually delete all the entries from that channel from the craft\_entries table, which got them back within storage limits and got the site back online. Rebuilding the search index got that table back down to a normal size. However, both the craft\_content and craft\_entryversions tables are still measured in GB.
  
  I don't know much about the database architecture in Craft, and raw MySQL is not my strong suit. Is there anyway to cleanup those tables based on a channel? As in, I need to remove any stored content that's related to a specific channel and leave the rest of it in tact.
  
  Any help would be greatly appreciated. Thanks.
  
  **Tags:** database
  
  
  ---
  
  ## 2 answers
  
  ### Answer by username_1 on 2017-02-15. Score: 0 | Accepted answer
  
  Assuming the SPAM posts came through in a contiguous block, one semi-easy way would be to restore the 300k entry database locally on a Craft install.
  
  Then you could do a SQL query like:
  
  ```
  DELETE FROM `craft_elements` WHERE `id` IN (
      SELECT `id` FROM `craft_entries` WHERE`dateCreated` BETWEEN '2017-02-01 00:00:00' AND '2017-02-02 00:00:00'
  )
  
  ```
  
  Adjusting the `dateCreated` values for the beginning and ending of the SPAM posts.
  
  The cascading deletes on the foreign keys that Craft use will kick in and clean up all of the content in the related tables like `craft_content` and `craft_entryrevisions` (as well as others).
  
  Then you could restore that cleaned-up database back to production.
  
  > **Comments:**
  > **username_0**: Thanks, that'd be solid except I nuked all the entries from craft_entries already and the client doesn't have backups enabled. I've been poking around the table structures a little, and I see there's a reference to 'sectionId' and 'entryId' in craft_entryversions.  So I could clean up that table by removing any entry where the sectionId matches the channel that got spammed right? If that's right, and before I do that, can I use a query to grab the entryIds from craft_entryversions that match the sectionId I need and use those to delete the entries from craft_content?  
  > **username_0**: Also, I realize it was dumb as hell to nuke anything from the DB like that without dumping it first haha. Never again, dodged a bullet this time and didn't screw anything up irrevocably.  
  > **username_1**: Ahh... yeah, so you'll want to run a left join SQL query on craft_elements and craft_entries to grab any rows that exist in the former, but not the latter. Keep track of those IDs and delete them from elements, content, entryversions, etc.  
  
  ---
  
  ### Answer by username_2 on 2017-02-15. Score: 0
  
  Put this: <https://github.com/aberkie/craft-recaptcha>
  
  (or something) on your forms going forward.
  
  
  ---
  
  