Immich•3y ago

Issue after docker update on Unraid

I upgraded the docker twice now but I am getting a migration error? If you need any other information let me know.

Migration "AddUserEmailUniqueConstraint1670633210032" failed, error: could not create unique index "UQ_97672ac88f789774dd47f7c8be3"
[Nest] 7  - 12/22/2022, 4:47:11 PM   ERROR [TypeOrmModule] Unable to connect to the database. Retrying (2)...
QueryFailedError: could not create unique index "UQ_97672ac88f789774dd47f7c8be3"
    at PostgresQueryRunner.query (/usr/src/app/node_modules/typeorm/driver/postgres/PostgresQueryRunner.js:211:19)
    at processTicksAndRejections (node:internal/process/task_queues:96:5)
    at async AddUserEmailUniqueConstraint1670633210032.up (/usr/src/app/dist/apps/immich/libs/database/src/migrations/1670633210032-AddUserEmailUniqueConstraint.js:9:9)
    at async MigrationExecutor.executePendingMigrations (/usr/src/app/node_modules/typeorm/migration/MigrationExecutor.js:178:17)
    at async DataSource.runMigrations (/usr/src/app/node_modules/typeorm/data-source/DataSource.js:248:35)
    at async DataSource.initialize (/usr/src/app/node_modules/typeorm/data-source/DataSource.js:137:17)

Migration "AddUserEmailUniqueConstraint1670633210032" failed, error: could not create unique index "UQ_97672ac88f789774dd47f7c8be3"
[Nest] 7  - 12/22/2022, 4:47:11 PM   ERROR [TypeOrmModule] Unable to connect to the database. Retrying (2)...
QueryFailedError: could not create unique index "UQ_97672ac88f789774dd47f7c8be3"
    at PostgresQueryRunner.query (/usr/src/app/node_modules/typeorm/driver/postgres/PostgresQueryRunner.js:211:19)
    at processTicksAndRejections (node:internal/process/task_queues:96:5)
    at async AddUserEmailUniqueConstraint1670633210032.up (/usr/src/app/dist/apps/immich/libs/database/src/migrations/1670633210032-AddUserEmailUniqueConstraint.js:9:9)
    at async MigrationExecutor.executePendingMigrations (/usr/src/app/node_modules/typeorm/migration/MigrationExecutor.js:178:17)
    at async DataSource.runMigrations (/usr/src/app/node_modules/typeorm/data-source/DataSource.js:248:35)
    at async DataSource.initialize (/usr/src/app/node_modules/typeorm/data-source/DataSource.js:137:17)

36 Replies

Alex Tran•3y ago

do you know whcih version you upgrade from? not sure what happen there,hmmm how many user do you have, do you use OAuth? my guess is that before we enforce the unique constraint for the user email, the same email has been used twice that is why the table cannot add the constraint

Mr RobotOP•3y ago

Yes, I had two users the same but deleted one. It was still there. I have no idea on my previous version. None use OAuth

jrasm91•3y ago

You will get this if there are two records in the DB with the same email. When you say deleted did you mean soft delete through the web admin page or db delete directly?

Mr RobotOP•3y ago

Soft delete through admin oage I believe it holds it for a little time?

jrasm91•3y ago

Yeah Does that user have any other data?

Mr RobotOP•3y ago

Nothing uploaded on that user

jrasm91•3y ago

You could try doing a db delete if you're comfortable or just wait for the 7 days to elapse. How long ago did you remove it?

Mr RobotOP•3y ago

A while back more that 7 days I can delete from db if needed

jrasm91•3y ago

There was a bug about users not being deleted, but I think that was recently fixed by Alex But you probably can't start up the server for that to work. The other option is to just update the email address so it's unique Then the migration should work and then the user delete should hopefully finish as expected.

Mr RobotOP•3y ago

No worries, I'll give it a go I'm in the databse I can see the tables but how do I see the users and remove the correct one?

Alex Tran•3y ago

can you go to the asset table and look at the userId column? if most/all of the asset has that same userId the other user ID would be the one that need to be deleted if you can capture the screenshot of the asset table and the user table we can help

Mr RobotOP•3y ago

Mr RobotOP•3y ago

currently here How do I list users?

Alex Tran•3y ago

select * from users;

Mr RobotOP•3y ago

and to drop by auth id?

Alex Tran•3y ago

can you show me the result if possible? if you worry abour privacy, you can do

select u.id from users u;

select u.id from users u;

so it will just show userId

Mr RobotOP•3y ago

I can see the one that says deleted at and is 2022-11-10

Alex Tran•3y ago

do you know which two ID has duplicated email address?

Mr RobotOP•3y ago

the ones ending in 6 and 5

Alex Tran•3y ago

can you copy and paste this one

select count(a.id), u.id
from assets a
left join users u on a."userId"::uuid = u."id"
group by u.id;

select count(a.id), u.id
from assets a
left join users u on a."userId"::uuid = u."id"
group by u.id;

Mr RobotOP•3y ago

exactly that?

Alex Tran•3y ago

yes, just to get the count of the asset of each user id to make sure we remove the correct one

Mr RobotOP•3y ago

Alex Tran•3y ago

so both of the email addresses that ending in 6 and 5 don't have any asset associate with them, is this correct?

Mr RobotOP•3y ago

Correct I can remove both and add back if need be

Alex Tran•3y ago

So we remove both ok

DELETE
FROM users
WHERE id = 'id-goes-here';

