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.

The Zero One or Infinity (ZOI) Rule

I learned something yesterday that I wanted to share really quick: there is no two in programming, but this can apply to troubleshooting and database design too.

What this means is that there are units that either aren’t allowed to exist, are allowed (or supposed) to exist only once, and those that are allowed to have no limit – but there is no other kind, no other in between those types.

For example, in Excel there can only be one name for a cell or range, but a near infinite amount of cells.  In Access there can only be one primary key, but a near infinite amount of entries.  In HTML there can only be one H1 tag, but many H2 tags.  In CSS, div id names must be only used once, but div classes can be used more than once (infinite).

It’s called The Zero One or Infinity (ZOI) Rule.