WordPress Update Shrunk SQL DB

I do a daily backup of my WordPress SQL database and was concerned when, after a recent auto-update to WordPress 4.7.5, the size of my SQL backup file shrank from 10MB to 7MB. That’s a lot of data that’s gone missing. What changed?

To see the changes:

1. Open both SQL backup files in Notepad++.

2. The lines in the backups are extremely long. Set Notepad++ to “Extended” replace mode, then replace “),(“ with “),\n(“ in both files. This will insert line breaks between fields.

WordPress SQL Shrank 1

3. Run Plugins > Compare > Compare to compare the files. Now you should be able to quickly identify what is missing from the new file.

In my case, I see that the missing items are mostly old comments that Akismet flagged as spam. For some reason the database upgrade must have deleted those. Fine by me. For example, my database no longer has this blocked ad for prescription drugs:

WordPress SQL Shrank 2

Update September 7, 2017

After some further research, I discovered that Akismet is apparently deleting old comments after 15 days in the spam folder:

WordPress SQL Shrank 3

Fine by me. That’s probably the reason that the database size is fluctuating, not the WordPress update.

Leave a Reply

Your email address will not be published. Required fields are marked *

Notify me of followup comments via e-mail. You can also subscribe without commenting.

This site uses Akismet to reduce spam. Learn how your comment data is processed.