#How to remigrate or dump "person" table

1 messages · Page 1 of 1 (latest)

tired birch
#

Hello all, I've done some digging and I think I understand this issue and how to resolve it, but I will need a little assistance.

I am running Immich via docker. I've attached the logs from the immich_server container as well as the immich_postgres containers. I believe the issue is that a section of the "person" table is corrupted, resulting in this memory allocation error; obviously I cannot allocate 18446744057.229267 GB of RAM.

psql (14.10 (Debian 14.10-1.pgdg120+1))
Type "help" for help.

immich=# select count(*) from assets;
 count
-------
  6918
(1 row)

immich=# select count(*) from asset_faces;
 count
-------
  5564
(1 row)

immich=# select count(*) from person;
ERROR:  invalid memory alloc request size 18446744057229267712

If possible, I'd like to fix this issue by wiping the "person" database, or at least a section of it, and I am not concerned about losing "person" or related face recognition data. Unfortunately, I've checked my $UPLOAD_LOCATION/backups directory and there are no backups listed there, despite having enabled backups (screenshot attached).

Please let me know if there's any additional information I can provide. I believe the database corruption could be due to me performing a hard power reset of the machine due to other issues.

Thank you for your help!

📎 immich_server_logs image.png
atomic knotBOT
#

:wave: Hey @tired birch,

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

#

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.

atomic knotBOT
sturdy jolt
#

Try taking a backup manually

atomic knotBOT
sturdy jolt
#

I'm guessing it errors out for this same reason

#

I think you should be fine to just delete & recreate the person table, then rerun the jobs, but I'm not entirely sure about the details so I'll tag in @clever surge for that 😇

tired birch
#

It doesn’t error out but it only creates a 4Kb file so I’m thinking something else is wrong

#

When I try to create a backup manually that is

clever surge
#

The "reset" option for face detection in the job panel will truncate the table and re-process all assets. That might fix the issue

tired birch
#

I believe this fails due to the same error. Here' s the logs:

# 
[Nest] 16  - 12/28/2024, 8:11:15 PM     LOG [Api:EventRepository] Websocket Disconnect: PaIRfDTzJ89uaxtMAAAB
[Nest] 16  - 12/28/2024, 8:11:16 PM     LOG [Api:EventRepository] Websocket Connect:    jW7nWcRVbVyLO-UOAAAD
[Nest] 16  - 12/28/2024, 8:11:21 PM   ERROR [Api:ErrorInterceptor~tjlnfllj] Database error: QueryFailedError: invalid memory alloc request size 18446744057229267712
[Nest] 16  - 12/28/2024, 8:17:51 PM     LOG [Api:EventRepository] Websocket Disconnect: jW7nWcRVbVyLO-UOAAAD
[Nest] 16  - 12/28/2024, 8:17:52 PM     LOG [Api:EventRepository] Websocket Connect:    bdtuGKRLLVZsyVfXAAAF
[Nest] 6  - 12/28/2024, 8:36:16 PM   ERROR [Microservices:JobService] Unable to run job handler (faceDetection/queue-face-detection): QueryFailedError: invalid memory alloc request size 18446744057229267712
[Nest] 6  - 12/28/2024, 8:36:16 PM   ERROR [Microservices:JobService] QueryFailedError: invalid memory alloc request size 18446744057229267712
    at PostgresQueryRunner.query (/usr/src/app/node_modules/typeorm/driver/postgres/PostgresQueryRunner.js:219:19)
    at process.processTicksAndRejections (node:internal/process/task_queues:105:5)
    at async DeleteQueryBuilder.execute (/usr/src/app/node_modules/typeorm/query-builder/DeleteQueryBuilder.js:52:33)
    at async PersonRepository.deleteFaces (/usr/src/app/dist/repositories/person.repository.js:68:9)
    at async PersonService.handleQueueDetectFaces (/usr/src/app/dist/services/person.service.js:194:13)
    at async JobService.onJobStart (/usr/src/app/dist/services/job.service.js:148:28)
    at async EventRepository.onEvent (/usr/src/app/dist/repositories/event.repository.js:134:13)
    at async Worker.processJob (/usr/src/app/node_modules/bullmq/dist/cjs/classes/worker.js:394:28)
    at async Worker.retryIfFailed (/usr/src/app/node_modules/bullmq/dist/cjs/classes/worker.js:581:24)
[Nest] 6  - 12/28/2024, 8:36:16 PM   ERROR [Microservices:JobService] Object:
{
  "force": true
}
clever surge
#

Oh, it doesn't actually truncate anymore. It does a filtered delete + vacuum to avoid deleting faces sourced from metadata

#

Try TRUNCATE asset_faces CASCADE;

tired birch
#

I think that's worked. Now I can just re-scan for faces and do that all over again, thanks!

clever surge
#

No problem!

latent vector
#

I would be very cautious in that there is probably other corruption in your database, FWIW

#

so keep a close eye to see if dumps are able to be taken now

tired birch
#

Hm, I think that running the "reset" job on face detection results in "No People" being shown on the people page

#

Seems like the table is still empty as well

# 

immich=# select count(*) from person;
 count
-------
     0
(1 row)

immich=# select count(*) from asset_faces;
 count
-------
   150
(1 row)
clever surge
#

It's processing assets right now. The facial recognition part will come once it's finished with face detection

tired birch
#

Sorry, one more update. Faces now are working and re-generated, all I have to do is add names back. The immich_postgres services is failing healthcheck though, and here's what happens when I try to run a manual backup:

> docker exec -t immich_postgres pg_dumpall --clean --if-exists --username=postgres | cat
WARNING:  page verification failed, calculated checksum 16088 but expected 37743
WARNING:  page verification failed, calculated checksum 16088 but expected 37743
pg_dumpall: error: query failed: ERROR:  invalid page in block 0 of relation base/1/1247
pg_dumpall: error: query was: SELECT pg_catalog.set_config('search_path', '', false);
latent vector
#

Unlikely that this is recoverable IMO

tired birch
#

I see

#

Well thank you for the help anyways

latent vector
#

It’s possible someone else has some ideas

#

But that error indicates missing data in a way that’s not easily fixable