Lightroom Classic: Keywords not loading (SQL database issue)

  • 2
  • Problem
  • Updated 9 months ago
  • (Edited)
Adobe LR Classic CC; Keywords are not loading in Library mode.

The keyword tags never load, it just says "working..." but nothing ever loads. If they do load it takes an unreasonably long time to load, over 15-30 minutes (or longer).

I have tried un-installing, re-installing, using the CC Cleaner App, installing the previous version of LR and coming back to the newest version. Nothing seems to work to get the keywords to load in a timely manner, or at all in some cases. 

Has anyone else had this problem or figured out a fix to this issue?
Photo of Jackie Burwood

Jackie Burwood

  • 4 Posts
  • 1 Reply Like
  • extremely frustrated

Posted 1 year ago

  • 2
Photo of Rick Spaulding

Rick Spaulding, Champion

  • 564 Posts
  • 225 Reply Likes
Does resetting your preferences help?

See this article: https://www.lightroomqueen.com/articles-page/how-do-i-reset-lightrooms-preferences/
Photo of Jackie Burwood

Jackie Burwood

  • 4 Posts
  • 1 Reply Like
I just tried resetting the preferences, but the keyword tags still don't appear. I have my keywords in; Keyword Suggestions, Keyword Set and Keyword List. 
However Keyword Tags for the individual image I am looking at still just says "working..." 
Photo of drobtitan

drobtitan

  • 29 Posts
  • 7 Reply Likes
Same problem here, and I only have 5000 pictures, and the worst: if you click: Metadata->Purge Unused Keywords, it will erase a lot of keywords in use without warning.
Photo of Michel DELFELD

Michel DELFELD

  • 247 Posts
  • 33 Reply Likes
make a folder synchronisation this would perhaps help you. What is the amount of picture in your LR catalog? 
Photo of Jackie Burwood

Jackie Burwood

  • 4 Posts
  • 1 Reply Like
I have and 309,405 images in my catalog. I haven't tried a folder synchronization yet, I'll give that a shot. Thank you for the suggestion.
Photo of Michel DELFELD

Michel DELFELD

  • 247 Posts
  • 33 Reply Likes
That's a very very big amount!!!! You perhaps have computer speed capacity problems! Just give a try to look at the .lrcat - One of mine is already 590 Mb for +/ 40.000 pictures. The size must be enormous. I estimate this around 4.5 Tb. This size may be your problem
Photo of Jackie Burwood

Jackie Burwood

  • 4 Posts
  • 1 Reply Like
It is big, but we've been using LR since it came out in 2006 and we've grown. Much of what we shoot includes Canon and Hasselblad 50MPix systems. The shoot folders reside in 60TB in multiple RAID 8-bay enclosures. In 9 years we've had no volume issues and LR resides in a robust relational database structure that should have no problems with 300k records. The LRCat is now 3.6GB on a 1TB SSD in a maxed MacPro (late 2013 machine that Apple chooses to not upgrade for some reason). We work with files extensively, so don't throw away 1:1 Previews and they occupy 2.3TB on a separate RAID drive. Something of great concern is the LRCat before the CC Classic upgrade/conversion was 6.6GB and now it's 3.6GB. This is all very scary for a business that runs on this database.
(Edited)
Photo of John R. Ellis

John R. Ellis, Champion

  • 3839 Posts
  • 1005 Reply Likes
