Thursday 31 October 2002

MT+MySQL·PHP 05a: Exporting and importing weblog posts

Transfering one’s posts and comments intact is probably the most critical task in moving your weblog from one host to another. Fortunately, Movable Type has an efficient import/export mechanism, as the Importing Entries section of the MT documentation explains:

In addition to exporting your data from another weblog CMS, you can export your entries from Movable Type itself. This allows you to keep local backups of your weblog content for safekeeping, as well as to transfer data between blogs, by combining the export and import mechanisms.

NOTE: be careful when exporting data from Internet Explorer; all versions of Internet Explorer have a bug where data marked as plain text can be intrepreted as HTML. The symptom of this bug is that all of your exported data will run together onto one line. If this is the case, use Netscape when exporting data.

I didn’t even bother with Internet Explorer. Apart from the glitches I’ll explain below—none of which were browser-related—importing and exporting via Mozilla worked smoothly.

Due to limitations with my current hosting service, I’m still running MT version 2.11 with the Berkeley database. I’ve installed MT version 2.21 with MySQL at my new host though I intend to upgrade to version 2.5 quite soon. The transfer process I’m about to describe depends on running the new weblog with a MySQL backend.

Exporting from the old weblog and importing into the new one works just as the documentation suggests, with one proviso, which is a variation on the perennial GIGO (Garbage In, Garbage Out) rule. In this case, it’s GOGI (Garbage Out, Garbage In).

Unless you are incredibly lucky, you’ll almost certainly have to run the import a number of times. A MySQL backend makes this quite straightforward. After each unsuccessful import, all you have to do is delete the MySQL database tables and reconfigure Movable Type to create a fresh MySQL database, ready for the next import attempt.

Paramount in this procedure is the fact that, if you wish to preserve your permalinks, the entry IDs in your new weblog must match those in your old weblog. If you’ve deleted posts from your old weblog and you want to avoid implementing a complicated set of redirects, you’ll need to add dummy posts to replace those you deleted. (You can filter out these dummy posts with Brad Choate’s SQL plug-in.)

Let’s assume that you’ve exported your posts from the old weblog then run the import, only to find that the import stalls or you end up with a mismatch between the number of posts in the old weblog and the number of posts in the new weblog. Both glitches happened to me. Firstly, the import stalled after only three posts had been imported. Secondly, I wound up with twice as many posts and comments in the new weblog compared to those I’d exported from the old weblog. I finally managed a successful import on my seventh attempt—but without MySQL, I would have been stuffed.

The stalled import occurred with the following error message:

Invalid convert breaks value ''

When I checked the export file, I found that the fourth post contained the lines:

CONVERT BREAKS:
PRIMARY CATEGORY:

whereas in posts one to three and five the corresponding lines were:

CONVERT BREAKS: 0
PRIMARY CATEGORY:

I used a regular expression find and replace in TextPad to replace:

CONVERT BREAKS: \nPRIMARY
with
CONVERT BREAKS: 0\nPRIMARY

At that point, with a weblog containing only three posts, I had to delete the tables from the MySQL database, reset MT, and start again. You’ll have to follow these steps after each unsuccessful import attempt:

  1. Use phpMyAdmin to drop all the tables from your MySQL database.
  2. Run mt-check.cgi to ensure that your MT installation is sound.
  3. Upload mt-load.cgi to your mt directory.
  4. Change the permissions on the mt-load.cgi file to 755.
  5. Run mt-load.cgi (this creates a fresh MT installation).
  6. Delete mt-load.cgi (failure to delete this file results in a serious security flaw in your MT installation).
  7. Log in to the fresh weblog with username Melody and password Nelson.
  8. Edit your profile, changing the username and password and adding your email address & birthplace (in case you forget your password).
  9. Log out then log in again to confirm your username and password.
  10. Modify your import file, based on the errors you encountered during the import process.
  11. Import again.

The import then proceeded smoothly, sort of. I’d exported 698 Entries (i.e. weblog posts) and 1297 Comments from my MT 2.11 + Berkeley DB weblog. When I imported these entries and comments into the MT 2.21/MySQL weblog, I wound up with 1408 Entries and 2594 Comments.

What went wrong? A bunch of different things. I’ll explain them in detail in the next installment.

Permalink

Comments

Just for future reference, you could also have used $ in your regex to mean "end of line."

Yours worked, which is the main thing.

Posted by: Dorothea Salo on 1 November 2002 at 12:13 AM

You know, in the number of blogs I've moved for clients, I have NEVER had to run an import "multiple times" as you said. I have never known anyone that has had to do that - and when I moved our client's sites I moved 15 MT blogs (at the time running MT 1.4 and Berkley DB) within a matter of hours. The imports were the easiest part - it was moving templates that then took me a long time. Now thanks to MT tweaks and other options, even that would be faster.

I once exported my blog with over 1500 entries and imported it in to a clean install of MT to just test it - and not a single hiccup.

You left out one step though - after logging in as Melody / Nelson and changing that, you should quickly move over to the Blog Configuration screen, set all of the settings there, and then change the Post Status to "publish" instead of "draft" (which is what the clean install of MT defaults to) If you don't, then you end up with 700 posts that are in draft mode, and it's a pain. There is a checkbox on the import screen for this too (because of suggestions made by those of us that made the same mistake.)

However, it sounds like you ended up with two imports? Hmmm... very interesting. I guess there is a first for everything.

Posted by: Christine on 1 November 2002 at 09:22 AM

Dorothea, I tried using $ for the end of line in TextPad and it didn't work (even though the Help on Regular expressions says that $ represents the end of a line). Could it be because there's a space before the end of line? (I tried using [:blank:] for that, to no avail.)

Christine, thanks for your comments. And for the tip on the Draft/Publish issue. I checked the Publish checkbox without giving it too much thought so I'm grateful that your suggestion resulted in its inclusion on the Import page.

As regards the duplicate entries, there is a first for everything and, if anyone's going to stumble on it, it'll probably be me. The problems I encountered are interesting, though, and there's a reasonable chance others will run into them. I'll look forward to your comments on the next post.

Posted by: Jonathon Delacour on 1 November 2002 at 12:55 PM

It's probably too late now, but you could try simply copying the content of the db directory to the new MT installation. That might work straightaway, or you might have to tweak the db files as described in the troubleshooting section of the manual, or itmight not work at all. But it it does work, you could use the cgi to convert to MySQL and all the post numbers will be preserved.

Once your entries are in MySQL, of course, you'll never have to go through this hell again. You can just dump your MySQL tables from from MT installation to the other. That's handy for keeping an up-to-date local MT installation for experimentation.

I had some practice with importing and exporting when MySQL support in MT was new. I tried doing a global change on my entries table, and due to a bug in the db structure all the entry dates were changed to NOW(). Luckily I had a fairly recent dump; I only had to recreate one week's entries and comments by hand.

Posted by: PapaScott on 1 November 2002 at 07:37 PM

Actually, some of these kinds of anamolies are showing up in my conversion from db to MySQL.

I'm finding that some entries have been duplicated. Even stranger, some of them repeat themselves within a single entry, but when you copy and paste the text to fix it there's only one set of text.

I've even had entries repeated in Categories, but not in the individual entries.

Posted by: Loren on 2 November 2002 at 03:13 PM

This discussion is now closed. My thanks to everyone who contributed.

© Copyright 2002-2003 Jonathon Delacour