Repair missing Folder Structure in broken Apple Photos DB

[jotzet79]

Dear @RhetTbull & developers,

Recently I had to switch off "iCloud Photo Library" on my iPad (as suggested by Apple Support to fix issues with face detection). After that I (manually) wiped all pictures from the iPad, in order to be able to start with a clean, empty lib that I could then sync again. Unfortunately I had to clean not only Pictures, but also the remainders / stubs of then empty Folders and Albums, because they were not deleted automatically.

So far so good... After this procedure I switched my iPad on and off, to assert that they are no left overs / hanging processes. Then I started the sync again by switching iCloud Photo Library ON again.

Next day -> nightmare: On all synced devices / Web iCloud.com all folders and sub-folders, as well as the albums were gone. 32000 pictures without anymore folder/album structure(s).

Wait, no - obviously when SEARCHING (no matter which device) for an album name, I can find this specific Album as search result (and the pictures within). But the Album/Folder does not appear in the left panel (Mac; or the Albums section on iDevices) anymore.

That's why I tried out osxphotos's REPL mode:
for album in photosdb.album_info: print(album.uuid, album.title, album.folder_names)

400+ Results - yeah
2069CFFD-B1B1-4E7C-A3EC-442DE1F05DE8 2008-01 - Berlin ['1111 - Various', '1111 - Staging', 'Events', '2008', '2008-01 - Berlin']

But
for folder in photosdb.folder_info: print(folder.uuid, folder.title)

Does NOT return the complete Folder Hierarchy anymore, although the Album from above DOES return a folder info '/1111 - Various/1111 - Staging/Events/2008/2008-01 - Berlin'.

My plan then was to recreate either the missing folder structure, or recreate album with the same name, and copy pictures to the newly created album, and wiping the old one.: I wanted to iterate those Album UUIDs using RhetTbull's PhotoScript Python lib, eg. by trying to select one pic in this mystic container:

selected = photoslib.selection
for i in selected:
    print(i.uuid)
    print(i.name)
    print(len(i.albums))

print(photoslib.album("2069CFFD-B1B1-4E7C-A3EC-442DE1F05DE8"))

Output:

A6134D7A-4AEF-4DF8-9622-A9E2437A135A

0
None

Is there some option to recreate the folder structure manually somehow / hack the sqlite DBs?

THANKS for any pointers,
Jo

PS: Of course I contacted Apple Support, but no real help here
PPS: Apparently I'm not alone -> https://discussions.apple.com/thread/251172018 / https://discussions.apple.com/thread/8303360

[RhetTbull]

@jotzet79 sorry to hear of this unfortunate situation! I do not recommend hacking on the sqlite DB itself as the schema is quite complex. The first thing I recommend you do is backup the entire .photoslibrary folder immediately. It seems some of the data is still in the DB so make a backup so that osxphotos can access this data even if Apple does something to it.

Then you can write a script using both photoscript and osxphotos to do what you want. Use osxphotos to walk through each album, get the album.folder_names, then go through each folder and create a folder using photoscript's PhotosLibrary().create_folder() then create the album in the folder using photoscript's PhotosLibrary().create_album(). See the photoscript docs for reference.
Then add the photo to the album using photoscript.

I think that by using both osxphotos to read the info that's still there and photoscript to recreate the folder structure you'll be able to fix this. BUT make a backup first!

If you get stuck on any of the code, let me know and I'll be glad to take a look. Good luck!

[RhetTbull]

To clarify a bit, photoscript communicates directly with Photos using AppleScript and will only be able to see the same information as appears in Photos. That's why the missing album info doesn't show up in your second code snippet. But osxphotos apparently can see the missing data by parsing the database so you'll need to use both to solve the problem.

If the library is large and backup is a problem, you can backup just the Photos.sqlite file from inside the Photos Library.photoslibrary/database directory or better yet, backup the entire database directory.

[RhetTbull]

@jotzet79 I think the following script will do what you want. I've not been able to fully test it as I don't have a corrupted library as you do. Make a backup of your library before trying the script! This will be slow (AppleScript is slow and thus, so is PhotoScript). Save the following to a file (e.g. fix_photos_folders.py) then run it. You'll need to pip install osxphotos if not already done so.

$ python3 fix_photos_folders.py   --help
Usage: fix_photos_folders.py [OPTIONS]

  Process all the photos in the library and add them to albums and folders as
  needed

Options:
  --library PATH  Path to the Photos library
  --dry-run       Don't modify anything, dry-run only
  --help          Show this message and exit.

Update: fixed an error. Tested this by adding a delay, deleting some folders and albums and then letting it run. The folder and album structure was correctly re-created.

""" fix_photos_folders.py: Fix missing folders in Apple photos, reference: https://github.com/RhetTbull/osxphotos/discussions/503"""

