1

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.

Improve this question

2 Answers 2

Reset to default
0

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.

Improve this answer
3
  • 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?
    – hypind
    Commented Feb 15, 2017 at 1:53
  • 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.
    – hypind
    Commented Feb 15, 2017 at 2:04
  • 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.
    – Brad Bell ♦
    Commented Feb 15, 2017 at 2:15
0

Put this: https://github.com/aberkie/craft-recaptcha

(or something) on your forms going forward.

Improve this answer
0

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.