Last month I [intlink id=”2831″ type=”post”]wrote about an error[/intlink] on this site that caused me to start fresh: the Publish button was missing from WordPress and you couldn’t post anything. Here’s a screenshot of the error.
At the time I couldn’t locate a fix for the problem, but last week the same thing happened to my wife’s site so that caused me to have a closer look. Since both sites were hosted at 1&1 that narrowed things down. I have an old hosting package with them that limits SQL database size to 100 MB; looking at the old database for eBabble and my wife’s site they were both well over that, about 140 MB each. When I created the new database for eBabble I noticed the new size restriction is 1024 MB so things are much better. Still, I didn’t want to have to recreate my wife’s site again; there had to be a fix for this other than redoing everything.
I decided to export the existing database and import it into a new database and then move the site’s files over. I was only able to export the database uncompressed as the compressed options failed. Then the import failed because the uncompressed size was over the 50 MB 1&1 limit. Wonderful. I called 1&1 tech support and was told the import failed because my hosting package didn’t include SSH and I should upgrade my package. They suggested exporting the database in pieces and trying it that way to get under the size restriction, which didn’t make much sense because I would still not have SSH on my hosting package. 1&1 earns an epic fail all around on this one.
The suggestion of separating the database got me looking at the WordPress tables and I noticed one in particular was 79 MB: wp_commentmeta. Searching the internet came up with lots of people experiencing the same issue: hugely inflated databases because of Akismet not clearing out spam entries in this comments table. It also seemed to be prevalent with 1&1 users because of the old 100 MB limit; wish I had found that out earlier!
Anyway, this issue seems to be from older versions of Akismet that carries forward as the plug-in is upgraded. This thread pursues the issue and mentions “Starting with 2.5 the Akismet plugin does store history data in commentmeta”. I checked some newer sites and they didn’t have this bloat. Fixing it means clearing the entries from the wp_commentmeta table, and one user set up easy step by step instructions using PHPMyAdmin. Basically you go to the wp_commentmeta table and run the following:
DELETE FROM wp_commentmeta WHERE comment_id NOT IN (SELECT comment_id FROM wp_comments)
This reduced wp_commentmeta from 79 MB to 2.2 MB. The same drastic size change occurred with eBabble Art. Things are running smoothly now and my wife is happy, which is the best way to be for both of us.
