Drupal key_value_expire Table is very large because of Honeypot Module

When investigating why I had such a large database on mark.ie (about 50MB - not huge, but a lot for a personal website) this evening, I noticed that the key_value_expire table in the database was about 40MB (after clearing cache). And that most of the entries in it (over 160,000) were for honeypot_time_restriction.

As it turns out, honeypot had been putting a time to expire of Jan 2038, so the table was just getting bigger and bigger. Then I did some Googling and found this: Honeypot key value entries never expire (effectively). That issue has been fixed for newer versions of honeypot, but all the old entries before that update are still in the DB.

There's another issue - Write update hook to clean up old honeypot_time_restriction values - to clean up all the old entries, but it's only at RTBC at the moment, and when it gets into the Honeypot module, it could take a long time for the update hook to run depending on how many entries you have in the table.

For my set up, I just ran DELETE FROM key_value_expire WHERE collection = "honeypot_time_restriction"; directly on the DB. It only took about 10 seconds to delete the 160,000+ rows, and reduced that table from about 40MB to 3MB.

It might be worth checking this on your site to try reduce your DB size.

Filed Under:

1. Drupal
2. Honeypot
3. SQL
4. Drupal Planet