import click
import photoscript

import osxphotos


@click.command()
@click.option(
    "--library", type=click.Path(exists=True), help="Path to the Photos library"
)
@click.option("--dry-run", is_flag=True, help="Don't modify anything, dry-run only")
def main(library, dry_run):
    """Process all the photos in the library and add them to albums and folders as needed"""
    click.echo(f"Opening Photos library")

    # get PhotosDB object for the library to read the database with osxphotos
    photosdb = osxphotos.PhotosDB(library)
    photos = photosdb.photos()

    click.echo(f"Processing {len(photos)} photos")

    # get library object to modify the library with photoscript
    photoslib = photoscript.PhotosLibrary()

    # process each photo
    for photo in photos:
        # if photo is not in an album, it'll be skipped here
        for album in photo.album_info:
            folder_names = album.folder_names

            # if album is in a folder, create the folder structure if necessary
            # then create the album if necessary
            new_album = None
            if folder_names:
                click.echo(
                    f"Adding photo {photo.original_filename} ({photo.uuid}) to album '{album.title}' in folders '{'/'.join(folder_names)}'"
                )
                if not dry_run:
                    new_album = photoslib.make_album_folders(
                        album.title, album.folder_names
                    )
            else:
                click.echo(
                    f"Adding photo {photo.original_filename} ({photo.uuid}) to top-level album '{album.title}'"
                )
                if not dry_run:
                    new_album = photoslib.album(album.title, top_level=True)
                    if not new_album:
                        new_album = photoslib.create_album(album.title, folder=None)
            if new_album is None and not dry_run:
                click.secho(
                    f"Error creating album {album.title} in folders '{'/'.join(folder_names)}'",
                    err=True,
                    fg="red",
                )
                continue
            if not dry_run:
                try:
                    # add the photo to the album
                    new_album.add([photoscript.Photo(photo.uuid)])
                except Exception as e:
                    click.secho(
                        f"Error adding photo {photo.original_filename} ({photo.uuid}) to album '{'/'.join([*folder_names, album.title])}': {e}",
                        err=True,
                        fg="red",
                    )


if __name__ == "__main__":
    main()

[RhetTbull]

You'll need to python3 -m pip install osxphotos to run the script. osxphotos will install the other prerequisites (click and photoscript)

[jotzet79]

Dear @RhetTbull ,

First of all, sorry that I didn't manage to answer sooner (soon to be father), and THANK YOU VERY MUCH for your script and immediate help.
I was trying out your script with a few changes, and limiting to only pictures out of a few albums (e.g. based on their filename), which indeed worked out. 🎉

However it could be a bit slow for 32k of photos, and somehow it felt strange to recreate the albums that are already there... So again I did some research to understand Apple's sqlite Dbs in more detail. And in the end I was successfully hacking the Photos.db.

First I searched for the albums in ZGENERICALBUM Table, and I found out that apparently Apple never ever deletes an album physically from the db, but sets only the ZTRASHEDSTATE flag. Of course osxphotos led me in to this direction, that obviously there must be some traces left.

When these albums are updated by ZTRASHEDSTATE to '0', the albums and folders showed up again IMMEDIATELY in MacOS Photos.
However my problem then was to propagate the manual change in the DB to the Cloud (and therefore all other devices), because whatever I tried I failed: E.g trying to create an album and delete it in the iCloud Web Version and comparing the diffs after the change was handled in the local DB using sqldiff. But this seemed to be too difficult to mimic the trigger manually for such iCloud syncs.

Luckily a simple creation of a top-level folder and move of affected albums (which are not yet visible on the other devices / web, but on the local Mac) to this new Folder did the trick and the changes got synced, and suddenly the missing folders appeared again on all other devices. 💥

