Howardisms: Corrupt Movable Type Database

After running into a strange problem with suddenly not being able to post to my Movable Type server, I noticed in MT’s support forums that the culprit is not uncommon. The ability to work with a blog varies, but one symptom is common… a few innocuous error lines at the end of MT’s pages.

MT::App::CMS=HASH(0x8069d14) substr outside of string at
        /var/www/html/mt/lib/MT/Serialize.pm line 58.
MT::App::CMS=HASH(0x8069d14) Use of uninitialized value
        in unpack at /var/www/html/mt/lib/MT/Serialize.pm line 58.
...
MT::App::CMS=HASH(0x8069d14) substr outside of string at
        /var/www/html/mt/lib/MT/Serialize.pm line 59.
MT::App::CMS=HASH(0x8069d14) Use of uninitialized value
        in addition (+) at /var/www/html/mt/lib/MT/Serialize.pm line 60.

If you see these lines, it means that you are probably using the standard Berkeley DB as database storage and it has become corrupted. The solution that everyone recommends is blowing everything away and starting again… and this time, using a SQL database.

But with the limited backup ability within MovableType, who really can afford to do that? And even if you did do a full backup (I did), it is always going to be old and you’ll end up loosing some data. In my case, my last backup was before a full weekend regenerating a lot of templates… and I didn’t feel like redoing all of them.

So, sit right back, and you’ll hear a tale of how I recovered my data…

While there are rumors for manipulating DB files (see below), it isn’t as straight-forward as making SQL calls, and it isn’t necessarily that the database is corrupt (from the database’s perspective), but MT is unable to correctly parse it.

First, I set up a MySQL server and prepared to run the mt-db2sql.cgi script to convert what was left of my data over to a new, more-stable, database storage. When I did, it told me the entry in which database archive was corrupt, and then failed.

Being an alpha geek (hear me roar), I wrote this perl script to read the database archive, and then I deleted the offending entry. It was the next to the last entry and so I deleted the last one as well.

I then dropped my SQL database and recreated it and then reran mt-db2sql.cgi and lo and behold, it worked! Note: Keep in mind that after you did this, you may have to restart your web server, since many people’s web server will cache the MT CGIs and won’t necessarily re-read the database configuration change.

I’m very happy, and I think that it is time to press Ben Trott into adding an export mechanism that would include templates and configuration data along with the entries. While we are at it, we might want to annoy him into robustifying his DB code, as its fragility is surprising.

However, I am also happy with using MySQL for the database storage, as I can now do other queries against my data. Actually, I thought I was using MySQL as every other MT installation that I’ve set up for family and friends, I’ve done that… I just forgot to update my own.

Thought originally posted on Thursday, 4 December 2003

© 2003-2005, Howard Abrams • Except where otherwise noted, all original content is licensed under a Creative Commons License (see details).

A comment to this from Po

If the DB database is actually corrupted, you might want to take a look at dbdump and dbload utilities.

The good news is you can get the raw data. The bad news is that without the right prefix, it might just be garbage.

Berkeley DB tools are thus:

db_archive db_deadlock db_load     db_recover  db_upgrade
db_checkpoint   db_dump     db_printlog db_stat     db_verify

Comment posted on Thursday, 4 December 2003

A comment to this from Ram

Your perl script was really useful for me. A friend of mine had a somewhat corrupted entry.db (he hit his disk quota). I used your utility to extract all the data out, then used a php script (since that’s what I know) to insert the data into an existing MySQL db.

Thanks, Ram

Comment posted on Sunday, 11 April 2004

A comment to this from Net Llama!

I’ve got a busticated BDB with MT, and this seemed like the answer to my prayers. When I run mt-db2sql.cgi, i see:

MT::Entry 10 100 101 102 103 104 105 106 107 108 109 11 110 111 112 113 114 115 116 117 118 119 12 120 121 122 123 124 125 126 127 128 129 13 130 131 132 133 134 135 136 137 138 139 14 140 141 142 143 144 145 146 147 148 149 15 150 151 152 153 154 155 156 157 158 159 16 160 162 163 164 165 166 167 168 169 17 170 171 172 173 174 175 176 177 178 179 18 180 181 182 183 184 185 186 187 188 189 19 190 191 192 193 194 195 196 197 198 199 2 20 200 201 202 203 204 205 206 207 208 209 21 210 211 212 213 214 215 216 217 218 219 22 220 221 222 223 224 225 226 227 228 229 23 230 231 232 233 234 235 236 237 238 239 24 240 241 242 243 244 245 246 247 248 249 25 250 251 252 253 254 255 256 257 258 259 26 260 261 262 263 ** WARNING: Use of uninitialized value in print at /home/netllama/cgi-bin/mt-db2sql.cgi line 72.

** WARNING: Use of uninitialized value in numeric gt (>) at /home/netllama/cgi-bin/mt-db2sql.cgi line 73.

An error occurred while loading data:

Column ‘entryblogid’ cannot be null

Unfortunately, this doesn’t seem to show which entry(s) are bad to me. I tried using your perl script to locate which record was bad, and I thought i saw 3 that were wonky. But when I tried to delete them, nothing happens: ./fixdb.pl —file db/entry.db —delete 103

Am i missing something obvious?

Comment posted on Wednesday, 18 August 2004

A comment to this from Howard Abrams

After replying directly to NetLlama with some verbal shoulder shrugging, he responded that he had fixed it with a great solution:

Thanks for your reply. Actually what I ended up doing (that worked!) was to just hack the mysql create schema script to remove the ‘not null’ requirements for each column that failed on import. This allowed me to get all the data into mysql successfully, at which time I could look at it and locate the NULL entries. There turned out to be 4 NULL records in the entry table that I easily deleted from the DB making life good again.

Comment posted on Sunday, 22 August 2004