I
Immich•5mo ago
cambelau

Database Migration Error with v1.136.0

I'm encountering a database migration error when upgrading to Immich v1.136.0. The server container continually restart due to a failed migration. Error Details: The migration "1752267649968-StandardizeNames" fails with: PostgresError: relation "session" already exists sql: 'ALTER TABLE "sessions" RENAME TO "session"' My Environment: - Immich version: 1.136.0 - Deployment method: Docker Compose - Database: PostgreSQL ( Steps I've Already Tried: 1. Examined both tables: - sessions appears to be the main sessions table with user data - session was a simple key-value table with only 3 columns (key, data, expiry) 2. Backed up and dropped the session table manually in PostgreSQL 3. Restarted containers, but the migration still fails with the same error My docker-compose configuration: ```yaml services: immich-server: container_name: immich_server image: ghcr.io/immich-app/immich-server:${IMMICH_VERSION:-release} environment: DB_HOSTNAME: immich_postgres DB_PASSWORD: ${IMMICH_DB_PASSWORD} DB_USERNAME: postgres DB_DATABASE_NAME: immich postgres: postgres immich: immich REDIS_HOSTNAME: immich_redis volumes: - ${IMMICH_UPLOAD_LOCATION}:/usr/src/app/upload - /etc/localtime:/etc/localtime:ro depends_on: - immich-redis - immich-database restart: always immich-database: container_name: immich_postgres image: ghcr.io/immich-app/postgres:14-vectorchord0.3.0-pgvectors0.2.0 environment: DB_HOSTNAME: immich_postgres POSTGRES_PASSWORD: ${IMMICH_DB_PASSWORD} POSTGRES_USER: postgres POSTGRES_DB: immich POSTGRES_INITDB_ARGS: '--data-checksums' DB_STORAGE_TYPE: 'HDD' volumes: - ${CONFIG_ROOT:-.}/immich/postgresql:/var/lib/postgresql/data restart: always profiles: - immich
24 Replies
Immich
Immich•5mo ago
:wave: Hey @cambelau, 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. :blue_square: read applicable release notes. 3. :blue_square: reviewed the FAQs for known issues. 4. :blue_square: reviewed Github for known issues. 5. :blue_square: tried accessing Immich via local ip (without a custom reverse proxy). 6. :blue_square: uploaded the relevant information (see below). 7. :blue_square: 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. Successfully submitted, a tag has been added to inform contributors. :white_check_mark:
cambelau
cambelauOP•5mo ago
Thanks to everyone who will take time to help me !
Sergey Katsubo
Sergey Katsubo•5mo ago
Hey @cambelau
Backed up and dropped the session table manually in PostgreSQL
Could you share that backup or simply post select * from session limit 3; ?
cambelau
cambelauOP•5mo ago
Session table is not created, so i cannot query it. The sessions table have those row (i replaced some field for safety) 
id                  |                    token                     |           createdAt           |           updatedAt           |                userId                | deviceType | deviceOS |               updateId               | pinExpiresAt | expiresAt | parentId
--------------------------------------+----------------------------------------------+-------------------------------+-------------------------------+--------------------------------------+------------+----------+--------------------------------------+--------------+-----------+----------
 4xx1a | token= | 2024-10-04 12:07:15.967266+00 | 2025-05-25 12:41:04.346+00    | 9xxxxd | Chrome     | Windows  | 019wwwcd96e |              |           |
 exxx87 | token= | 2024-11-18 07:09:01.156405+00 | 2025-06-21 01:26:04.878337+00 | 9xxxd | iPhone15,2 | iOS      | 019wwwcffe2 |              |           |
 bxxx5 | token= | 2024-12-12 14:18:51.314967+00 | 2025-07-17 09:47:11.166109+00 | 9xxxxd | Chrome     | Windows  | 01981wwww70 |              |           |
(3 rows)
id                  |                    token                     |           createdAt           |           updatedAt           |                userId                | deviceType | deviceOS |               updateId               | pinExpiresAt | expiresAt | parentId
--------------------------------------+----------------------------------------------+-------------------------------+-------------------------------+--------------------------------------+------------+----------+--------------------------------------+--------------+-----------+----------
 4xx1a | token= | 2024-10-04 12:07:15.967266+00 | 2025-05-25 12:41:04.346+00    | 9xxxxd | Chrome     | Windows  | 019wwwcd96e |              |           |
 exxx87 | token= | 2024-11-18 07:09:01.156405+00 | 2025-06-21 01:26:04.878337+00 | 9xxxd | iPhone15,2 | iOS      | 019wwwcffe2 |              |           |
 bxxx5 | token= | 2024-12-12 14:18:51.314967+00 | 2025-07-17 09:47:11.166109+00 | 9xxxxd | Chrome     | Windows  | 01981wwww70 |              |           |
(3 rows)
From logs i can see that i have different errors at each restart : PostgresError: relation "session" already exists microservices worker error: PostgresError: relation "session" already exists, stack: PostgresError: relation "session" already exists error: PostgresError: relation "user" already exists PostgresError: relation "user" already exists microservices worker error: PostgresError: relation "user" already exists, stack: PostgresError: relation "user" already exists Both in the same migration job Migration "1752267649968-StandardizeNames"
Sergey Katsubo
Sergey Katsubo•5mo ago
Interesting, so it complains about more than 1 table already existing... 1. Could you post your DB migration history and update history by querying the database: 
docker exec immich_postgres psql -d immich -U postgres -c "select * from kysely_migrations" -c "select * from version_history"
docker exec immich_postgres psql -d immich -U postgres -c "select * from kysely_migrations" -c "select * from version_history"
2. Do you still have logs from the first upgrade attempt in docker compose logs? Right after you changed to / pulled new version and did "compose up". Would be nice to view them. If they are already gone, then please post logs from one of restarts/attempts. 2.1. You can increase logging verbosity, it could be helpful, https://immich.app/docs/install/environment-variables/#general 
IMMICH_LOG_LEVEL=debug
# or even more detailed
IMMICH_LOG_LEVEL=verbose
IMMICH_LOG_LEVEL=debug
# or even more detailed
IMMICH_LOG_LEVEL=verbose
3. Regarding session
Examined both tables: sessions appears to be the main sessions table with user data session was a simple key-value table with only 3 columns (key, data, expiry)
So session table existed at some point, then you dropped it, then on subsequent upgrade attempts: >Session table is not created right?
cambelau
cambelauOP•5mo ago
I joined logs from the start and the restart, and the version-history (that looks good i think) I dont have the first upgrade attempt sadly :/

logs.txt

version-history.txt

cambelau
cambelauOP•5mo ago
Yes regarding session its what happened and the actual state of both table is this one : 
immich=# \d sessions
                                Table "public.sessions"
    Column    |           Type           | Collation | Nullable |        Default
--------------+--------------------------+-----------+----------+-----------------------
 id           | uuid                     |           | not null | uuid_generate_v4()
 token        | character varying        |           | not null |
 createdAt    | timestamp with time zone |           | not null | now()
 updatedAt    | timestamp with time zone |           | not null | now()
 userId       | uuid                     |           | not null |
 deviceType   | character varying        |           | not null | ''::character varying
 deviceOS     | character varying        |           | not null | ''::character varying
 updateId     | uuid                     |           | not null | immich_uuid_v7()
 pinExpiresAt | timestamp with time zone |           |          |
 expiresAt    | timestamp with time zone |           |          |
 parentId     | uuid                     |           |          |
immich=# \d sessions
                                Table "public.sessions"
    Column    |           Type           | Collation | Nullable |        Default
--------------+--------------------------+-----------+----------+-----------------------
 id           | uuid                     |           | not null | uuid_generate_v4()
 token        | character varying        |           | not null |
 createdAt    | timestamp with time zone |           | not null | now()
 updatedAt    | timestamp with time zone |           | not null | now()
 userId       | uuid                     |           | not null |
 deviceType   | character varying        |           | not null | ''::character varying
 deviceOS     | character varying        |           | not null | ''::character varying
 updateId     | uuid                     |           | not null | immich_uuid_v7()
 pinExpiresAt | timestamp with time zone |           |          |
 expiresAt    | timestamp with time zone |           |          |
 parentId     | uuid                     |           |          |
Indexes:
    "PK_48cb6b5c20faa63157b3c1baf7f" PRIMARY KEY, btree (id)
    "IDX_57de40bc620f456c7311aa3a1e" btree ("userId")
    "IDX_afbbabbd7daf5b91de4dca84de" btree ("parentId")
    "IDX_sessions_update_id" btree ("updateId")
Foreign-key constraints:
    "FK_57de40bc620f456c7311aa3a1e6" FOREIGN KEY ("userId") REFERENCES users(id) ON UPDATE CASCADE ON DELETE CASCADE
    "FK_afbbabbd7daf5b91de4dca84de8" FOREIGN KEY ("parentId") REFERENCES sessions(id) ON UPDATE CASCADE ON DELETE CASCADE
Referenced by:
    TABLE "sessions" CONSTRAINT "FK_afbbabbd7daf5b91de4dca84de8" FOREIGN KEY ("parentId") REFERENCES sessions(id) ON UPDATE CASCADE ON DELETE CASCADE
    TABLE "session_sync_checkpoints" CONSTRAINT "FK_d8ddd9d687816cc490432b3d4bc" FOREIGN KEY ("sessionId") REFERENCES sessions(id) ON UPDATE CASCADE ON DELETE CASCADE
Triggers:
    sessions_updated_at BEFORE UPDATE ON sessions FOR EACH ROW EXECUTE FUNCTION updated_at()

immich=# \d session
Did not find any relation named "session".
Indexes:
    "PK_48cb6b5c20faa63157b3c1baf7f" PRIMARY KEY, btree (id)
    "IDX_57de40bc620f456c7311aa3a1e" btree ("userId")
    "IDX_afbbabbd7daf5b91de4dca84de" btree ("parentId")
    "IDX_sessions_update_id" btree ("updateId")
Foreign-key constraints:
    "FK_57de40bc620f456c7311aa3a1e6" FOREIGN KEY ("userId") REFERENCES users(id) ON UPDATE CASCADE ON DELETE CASCADE
    "FK_afbbabbd7daf5b91de4dca84de8" FOREIGN KEY ("parentId") REFERENCES sessions(id) ON UPDATE CASCADE ON DELETE CASCADE
Referenced by:
    TABLE "sessions" CONSTRAINT "FK_afbbabbd7daf5b91de4dca84de8" FOREIGN KEY ("parentId") REFERENCES sessions(id) ON UPDATE CASCADE ON DELETE CASCADE
    TABLE "session_sync_checkpoints" CONSTRAINT "FK_d8ddd9d687816cc490432b3d4bc" FOREIGN KEY ("sessionId") REFERENCES sessions(id) ON UPDATE CASCADE ON DELETE CASCADE
Triggers:
    sessions_updated_at BEFORE UPDATE ON sessions FOR EACH ROW EXECUTE FUNCTION updated_at()

immich=# \d session
Did not find any relation named "session".
Sergey Katsubo
Sergey Katsubo•5mo ago
I assume the user table is not present either when you check it with \d user, right? Would you like to dig deeper and enable verbose logging and DB query log? To understand how it encounters duplicate tables, this must be happening in a transaction because there are no traces in the database afterwards. Below is step-by-step guide. 1. Enable extra logging Use the attached file with extra log settings. Add it as docker-compose.override.yml or compose.override.yaml depending on the main compose yml name. Alternatively, incorporate its content/settings into your existing compose file. 2. Prepare 2.1. Stop and pull. 
docker compose down
docker compose pull
docker compose down
docker compose pull
2.2 Show config. 
docker compose config --no-path-resolution immich-server | grep -vi password
docker compose config --no-path-resolution immich-server | grep -vi password
2.3. Start DB and show tables. 
docker compose up -d database
docker exec --user postgres immich_postgres psql -Ppager=no -d immich -c 'select * from pg_catalog.pg_tables' > out-tables1.txt
docker compose up -d database
docker exec --user postgres immich_postgres psql -Ppager=no -d immich -c 'select * from pg_catalog.pg_tables' > out-tables1.txt
3. Start Immich server 
docker compose up -d immich-server
docker compose up -d immich-server
Wait for it to stop after failed migrations. 4. Grab info 4.1. Show tables once more. 
docker exec --user postgres immich_postgres psql -Ppager=no -d immich -c 'select * from pg_catalog.pg_tables' > out-tables2.txt
docker exec --user postgres immich_postgres psql -Ppager=no -d immich -c 'select * from pg_catalog.pg_tables' > out-tables2.txt
4.2. Grab full verbose logs. 
docker compose logs -t --no-color immich-server database | sort -k3,3 > out-verbose.txt
docker compose logs -t --no-color immich-server database | sort -k3,3 > out-verbose.txt
Post all 3 out-*.txt files. PS. I've noticed there is profiles key in your compose, so the commands may need to be adjusted accordingly.
Sergey Katsubo
Sergey Katsubo•5mo ago
Compose override with extra logging

docker-compose.overr...

cambelau
cambelauOP•5mo ago
Sergey Katsubo
Sergey Katsubo•5mo ago
Thanks a lot, I will have a look. session_old - what's there, do you have an idea what is it, a copy of session?
cambelau
cambelauOP•5mo ago
I backed up the sessions table one time before trying to delete it Thank you for helping me
Sergey Katsubo
Sergey Katsubo•5mo ago
Could you select * from ... limit 3 a few lines from session_old and from user? I'm wondering what process created them - the DB migration or smth else. Two user(s) tables coexist currently: 
tablename | hasindexes | hastriggers 
----------+------------+-------------
 user     | t          | f           
 users    | t          | t           
tablename | hasindexes | hastriggers 
----------+------------+-------------
 user     | t          | f           
 users    | t          | t
Maybe you fixed session and now only user remains unfixed, can you do the same to user?
cambelau
cambelauOP•5mo ago
session_old is empty 
 key | data | expiry 
-----+------+--------
(0 rows)
 key | data | expiry 
-----+------+--------
(0 rows)
user is very strange its contains this 
   user   
----------
 postgres
(1 row)
   user   
----------
 postgres
(1 row)
users contains my correct users 
                  id                  |          email          |                           password                           |           createdAt           |                                       profileImagePath                                       | isAdmin | shouldChangePassword | deletedAt | oauthId |           updatedAt           | storageLabel |   name   | quotaSizeInBytes | quotaUsageInBytes | status |       profileChangedAt        |               updateId               | avatarColor | pinCode 
--------------------------------------+-------------------------+--------------------------------------------------------------+-------------------------------+----------------------------------------------------------------------------------------------+---------+----------------------+-----------+---------+-------------------------------+--------------+----------+------------------+-------------------+--------+-------------------------------+--------------------------------------+-------------+---------
 c7www9cc281 | wwww@gmail.com     | $2b$10$jwwwww| 2024-12-06 13:28:48.782751+00 |                                                                                              | f       | f                    |           |         | 2025-07-24 00:00:01.945829+00 |              | Mwwwieu |       53www120 |              1992 | active | 2024-12-06 13:28:48.782751+00 | 01wwwwwecda |             | 
 90d90wwww693d | swwwweu@gmail.com | $2b$10$Lywwww2Xzwbc6G | 2024-09-24 20:42:07.876339+00 | upload/profile/90dwww-9dfa-81394wwww-a01www02.jpg | t       | t                    |           |         | 2025-07-24 00:00:02.054837+00 | admin        | wwww|                  |      191wwwww5144 | active | 2024-09-28 18:37:51.373+00    | 01983www9dff4 |             | 
(2 rows)
                  id                  |          email          |                           password                           |           createdAt           |                                       profileImagePath                                       | isAdmin | shouldChangePassword | deletedAt | oauthId |           updatedAt           | storageLabel |   name   | quotaSizeInBytes | quotaUsageInBytes | status |       profileChangedAt        |               updateId               | avatarColor | pinCode 
--------------------------------------+-------------------------+--------------------------------------------------------------+-------------------------------+----------------------------------------------------------------------------------------------+---------+----------------------+-----------+---------+-------------------------------+--------------+----------+------------------+-------------------+--------+-------------------------------+--------------------------------------+-------------+---------
 c7www9cc281 | wwww@gmail.com     | $2b$10$jwwwww| 2024-12-06 13:28:48.782751+00 |                                                                                              | f       | f                    |           |         | 2025-07-24 00:00:01.945829+00 |              | Mwwwieu |       53www120 |              1992 | active | 2024-12-06 13:28:48.782751+00 | 01wwwwwecda |             | 
 90d90wwww693d | swwwweu@gmail.com | $2b$10$Lywwww2Xzwbc6G | 2024-09-24 20:42:07.876339+00 | upload/profile/90dwww-9dfa-81394wwww-a01www02.jpg | t       | t                    |           |         | 2025-07-24 00:00:02.054837+00 | admin        | wwww|                  |      191wwwww5144 | active | 2024-09-28 18:37:51.373+00    | 01983www9dff4 |             | 
(2 rows)
Sergey Katsubo
Sergey Katsubo•5mo ago
Yeah, strange. They both definitely do not belong to Immich database schema. Could you also check their definitions? 
\d+ session_old
\d+ user
\d+ session_old
\d+ user
Does Grafana ring a bell? https://gist.github.com/magikid/06850c081f14dd5e5f4be4dc93d9b285#file-grafana-sql-L1073-L1078 
CREATE TABLE `session` (
  `key` char(16) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `data` blob NOT NULL,
  `expiry` int NOT NULL,
  PRIMARY KEY (`key`)
CREATE TABLE `session` (
  `key` char(16) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `data` blob NOT NULL,
  `expiry` int NOT NULL,
  PRIMARY KEY (`key`)
cambelau
cambelauOP•5mo ago
                                          Table "public.session_old"
 Column |     Type      | Collation | Nullable | Default | Storage  | Compression | Stats target | Description
--------+---------------+-----------+----------+---------+----------+-------------+--------------+-------------
 key    | character(16) |           |          |         | extended |             |              |
 data   | bytea         |           |          |         | extended |             |              |
 expiry | bigint        |           |          |         | plain    |             |              |
Access method: heap
                                          Table "public.session_old"
 Column |     Type      | Collation | Nullable | Default | Storage  | Compression | Stats target | Description
--------+---------------+-----------+----------+---------+----------+-------------+--------------+-------------
 key    | character(16) |           |          |         | extended |             |              |
 data   | bytea         |           |          |         | extended |             |              |
 expiry | bigint        |           |          |         | plain    |             |              |
Access method: heap
cambelau
cambelauOP•5mo ago
Sergey Katsubo
Sergey Katsubo•5mo ago
Also Gitea overlaps pretty much with out-tables1.txt https://github.com/go-gitea/gitea/tree/main/models/repo - attachment, collaboration, pushmirror/push_mirror, language_stat, ... Could it be that the same DB was used by - Immich - Grafana - Gitea ?
cambelau
cambelauOP•5mo ago
I have a Gitea stack deployed on the same machine but it use another container postgres and should not be related to the immich one And no Grafana in the stack
Sergey Katsubo
Sergey Katsubo•5mo ago
Immich
Immich•5mo ago
type Session struct {
    Key    string             \`xorm:"pk CHAR(16)"\` // has to be Key to match with go-chi/session
    Data   []byte             \`xorm:"BLOB"\`        // on MySQL this has a maximum size of 64Kb - this may need to be increased
    Expiry timeutil.TimeStamp // has to be Expiry to match with go-chi/session
}
type Session struct {
    Key    string             \`xorm:"pk CHAR(16)"\` // has to be Key to match with go-chi/session
    Data   []byte             \`xorm:"BLOB"\`        // on MySQL this has a maximum size of 64Kb - this may need to be increased
    Expiry timeutil.TimeStamp // has to be Expiry to match with go-chi/session
}
cambelau
cambelauOP•5mo ago
So you mean my database is corrupted with some table of gitea ?
Sergey Katsubo
Sergey Katsubo•5mo ago
Kind of... infiltrated ) If you remove/rename user it will allow Immich to proceed with renaming its own tables
cambelau
cambelauOP•5mo ago
Hello @Sergey Katsubo, I had a little time to try it out, and sure enough, it was the user table that was causing the problem. Deleting it worked! Thank you so much for your help !

Did you find this page helpful?