4

I get the following error in Photos app on my Mac.

Missing File
Photos with unavailable original files cannot be opened.
The original photo “IMG_3076.JPG” is either offline or cannot be found. Click “Find Original” to reconnect.

I want to follow the instructions from how to avoid getting missing file error in photos, so my question now is,
Is there some way to get all missing files from Photos library? It is going to be impossible to double click every photo to find out if it is missing. I need just the directories, i can place them back one by one and consolidate.

2
  • I had an idea, but I can't really test it. If you copy all potentially missing photos into one single directory, maybe on an external disk, then attempt to consolidate... I wonder whether it will look in that directory for all, once one has been discovered there manually. iTunes can do a similar thing, afaik.
    – Tetsujin
    Nov 1, 2016 at 12:52
  • Let me try this, I have copied the Photo Library to the backup drive that has the folder structure in place, I will try to open that and run a consolidate on that copy (the one on the external hard drive). Will that work?
    – A j
    Nov 1, 2016 at 13:45

5 Answers 5

3

Short answer: you can use the script in this GitHub repo.

Long answer: I had the same question, and it turns out that the photo library is backed by a SQLite database under the covers. On your machine, you can find the database file at <photo library root>/database/photos.db. There's a table called RKMaster, with a row for every photo, and a fileIsReference column that tells you whether the photo is "external" or not.

The script I linked to simply dumps the list of all external files, and then iterates through to check that each one exists on disk.

Note that the table also includes an isMissing column, but this is not good enough by itself. It only tells you about the files that Photos "knows" are missing, because you tried to double-click them, or use them in some way. If you simply delete a file from disk, but don't try to access it in Photos, the isMissing column will be false.

3
  • I have not yet been able to confirm that the list generated is accurate. But will mark this as answered.
    – A j
    Aug 31, 2017 at 9:09
  • RKMaster is no longer the table with the key data [ ./find-missing-photo-references.sh | Error: no such table: rkmaster ]. I'm attempting to rescue photos from an MBP that has so little space that timemachine can't even make a snapshot to do a backup, so I don't have the ability to troubleshoot just yet.
    – BISI
    Aug 31, 2021 at 23:58
  • You can force the "isMissing" flag to be updated by using the "Repair" operation, which you can activate by launching Photos.app with the cmd and option keys held down. Oct 16, 2022 at 10:48
2

If what you want is just a list of all missing files, you can get that using osxphotos with the command

osxphotos query --missing

That will only give you the UUID of each file, not the path. However, once RepairPhotosBookmarks is integrated into OSXphotos (due shortly) you don't need the file path because that powerful app will be able to handle those missing files for you.

2
  • The danger of a non-Applescript solution is that, if it tries to update the database directly, it could introduce errors if the programmer didn't consider all special cases or if Apple changes the layout in a way that make the code not work right any more. That's why my solution tries to access the database only to find the missing items, but then uses AppleScript to tell Photos safely to add the missing images to a new album. Therefore, to be on the safe side, I suggest you make a full backup of the library before you try this solution. Oct 19, 2022 at 15:15
  • My understanding is that OSXphotos builds on [PhotoScript](github.com/RhetTbull/PhotoScript) which in turn uses the Photo-Apps Apple script interface. But I may be wrong. I like your script solution, though, because - if it worked - it would be much easier to implement.
    – Christoph
    Oct 20, 2022 at 9:25
1

I've found that the isMissing flag doesn't mean that the image is gone, just that it's not currently on disk. Using the Python script here, I was trying to copy all of my images out of Photos for backup. I noticed that several images were not being copied, and it turned out it was because they had the isMissing flag on them. But, I was able to take the image name (e.g., IMG_1234.JPG), search for it in Photos, and it found the image. And when I opened the image in Photos for viewing, there was a progress circle shown in the lower right corner (as though it was loading the file from somewhere), and when I checked the folder inside the Photos library on disk, sure enough, the missing file was now there. So now the mystery is, where are these "missing" files stored, and how do I access them (without having to visit each & every one within Photos)? And has anyone reverse engineered & documented the schema of the Photos SQLLite DB, including what the various fields mean, like "isMissing"? I haven't been able to find it myself.

