Lightroom Classic: Needs tools to validate catalog and all content

  • 3
  • Idea
  • Updated 7 days ago
  • (Edited)
Lightroom was designed as a tool for gathering, organizing, and storing large collections of photographs.  Many photographers, like me, use it to store their entire collection. It is thus imperative to check the collection integrity from time to time, perhaps monthly.

Lightroom Classic CC has menu items to "Validate DNG files" and to "Find all missing photos".  These are a good start - but are not a complete solution.

LR really needs "Validate all photos" and "Validate Catalog" menu items. 

The "Validate all photos" should check the availability and integrity of all items in the catalog - not just dng files. My catalog includes jpg, tif, png, mov, mp4 in addition to dng files.  This feature is simple to implement - just compute an MD5 hash of the file and record it in the catalog; when later validating, recompute the hash and compare with what is recorded in the catalog.

The "Validate Catalog" feature should run internal consistency checks on the catalog itself.  Presently (v8.1) there is no such feature, unless it is a precursor to "Optimize catalog...".
Photo of DAVID KOTZ

DAVID KOTZ

  • 10 Posts
  • 0 Reply Likes

Posted 4 weeks ago

  • 3
Photo of dmeephd

dmeephd

  • 291 Posts
  • 69 Reply Likes
Agree completely.  Optimization does NOT perform most of the tasks of a database integrity and data validation utility.  These do indeed exist for SQLite, but they are typically not open source and therefore not free.  So don't hold your breath expecting Adobe to add them any time soon.

There are commands built into SQLite, such as "sqlite>PRAGMA integrity_check;"; however, I am unsure if Optimization performs this task or if this is all that Optimization does.  I suspect the latter.

A utility such as Pragma performs much more.  Pragma does an integrity check of the entire database.  It looks for out-of-order records, missing pages, malformed records, and corrupt indices.  If any problems are found, then strings are returned (as multiple rows with a single column per row) which describe the problems.  At most integer errors will be reported before the analysis quits.  The default value for integer is 100.  If no errors are found, a single row with the value “ok” is returned.  If no "ok" is returned, then other commands must be used to reindex the database.

Pretty sure LR Optimization does none of this.  It certainly cannot check for referential integrity as SQLite does not support any level of Referential Integrity beyond the lowest level; i.e. primary keys.  Hence all the catalog issues users report, especially as the catalogs grow larger.

However, obtaining these tools on your own and using on your LRcat is a recipe for disaster—I know, I've tried—as LR adds some proprietary header info to the database file and the returned and corrected catalog cannot be recognized or opened by LR.  Oops.

Photo of Roelof Moorlag

Roelof Moorlag

  • 131 Posts
  • 34 Reply Likes
Yes, i'm with you. Lightroom should get better validation options but there are some workarounds:

Linwood Ferguson wrote a plugin (LRValidate) that does validation on all files in Lightroom: https://archive.codeplex.com/?p=lrvalidate

And for checking the integrity of the Lightroom database itself i use SQLite Expert Personal. I wrote a (dutch) blog about it: https://digitaalfotobeheer.blogspot.com/2018/05/lightroom-back-up-testen.html



(Edited)
Photo of DAVID KOTZ

DAVID KOTZ

  • 10 Posts
  • 0 Reply Likes
Thanks!  LRValidate looks useful, assuming it is still compatible.  (It is 4 years old and may be limited to Windows.). 

Still, this should be a basic function built-into LR itself.
Photo of Jim Wilde

Jim Wilde, Champion

  • 332 Posts
  • 120 Reply Likes
I don't know if you guys are aware of the catalog "Test Integrity" option that is in the catalog backup dialog and also in the start up dialog if you launch Lightroom using the "Prompt Me" option or you hold down Ctrl/Cmd when launching.




Obviously I don't know how comprehensive that option is, but there are sufficient reports in the various Lightroom forums of LR reporting that the "Catalog may be corrupt and needs to be repaired" to make me think that it's doing something.
Photo of Linwood Ferguson

Linwood Ferguson

  • 29 Posts
  • 18 Reply Likes
No, I meant exactly that, I create a table (I think two actually).  It's safer to create new structures while co-existing with LR's code than to add a new column to an existing table; the latter can cause errors depending on how LR inserts or modifies rows, but generally speaking LR will just never see my tables.  Bear in mind I did not create a plugin, it's a standalone program you run when LR is not in use.  One advantage of doing it that way is I can more directly access the database and files, and I can create multiple threads to do the checksum validation, not limited to the speed (i.e. slowness) and features of LUA.
Photo of David Roberts

David Roberts

  • 1 Post
  • 2 Reply Likes
Hi, I was having LR Classic sync issues (crash when sync started each time LR was run - OK if sync paused)

LRValidate found 3 corrupt jpeg files, which i fixed from original pre-import backups, and now sync seems to be working!  So many thanks for that, and I will continue to use it regularly.

I did have to make one small change to the software, to handle a filename with a single quote in it, which needs changing to 2 x single quote in sqllite sql.

what would you like me to do with this small fix?
Photo of Johan Elzenga

Johan Elzenga, Champion

  • 1711 Posts
  • 698 Reply Likes
Here's a plugin that works with Mac and Windows. Old too, but it still works fine. http://bayimages.net/blog/lightroom/validator/
Photo of DAVID KOTZ

DAVID KOTZ

  • 10 Posts
  • 0 Reply Likes
Thanks, I'll check out that old plugin.  Ironically, I have been writing something very much like it, but only as a separate shell script - not a plugin. 
Photo of Linwood Ferguson

Linwood Ferguson

  • 29 Posts
  • 18 Reply Likes
@David Roberts, you can put it on github with the project https://github.com/Linwood-F/LRValidate or if very short post here. I don't see a way to PM on this site, and hesitate to post my email, but am very interested in getting the bug fixed.