Is there someway to get a list of all missing photos from the photo library?

Question

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.

Answer 1

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.

Answer 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.

Answer 3

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.

Answer 4

(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

Answer 5

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