#5 pictures getting skipped from backup as duplicates; not really duplicates

1 messages · Page 1 of 1 (latest)

fading nimbus
#

So I have 5 pictures that aren't backing up for some reason from iOS to Immich. The backup process seems to skip them, showing that they are duplicates. However, the assets themselves are nowhere to be found in immich; either in pictures for that date, or by searching for the filename even. The ID displayed in the backup screen is also not present in the assets table of the Postgres database. I'm at a loss here.

mild pantherBOT
#

:wave: Hey @fading nimbus,

Thanks for reaching out to us. Please follow the recommended actions below; this will help us be more effective in our support effort and leave more time for building Immich immich.

References

Checklist

  1. :ballot_box_with_check: I have verified I'm on the latest release(note that mobile app releases may take some time).
  2. :ballot_box_with_check: I have read applicable release notes.
  3. :ballot_box_with_check: I have reviewed the FAQs for known issues.
  4. :ballot_box_with_check: I have reviewed Github for known issues.
  5. :ballot_box_with_check: I have tried accessing Immich via local ip (without a custom reverse proxy).
  6. :blue_square: I have uploaded the relevant logs, docker compose, and .env files using the buttons below or the /upload command.
  7. :ballot_box_with_check: I have tried an incognito window, disabled extensions, cleared mobile app cache, logged out and back in, different browsers, etc. as applicable

(an item can be marked as "complete" by reacting with the appropriate number)

If this ticket can be closed you can use the /close command, and re-open it later if needed.

fading nimbus
#

Well I solved it by manually finding the duplicates, clearing the iOS duplicate blacklist, and quitting/reopening the app.

#

After clearing the blacklist initially and restarting the app, messages like this one showed up in the logs of immich_postgres:

# 
2024-04-08 00:26:39.135 UTC [2623] ERROR:  duplicate key value violates unique constraint "UQ_assets_owner_library_checksum"
2024-04-08 00:26:39.135 UTC [2623] DETAIL:  Key ("ownerId", "libraryId", checksum)=(3af8aab0-b80d-4ffb-a994-8d9bf33ddf2c, b5ff9ef7-9cce-4851-9317-1a0e95f9aca4, \x53601b05cd09b631c4eb0373c40842650da59513) already exists.
#

I identified rows that had the checksums in question like this:

# 
docker exec -it immich_postgres bash
su postgres
psql
\c immich
SELECT * FROM public.assets WHERE checksum::text = '\x53601b05cd09b631c4eb0373c40842650da59513';
#

Once I was sure I had the culprits, I deleted them from the database:
DELETE FROM public.assets WHERE checksum::text = '\x53601b05cd09b631c4eb0373c40842650da59513';

#

Hopefully this can help someone in a similar situation where some pictures aren't being backed up, yet aren't actually present in Immich. I'm blaming a failed past backup for this weird issue.

#

Perhaps the app could offer an option to force deletion/overwriting from the Postgres table if the user knows what they're doing?

rapid quail
#

What did the rows with that checksum look like..?

fading nimbus
#

They contained files that had the same basename, like IMG_0134, but a different extension. (.mov instead of .heic)

rapid quail
#

Only one row for each? Sounds like a motion photo

fading nimbus
#

It's like something weird happened with the backup of some live photos

rapid quail
#

Also, you probably have some orphan records now in the exif table …. Not sure if deleting those was the right call

#

The IDs are randomly generated as far as I know so I wouldn’t necessarily expect them to show up under that same ID if a duplicate

fading nimbus
fading nimbus
rapid quail
#

Ehhh the exif table contains GPS coords, so it’s JOINed to assets for views like the map I believe

fading nimbus
fading nimbus
rapid quail
#

Yeah, that is a good idea. I don’t assume you have the actual rows handy anymore?

#

To test you can probably pull up and map and see if any errors get logged

fading nimbus
fading nimbus
tidal shore
#

try search in the archive or trash page

rapid quail
#

Unfortunately the rows have already been deleted inside PG it seems ..

#

Unless that might show something?

fading nimbus
fading nimbus
tidal shore
#

@topaz flame From the error message, we see this constraint "ownerId", "libraryId", checksum) from what I remember, we don't use checksum to check for duplication for external libary, right? Or have we changed that recently?

rapid quail
#

This is not an external library, is it @fading nimbus ?

fading nimbus
#

No, just a standard library

#

I'm thinking I must have deleted the picture from the filesystem at some point?

#

I think at one point I was trying to re-upload some live pictures that were not linked properly (video and photo stored separately in immich)

#

And I (probably) deleted the picture from the filesystem.

#

I know that's not the way.

#

All I'm saying is, maybe the repair should catch if the video part of a live video is orphaned and remove it from the fs and the db

topaz flame
fading nimbus
# rapid quail Unfortunately the rows have already been deleted inside PG it seems ..

Alright, I ran zcat on my backups to see what changed after manually deleting the offending rows.
Before (this asset is nowhere to be found, not present in search, archive, etc.):
7a04bd8e-c965-4f01-b804-dd0134a9f7c3 E6D6589A-6E12-4077-923F-744CAA0F5D40/L0/001 3af8aab0-b80d-4ffb-a994-8d9bf33ddf2c 2454d69e77753fc3c69d974c1dbde90e8a5cedd1c8fe847835bb780ff54e3df9 VIDEO upload/library/admin/2023/07/IMG_4834.MOV.mov \N 2023-07-19 17:14:34+00 2024-03-26 17:53:43+00 f 0:00:00.000000 upload/encoded-video/3af8aab0-b80d-4ffb-a994-8d9bf33ddf2c/7a/04/7a04bd8e-c965-4f01-b804-dd0134a9f7c3.mp4 \\xef209f43b6603b947a50f12d2e2e52867f560fcf f \N 2024-03-27 17:17:02.220887+00 2024-03-27 10:54:41.083537+00 f IMG_4834.MOV \N \N f b5ff9ef7-9cce-4851-9317-1a0e95f9aca4 f \N 2023-07-19 17:14:34+00 \N