I enjoyed reading the book What Happened by former Bush press secretary Scott McClellan, but I did not particularly enjoy what happened over the last 8 hours when there was a question of whether Obama Conspiracy Theories would continue to exist outside of the Internet WayBack machine.
I’ll begin the story in January of 2012, when I moved the blog to a new host and in the process messed up the database encoding, leaving non-displayable special characters scattered through the whole database. I’ve been finding and fixing them ever since, and that was what I was doing today when I trashed the database. Instead of replacing the content of the article on Orly Taitz using an SQL update statement1, I replaced every post and page on the blog with the article on Orly Taitz. The home page was 10 copies of Orly Taitz. The Visitor’s Guide was Orly Taitz. The Debunker’s Guide to Obama Conspiracy Theories was Orly Taitz.2
I shut down the blog.
First, I thought “backup.” My web host had nothing in their documentation about backups being available that you don’t do yourself. I knew my own backups were not current, but I went to the folder where I keep them and got the message “A hardware error prevents the opening of this folder.” I read up on MySQL on the Internet, and there was no rollback available since I hadn’t used transaction control. That was the low point: No blog, no backup.
In my career, I fought my way out of many an IT disaster, and panic was not an option. So I opened a support ticket with VPS.NET and asked if there were any database backups. There was one from January 25 and they gave me a copy of the raw MySQL database (after three tries and having them reset the permissions I was able to download it). I thought that I could extract some of the missing content from Before It’s News, where this blog is syndicated, although they don’t have updates, and I update my recent articles a lot.
So the first problem was restoring the content of the articles and pages from the raw database files prior to January 26. I couldn’t just reload the tables and add the missing articles because that would detach all the comments. Comments are important. So I downloaded the raw files to my desktop computer and started looking at how to get at them. Step 1 was to install MySQL 5.1, which was pretty easy. There is a neat Windows utility called procmon from the Sysinternals Suite that monitors processes and tells you what files they are looking for. I cranked that up and watched what MySQL was doing so I could find where it hid its databases (c:\programdata…). Then I copied the raw database into that directory structure and voila, a database I could get to. I use the HeidiSQL client to access this blog on the Internet and it worked easily on the desktop version (that’s also what I used to trash the database). I also used HeidiSQL all throughout the debugging process to make sure the live database was being updated correctly.
So next, I needed to write some software that would take the desktop database and update the post content column of all the posts and pages. To do that, I needed to get Delphi XE4 working with MySQL and that didn’t fly right out of the box. I did a lot of reading on the Internet and thought I had figured out the problem. I actually ended up running the Windows cmd shell utility to enter a path command. It still didn’t work, even though procmon showed that the driver files were being found. It turns out that I had installed 64-bit MySQL, but was trying to access it from 32-bit code. That was easy to fix, switch the code to 64-bit. That worked a bit, but the Delphi XE4 development environment is 32-bit and it couldn’t connect to the database. So I had to uninstall MySQL and start over with the 32-bit version. Only the uninstall left remnants, and before it was over, I was deleting Windows registry entries and manually disabling services. FINALLY, the code and the IDE could connect to the local database. I had to learn a new component set to access MySQL, but that was straightforward, and I had to read up to remind myself of the syntax for parameterized queries. Pretty soon I had a program running, updating the live database from the backup, all 13,105 pages, articles, and revisions. It did all that over the Internet in around 5 minutes.
So then I thought about looking into the possibility that Windows Live Write! that I use to write most of the articles might have some backup versions of the files taken before uploading. It did (procmon found them), but when I tried to open one of them, it just downloaded the latest version from the web site (whose publishing API was still live). I looked all over the Internet to see if I could find a way to open those binary files and get at the content. No luck, but then I had an idea. I unplugged the LAN cable and tried again. After a few error messages, it opened the disk version. I plugged the cable back in and created a Windows Firewall rule to muzzle Live Write and wrote some more code to take HTML from the clipboard and stuff it in the database.
That worked great except for articles with pictures. Live Write! doesn’t use real links to pictures, but symbolic ones. Articles with photos I got from Before It’s News, and updated them with text from Live Write! Finally it was all done, and the blog is pretty much back to normal, except for a handful of changes I made over the last few days to very old articles, replacing dead hyperlinks.
Now I need to go fix those funky characters3 in the article on Orly Taitz 😯
1What I did was paste some text (the entire HTML for the article) and accidentally erased the where clause in the SQL statement that was supposed to limit the update to one row. One glance at the SQL told me what it had done.
2I am reminded of two things as I write of everything on the blog being Orly Taitz. There was a Russian comedian named Yakov Smirnoff who tried to describe the Soviet Union. He said that it was like being in Milwaukee, only everywhere you went for a thousand miles, you were still in Milwaukee.
The other is the dialog from the movie Demolition Man [video clip]:
Lenina: You do not realize that Taco Bell was the only restaurant to survive the franchise wars.”
Lenina: “So now all restaurants are Taco Bells.”