Sleepless in MySQL

It’s not often that you wake up in the middle of the night with the solution to a problem. In this case, the problem was this blog. If you’ve read anything between yesterday and today, you’d have either seen nothing, the out-0f-the-box WordPress ‘Hello World’ posting, or something from me about Blog Amnesia.

What follows is somewhat geeky, so if you don’t care about databases, domain names or any of that, feel free to skip to the end:

The situation was that in the middle of the move from one ISP to another, the export file I had from the previous copy of the blog refused to be imported by the new copy. I had downloaded a backup of all of the files, graphics, and other data, but all of the posts, comments, categories, blogroll and any other text were still locked up in the MySQL database on the old system. What’s worse, in order to install the new copy, I had edited the DNS records to point to the new ISP, so I had literally lost my connection to the old site.

Then, at 3:51 AM, I awoke with a start. Maybe I could connect to the old site, through its IP address. But where would that be? Of course! The FTP program I had used to upload files when I first set it up at that address! Fortunately I had never bothered to replace the IP with the domain name after the site went live.

So, I crept over to my office, (not wanting to wake Pam) fired up the browser and copied the IP address in: It worked! I connected to the web-based Control Panel for the site on the old ISP, and launched PHPMyAdmin. Through that web interface, I managed to export the database of postings, comments, and other data to a MySQL export file, which I downloaded to my computer.

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

In the morning, as I examined the errors, it became clear that the SQL export file that I was creating needed to overwrite the existing data, even if it had some duplicate entries and needed to create some new tables. Why was I creating new tables? To make matters worse, I discovered that I was moving data from a version 2.25 WordPress database, to v 2.3 and data tables had different names, different relationships, etc. – Oh great…

After about 3 or 4 tries, I succeeded in importing all the data.

Then I looked at my new blog. I was very relieved when I saw the first page, looking pretty much as it should, but then I saw that none of the links worked, and that there were no Categories. So, I brought up the new blog’s Dashboard. ‘You need to upgrade your WordPress Database’, it said. I clicked on Continue. After a minor glitch reported by the upgrade script, I saw the standard blog Dashboard. I gulped, and clicked on the ‘View Site’ link…

This time, everything from the past 2 1/2 years was there, and as far as I can tell, it all seems to be working. My dreams of IP addresses, PHPMyAdmin and MySQL ended up getting back my 265 postings and 419 comments. I guess that it really is true that you continue to work on problems in your sleep. This is first time, however, that I ever awoke with a ‘Eureka’.