1

(Note: The solution appears to only work if the Photos library has been converted from an older iPhoto library, and the images were missing already at that time. I leave it here for educational purpose, but have added another answer with a more thorough detection.)

Here's an AppleScript that can add all images in Photos.app to a new album, from where you can then select and delete them (e.g. with cmd-delete).

It only works if Photos has already detected that the file is missing. I don't know what causes this detection, it's just that I found a Photos library where this was the case, and then this script worked. But when I tried to reproduce this with another library, where I deleted the original files (and also made sure they were not recreated, which happens when they came from iCloud), even relaunching Photos with forcing a library repair would not update the image properties to a state where my script would detect them as missing. Maybe this only works with libraries that were converted from former iPhoto libraries (which I know the first one was).

To use it, launch Photos, switch to view all photos, and select all photos you want checked, e.g. with cmd-A. Then run the script, and it'll add all those photos for which there's no known or accessible image file to the automatically created "missing images" album.

tell application "Photos"
    set media_items to selection
    set missing_items to {}
    repeat with media_item in media_items
        if width of media_item is 0 then
            set end of missing_items to media_item
        end if
    end repeat
    if missing_items is {} then
        display dialog "Found no missing files" buttons ¬
            "OK" default button "OK"
    else
        if not (exists album "missing images") then
            make new album named "missing images"
        end if
        set dest to album "missing images"
        add missing_items to dest
        display dialog "Found " & (count of missing_items) & ¬
            " missing files" buttons "OK" default button "OK"
    end if
