I
Immich•5d ago
jaeyr

Error migrating from v1.135.3

Hi all, I've been trying to migrate from v1.135.3 for a while now but I keep encountering this issue: 
PostgresError: insert or update on table "asset" violates foreign key constraint "asset_livePhotoVideoId_fkey", stack: PostgresError: insert or update on table "asset" violates foreign key constraint "asset_livePhotoVideoId_fkey"
PostgresError: insert or update on table "asset" violates foreign key constraint "asset_livePhotoVideoId_fkey", stack: PostgresError: insert or update on table "asset" violates foreign key constraint "asset_livePhotoVideoId_fkey"
I've tried to restore from backups into a new postgres and immich instance but it doesnt work. Issue seems to resolve if I revert back to v1.135.3.

env

log.txt

compose.yaml

2 Replies
Immich
Immich•5d ago
:wave: Hey @jaeyr, Thanks for reaching out to us. Please carefully read this message and follow the recommended actions. This will help us be more effective in our support effort and leave more time for building Immich :immich:. References - Container Logs: docker compose logs docs - Container Status: docker ps -a docs - Reverse Proxy: https://immich.app/docs/administration/reverse-proxy - Code Formatting https://support.discord.com/hc/en-us/articles/210298617-Markdown-Text-101-Chat-Formatting-Bold-Italic-Underline#h_01GY0DAKGXDEHE263BCAYEGFJA Checklist I have... 1. :ballot_box_with_check: verified I'm on the latest release(note that mobile app releases may take some time). 2. :ballot_box_with_check: read applicable release notes. 3. :ballot_box_with_check: reviewed the FAQs for known issues. 4. :ballot_box_with_check: reviewed Github for known issues. 5. :ballot_box_with_check: tried accessing Immich via local ip (without a custom reverse proxy). 6. :ballot_box_with_check: uploaded the relevant information (see below). 7. :ballot_box_with_check: 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) Information In order to be able to effectively help you, we need you to provide clear information to show what the problem is. The exact details needed vary per case, but here is a list of things to consider: - Your docker-compose.yml and .env files. - Logs from all the containers and their status (see above). - All the troubleshooting steps you've tried so far. - Any recent changes you've made to Immich or your system. - Details about your system (both software/OS and hardware). - Details about your storage (filesystems, type of disks, output of commands like fdisk -l and df -h). - The version of the Immich server, mobile app, and other relevant pieces. - Any other information that you think might be relevant. Please paste files and logs with proper code formatting, and especially avoid blurry screenshots. Without the right information we can't work out what the problem is. Help us help you ;) If this ticket can be closed you can use the /close command, and re-open it later if needed.
jaeyr
jaeyrOP•4d ago
I think I resolved the issue using chatgpt but maybe someone with more experience can correct me if im wrong. the commands I used 
# Snapshot the probelm rows
\copy (
  SELECT a.id AS asset_id, a."livePhotoVideoId", a."originalFileName", a."originalPath"
  FROM public."assets" a
  LEFT JOIN public."assets" v ON v.id = a."livePhotoVideoId"
  WHERE a."livePhotoVideoId" IS NOT NULL
    AND v.id IS NULL
) TO 'orphan_livephoto_before.csv' CSV HEADER;


# Null bad foreign keys
BEGIN;

WITH bad AS (
  SELECT a.id
  FROM public."assets" a
  LEFT JOIN public."assets" v ON v.id = a."livePhotoVideoId"
  WHERE a."livePhotoVideoId" IS NOT NULL
    AND v.id IS NULL
)
UPDATE public."assets" a
SET "livePhotoVideoId" = NULL
FROM bad
WHERE a.id = bad.id;

-- Confirm it's clean (should return 0 rows)
SELECT a.id, a."livePhotoVideoId"
FROM public."assets" a
LEFT JOIN public."assets" v ON v.id = a."livePhotoVideoId"
WHERE a."livePhotoVideoId" IS NOT NULL
  AND v.id IS NULL;

COMMIT;
# Snapshot the probelm rows
\copy (
  SELECT a.id AS asset_id, a."livePhotoVideoId", a."originalFileName", a."originalPath"
  FROM public."assets" a
  LEFT JOIN public."assets" v ON v.id = a."livePhotoVideoId"
  WHERE a."livePhotoVideoId" IS NOT NULL
    AND v.id IS NULL
) TO 'orphan_livephoto_before.csv' CSV HEADER;


# Null bad foreign keys
BEGIN;

WITH bad AS (
  SELECT a.id
  FROM public."assets" a
  LEFT JOIN public."assets" v ON v.id = a."livePhotoVideoId"
  WHERE a."livePhotoVideoId" IS NOT NULL
    AND v.id IS NULL
)
UPDATE public."assets" a
SET "livePhotoVideoId" = NULL
FROM bad
WHERE a.id = bad.id;

-- Confirm it's clean (should return 0 rows)
SELECT a.id, a."livePhotoVideoId"
FROM public."assets" a
LEFT JOIN public."assets" v ON v.id = a."livePhotoVideoId"
WHERE a."livePhotoVideoId" IS NOT NULL
  AND v.id IS NULL;

COMMIT;
I ran the above commands in the postgres container with the immich container created but not started/running. i only upgraded to the latest release when i finished running the commands and then restarted the entire stack. didnt see any errors in my docker logs when i did so, didnt see any issues on my immich web either. i'd like to think this means i resolved the issue but if any of the staff here is free, i'd appreciate someone going through this to validate my results

Did you find this page helpful?