Dylan: February 2004 Archives

Corrupted Movable Type Database

| | Comments (7)
I recently received an email letting me know that comment posting no longer worked on my site. A few weeks later I got around to checking it out found out I couldn't even login to my site. I have no idea how long it has been like this since I haven't blogged anything since last October. I first thought my data had been blown away, but a quick check of the Movable Type database directory showed what appeared to be an intact set of files. But when I tried to login it or retrieve my 'forgotten' password, I was just told that no such user existed. For some reason Movable Type decided my data files were no longer readable.

To break the suspense, let me tell you that after many many hours I fully recovered all my data and decided to celebrate by writing this entry about why it broke and how I fixed it. Perhaps others with a similar problem will benefit.

To start, I did some searches on the symptoms I was seeing and came across this handy program: MT-Medic. It is a very useful CGI program that provides a nice GUI view to your Movable Type database. Apparently very handy for resetting user's forgotten passwords. MT-Medic was also unable to retrieve any data from my data files.

Next I tried searching on the Movable Type Support Forums and found a reference to a question in the Troubleshooting section of the Movable Type documentation.
I changed hosts, and now I can't log in to Movable Type.
When you change hosting providers (or just move to a different server at the same provider), it is possible that the two servers have different versions of the Berkeley DB library; this is the library that Movable Type uses to store your data. When you copy the DB files from one host to another, if the two hosts have different versions of Berkeley DB, the new host will not be able to read the files from the old host.
Click here to read the full text
I had this faint memory of receiving an email from my hosting company a few months back that let me know they were upgrading servers. I thought I had found my solution and started following the detailed instructions. First off, my web hosting site didn't appear to have db_upgrade, db_dump, db_dump185 or db_load installed. I hunted all over the directory tree and eventually found them in /usr/local/bin but they had the prefix db3_ instead of db_. Be aware your hosting company may have renamed these utilities. Remember... the locate and whereis commands are your friend.

First thing I did was create a backup of my data files so that I could always revert back to how I originally found them. Next I tried db3_upgrade *.idx *.db and it returned immediately without any output. The files were apparently unchanged. A quick check showed that MT-Medic still couldn't read them. Next I laboriously executed db3_dump and then db3_load on all of them (I later wrote a shell script to do this) and was at first excited since it seemed to have definitely modified the files. Instead of the smallest files being 16K, they were now 32K. Progress! MT-Medic politely informed me that the files were still unreadable. Argh.

As a side note, the fact that db_dump produces data files consisting of mostly numeric hex-codes is just fine. I at first was suspicious that it was producing garbled data but everything worked out in the end.

I started to wonder if my installation was faulty and upgraded Movable Type to the latest version. No luck. Still unreadable.

Next I wiped out my data directory (still had the backup) and ran mt-load.cgi which is normally only run on a new Movable Type installation to create the initial database files with the default data. MT-Medic had no problem reading the files now and correctly displayed the one default user named Melody. Hmm.

I tried updating just the author.db file with my old data and again MT-Medic can't read it. Hmm.

I then tried running db3_dump on the newly created default database files just to see what happens and interestingly enough it gave an error message saying the format was inappropriate. I then tried running db3_dump185 on them and it successfully dumped the data. Interesting! This means that the database files that Movable Type just created are in the somewhat older BerkeleyDB v1.85/1.86 format. I then tried running the file command on my originally saved data to see what format they were in:
> file author.db
author.db: Berkeley DB (Btree, version 8, native byte-order)
Interesting! My old data files are apparently in a newer BerkeleyDB format than the files the Movable Type just generated from scratch. At this point I surmised that when my web hosting company moved my account to a new server, they actually moved it to a server with an older version of BerkeleyDB. So what could I do? All of the utilities were designed for upgrading the database files and so I decided that if I could somehow downgrade my data files to an earlier version of BerkeleyDB I would be set. I downloaded v1.85 of BerkeleyDB and was hoping to compile and run a v1.85 copy of db_load in the hope's that it would import the data into the v1.85 format. Well, it turns out they hadn't written a db_load for v1.85 since there was no data that needed to be migrated yet. The database format hadn't changed drastically enough yet to require a format import utility. Hmm.

I tried a few slightly later versions of BerkeleyDB and was still unable to downgrade the data to the format I needed. I eventually gave up on that approach and thought more about exactly what was happening. There was a very recent version of BerkeleyDB installed on the server but for some reason Movable Type wasn't using it and instead was using some very old version of the libraries. The Perl module that Movable Type uses to interface with BerkeleyDB is called DB_File. I checked which version was installed and saw that it also was a very recent version (v1.806). So why was Movable Type still trying to read and write BerkeleyDB database files in an outdated format?

While doing lots of reading on the above subjects I found many references to the fact that DB_File had to be compiled. This confused me since the Perl module, DB_File.pm, was just a plain non-compiled text file. I decided to download the latest version of DB_File (v1.808) and see what the deal was. Sure enough the DB_File module did need to be compiled and this is because it produces a DB_File.so shared library that contains information on how to read/write BerkeleyDB files. The version of the BerkeleyDB that the DB_File.so uses is whatever version it was originally compiled for. So it would seem that the DB_File.so library installed on the system was originally compiled for BerkeleyDB v1.85 and it doesn't matter HOW up to date my DB_File.pm is since in the end it is at the mercy of this shared library.

Alright, so the solution then was to update both the DB_File.pm Perl module and the DB_File.so shared library used by Perl. I needed to update both of them because when they are first compiled they appropriately sync their versions and complain if their version numbers don't match. The problem though is that I did not have root access on my web host and so needed to figure out where I could push these files so that Movable Type would find them before finding the system-installed versions. I could of course just ask my web host company to fix it for me, and I will, but that could take some unknown amount of time and numerous emails to complete. I wanted to make it work now so I needed to figure out somewhere that I could access where I could put the files so that Movable Type would use them instead of the older ones.

In the end the magic set of file locations under my home directory was the following:
I'm not entirely sure what the autosplit.ix file is for, but when I enabled CGI errors and warnings by adding use CGI::Carp qw(fatalsToBrowser); to the beginning of my Perl scripts (a very useful debugging trick!), it started complaining that it couldn't find this file and so I copied it from my recent DB_File compilation. I finally went and tried running MT-Medic again and quite wonderfully all of my data was back and readable and intact. Woo hoo!

In the end I made lots of unnecessary changes that ultimately didn't contribute to the successful retrieval of my data, but hey, it was a learning experience. The typical problem people have with corrupted Movable Type databases is they need to migrate their databases to a later version. For me, I needed to either downgrade my data (never figured out how), or update the Perl DB_File module so it could read the later format my data files were already in. While searching around I read a fair number of cases where people tried using db_upgrade etc without success and in the end had to just obliterate their data and start from scratch. Hopefully the information above will help a few people get their data back before giving up.

Last but not least, as soon as I saw my data was back and readable and intact, I ran mt-db2sql.cgi and migrated all of my data the hell away from BerkeleyDB and into MySQL. I read numerous reports of data size limits with BerkeleyDB where, once your blog gets large enough, your transactions will just fail to complete successfully. I would rather avoid such problems and any other problems related to server and modules upgrades. MySQL is apparently more reliable and a lot faster.

About this Archive

This page is a archive of recent entries written by Dylan in February 2004.

Dylan: October 2003 is the previous archive.

Dylan: March 2004 is the next archive.

Find recent content on the main index or look in the archives to find all content.