end tell
5
  • Wenn I run that script on a couple of photos, I get: error "Photos got an error: Can't add the provided media items to the album." number 6 This happens regardless of whether those photos have missing files or not.
    – Christoph
    Oct 14, 2022 at 9:58
  • Update: The error seems to be due to no items being selected (the error disappears with if missing_items is not {} then add missing_items to dest end if) So the problem seems to be with the selection condition ìf width of media_item is 0`. Apparently, the query does not return 0 when the item doesn't exist...
    – Christoph
    Oct 14, 2022 at 12:15
  • If you have suggestions for improving the script to make this work more reliably, feel free to edit my answer if you can. Did it work for you once you had actually missing files, or is it not working at all for you? Oct 15, 2022 at 11:31
  • I sat for quite a while trying to make it work but didn’t succeed. It doesn’t work with missing files. Even when the file goes missing, Photos still knows the width (and other info abt the image. I have no idea how to check whether the image is missing.
    – Christoph
    Oct 16, 2022 at 20:01
  • 1
    To make this work, it would require to determine the location of the master image file, and then check if that file really exists. Sadly, the AppleScript API does not provide a way to determine the actual location of the file - one could get its location if it's stored inside the Masters folder, but that won't work for externally ref'd files (and there's no way via AppleScript to determine whether they're internal or external, either). Apple provided a badly designed AppleScript interface for Photos, and they're not doing anything about it. Oct 20, 2022 at 13:52
0

Note: The following solution, while working well in my own test case, is probably not suitable for large (100'000s) of images in the library, nor with Photos offloaded into iCloud. Take this as an educational answer but don't expect it to work in your case. I welcome others to improve on this script and post it in their own answers.

The following AppleScript parses the sqlite database to identify the paths to the files, then checks if those files are present. If it finds missing files, it then launches Photos.app and tries to identify the related images (or movies) and adds them to an album named "missing images".

It works with version 2 and 3 of the Photos library, tested with Photos.app v3.0 (on macOS 10.13 "High Sierra") and v7.0 (on macOS 12.6 "Montery").

To use this script, copy the code below and paste it into a new "Script Editor.app" document. If you only want to check particular images for missing files, open Photos, then select the items you want checked. Otherwise, to check all items (photos, movies), quit Photos. Then run the script. Once the script has checked all files, it will launch Photos and add the missing items to the album "missing images". When finished, it will show a message about it. Then you can view the album in Photos, select all items and delete them with cmd-delete if you want to get rid of them.

Beware, though, that if you have some images on external media and those disks are not mounted, then they'll also be considered missing by this script – yet, you should probably not want to delete those items, then!

use scripting additions

set path_to_photos_library to POSIX path of (choose file of type ¬
    "com.apple.photos.library" with prompt ¬
    "Choose the photos library that Photos.app currently uses" default location path to pictures folder)

--
-- Get the current selection from Photos
--
set selectedIDs to {}
if application "Photos" is running then
    tell application "Photos"
        local media_items
        set media_items to selection
        repeat with media_item in media_items
            set end of selectedIDs to id of media_item
        end repeat
        quit -- need to quit Photos so that we can access its database
        delay 0.5
    end tell
end if

--
-- Make sure the database is accessible and determine which DB version to use
--
repeat
    try
        local files_dir
        do shell script "sqlite3 " & quoted form of path_to_photos_library ¬
            & "database/photos.db 'SELECT value FROM LiGlobals WHERE keyPath=\"metaSchemaVersion\"'"
        ignoring white space
            if result = "2" then
                set db_version to 2
                set files_dir to "Masters"
                set db_name to "photos.db"
            else if result = "3" then
                set db_version to 3
                set files_dir to "originals"
                set db_name to "Photos.sqlite"
            else
                my showMessage("Sorry, the database appears to be in an unsupported format.")
                quit
            end if
        end ignoring
        set db_path to quoted form of path_to_photos_library & "database/" & db_name
        set files_path to path_to_photos_library & files_dir & "/"
        delay 0.1
        exit repeat
    on error
        with timeout of 86400 seconds
            display dialog "Can't access database - make sure Photos.app is quit, then click OK"
        end timeout
    end try
end repeat

--
-- Determine the file paths by querying the photos database
--
if selectedIDs is {} then
    set where_clause to ""
else
    if db_version = 2 then
        set where_clause to ¬
            " JOIN RKVersion v ON (m.uuid=v.masterUuid)" & ¬
            " WHERE v.uuid IN (\"" & joinText(selectedIDs, "\",\"") & "\")"
    else
        -- need to remove the "/L0/001" suffix from the IDs first
        set uuids to {}
        repeat with anID in selectedIDs
            set end of uuids to first item of splitText(anID, "/")
        end repeat
        set where_clause to " WHERE ZUUID IN (\"" & joinText(uuids, "\",\"") & "\")"
    end if
end if
if db_version = 2 then
    set sql to " 'SELECT m.imagePath FROM RKMaster m" & where_clause & "'"
else
    set sql to " 'SELECT printf(\"%s/%s\",ZDIRECTORY,ZFILENAME) FROM ZASSET" & where_clause & "'"
end if
do shell script "sqlite3 " & db_path & sql
set check_paths to my splitText(result, return)
delay 0.1

--
-- Check the availability of every file
--
set missing_paths to {}
repeat with relPath in check_paths
    set fullPath to (files_path & relPath)
    set theFile to POSIX file fullPath
    try
        (theFile as alias)
    on error
        set end of missing_paths to relPath as string
    end try
end repeat

if missing_paths is {} then
    my showMessage("Found no missing files")
    quit
end if

--
-- Query the database again to get the ids of the images that reference the missing files
--
with timeout of 86400 seconds
    if db_version = 2 then
        do shell script "sqlite3 " & db_path & ¬
            " 'SELECT v.uuid FROM RKMaster m JOIN RKVersion v ON (m.uuid=v.masterUuid)" & ¬
            " WHERE m.imagePath IN (\"" & joinText(missing_paths, "\",\"") & "\")'"
        set missing_ids to my splitText(result, return)
    else
        do shell script "sqlite3 " & db_path & ¬
            " 'SELECT ZUUID FROM ZASSET" & ¬
            " WHERE printf(\"%s/%s\",ZDIRECTORY,ZFILENAME) IN (\"" & joinText(missing_paths, "\",\"") & "\")'"
        set missing_ids to my splitText(result, return)
    end if
end timeout
delay 0.1

--
-- Launch Photos, locate the images by their ids and add them to the "missing images" album
--
tell application "Photos"
    set unlocated to {}
    set missing_items to {}
    set all_items to media items
    repeat with anID in missing_ids
        try
            if db_version = 2 then
                set anItem to media item id anID
            else
                set anItem to media item id (anID & "L0/001")
            end if
            set end of missing_items to anItem
        on error
            set end of unlocated to anID
        end try
    end repeat
    if missing_items is {} then
        my showMessage("Can't locate the images for the missing files." & return & return & ¬
            "Make sure you choose the same library that Photos.app currently uses!")
    else
        if unlocated is not {} then
            with timeout of 86400 seconds
                display dialog "" & (count of unlocated) & " missing items could not be found in Photos.app" & ¬
                    return & return & "This means that the script needs to be updated to handle this."
            end timeout
        end if
        if not (exists album "missing images") then
            make new album named "missing images"
        end if
        set dest to album "missing images"
        add missing_items to dest
        my showMessage("Found " & (count of missing_items) & " missing files")
    end if
end tell

--
-- Finished
--

on showMessage(theText)
    with timeout of 86400 seconds
        display dialog theText buttons "OK" default button "OK"
    end timeout
end showMessage

on splitText(theText, theDelimiter)
    set old_delimts to AppleScript's text item delimiters
    set AppleScript's text item delimiters to theDelimiter
    set theTextItems to every text item of theText
    set AppleScript's text item delimiters to old_delimts
    return theTextItems
end splitText

on joinText(theList, theDelimiter)
    set old_delimts to AppleScript's text item delimiters
    set AppleScript's text item delimiters to theDelimiter
    set theText to theList as string
    set AppleScript's text item delimiters to old_delimts
    return theText
end joinText
6
  • Looks promising. But it gives me an "error of type 100002" (after running for a while). Going by what the script editor highlights when it fails, the error occurs in the "Query the database again" part. More specifically, it is the else shell script that is highlighted.
    – Christoph
    Oct 19, 2022 at 8:15
  • 1
    Interacting with the Photos app via scripts seems to be a rather complex and error-prone process. The most advanced attempts at doing it anyway seem to be those by RhetTbull with the osxphotosapp and the python interface PhotoScript (see github.com/RhetTbull/osxphotos and github.com/RhetTbull/PhotoScript, as well as my answer below).
    – Christoph
    Oct 19, 2022 at 8:31
  • @Christoph Do you have Phots in the mode where it keeps all Originals on the Mac, or stores only compressed versions on the Mac? –– No idea why you'd get the error 100002, though. That part of the script is just a call into the sqlite3 command, which shouldn't fail. Unless it's simply a timeout by the script. Can you wrap that into "with timeout of 86400 seconds … end" and try again, please? Oct 19, 2022 at 15:08
  • I am not using iCloud for Photos at all, if that answers your question. I have tried your wrapper on the "do shell script ...." part, as well as on that plus "set missing_ids..." but no luck. It fails with the same 100002 error. Instead of timeout, could it be some kind of overflow? The number of missing files in my library is huge (several thousand)...
    – Christoph
    Oct 20, 2022 at 9:37
  • 1
    Yes, that's what I meant with my comment about this being an error-prone process: there is a reason why this hasn't been conclusively solved yet, so investing time in trying to fix this script is a risky investment. As much as I would like to see that script work, I totally understand why you don't want to pursue it further.
    – Christoph
    Oct 20, 2022 at 19:55

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .