2 people found this helpful
I have managed to get Lightroom running and opening the catalog again. Please note that I do not consider this issue resolved and expect a response from adobe on this. I see other posts in the forum talking about corrupt catalogs, backups going corrupt, the "test catalog integrity" function in backup not actually working. I need to know what is going on here..I have two different backup streams all testing catalog integrity on catalogs I have been working on for weeks...and all of a sudden they're all corrupt? Sorry, but there is something going on with lightroom here.
Here's what I did.
I decided to dig in to this with SQLite. For anyone reading this, please note that while I have a background in tech, my database skills were never great. What I'm doing below removes lines which are causing problems from the catalog, but this is kind of ham fisted...who knows what you might be removing here. I would recommend getting adobe to look at your catalog...someone who knows more about database might be able to understand the offending lines better and make more intelligent decisions about what to do with them.
The below is just what I did. Back up your catalog. Back up your backups. Keep your backups from before doing this because you just did what some yahoo on the internet recommended and you may well run into problems months down the road.
- Download SQLite, put it in a folder, make a COPY of your lrcat file and put it in the folder.
- run sqlite3 "INSERT_CATALOG_NAME_HERE.lrcat" .dump > Temp.sql
- run sqlite3 -init Temp.sql NEW_CATALOG_NAME.lrcat
- This is where the existing online tutorials break down, if there are errors, it will not dump out the new database file! You can't just tell sqlite to dump a corrupt catalog to corrupt sql and back to a corrupt catalog...you're not fixing anything.
- Note the errors that the last command generates. It will give you line numbers for the errors. For me, I was getting "UNIQUE constraint failed" errors and "no such table" errors for the table sqlite_stat4 on a couple hundred lines or so.
- Open the Temp.sql file in an editor. Notepad will work but it's way easier to deal with line numbers in something like Notepad++.
- Remove the exact lines which are generating errors and only those lines. As you are working in the file, you may need to be patient with your editor if the file is large. Jumping around, deleting lines, saving, may all take some time to complete...just wait for notepad++ to do its thing and unfreeze as you're working.
- If you try to use the file at this point, it won't work! it will generate a 0 byte catalog. The reason for this is that SQLite writes "ROLLBACK;" as the last line in the file due to the errors it encountered during the dump process. See here: sqlite - Sqlite3 recreates empty database from dump file - Stack Overflow . What you need to do is change that last line to say "COMMIT;" to commit the changes instead of rolling back.
- Now save the file...let's use TempBadLinesRemoved.sql and exit notepad++ or your preferred editor.
- run sqlite3 -init TempBadLinesRemoved.sql NEW_CATALOG_NAME.lrcat
- for me, the command completed and left me at the sqlite> prompt. Just type .quit to exit sqlite
- Open the catalog in lightroom and it should work.
You just sliced random stuff out of your lightroom catalog. Who knows what you removed. For me, it appears that everything is in tact but I can't possibly check everything in an 80,000 image catalog with tons of collections. You might lose something important which is why you must keep your backups from before trying this, because you are following instructions from someone who does not completely know what they are doing!!!!
I am going to keep working with this recovered catalog and see how things go, but I want an answer from Adobe on this.
I have had so many problems with corrupted catalogs lately that I think I may have to write a script to regularly check the integrity using SQLite. Sorry, but that's not acceptable. Lightroom needs to do better on this. Backups which complete with test catalog integrity enabled should NOT be somehow corrupt a week later without ever being touched.
This worked for me! Thank you so much!
Having the same problem for like 1 month now, after i re open the catalog 2-3 times the smart previews start going corrupt , destroying catalogs and giving me a hard time since i am a proffesional retoucher and working with clients catalogs build with smart previews, is there any chance that lightroom doesnt comply with latest drivers on nvidia ?
From one random guy on the internet to another, thanks!
According to the information in this link: https://www.sqlite.org/fileformat.html, the sqlite_stat4 table is an internal SQLite table used for indexing. Thus it's most likely that there's no catalog specific information in there.
Again from that page, that table is created by SQLite (not by the SQL script you are extracting from the catalog), but it needs to be a special version of SQLite (compiled with a certain flag). Probably if you did that this error would go away.