DELETE
FROM users
WHERE id = 'id-goes-here';

Mr RobotOP•3y ago

Lovely I'll do that now

immich=# DELETE
immich-# FROM users
immich-# WHERE id = '607abeb9-6886-4483-afb6-dac18098a155';
ERROR:  update or delete on table "users" violates foreign key constraint "FK_543c31211653e63e080ba882eb5" on table "user_shared_album"
DETAIL:  Key (id)=(607abeb9-6886-4483-afb6-dac18098a155) is still referenced from table "user_shared_album".
immich=#

immich=# DELETE
immich-# FROM users
immich-# WHERE id = '607abeb9-6886-4483-afb6-dac18098a155';
ERROR:  update or delete on table "users" violates foreign key constraint "FK_543c31211653e63e080ba882eb5" on table "user_shared_album"
DETAIL:  Key (id)=(607abeb9-6886-4483-afb6-dac18098a155) is still referenced from table "user_shared_album".
immich=#

I have shared and album with them though

Alex Tran•3y ago

ok we have to resolve that relationship in the user_shared_album table can you do

select * from user_shared_album;

select * from user_shared_album;

Mr RobotOP•3y ago

immich=# select * from user_shared_album;
 id |               albumId                |             sharedUserId             
----+--------------------------------------+--------------------------------------
  1 | f02e6bf1-573b-4816-bfe7-15be73ce9c98 | 607abeb9-6886-4483-afb6-dac18098a155
  2 | ed1b3fce-2f10-4110-9cb6-f0b5d6119382 | dc4bf3ef-8b9a-4621-805f-9fcd154d4276
  3 | a00b56a9-7c6e-4ad0-994e-a043d9448d47 | 607abeb9-6886-4483-afb6-dac18098a155
  4 | a00b56a9-7c6e-4ad0-994e-a043d9448d47 | dc4bf3ef-8b9a-4621-805f-9fcd154d4276
  5 | a00b56a9-7c6e-4ad0-994e-a043d9448d47 | 4311fdcf-09ee-451c-84ea-bf3f5d9717c2
  6 | fcfec920-bf05-4032-b8da-3e544ada163a | 607abeb9-6886-4483-afb6-dac18098a155
  7 | fcfec920-bf05-4032-b8da-3e544ada163a | dc4bf3ef-8b9a-4621-805f-9fcd154d4276
  8 | fcfec920-bf05-4032-b8da-3e544ada163a | 4311fdcf-09ee-451c-84ea-bf3f5d9717c2
  9 | faae0d7a-fdf3-46b7-a786-8cb75ee95f42 | dc4bf3ef-8b9a-4621-805f-9fcd154d4276
 10 | faae0d7a-fdf3-46b7-a786-8cb75ee95f42 | 607abeb9-6886-4483-afb6-dac18098a155
 11 | faae0d7a-fdf3-46b7-a786-8cb75ee95f42 | 4311fdcf-09ee-451c-84ea-bf3f5d9717c2
(11 rows)

immich=#

immich=# select * from user_shared_album;
 id |               albumId                |             sharedUserId             
----+--------------------------------------+--------------------------------------
  1 | f02e6bf1-573b-4816-bfe7-15be73ce9c98 | 607abeb9-6886-4483-afb6-dac18098a155
  2 | ed1b3fce-2f10-4110-9cb6-f0b5d6119382 | dc4bf3ef-8b9a-4621-805f-9fcd154d4276
  3 | a00b56a9-7c6e-4ad0-994e-a043d9448d47 | 607abeb9-6886-4483-afb6-dac18098a155
  4 | a00b56a9-7c6e-4ad0-994e-a043d9448d47 | dc4bf3ef-8b9a-4621-805f-9fcd154d4276
  5 | a00b56a9-7c6e-4ad0-994e-a043d9448d47 | 4311fdcf-09ee-451c-84ea-bf3f5d9717c2
  6 | fcfec920-bf05-4032-b8da-3e544ada163a | 607abeb9-6886-4483-afb6-dac18098a155
  7 | fcfec920-bf05-4032-b8da-3e544ada163a | dc4bf3ef-8b9a-4621-805f-9fcd154d4276
  8 | fcfec920-bf05-4032-b8da-3e544ada163a | 4311fdcf-09ee-451c-84ea-bf3f5d9717c2
  9 | faae0d7a-fdf3-46b7-a786-8cb75ee95f42 | dc4bf3ef-8b9a-4621-805f-9fcd154d4276
 10 | faae0d7a-fdf3-46b7-a786-8cb75ee95f42 | 607abeb9-6886-4483-afb6-dac18098a155
 11 | faae0d7a-fdf3-46b7-a786-8cb75ee95f42 | 4311fdcf-09ee-451c-84ea-bf3f5d9717c2
(11 rows)

immich=#

Again happy to delete all

Alex Tran•3y ago

we can just delete the necessary one

delete from user_shared_album
where "sharedUserId" = 'user-id-goes-here';

delete from user_shared_album
where "sharedUserId" = 'user-id-goes-here';

Mr RobotOP•3y ago

Nice all working now|

Alex Tran•3y ago

OKay migration went through and update works?

Mr RobotOP•3y ago

Yeah, no more errors and can log in. Thank you for the help!

Alex Tran•3y ago

No problem, happy holiday!

Mr RobotOP•3y ago

Same goes for you!

Gaming

Programming

Issue after docker update on Unraid

Did you find this page helpful?