Others have reported successfully using catalogs with many more photos. (I've seen a report of 1M photos.)
Photo of John R. Ellis

John R. Ellis, Champion

  • 3839 Posts
  • 1005 Reply Likes
"Something of great concern is the LRCat before the CC Classic upgrade/conversion was 6.6GB and now it's 3.6GB."

This is to be expected. LR 7 compresses the history field of photos in the catalog database to get better performance with larger catalogs.  My (much smaller) catalog had a similar decrease in size.
Photo of John R. Ellis

John R. Ellis, Champion

  • 3857 Posts
  • 1014 Reply Likes
In your other thread, you wrote: "As a workaround, if you select Metadata > Save Metadata to File, then Metadata > Read Metadata from File, it will fix the keywords however this would require a massive amount of manual effort for the rest of our lives. This is perhaps a single image fix."

What if you selected all the photos at once and then did Metadata > Save Metadata To File followed by Read Metadata From File.  This would take a very long time (17 hours at 5 photos/sec) and generate a huge load on your backups, but if that's your only solution, you might consider it.
Photo of John R. Ellis

John R. Ellis, Champion

  • 3857 Posts
  • 1014 Reply Likes
LR often shows spurious metadata conflicts. If you haven't modified your images' metadata with an external tool after importing them into LR, then you're assured that the catalog contains the most recent metadata, and doing Metadata > Save Metadata To File is always safe.
Photo of J.R. Harvey

J.R. Harvey

  • 5 Posts
  • 0 Reply Likes
I tried a couple of batch updates and during one of them, Lightroom seemed to get hung up writing Metadata to a particular image. It never seemed to finish, so I told it to Quit, but a message appeared saying it was still writing to Metadata, so I waited for a long time for it to finish, but I'm not sure if it ever did. I couldn't tell which file it had a problem with, or whether it had finished writing to all of the other files. I suspect is might have been a problem writing to a large (~400mb) Photoshop file, but I'm not sure.

Also, if the keywords in the file match the LR keywords, will it write to the file anyway? If I tell LR to write all Metadata, and it modifies all the files, this is going to be multiple terabytes of "new" data for the cloud backup. That's a significant problem for this workaround.
Photo of John R. Ellis

John R. Ellis, Champion

  • 3857 Posts
  • 1014 Reply Likes
"It never seemed to finish"

Does that still happen after you restarted LR?  LR 7 seems to have a number of issues with long-running commands.
Photo of John R. Ellis

John R. Ellis, Champion

  • 3857 Posts
  • 1014 Reply Likes
"If the keywords in the file match the LR keywords, will it write to the file anyway?" 

Yes, it will rewrite all the metadata to all the selected files (or their .xmp sidecars, if they're raws).

"If I tell LR to write all Metadata, and it modifies all the files, this is going to be multiple terabytes of "new" data for the cloud backup. That's a significant problem for this workaround."

Right, that's what I meant when I said it could "take a very long time (17 hours at 5 photos/sec) and generate a huge load on your backups".  If these are mostly raws, it will go much faster and generate a much lower load on your backup, since only the .xmp sidecars get modified.
Photo of J.R. Harvey

J.R. Harvey

  • 5 Posts
  • 0 Reply Likes
The workaround I mentioned earlier has ended up losing keywords. Whatever the problem is caused a reduced set of keywords to be written to the file during the Save Metadata to File step. On the file I looked at, it had previously been written to with 7 keywords. After I did Save Metadata to File it had only 5 keywords. 2 of the keywords which should have been there were gone.
Photo of Sabine Joosten

Sabine Joosten

  • 1 Post
  • 0 Reply Likes
I've exactly the same, can't figure out how to solve....
Photo of John Carlomano

John Carlomano

  • 4 Posts
  • 1 Reply Like
I have had a problem with some existing keywords not displaying. My catalog has 18K images. Keywords have been added through various versions of Lightroom starting LR 4 and up to and including Lightroom CC Classic. What happens now is that images with one specific keyword will not display any keywords instead it displays "working...". If I rekey new keywords, the keystrokes are accepted but the keyword display box reverts to the "working... message.  The one solution that I found so far is display the image that has the problematic keyword, then click on the "tag" icon in the lower right hand corner of the display and then key in new keywords. That method eliminates the "working..."  message and does in fact save the new keywords. 
Photo of J.R. Harvey

J.R. Harvey

  • 5 Posts
  • 0 Reply Likes
Based on the failure of the workaround I originally mentioned, I had a look at the internal tables for that image in an old backup and in the updated lrcat file.

I found these relationships for a CR2 file in both the backup and updated file:


sqlite> select * from AgLibraryKeywordImage where image = 50747733;
id_local|image|tag
50747742|50747733|12139958
50747743|50747733|1430
50747744|50747733|7620665
50749765|50747733|48569139
50749766|50747733|726740
50749767|50747733|42786
50749768|50747733|1179561

Then I checked for the keyword entry in a backup from 2016-08-01

sqlite> select * from AgLibraryKeyword where name like 'shoreline';
id_local|id_global|dateCreated|genealogy|imageCountCache|includeOnExport|includeParents|includeSynonyms|keywordType|lastApplied|lc_name|name|parent
726740|8C112ACD-D179-11DB-91C7-0016CB39E5CE|195493390.87469|/17/6726740|274.0|1|1|1||460328558.661498|shoreline|shoreline|7
11701646|D1586273-0583-41AD-89FB-A62B4614F227|227927108.962236|/17/811701646|3.0|1|1|1||227988443.070911|shoreline|Shoreline|7

That shows there are two "shoreline" keywords, one all lower case, and one with upper case "S"

In a backup from the converted file from 2017-10-29

sqlite> select * from AgLibraryKeyword where name like 'shoreline';
id_local|id_global|dateCreated|genealogy|imageCountCache|includeOnExport|includeParents|includeSynonyms|keywordType|lastApplied|lc_name|name|parent
11701646|D1586273-0583-41AD-89FB-A62B4614F227|227927108.962236|/17/811701646|3.0|1|1|1||227988443.070911|shoreline|Shoreline|7

The all lower case keyword is gone. The lower case one, with id 726740 is the one which was assigned to the CR2 image. Since the conversion process seems to have dropped that one, but kept the relationship to it, Lightroom is perhaps trying to look up the right keyword for id 726740 and not finding it, showing the "Working..." message instead of any keywords.

This means for any of the files I did the workaround on, at least 1 keyword per affected record has been lost.

So in this case, a possible workaround has gotten a lot more complicated.

I think it means the pre-conversion LR file will have to be opened, Save Metadata to File if there is any indication the file is not synced, then open the converted LR file and Read Metadata from File.
Photo of Chris Crumley

Chris Crumley

  • 4 Posts
  • 0 Reply Likes
The Problem: The Adobe LR CS6-to-CC Classic catalog conversation program left many keyword operators inoperative with a "Working" message that never resolved. The Adobe catalog conversion program looked at keywords and if there were duplicates (one all lower case and one with an upper case letter), it deleted one but left the index in the catalog. That index could never find the keyword and went into the terminal fault "Working."

The Resolution: We resolved by going into the LR SQL Database and repairing the keyword corruptions caused by the conversion program. We had to go back to the backup just prior to the 10/24/2017 conversion/migration then identify and repost/import all add/change/delete/imports from 10/24/2017 to our current date (3 months). All this required an enormous amount of Tech Time to research, identify the problem, create fixes to the SQL database indexes and counters and reapply all the work done over 3 three months to bring the catalog to a current and working status.
(Edited)