David's World

February 28, 2007

Blogger's delight: restore a corrupted Berkeley DB in Movable Type

Here's a technical bit for all fellow bloggers who use Movable Type and haven't changed the default database backend from Berkeley DB.

The first tip is: switch to an SQL database. If you use SQLite,, you won't even need a separate database process on the server - you can install everything locally, and as CGI.

The second tip: should you ever get stuck with a dysfunctional Movable Type installation because the database is corrupted, then read on.

Berkeley DB database files seem to get corrupted quite often. This can happen when, for instance, the harddrive fills up or you exceed your quota on the server. Or when database accesses get interrupted by a server that looses power, or a server that kills processes. There are many reasons, and every one of them a motivation to switch to a better database backend.

What happened to me was that MT could not finish its background tasks any longer. A process (mt-feed.cgi) that is used to kick off such tasks was hogging all the CPU cycles on the server, and I started getting complaints from the local system administrator.

A couple of weeks ago I had noticed that MT would not list comments, trackbags or log entries any longer: "No comments could be found."

I ignored the problem for a while, but when I got words that my MT installation was blocking the server, I decided to investigate.

I noticed that MT would clear comments from some popular entries when they were rebuild (which happens as soon as someone posts a new comment!). That sucks!

It turned out that my database was corrupted.

~:/web/data/mt/db$ db_verify comment.db 
db_verify: Page 5598: btree or recno page is of inappropriate type 0 
db_verify: Page 5593: btree or recno page is of inappropriate type 0 
db_verify: Page 9863: btree or recno page is of inappropriate type 7 
db_verify: comment.db: DB_VERIFY_BAD: Database verification failed

Various attempts to save the data using tools such as db_dump were futile. Exporting it all using MT's export function didn't work: only 90 out of 530 comments were saved, and many interesting discussions from my readers lost.

Similarly, logs were corrupted, too. I managed to export the log entries to a CSV (plain-text) file, and reset the whole log, using some trickery.

But what to do about the comments? I tried switching to an SQL database, in my case: SQLLite. (Postgres and MySQL are alternatives.) SixApart, the maker of MovableType, provides a conversion script with every MT installation: mt-db2sql.cgi. But unfortunately this script blatantly refused to convert any comments from the corrupted comment table.

I did notice, however, that I could use MT's search function on the comments - this worked just fine. The reason must have been that the search can ignore junk (spam) for comments and trackbacks. So my comments were still there!

Chances are that this is the case for other MT installations as well. Given the recent wave of comment and trackback spam, it's quite likely that corruption occurrs in junk messages. Note that junk is stored in the database, even if it doesn't make it onto your published blog. The solution was to extract the data from the comments table, not using the original mt-db2sql.cgi tool provided, but using a modified version.

Wonderful: All 530 comments rescued and all data recovered. And I've switched to SQLite in the process, so it hopefully won't happen again.

Contact me if you would like me to attempt the same for your blog. No promises though!