Friday, January 11, 2013

Recovering corrupt Lightroom catalogs

Back in December, something disconcerting happened. My Adobe Lightroom 4 catalog wouldn't load. Disconcerting because I had something on the order of 50,000 photos in that catalog and recreating the whole shooting match would be a frightful chore. Indeed, I wasn't even quite sure how frightful that chore would be. 

However, I keep good backups in various forms and I was able to find a recent backup of my catalog that was happy enough to load. (The catalog does not include the photos themselves but it does include much of the information related to their organization and other metadata.)

But the problems didn't go away. And, today, even after a migration from a Windows system to a new Mac Mini, they came back in spades. I simply couldn't make a backup of a catalog from within Lightroom, which also refused to optimize it. I could pull in a somewhat earlier backup of the catalog through the Mac's Time Machine backup program, but there was obviously something deep wrong here. And Lightroom's repair catalog function refused to do anything useful.

As it turns out though, hat tip to Stephen Shankland for the info, Lightroom uses a SQLite3 database which has various tools one can use to fix corrupted databases.

The first thing I did was download SQLite3 and SQLite3_analyze. 

SQLite3_analyze confirmed there was indeed some sort of problem. "database image is malformed" it told me. Some searching never did tell me what that means exactly but, in any case, it confirmed there was a problem with the database.

The real find came from Gerhard Strasse's blog. Read the blog but I just want to add a few comments.

Basically the steps are pretty simple.

First you dump the existing catalog into a text file as a bunch of SQL commands:

echo .dump | ./sqlite3 ~/lightroom_catalog.lrcat > ~/lightroom_catalog.sql

then, in theory, you can just suck that text into a new database file with:

./sqlite3 -init ~/lightroom_catalog.sql ~/lightroom_catalog_restore.lrcat

The blog notes that you may get an error or two (i.e. the duplicate keys or whatever that were causing the database issue) but it should work. It didn't in my case. (lightroom_catalog_restore.lrcat was a zero byte file on the first try.) The secret for me was at the end of the comments.

Reader Chairat Juengmongkolwong noted that if, after the first step, you go into the text file (lightroom_catalog.sql in this case) and replace the last line that says:

ROLLBACK; — due to errors

with the line:

COMMIT TRANSACTION;

And THEN do the 

./sqlite3 -init ~/lightroom_catalog.sql ~/lightroom_catalog_restore.lrcat

it should work. Which it did for me. I never did see any errors so I'm not sure what the problem was. Hopefully, it is fixed now. 

Obviously, if you've never touched a Unix/Linux command line this is probably a bit intimidating and you'll probably need to enlist some help. Make copies and work on backups!  This process can doubtless be done on Windows as well although the details will be a bit different. The terminal console on a Mac is a Unix command line. I'd also note that if you have a very large catalog—mine is about 500MB—a lot of text editors won't be able to handle it. I ended up downloading and installing Vim which handled what a couple of others couldn't. 

Another possible  option, BTW, is to fire up a new catalog and import your existing (corrupt) catalog although this will cause you to lose any publishing services that you have setup and possibly other things as well. However, when I did this, it only imported a quarter or so of my photos. (Presumably it quit at the spot of the error.)

My configuration was a Mac Mini running Mountain Lion and Lightroom 4.3.

The one other thing I might add is that, given this is a fairly straightforward procedure, and one that is fairly standard for this particular SQLite database error as far as I can tell (whether for Lightroom or otherwise), it's unclear why it isn't embedded within Lightroom's own corrupt database recovery process.

7 comments:

Rob said...

I was having the same problems with one of my larger LR4.3 catalogs with 130,000 images - it would open ok, but then would not backup or optimize, and on next opening would indicate it needed repairing which would then fail. Trying to import from it into another catalog or exporting out of it to a new catalog would both fail.

But the sqlite .dump and recreate technique described here worked, with the replacement of the rollback with a commit.

Many thanks ;)

Gordon Haff said...

Glad I was able to help even if it was mostly just passing on information. Appears to be a pretty rare problem but a potentially serious one even for folks who are good about backups.

What I suspect happened in my case was that the computer crashed hard while writing to the catalog. I mentioned in my post that I had switched from a Windows PC to a Mac. The reason was that the PC developed what I suspect was some sort of hardware problem that caused intermittent resets.

Levente Engel said...
This comment has been removed by the author.
Levente Engel said...

Hello!

I tried what you suggested but I couldn't managed to do anything!

I uploaded my file here can you please check it => http://dl.transfer.ro/transfer_ro-25apr-05aa9f867dae4.zip

I want to mention that I'm on win7 64 bit and using lightroom 4.3

thank you in advance

Gordon Haff said...

Levente,

I have no connection with Adobe. I was just passing on a technique which worked for me when I had a problem. I'd suggest contacting Adobe if the technique I posted doesn't work.

Rob said...

My catalog seemed to have got corrupted when I upgraded LR (from 4 to 4.something). I've recently upgraded to 4.4 and now found that another catalog (this one has 300,000 images - yikes) is showing the same symptoms :( [maybe I'm just lucky!]

ElPelicano said...

Hello,

I have a corrupt LRCAT File.
I don't know anything about SQLITE.
Can you help me please?

Thank you vey much!