Sleepless in MySQL

It’s not often that you wake up in the mid­dle of the night with the solu­tion to a prob­lem. In this case, the prob­lem was this blog. If you’ve read any­thing between yes­ter­day and today, you’d have either seen noth­ing, the out-0f-the-box Word­Press ‘Hel­lo World’ post­ing, or some­thing from me about Blog Amne­sia.

What fol­lows is some­what geeky, so if you don’t care about data­bas­es, domain names or any of that, feel free to skip to the end:

The sit­u­a­tion was that in the mid­dle of the move from one ISP to anoth­er, the export file I had from the pre­vi­ous copy of the blog refused to be import­ed by the new copy. I had down­loaded a back­up of all of the files, graph­ics, and oth­er data, but all of the posts, com­ments, cat­e­gories, blogroll and any oth­er text were still locked up in the MySQL data­base on the old sys­tem. What’s worse, in order to install the new copy, I had edit­ed the DNS records to point to the new ISP, so I had lit­er­al­ly lost my con­nec­tion to the old site.

Then, at 3:51 AM, I awoke with a start. Maybe I could con­nect to the old site, through its IP address. But where would that be? Of course! The FTP pro­gram I had used to upload files when I first set it up at that address! For­tu­nate­ly I had nev­er both­ered to replace the IP with the domain name after the site went live.

So, I crept over to my office, (not want­i­ng to wake Pam) fired up the brows­er and copied the IP address in: It worked! I con­nect­ed to the web-based Con­trol Pan­el for the site on the old ISP, and launched PHP­MyAd­min. Through that web inter­face, I man­aged to export the data­base of post­ings, com­ments, and oth­er data to a MySQL export file, which I down­loaded to my com­put­er.

Then I went to import that file into the new, emp­ty blog: No dice. I kept get­ting errors. ‘OK, at least I have the data’, I told myself, and went back to bed.

In the morn­ing, as I exam­ined the errors, it became clear that the SQL export file that I was cre­at­ing need­ed to over­write the exist­ing data, even if it had some dupli­cate entries and need­ed to cre­ate some new tables. Why was I cre­at­ing new tables? To make mat­ters worse, I dis­cov­ered that I was mov­ing data from a ver­sion 2.25 Word­Press data­base, to v 2.3 and data tables had dif­fer­ent names, dif­fer­ent rela­tion­ships, etc. — Oh great…

After about 3 or 4 tries, I suc­ceed­ed in import­ing all the data.

Then I looked at my new blog. I was very relieved when I saw the first page, look­ing pret­ty much as it should, but then I saw that none of the links worked, and that there were no Cat­e­gories. So, I brought up the new blog’s Dash­board. ‘You need to upgrade your Word­Press Data­base’, it said. I clicked on Con­tin­ue. After a minor glitch report­ed by the upgrade script, I saw the stan­dard blog Dash­board. I gulped, and clicked on the ‘View Site’ link…

This time, every­thing from the past 2 1/2 years was there, and as far as I can tell, it all seems to be work­ing. My dreams of IP address­es, PHP­MyAd­min and MySQL end­ed up get­ting back my 265 post­ings and 419 com­ments. I guess that it real­ly is true that you con­tin­ue to work on prob­lems in your sleep. This is first time, how­ev­er, that I ever awoke with a ‘Eure­ka’.