How I Almost Lost Everything

Well, not everything, but my entire blog.

I went to create a blog post yesterday and instead of a publish button, there was a “Submit for Review”. Not really thinking, I just clicked it anyway, the page refreshed, and the post was gone.
After doing some research it appeared that the database was full. I was using 150.6 MB out of an allotted 100 MB.
While modern databases now come in 1000MB chunks, this database was started back when the standard was 100 MB and they had graciously let me go over by 50% until cutting me off (without notice).
What else happened because of this?
Backups had stopped running. Since there was no way for the plugin to add more data to the database, a new backup could not be created.
One option was to clean up the database with a plugin, but I couldn’t add any new plugins because again, the database was so full, it couldn’t make an entry.
How did I fix it?
I created a new 1000 MB database, did an export, then attempted to import into the new database using WordPress’ import tool, but the file was too big (6 MB). The max size is 2 MB.
So instead I went into the web host and used PHPMyAdmin to manually export and attempt to import the data. This also didn’t work because it exceeded the “max packet size”.
Now I was starting to get a little panicked.
I went back to the website and deleted all spam messages (~3000), which freed up enough space to install a plugin called WP-Optimize. I ran that, which deleted all revisions, freeing up almost half of the database, but the export file was still just as big.
Another thing I noticed was that the theme I recently installed (Jupiter) had created several new tables and may have been what put me over the top in space so I removed it.
Finally I remembered that posts can be exported by category, author, or date range so I exported them individually by category, which kept each file under 2 MB.
I imported them into the new WordPress database 1 by 1, semi-automatically restoring the data to the new database. This worked.
All 619 posts and all 5 pages have been restored.
This is just another example of The White Album Problem. As databases outgrow their restraints they will need moved to larger databases over time and knowing how to manage this data transfer process is critical to preventing data loss.
In addition, this process highlighted the need to monitor database usage at least on an annual scale.
It would be nice if the web host would provide a function to expand out an existing database, but maybe this is an option they or some enterprising young entrepreneur will do in the future.

Leave a Reply

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