Whenever somebody bumps on this issue (which was definitely caused by Apple's wrong API calls from the beginning), this might be of help at least for posterity.

As you suggested backup the photos.db file(s) for safety...

sqlite3 /Users/USERID/Pictures/Photos.photoslibrary/database/photos.db 
.mode column
.headers on
.width 40
### Find your missing albums
select ZTITLE,Z_PK,ZPARENTFOLDER,Z_FOK_PARENTFOLDER,ZKIND,ZUUID,ZCLOUDDELETESTATE,ZCLOUDLOCALSTATE,ZTRASHEDSTATE from ZGENERICALBUM where ZTITLE like '%[FILTEREXPRESSION/keyword/albumname]%';
ZTITLE                                    Z_PK        ZPARENTFOLDER  Z_FOK_PARENTFOLDER  ZKIND       ZUUID                                 ZCLOUDDELETESTATE  ZCLOUDLOCALSTATE  ZTRASHEDSTATE
----------------------------------------  ----------  -------------  ------------------  ----------  ------------------------------------  -----------------  ----------------  -------------
1111 - Staging                            197         1647           6144                4000        883C12B1-4CE8-4228-9162-C82C33A64F1F  0                  1                 1            
1111 - Wipe Folder Level 1                4800        3              38154               4000        B71DBD53-328B-4F3C-8BC5-808976ED0745  0                  1                 0            
1111 - Wipe Folder Level 2                4801        4800           2048                4000        52A7C49B-DAD5-405B-B59A-3B0B532EB162  0                  1                 0            
1111 - Wipe Folder Level 3.1              4802        4801           2048                4000        9716778C-FA6F-4379-8D40-1D45EFF5D666  0                  1                 0            


### Update the TRASHED State Flag where it applies
update ZGENERICALBUM set ZTRASHEDSTATE = '0' where [FILL IN YOUR FILTER]

select ZTITLE,Z_PK,ZPARENTFOLDER,Z_FOK_PARENTFOLDER,ZKIND,ZUUID,ZCLOUDDELETESTATE,ZCLOUDLOCALSTATE,ZTRASHEDSTATE from ZGENERICALBUM where ZTITLE like '%1111%';
ZTITLE                                    Z_PK        ZPARENTFOLDER  Z_FOK_PARENTFOLDER  ZKIND       ZUUID                                 ZCLOUDDELETESTATE  ZCLOUDLOCALSTATE  ZTRASHEDSTATE
----------------------------------------  ----------  -------------  ------------------  ----------  ------------------------------------  -----------------  ----------------  -------------
1111 - Staging                            197         1647           6144                4000        883C12B1-4CE8-4228-9162-C82C33A64F1F  0                  1                 0            
1111 - Wipe Folder Level 1                4800        3              38154               4000        B71DBD53-328B-4F3C-8BC5-808976ED0745  0                  1                 0            
1111 - Wipe Folder Level 2                4801        4800           2048                4000        52A7C49B-DAD5-405B-B59A-3B0B532EB162  0                  1                 0            
1111 - Wipe Folder Level 3.1              4802        4801           2048                4000        9716778C-FA6F-4379-8D40-1D45EFF5D666  0                  1                 0            

Again big up to you, your helpful attitude and of course your amazing tools!!!
Stay healthy, kinds regards
Joachim

[RhetTbull]

Thanks for the explanation. Glad you got this issue fixed!

[mPloid]

Parse error: no such table: ZGENERICALBUM
I know this post is two years old. I don't know much about how the Photos app works but I seem to be having the same problem. I used Time Machine to restore to an older version of the Library but iCloud keeps overriding/deleting the albums. I restored to an even older version of Library and it's syncing at the moment with all my albums still intact.

[RhetTbull]

Edit: see updated commands below to include library path

Parse error: no such table: ZGENERICALBUM

What were you attempting to do? What steps led to this error? What's the exact command line that led to the error?

I think your issue is different than this one (corrupted database). It sounds like you deleted albums then synced to iCloud. Restoring from a Time Machine backup won't work because iCloud likely synced the album deletions.

I restored to an even older version of Library and it's syncing at the moment with all my albums still intact.

It's possible this could trigger iCloud to recreate the albums but you may find that the albums eventually get deleted.

If you deleted the albums and it synced to iCloud then the deletion also got synced so restoring from backup won't work. iCloud will eventually bring the backup to the present state without the albums. However, all hope is not lost. You can use osxphotos to fix this.

First, restore a copy of the Photos library from Time Machine from before the deletion. (turn off Wifi/networking so nothing syncs to iCloud)

Install osxphotos via these steps then run these commands in the terminal:

osxphotos sync --export sync.db --library /path/to/restored/library.photoslibrary

This creates a database with all the metadata for each photo (including album/folder structure).

Then re-enable networking and allow Time Machine to fully sync. Once that happens, you can run this command in the Terminal to restore the missing albums:

osxphotos sync --import sync.db --set albums --report sync.csv

This reads the metadata from the exported sync database, finds the matching photos in the new library, then updates the albums to match. If only some albums are messed up or you've made new albums that got synced to iCloud in the mean time, you can use this to merge albums instead of overwriting what's in Photos:

osxphotos sync --import sync.db --merge albums --report sync.csv

When finished, the file sync.csv will contain a list of all files that were updated and what was done (e.g. which albums they were moved to).

[mPloid]

Hey, if you haven’t noticed, I’m the same person you replied to on Reddit. 😅

I’ll just repeat what I said there in case anyone is curious to know what went down: When I restored to the even older version of the Photos Library using Time Machine, somehow iCloud didn’t override the deletions. In the afternoon, Photos appeared to re-sync my whole library, based on the item count on the bottom. The albums are now on all my devices again and there seems to be no lost photos or videos.