starred/pocket-id-pocket-id-1
1
0
Fork 0
mirror of https://github.com/pocket-id/pocket-id.git synced 2025-12-09 14:53:00 +03:00
Code Issues 122 Packages Projects Releases 10 Wiki Activity

🐛 Bug Report: Failed upgrade to 1.8.0 #70

New Issue
Closed
opened 2025-10-07 23:51:43 +03:00 by OVERLORD · 33 comments
OVERLORD commented 2025-10-07 23:51:43 +03:00
Owner
Copy Link

Originally created by @stanrc85 on GitHub.

Reproduction steps

I just updated to 1.8 and my docker container shows the following error repeatedly and won't start

time=2025-08-23T18:06:20.889Z level=ERROR msg="Failed to run pocket-id" app=pocket-id version=1.8.0 error="failed to initialize database: failed to run migrations: failed to apply migrations: Dirty database version 20250822000000. Fix and force version."

Expected behavior

Upgrade and start as normal

Actual Behavior

Error loop

Version and Environment

1.8 docker container

Log Output

No response

Originally created by @stanrc85 on GitHub. ### Reproduction steps I just updated to 1.8 and my docker container shows the following error repeatedly and won't start ``` time=2025-08-23T18:06:20.889Z level=ERROR msg="Failed to run pocket-id" app=pocket-id version=1.8.0 error="failed to initialize database: failed to run migrations: failed to apply migrations: Dirty database version 20250822000000. Fix and force version." ``` ### Expected behavior Upgrade and start as normal ### Actual Behavior Error loop ### Version and Environment 1.8 docker container ### Log Output _No response_
OVERLORD commented 2025-10-07 23:51:45 +03:00
Author
Owner
Copy Link

@Taywee commented on GitHub:

Saame here, also postgres. My dirty database version shows as 20250814121300, though.

Also, I can't seem to drop back to v1.7.0, either. I get the same error on that version now.

@Taywee commented on GitHub: Saame here, also postgres. My dirty database version shows as `20250814121300`, though. Also, I can't seem to drop back to v1.7.0, either. I get the same error on that version now.
OVERLORD commented 2025-10-07 23:51:45 +03:00
Author
Owner
Copy Link

@Ulrar commented on GitHub:

Same here with the default sqlite, and unfortunately rolling back does not fix it.

@Ulrar commented on GitHub: Same here with the default sqlite, and unfortunately rolling back does not fix it.
OVERLORD commented 2025-10-07 23:51:46 +03:00
Author
Owner
Copy Link

@rjvdw commented on GitHub:

Facing the same issue, using postgres. The error that caused the failed migration seems to be:

pocket-id-1.8.0-linux-amd64[421851]: time=2025-08-23T20:11:26.397+02:00 level=ERROR msg="Failed to run pocket-id" app=pocket-id version=1.8.0 error="failed to initialize database: failed to run migrations: failed to apply migrations: migration failed in line 0: ALTER TABLE oidc_clients ADD COLUMN requires_reauthentication BOOLEAN NOT NULL DEFAULT FALSE;\n\nCREATE TABLE reauthentication_tokens (\n    id TEXT PRIMARY KEY,\n    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,\n    token TEXT NOT NULL UNIQUE,\n    expires_at TIMESTAMPTZ NOT NULL,\n    user_id TEXT NOT NULL REFERENCES users ON DELETE CASCADE\n);\n\nCREATE INDEX idx_reauthentication_tokens_token ON reauthentication_tokens(token); (details: ERROR: foreign key constraint \"reauthentication_tokens_user_id_fkey\" cannot be implemented (SQLSTATE 42804))"
@rjvdw commented on GitHub: Facing the same issue, using postgres. The error that caused the failed migration seems to be: ``` pocket-id-1.8.0-linux-amd64[421851]: time=2025-08-23T20:11:26.397+02:00 level=ERROR msg="Failed to run pocket-id" app=pocket-id version=1.8.0 error="failed to initialize database: failed to run migrations: failed to apply migrations: migration failed in line 0: ALTER TABLE oidc_clients ADD COLUMN requires_reauthentication BOOLEAN NOT NULL DEFAULT FALSE;\n\nCREATE TABLE reauthentication_tokens (\n id TEXT PRIMARY KEY,\n created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,\n token TEXT NOT NULL UNIQUE,\n expires_at TIMESTAMPTZ NOT NULL,\n user_id TEXT NOT NULL REFERENCES users ON DELETE CASCADE\n);\n\nCREATE INDEX idx_reauthentication_tokens_token ON reauthentication_tokens(token); (details: ERROR: foreign key constraint \"reauthentication_tokens_user_id_fkey\" cannot be implemented (SQLSTATE 42804))" ```
OVERLORD commented 2025-10-07 23:51:46 +03:00
Author
Owner
Copy Link

@lhw commented on GitHub:

To fallback to 1.7 pin the tag in the compose file and run this on your database

update schema_migrations set version = 20250810144214, dirty=false;

That brought mine back into a working state until the 1.8 is fixed.

@lhw commented on GitHub: To fallback to 1.7 pin the tag in the compose file and run this on your database ```sql update schema_migrations set version = 20250810144214, dirty=false; ``` That brought mine back into a working state until the 1.8 is fixed.
OVERLORD commented 2025-10-07 23:51:47 +03:00
Author
Owner
Copy Link

@sblvkr commented on GitHub:

Facing a similar issue after upgrading to 1.8 using Postgres. The migration process fails with a foreign key constraint error, preventing the database from initializing properly

@sblvkr commented on GitHub: Facing a similar issue after upgrading to 1.8 using Postgres. The migration process fails with a foreign key constraint error, preventing the database from initializing properly
OVERLORD commented 2025-10-07 23:51:49 +03:00
Author
Owner
Copy Link

@Ulrar commented on GitHub:

Sure, here it is :

time=2025-08-23T22:22:25.154Z level=INFO msg="Pocket ID is starting" app=pocket-id version=1.8.0
time=2025-08-23T22:22:25.156Z level=INFO msg="Connected to database" app=pocket-id version=1.8.0 provider=sqlite
time=2025-08-23T22:22:25.157Z level=ERROR msg="Failed to run pocket-id" app=pocket-id version=1.8.0 error="failed to initialize database: failed to run migrations: failed to apply migrations: Dirty database version 20250822000000. Fix and force version."

Same as the OP

@Ulrar commented on GitHub: Sure, here it is : ``` time=2025-08-23T22:22:25.154Z level=INFO msg="Pocket ID is starting" app=pocket-id version=1.8.0 time=2025-08-23T22:22:25.156Z level=INFO msg="Connected to database" app=pocket-id version=1.8.0 provider=sqlite time=2025-08-23T22:22:25.157Z level=ERROR msg="Failed to run pocket-id" app=pocket-id version=1.8.0 error="failed to initialize database: failed to run migrations: failed to apply migrations: Dirty database version 20250822000000. Fix and force version." ``` Same as the OP
OVERLORD commented 2025-10-07 23:51:49 +03:00
Author
Owner
Copy Link

@Ulrar commented on GitHub:

To fallback to 1.7 pin the tag in the compose file and run this on your database

update schema_migrations set version = 20250810144214, dirty=false;

That brought mine back into a working state until the 1.8 is fixed.

How would that work for sqlite ?

@Ulrar commented on GitHub: > To fallback to 1.7 pin the tag in the compose file and run this on your database > > update schema_migrations set version = 20250810144214, dirty=false; > > That brought mine back into a working state until the 1.8 is fixed. How would that work for sqlite ?
OVERLORD commented 2025-10-07 23:51:51 +03:00
Author
Owner
Copy Link

@ItalyPaleAle commented on GitHub:

I think I found the cause, and should be fixed by #869 Seems to be a Postgres-specific error

@ItalyPaleAle commented on GitHub: I think I found the cause, and should be fixed by #869 Seems to be a Postgres-specific error
OVERLORD commented 2025-10-07 23:51:51 +03:00
Author
Owner
Copy Link

@Ulrar commented on GitHub:

It's definitely not pg specific

@Ulrar commented on GitHub: It's definitely not pg specific
OVERLORD commented 2025-10-07 23:51:53 +03:00
Author
Owner
Copy Link

@ItalyPaleAle commented on GitHub:

It's definitely not pg specific

Can you please share logs with SQLite? I cannot repro

@ItalyPaleAle commented on GitHub: > It's definitely not pg specific Can you please share logs with SQLite? I cannot repro
OVERLORD commented 2025-10-07 23:51:54 +03:00
Author
Owner
Copy Link

@Ulrar commented on GitHub:

Unfortunately not, it's running in k8s so it crashed and got retried pretty much right away, I never saw anything else but it might just be because the container had already been restarted a bunch of times by the time I looked.

@Ulrar commented on GitHub: Unfortunately not, it's running in k8s so it crashed and got retried pretty much right away, I never saw anything else but it might just be because the container had already been restarted a bunch of times by the time I looked.
OVERLORD commented 2025-10-07 23:51:54 +03:00
Author
Owner
Copy Link

@ItalyPaleAle commented on GitHub:

@Ulrar sadly these logs just show that the database is in a dirty version. If you have a backup of the database on version 1.7.x, can you please try rolling back, and then upgrade to 1.8.0? What we need are the logs showing exactly what query failed, such as here: https://github.com/pocket-id/pocket-id/issues/866#issuecomment-3217240168

@ItalyPaleAle commented on GitHub: @Ulrar sadly these logs just show that the database is in a dirty version. If you have a backup of the database on version 1.7.x, can you please try rolling back, and then upgrade to 1.8.0? What we need are the logs showing exactly what query failed, such as here: https://github.com/pocket-id/pocket-id/issues/866#issuecomment-3217240168
OVERLORD commented 2025-10-07 23:51:54 +03:00
Author
Owner
Copy Link

@ItalyPaleAle commented on GitHub:

@halictuz Please see here: https://github.com/pocket-id/pocket-id/issues/866#issuecomment-3217887941 If you can provide the logs it would be very helpful

@ItalyPaleAle commented on GitHub: @halictuz Please see here: https://github.com/pocket-id/pocket-id/issues/866#issuecomment-3217887941 If you can provide the logs it would be very helpful
OVERLORD commented 2025-10-07 23:51:55 +03:00
Author
Owner
Copy Link

@ItalyPaleAle commented on GitHub:

@Ulrar can i ask for your help trying to roll back the migrations manually, then perform an upgrade, and try to capture the logs?

I am not able to repro this with SQLite in any way :(

To perform a manual rollback:

  1. Make a backup of the current SQLite DB (just in case .... include the WAL and SHM files too)
  2. Set the migration level to an old one (query above by @lhw ): 
    UPDATE schema_migrations SET version = 20250810144214, dirty=false;
  3. Remove the tables that were created by the latest migrations: 
    ALTER TABLE oidc_clients DROP COLUMN requires_reauthentication;
DROP INDEX IF EXISTS idx_reauthentication_tokens_token;
DROP TABLE IF EXISTS reauthentication_tokens;

This should be enough to bring back the database to the 1.7.0 .... should, because there were some other queries that altered the data (and which caused #865 too), that can't be rolled back without a backup

@ItalyPaleAle commented on GitHub: @Ulrar can i ask for your help trying to roll back the migrations manually, then perform an upgrade, and try to capture the logs? I am not able to repro this with SQLite in any way :( To perform a manual rollback: 1. Make a backup of the current SQLite DB (just in case .... include the WAL and SHM files too) 2. Set the migration level to an old one (query above by @lhw ): ```sql UPDATE schema_migrations SET version = 20250810144214, dirty=false; ``` 3. Remove the tables that were created by the latest migrations: ```sql ALTER TABLE oidc_clients DROP COLUMN requires_reauthentication; DROP INDEX IF EXISTS idx_reauthentication_tokens_token; DROP TABLE IF EXISTS reauthentication_tokens; ``` This should be enough to bring back the database to the 1.7.0 .... _should_, because there were some other queries that altered the data (and which caused #865 too), that can't be rolled back without a backup
OVERLORD commented 2025-10-07 23:51:56 +03:00
Author
Owner
Copy Link

@halictuz commented on GitHub:

Sure, here it is :

time=2025-08-23T22:22:25.154Z level=INFO msg="Pocket ID is starting" app=pocket-id version=1.8.0
time=2025-08-23T22:22:25.156Z level=INFO msg="Connected to database" app=pocket-id version=1.8.0 provider=sqlite
time=2025-08-23T22:22:25.157Z level=ERROR msg="Failed to run pocket-id" app=pocket-id version=1.8.0 error="failed to initialize database: failed to run migrations: failed to apply migrations: Dirty database version 20250822000000. Fix and force version."

Same as the OP

Same issue for me. With sqlite.

@halictuz commented on GitHub: > Sure, here it is : > > ``` > time=2025-08-23T22:22:25.154Z level=INFO msg="Pocket ID is starting" app=pocket-id version=1.8.0 > time=2025-08-23T22:22:25.156Z level=INFO msg="Connected to database" app=pocket-id version=1.8.0 provider=sqlite > time=2025-08-23T22:22:25.157Z level=ERROR msg="Failed to run pocket-id" app=pocket-id version=1.8.0 error="failed to initialize database: failed to run migrations: failed to apply migrations: Dirty database version 20250822000000. Fix and force version." > ``` > > Same as the OP Same issue for me. With sqlite.
OVERLORD commented 2025-10-07 23:51:57 +03:00
Author
Owner
Copy Link

@tilwegener commented on GitHub:

I’m experiencing the same issue.

My setup: Postgres 16.10 and using the distroless image of pocketid.

@tilwegener commented on GitHub: I’m experiencing the same issue. My setup: Postgres 16.10 and using the distroless image of pocketid.
OVERLORD commented 2025-10-07 23:51:57 +03:00
Author
Owner
Copy Link

@Metzlmane commented on GitHub:

This hit me hard, exactly the same issue for me.
Thanks! https://github.com/pocket-id/pocket-id/issues/866#issuecomment-3217345784
that fixed it for now

@Metzlmane commented on GitHub: This hit me hard, exactly the same issue for me. Thanks! https://github.com/pocket-id/pocket-id/issues/866#issuecomment-3217345784 that fixed it for now
OVERLORD commented 2025-10-07 23:51:57 +03:00
Author
Owner
Copy Link

@yuri-becker commented on GitHub:

I also have the issue. My Postgres version is 16.10 and I use the distroless image of pocketid.

time=2025-08-24T06:02:05.738Z level=ERROR msg="Failed to run pocket-id" app=pocket-id version=1.8.0 error="failed to initialize database: failed to run migrations: failed to apply migrations: migration failed in line 0: ALTER TABLE oidc_clients ADD COLUMN requires_reauthentication BOOLEAN NOT NULL DEFAULT FALSE;\n\nCREATE TABLE reauthentication_tokens (\n    id TEXT PRIMARY KEY,\n    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,\n    token TEXT NOT NULL UNIQUE,\n    expires_at TIMESTAMPTZ NOT NULL,\n    user_id TEXT NOT NULL REFERENCES users ON DELETE CASCADE\n);\n\nCREATE INDEX idx_reauthentication_tokens_token ON reauthentication_tokens(token); (details: ERROR: foreign key constraint \"reauthentication_tokens_user_id_fkey\" cannot be implemented (SQLSTATE 42804))"
time=2025-08-24T06:02:08.182Z level=INFO msg="Pocket ID is starting" app=pocket-id version=1.8.0
time=2025-08-24T06:02:08.191Z level=INFO msg="Connected to database" app=pocket-id version=1.8.0 provider=postgres
time=2025-08-24T06:02:08.195Z level=ERROR msg="Failed to run pocket-id" app=pocket-id version=1.8.0 error="failed to initialize database: failed to run migrations: failed to apply migrations: Dirty database version 20250814121300. Fix and force version."
@yuri-becker commented on GitHub: I also have the issue. My Postgres version is 16.10 and I use the distroless image of pocketid. ``` time=2025-08-24T06:02:05.738Z level=ERROR msg="Failed to run pocket-id" app=pocket-id version=1.8.0 error="failed to initialize database: failed to run migrations: failed to apply migrations: migration failed in line 0: ALTER TABLE oidc_clients ADD COLUMN requires_reauthentication BOOLEAN NOT NULL DEFAULT FALSE;\n\nCREATE TABLE reauthentication_tokens (\n id TEXT PRIMARY KEY,\n created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,\n token TEXT NOT NULL UNIQUE,\n expires_at TIMESTAMPTZ NOT NULL,\n user_id TEXT NOT NULL REFERENCES users ON DELETE CASCADE\n);\n\nCREATE INDEX idx_reauthentication_tokens_token ON reauthentication_tokens(token); (details: ERROR: foreign key constraint \"reauthentication_tokens_user_id_fkey\" cannot be implemented (SQLSTATE 42804))" time=2025-08-24T06:02:08.182Z level=INFO msg="Pocket ID is starting" app=pocket-id version=1.8.0 time=2025-08-24T06:02:08.191Z level=INFO msg="Connected to database" app=pocket-id version=1.8.0 provider=postgres time=2025-08-24T06:02:08.195Z level=ERROR msg="Failed to run pocket-id" app=pocket-id version=1.8.0 error="failed to initialize database: failed to run migrations: failed to apply migrations: Dirty database version 20250814121300. Fix and force version." ```
OVERLORD commented 2025-10-07 23:51:58 +03:00
Author
Owner
Copy Link

@oechsler commented on GitHub:

Hi there 👋

When using PostgreSQL, it seems the issue is caused by a type mismatch in the migration’s CREATE TABLE statement:

20250814121300_requires_reauthentication.up.sql

ALTER TABLE oidc_clients ADD COLUMN requires_reauthentication BOOLEAN NOT NULL DEFAULT FALSE;

CREATE TABLE reauthentication_tokens (
    id TEXT PRIMARY KEY,
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    token TEXT NOT NULL UNIQUE,
    expires_at TIMESTAMPTZ NOT NULL,
    -- user_id should be of type UUID instead of TEXT
    user_id UUID NOT NULL REFERENCES users ON DELETE CASCADE
);

CREATE INDEX idx_reauthentication_tokens_token ON reauthentication_tokens(token);

TLDR: Switching reauthentication_tokens.user_id to UUID resolved the issue for me ✅

On PostgreSQL this fails because users.id is UUID, but reauthentication_tokens.user_id was created as TEXT. FK columns must match the referenced type; TEXT ↔ UUID isn’t implicitly castable. Changing user_id to UUID fixes it.

Not sure if this is still relevant or already fixed, but I figured I’d share in case it helps someone.

@oechsler commented on GitHub: Hi there 👋 When using PostgreSQL, it seems the issue is caused by a type mismatch in the migration’s CREATE TABLE statement: [20250814121300_requires_reauthentication.up.sql](https://github.com/pocket-id/pocket-id/blob/9c54e2e6b0f531c56fa152c1226deceb9d2b264e/backend/resources/migrations/postgres/20250814121300_requires_reauthentication.up.sql) ```sql ALTER TABLE oidc_clients ADD COLUMN requires_reauthentication BOOLEAN NOT NULL DEFAULT FALSE; CREATE TABLE reauthentication_tokens ( id TEXT PRIMARY KEY, created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP, token TEXT NOT NULL UNIQUE, expires_at TIMESTAMPTZ NOT NULL, -- user_id should be of type UUID instead of TEXT user_id UUID NOT NULL REFERENCES users ON DELETE CASCADE ); CREATE INDEX idx_reauthentication_tokens_token ON reauthentication_tokens(token); ``` TLDR: Switching `reauthentication_tokens.user_id` to `UUID` resolved the issue for me ✅ On PostgreSQL this fails because `users.id` is `UUID`, but `reauthentication_tokens.user_id` was created as `TEXT`. FK columns must match the referenced type; `TEXT` ↔ `UUID` isn’t implicitly castable. Changing `user_id` to `UUID` fixes it. Not sure if this is still relevant or already fixed, but I figured I’d share in case it helps someone.
OVERLORD commented 2025-10-07 23:51:58 +03:00
Author
Owner
Copy Link

@halictuz commented on GitHub:

@halictuz Please see here: #866 (comment) If you can provide the logs it would be very helpful

I already rolled back to a snapshot of my server before the update to v.1.8.0. I didnt watch any further but the docker log only. Thats why I rolled back instantly, I didnt have much time for it. So I am on v1.7.0 again.

Only other info I have is, that I run the distroless version of Pocket-id. Not sure if that even matters.

@halictuz commented on GitHub: > [@halictuz](https://github.com/halictuz) Please see here: [#866 (comment)](https://github.com/pocket-id/pocket-id/issues/866#issuecomment-3217887941) If you can provide the logs it would be very helpful I already rolled back to a snapshot of my server before the update to v.1.8.0. I didnt watch any further but the docker log only. Thats why I rolled back instantly, I didnt have much time for it. So I am on v1.7.0 again. Only other info I have is, that I run the distroless version of Pocket-id. Not sure if that even matters.
OVERLORD commented 2025-10-07 23:52:00 +03:00
Author
Owner
Copy Link

@Ulrar commented on GitHub:

Unfortunately the container image does not seem to ship with the sqlite binary, so it's pretty hard to recover from this. I'm looking for a workaround.

@Ulrar commented on GitHub: Unfortunately the container image does not seem to ship with the sqlite binary, so it's pretty hard to recover from this. I'm looking for a workaround.
OVERLORD commented 2025-10-07 23:52:00 +03:00
Author
Owner
Copy Link

@ItalyPaleAle commented on GitHub:

@Ulrar ok I may have an idea.

Since I still cannot repro this, could I ask you for one last favor please?

Can you add this env var to your podspec and try upgrading to 1.8.0 again?

SQLITE_TMPDIR=/app/backend/data

If you could help with confirming this fixes the issue, I can bake it directly into the container

@ItalyPaleAle commented on GitHub: @Ulrar ok I may have an idea. Since I still cannot repro this, could I ask you for one last favor please? Can you add this env var to your podspec and try upgrading to 1.8.0 again? ``` SQLITE_TMPDIR=/app/backend/data ``` If you could help with confirming this fixes the issue, I can bake it directly into the container
OVERLORD commented 2025-10-07 23:52:00 +03:00
Author
Owner
Copy Link

@Ulrar commented on GitHub:

Sure, here's the pod :

apiVersion: v1
kind: Pod
metadata:
  annotations:
    backup.velero.io/backup-volumes: pocketid-data
  creationTimestamp: '2025-08-24T12:43:03Z'
  generateName: pocketid-
  generation: 1
  labels:
    app.kubernetes.io/instance: pocketid
    app.kubernetes.io/managed-by: Helm
    app.kubernetes.io/name: pocketid
    app.kubernetes.io/version: 1.8.0
    apps.kubernetes.io/pod-index: '0'
    controller-revision-hash: pocketid-7d7b5c6df4
    helm.sh/chart: pocketid-1.8.0
    statefulset.kubernetes.io/pod-name: pocketid-0
  name: pocketid-0
  namespace: pocketid
  ownerReferences:
    - apiVersion: apps/v1
      blockOwnerDeletion: true
      controller: true
      kind: StatefulSet
      name: pocketid
      uid: 0f8db34c-9593-417b-a757-9d6297d06569
  resourceVersion: '326585751'
  uid: 77533758-c190-4fb1-beb8-7ee837f67d9d
spec:
  containers:
    - envFrom:
        - configMapRef:
            name: pocketid-config
      image: >-
        ghcr.io/pocket-id/pocket-id@sha256:b3ef0b22402b34815a7aeba800ca642baa8eafdd887677b884a1e5a8c81558aa
      imagePullPolicy: IfNotPresent
      livenessProbe:
        failureThreshold: 3
        httpGet:
          path: /health
          port: http
          scheme: HTTP
        initialDelaySeconds: 60
        periodSeconds: 10
        successThreshold: 1
        timeoutSeconds: 1
      name: pocketid
      ports:
        - containerPort: 1411
          name: http
          protocol: TCP
      readinessProbe:
        failureThreshold: 3
        httpGet:
          path: /health
          port: http
          scheme: HTTP
        initialDelaySeconds: 60
        periodSeconds: 10
        successThreshold: 1
        timeoutSeconds: 1
      resources:
        requests:
          memory: 512Mi
      securityContext:
        allowPrivilegeEscalation: false
        readOnlyRootFilesystem: true
        runAsGroup: 1000
        runAsUser: 1000
      terminationMessagePath: /dev/termination-log
      terminationMessagePolicy: File
      volumeMounts:
        - mountPath: /app/backend/data
          name: pocketid-data
        - mountPath: /secret
          name: pocketid-encryption-key
          readOnly: true
        - mountPath: /var/run/secrets/kubernetes.io/serviceaccount
          name: kube-api-access-qrstl
          readOnly: true
  dnsPolicy: ClusterFirst
  enableServiceLinks: true
  hostname: pocketid-0
  nodeName: talos-41p-8ib
  preemptionPolicy: PreemptLowerPriority
  priority: 0
  restartPolicy: Always
  schedulerName: default-scheduler
  securityContext:
    fsGroup: 1000
  serviceAccount: pocketid
  serviceAccountName: pocketid
  terminationGracePeriodSeconds: 30
  tolerations:
    - effect: NoExecute
      key: node.kubernetes.io/not-ready
      operator: Exists
      tolerationSeconds: 300
    - effect: NoExecute
      key: node.kubernetes.io/unreachable
      operator: Exists
      tolerationSeconds: 300
  volumes:
    - name: pocketid-data
      persistentVolumeClaim:
        claimName: pocketid-data-pocketid-0
    - name: pocketid-encryption-key
      secret:
        defaultMode: 420
        secretName: pocketid-encryption-key
    - name: kube-api-access-qrstl
      projected:
        defaultMode: 420
        sources:
          - serviceAccountToken:
              expirationSeconds: 3607
              path: token
          - configMap:
              items:
                - key: ca.crt
                  path: ca.crt
              name: kube-root-ca.crt
          - downwardAPI:
              items:
                - fieldRef:
                    apiVersion: v1
                    fieldPath: metadata.namespace
                  path: namespace

And the config :

  ALLOW_USER_SIGNUPS: withToken
  APP_URL: https://...
  DB_CONNECTION_STRING: >-
    file:/app/backend/data/pocket-id.db?_pragma=journal_mode(WAL)&_pragma=busy_timeout(2500)&_txlock=immediate
  DB_PROVIDER: sqlite
  EMAIL_LOGIN_NOTIFICATION_ENABLED: 'true'
  EMAIL_ONE_TIME_ACCESS_AS_ADMIN_ENABLED: 'true'
  ENCRYPTION_KEY_FILE: /secret/pocketid-encryption-key
  GEOLITE_DB_PATH: /app/backend/data/GeoLite2-City.mmdb
  KEYS_PATH: /app/backend/data/keys
  METRICS_ENABLED: 'true'
  OTEL_METRICS_EXPORTER: prometheus
  PGID: '1000'
  PUBLIC_APP_URL: https://...
  PUID: '1000'
  SMTP_FROM: .
  SMTP_HOST: ...
  SMTP_PASSWORD: ...
  SMTP_PORT: '587'
  SMTP_TLS: starttls
  SMTP_USER: ...
  TRUST_PROXY: 'true'
  UPLOAD_PATH: /app/backend/data/uploads
@Ulrar commented on GitHub: Sure, here's the pod : ```yaml apiVersion: v1 kind: Pod metadata: annotations: backup.velero.io/backup-volumes: pocketid-data creationTimestamp: '2025-08-24T12:43:03Z' generateName: pocketid- generation: 1 labels: app.kubernetes.io/instance: pocketid app.kubernetes.io/managed-by: Helm app.kubernetes.io/name: pocketid app.kubernetes.io/version: 1.8.0 apps.kubernetes.io/pod-index: '0' controller-revision-hash: pocketid-7d7b5c6df4 helm.sh/chart: pocketid-1.8.0 statefulset.kubernetes.io/pod-name: pocketid-0 name: pocketid-0 namespace: pocketid ownerReferences: - apiVersion: apps/v1 blockOwnerDeletion: true controller: true kind: StatefulSet name: pocketid uid: 0f8db34c-9593-417b-a757-9d6297d06569 resourceVersion: '326585751' uid: 77533758-c190-4fb1-beb8-7ee837f67d9d spec: containers: - envFrom: - configMapRef: name: pocketid-config image: >- ghcr.io/pocket-id/pocket-id@sha256:b3ef0b22402b34815a7aeba800ca642baa8eafdd887677b884a1e5a8c81558aa imagePullPolicy: IfNotPresent livenessProbe: failureThreshold: 3 httpGet: path: /health port: http scheme: HTTP initialDelaySeconds: 60 periodSeconds: 10 successThreshold: 1 timeoutSeconds: 1 name: pocketid ports: - containerPort: 1411 name: http protocol: TCP readinessProbe: failureThreshold: 3 httpGet: path: /health port: http scheme: HTTP initialDelaySeconds: 60 periodSeconds: 10 successThreshold: 1 timeoutSeconds: 1 resources: requests: memory: 512Mi securityContext: allowPrivilegeEscalation: false readOnlyRootFilesystem: true runAsGroup: 1000 runAsUser: 1000 terminationMessagePath: /dev/termination-log terminationMessagePolicy: File volumeMounts: - mountPath: /app/backend/data name: pocketid-data - mountPath: /secret name: pocketid-encryption-key readOnly: true - mountPath: /var/run/secrets/kubernetes.io/serviceaccount name: kube-api-access-qrstl readOnly: true dnsPolicy: ClusterFirst enableServiceLinks: true hostname: pocketid-0 nodeName: talos-41p-8ib preemptionPolicy: PreemptLowerPriority priority: 0 restartPolicy: Always schedulerName: default-scheduler securityContext: fsGroup: 1000 serviceAccount: pocketid serviceAccountName: pocketid terminationGracePeriodSeconds: 30 tolerations: - effect: NoExecute key: node.kubernetes.io/not-ready operator: Exists tolerationSeconds: 300 - effect: NoExecute key: node.kubernetes.io/unreachable operator: Exists tolerationSeconds: 300 volumes: - name: pocketid-data persistentVolumeClaim: claimName: pocketid-data-pocketid-0 - name: pocketid-encryption-key secret: defaultMode: 420 secretName: pocketid-encryption-key - name: kube-api-access-qrstl projected: defaultMode: 420 sources: - serviceAccountToken: expirationSeconds: 3607 path: token - configMap: items: - key: ca.crt path: ca.crt name: kube-root-ca.crt - downwardAPI: items: - fieldRef: apiVersion: v1 fieldPath: metadata.namespace path: namespace ``` And the config : ``` ALLOW_USER_SIGNUPS: withToken APP_URL: https://... DB_CONNECTION_STRING: >- file:/app/backend/data/pocket-id.db?_pragma=journal_mode(WAL)&_pragma=busy_timeout(2500)&_txlock=immediate DB_PROVIDER: sqlite EMAIL_LOGIN_NOTIFICATION_ENABLED: 'true' EMAIL_ONE_TIME_ACCESS_AS_ADMIN_ENABLED: 'true' ENCRYPTION_KEY_FILE: /secret/pocketid-encryption-key GEOLITE_DB_PATH: /app/backend/data/GeoLite2-City.mmdb KEYS_PATH: /app/backend/data/keys METRICS_ENABLED: 'true' OTEL_METRICS_EXPORTER: prometheus PGID: '1000' PUBLIC_APP_URL: https://... PUID: '1000' SMTP_FROM: . SMTP_HOST: ... SMTP_PASSWORD: ... SMTP_PORT: '587' SMTP_TLS: starttls SMTP_USER: ... TRUST_PROXY: 'true' UPLOAD_PATH: /app/backend/data/uploads ```
OVERLORD commented 2025-10-07 23:52:00 +03:00
Author
Owner
Copy Link

@ItalyPaleAle commented on GitHub:

@Ulrar thanks, this is very helpful

You mention you're using the distroless container, could you share your container's config? (Docker Compose, K8s pod, etc)

@ItalyPaleAle commented on GitHub: @Ulrar thanks, this is very helpful You mention you're using the distroless container, could you share your container's config? (Docker Compose, K8s pod, etc)
OVERLORD commented 2025-10-07 23:52:01 +03:00
Author
Owner
Copy Link

@Ulrar commented on GitHub:

@Ulrar can i ask for your help trying to roll back the migrations manually, then perform an upgrade, and try to capture the logs?

I am not able to repro this with SQLite in any way :(

To perform a manual rollback:

1. Make a backup of the current SQLite DB (just in case .... include the WAL and SHM files too)

2. Set the migration level to an old one (query above by [@lhw](https://github.com/lhw) ):
   UPDATE schema_migrations SET version = 20250810144214, dirty=false;

3. Remove the tables that were created by the latest migrations:
   ALTER TABLE oidc_clients DROP COLUMN requires_reauthentication;
   DROP INDEX IF EXISTS idx_reauthentication_tokens_token;
   DROP TABLE IF EXISTS reauthentication_tokens;

This should be enough to bring back the database to the 1.7.0 .... should, because there were some other queries that altered the data (and which caused #865 too), that can't be rolled back without a backup

Hi @ItalyPaleAle ,

I've found a random static binary of sqlite on github, hopefully it was legitimate.
Here's the output after running your queries, then running 1.8 again :

time=2025-08-24T12:38:39.785Z level=INFO msg="Pocket ID is starting" app=pocket-id version=1.8.0
time=2025-08-24T12:38:39.786Z level=INFO msg="Connected to database" app=pocket-id version=1.8.0 provider=sqlite
time=2025-08-24T12:38:39.854Z level=ERROR msg="Failed to run pocket-id" app=pocket-id version=1.8.0 error="failed to initialize database: failed to run migrations: failed to apply migrations: 2 errors occurred:\n\t* disk I/O error (6410)\n\t* SQL logic error: cannot rollback - no transaction is active (1)\n\n in line 0: ---------------------------\n-- Delete all orphaned rows\n---------------------------\nUPDATE oidc_clients\nSET created_by_id = NULL\nWHERE created_by_id IS NOT NULL\n  AND created_by_id NOT IN (SELECT id FROM users);\n\nDELETE FROM oidc_authorization_codes WHERE user_id NOT IN (SELECT id FROM users);\nDELETE FROM one_time_access_tokens WHERE user_id NOT IN (SELECT id FROM users);\nDELETE FROM webauthn_credentials WHERE user_id NOT IN (SELECT id FROM users);\nDELETE FROM audit_logs WHERE user_id IS NOT NULL AND user_id NOT IN (SELECT id FROM users);\nDELETE FROM api_keys WHERE user_id IS NOT NULL AND user_id NOT IN (SELECT id FROM users);\n\nDELETE FROM oidc_refresh_tokens WHERE user_id NOT IN (SELECT id FROM users) OR client_id NOT IN (SELECT id FROM oidc_clients);\nDELETE FROM oidc_device_codes WHERE (user_id IS NOT NULL AND user_id NOT IN (SELECT id FROM users)) OR client_id NOT IN (SELECT id FROM oidc_clients);\nDELETE FROM user_authorized_oidc_clients WHERE user_id NOT IN (SELECT id FROM users) OR client_id NOT IN (SELECT id FROM oidc_clients);\n\nDELETE FROM user_groups_users WHERE user_id NOT IN (SELECT id FROM users) OR user_group_id NOT IN (SELECT id FROM user_groups);\n\nDELETE FROM custom_claims WHERE (user_id IS NOT NULL AND user_id NOT IN (SELECT id FROM users)) OR (user_group_id IS NOT NULL AND user_group_id NOT IN (SELECT id FROM user_groups));\n\nDELETE FROM oidc_clients_allowed_user_groups WHERE oidc_client_id NOT IN (SELECT id FROM oidc_clients) OR user_group_id NOT IN (SELECT id FROM user_groups);\n\nDELETE FROM reauthentication_tokens WHERE user_id NOT IN (SELECT id FROM users);\n\n---------------------------\n-- Add missing foreign keys and edit cascade behavior where necessary\n---------------------------\n\n-- reauthentication_tokens: add missing FK user_id → users\nCREATE TABLE reauthentication_tokens_new\n(\n    id         TEXT PRIMARY KEY,\n    created_at DATETIME NOT NULL,\n    token      TEXT NOT NULL UNIQUE,\n    expires_at INTEGER NOT NULL,\n    user_id    TEXT NOT NULL REFERENCES users ON DELETE CASCADE\n);\nINSERT INTO reauthentication_tokens_new (id, created_at, token, expires_at, user_id)\nSELECT id, created_at, token, expires_at, user_id\nFROM reauthentication_tokens;\nDROP TABLE reauthentication_tokens;\nALTER TABLE reauthentication_tokens_new RENAME TO reauthentication_tokens;\nCREATE INDEX idx_reauthentication_tokens_token\n    ON reauthentication_tokens (token);\n\n-- oidc_authorization_codes: add FK client_id, user_id → CASCADE\nCREATE TABLE oidc_authorization_codes_new\n(\n    id                           TEXT PRIMARY KEY,\n    created_at                   DATETIME NOT NULL,\n    code                         TEXT NOT NULL UNIQUE,\n    scope                        TEXT NOT NULL,\n    nonce                        TEXT,\n    expires_at                   DATETIME NOT NULL,\n    user_id                      TEXT NOT NULL REFERENCES users ON DELETE CASCADE,\n    client_id                    TEXT NOT NULL REFERENCES oidc_clients ON DELETE CASCADE,\n    code_challenge               TEXT,\n    code_challenge_method_sha256 NUMERIC\n);\nINSERT INTO oidc_authorization_codes_new\n    (id, created_at, code, scope, nonce, expires_at, user_id, client_id, code_challenge, code_challenge_method_sha256)\nSELECT id, created_at, code, scope, nonce, expires_at, user_id, client_id, code_challenge, code_challenge_method_sha256\nFROM oidc_authorization_codes;\nDROP TABLE oidc_authorization_codes;\nALTER TABLE oidc_authorization_codes_new RENAME TO oidc_authorization_codes;\n\n-- user_authorized_oidc_clients: add FK user_id, cascade client_id\nCREATE TABLE user_authorized_oidc_clients_new\n(\n    scope        TEXT,\n    user_id      TEXT NOT NULL REFERENCES users ON DELETE CASCADE,\n    client_id    TEXT NOT NULL REFERENCES oidc_clients ON DELETE CASCADE,\n    last_used_at DATETIME NOT NULL,\n    PRIMARY KEY (user_id, client_id)\n);\nINSERT INTO user_authorized_oidc_clients_new (scope, user_id, client_id, last_used_at)\nSELECT scope, user_id, client_id, last_used_at\nFROM user_authorized_oidc_clients;\nDROP TABLE user_authorized_oidc_clients;\nALTER TABLE user_authorized_oidc_clients_new RENAME TO user_authorized_oidc_clients;\n\n-- audit_logs: user_id → CASCADE\nCREATE TABLE audit_logs_new\n(\n    id         TEXT PRIMARY KEY,\n    created_at DATETIME NOT NULL,\n    event      TEXT NOT NULL,\n    ip_address TEXT,\n    user_agent TEXT NOT NULL,\n    data       BLOB NOT NULL,\n    user_id    TEXT REFERENCES users ON DELETE CASCADE,\n    country    TEXT,\n    city       TEXT\n);\nINSERT INTO audit_logs_new\n    (id, created_at, event, ip_address, user_agent, data, user_id, country, city)\nSELECT id, created_at, event, ip_address, user_agent, data, user_id, country, city\nFROM audit_logs;\nDROP TABLE audit_logs;\nALTER TABLE audit_logs_new RENAME TO audit_logs;\nCREATE INDEX idx_audit_logs_client_name ON audit_logs((json_extract(data, '$.clientName')));\nCREATE INDEX idx_audit_logs_country ON audit_logs (country);\nCREATE INDEX idx_audit_logs_created_at ON audit_logs (created_at);\nCREATE INDEX idx_audit_logs_event ON audit_logs (event);\nCREATE INDEX idx_audit_logs_user_agent ON audit_logs (user_agent);\nCREATE INDEX idx_audit_logs_user_id ON audit_logs (user_id);\n\n-- oidc_clients: created_by_id → SET NULL\nCREATE TABLE oidc_clients_new\n(\n    id                        TEXT PRIMARY KEY,\n    created_at                DATETIME NOT NULL,\n    name                      TEXT,\n    secret                    TEXT,\n    callback_urls             BLOB,\n    image_type                TEXT,\n    created_by_id             TEXT REFERENCES users ON DELETE SET NULL,\n    is_public                 BOOLEAN DEFAULT FALSE,\n    pkce_enabled              BOOLEAN DEFAULT FALSE,\n    logout_callback_urls      BLOB,\n    credentials               TEXT,\n    launch_url                TEXT,\n    requires_reauthentication BOOLEAN DEFAULT FALSE NOT NULL\n);\nINSERT INTO oidc_clients_new\n    (id, created_at, name, secret, callback_urls, image_type, created_by_id,\n     is_public, pkce_enabled, logout_callback_urls, credentials, launch_url, requires_reauthentication)\nSELECT id, created_at, name, secret, callback_urls, image_type, created_by_id,\n       is_public, pkce_enabled, logout_callback_urls, credentials, launch_url, requires_reauthentication\nFROM oidc_clients;\nDROP TABLE oidc_clients;\nALTER TABLE oidc_clients_new RENAME TO oidc_clients;\n\n-- one_time_access_tokens: user_id → CASCADE\nCREATE TABLE one_time_access_tokens_new\n(\n    id         TEXT PRIMARY KEY,\n    created_at DATETIME NOT NULL,\n    token      TEXT NOT NULL UNIQUE,\n    expires_at DATETIME NOT NULL,\n    user_id    TEXT NOT NULL REFERENCES users ON DELETE CASCADE\n);\nINSERT INTO one_time_access_tokens_new\n    (id, created_at, token, expires_at, user_id)\nSELECT id, created_at, token, expires_at, user_id\nFROM one_time_access_tokens;\nDROP TABLE one_time_access_tokens;\nALTER TABLE one_time_access_tokens_new RENAME TO one_time_access_tokens;\n\n-- webauthn_credentials: user_id → CASCADE\nCREATE TABLE webauthn_credentials_new\n(\n    id               TEXT PRIMARY KEY,\n    created_at       DATETIME NOT NULL,\n    name             TEXT NOT NULL,\n    credential_id    TEXT NOT NULL UNIQUE,\n    public_key       BLOB NOT NULL,\n    attestation_type TEXT NOT NULL,\n    transport        BLOB NOT NULL,\n    user_id          TEXT REFERENCES users ON DELETE CASCADE,\n    backup_eligible  BOOLEAN DEFAULT FALSE NOT NULL,\n    backup_state     BOOLEAN DEFAULT FALSE NOT NULL\n);\nINSERT INTO webauthn_credentials_new\n    (id, created_at, name, credential_id, public_key, attestation_type,\n     transport, user_id, backup_eligible, backup_state)\nSELECT id, created_at, name, credential_id, public_key, attestation_type,\n       transport, user_id, backup_eligible, backup_state\nFROM webauthn_credentials;\nDROP TABLE webauthn_credentials;\nALTER TABLE webauthn_credentials_new RENAME TO webauthn_credentials;"

Good news is I was able to rollback to 1.7 after running these queries again, so at least I'm back up. thanks

@Ulrar commented on GitHub: > [@Ulrar](https://github.com/Ulrar) can i ask for your help trying to roll back the migrations manually, then perform an upgrade, and try to capture the logs? > > I am not able to repro this with SQLite in any way :( > > To perform a manual rollback: > > 1. Make a backup of the current SQLite DB (just in case .... include the WAL and SHM files too) > > 2. Set the migration level to an old one (query above by [@lhw](https://github.com/lhw) ): > UPDATE schema_migrations SET version = 20250810144214, dirty=false; > > 3. Remove the tables that were created by the latest migrations: > ALTER TABLE oidc_clients DROP COLUMN requires_reauthentication; > DROP INDEX IF EXISTS idx_reauthentication_tokens_token; > DROP TABLE IF EXISTS reauthentication_tokens; > > > This should be enough to bring back the database to the 1.7.0 .... _should_, because there were some other queries that altered the data (and which caused [#865](https://github.com/pocket-id/pocket-id/issues/865) too), that can't be rolled back without a backup Hi @ItalyPaleAle , I've found a random static binary of sqlite on github, hopefully it was legitimate. Here's the output after running your queries, then running 1.8 again : ``` time=2025-08-24T12:38:39.785Z level=INFO msg="Pocket ID is starting" app=pocket-id version=1.8.0 time=2025-08-24T12:38:39.786Z level=INFO msg="Connected to database" app=pocket-id version=1.8.0 provider=sqlite time=2025-08-24T12:38:39.854Z level=ERROR msg="Failed to run pocket-id" app=pocket-id version=1.8.0 error="failed to initialize database: failed to run migrations: failed to apply migrations: 2 errors occurred:\n\t* disk I/O error (6410)\n\t* SQL logic error: cannot rollback - no transaction is active (1)\n\n in line 0: ---------------------------\n-- Delete all orphaned rows\n---------------------------\nUPDATE oidc_clients\nSET created_by_id = NULL\nWHERE created_by_id IS NOT NULL\n AND created_by_id NOT IN (SELECT id FROM users);\n\nDELETE FROM oidc_authorization_codes WHERE user_id NOT IN (SELECT id FROM users);\nDELETE FROM one_time_access_tokens WHERE user_id NOT IN (SELECT id FROM users);\nDELETE FROM webauthn_credentials WHERE user_id NOT IN (SELECT id FROM users);\nDELETE FROM audit_logs WHERE user_id IS NOT NULL AND user_id NOT IN (SELECT id FROM users);\nDELETE FROM api_keys WHERE user_id IS NOT NULL AND user_id NOT IN (SELECT id FROM users);\n\nDELETE FROM oidc_refresh_tokens WHERE user_id NOT IN (SELECT id FROM users) OR client_id NOT IN (SELECT id FROM oidc_clients);\nDELETE FROM oidc_device_codes WHERE (user_id IS NOT NULL AND user_id NOT IN (SELECT id FROM users)) OR client_id NOT IN (SELECT id FROM oidc_clients);\nDELETE FROM user_authorized_oidc_clients WHERE user_id NOT IN (SELECT id FROM users) OR client_id NOT IN (SELECT id FROM oidc_clients);\n\nDELETE FROM user_groups_users WHERE user_id NOT IN (SELECT id FROM users) OR user_group_id NOT IN (SELECT id FROM user_groups);\n\nDELETE FROM custom_claims WHERE (user_id IS NOT NULL AND user_id NOT IN (SELECT id FROM users)) OR (user_group_id IS NOT NULL AND user_group_id NOT IN (SELECT id FROM user_groups));\n\nDELETE FROM oidc_clients_allowed_user_groups WHERE oidc_client_id NOT IN (SELECT id FROM oidc_clients) OR user_group_id NOT IN (SELECT id FROM user_groups);\n\nDELETE FROM reauthentication_tokens WHERE user_id NOT IN (SELECT id FROM users);\n\n---------------------------\n-- Add missing foreign keys and edit cascade behavior where necessary\n---------------------------\n\n-- reauthentication_tokens: add missing FK user_id → users\nCREATE TABLE reauthentication_tokens_new\n(\n id TEXT PRIMARY KEY,\n created_at DATETIME NOT NULL,\n token TEXT NOT NULL UNIQUE,\n expires_at INTEGER NOT NULL,\n user_id TEXT NOT NULL REFERENCES users ON DELETE CASCADE\n);\nINSERT INTO reauthentication_tokens_new (id, created_at, token, expires_at, user_id)\nSELECT id, created_at, token, expires_at, user_id\nFROM reauthentication_tokens;\nDROP TABLE reauthentication_tokens;\nALTER TABLE reauthentication_tokens_new RENAME TO reauthentication_tokens;\nCREATE INDEX idx_reauthentication_tokens_token\n ON reauthentication_tokens (token);\n\n-- oidc_authorization_codes: add FK client_id, user_id → CASCADE\nCREATE TABLE oidc_authorization_codes_new\n(\n id TEXT PRIMARY KEY,\n created_at DATETIME NOT NULL,\n code TEXT NOT NULL UNIQUE,\n scope TEXT NOT NULL,\n nonce TEXT,\n expires_at DATETIME NOT NULL,\n user_id TEXT NOT NULL REFERENCES users ON DELETE CASCADE,\n client_id TEXT NOT NULL REFERENCES oidc_clients ON DELETE CASCADE,\n code_challenge TEXT,\n code_challenge_method_sha256 NUMERIC\n);\nINSERT INTO oidc_authorization_codes_new\n (id, created_at, code, scope, nonce, expires_at, user_id, client_id, code_challenge, code_challenge_method_sha256)\nSELECT id, created_at, code, scope, nonce, expires_at, user_id, client_id, code_challenge, code_challenge_method_sha256\nFROM oidc_authorization_codes;\nDROP TABLE oidc_authorization_codes;\nALTER TABLE oidc_authorization_codes_new RENAME TO oidc_authorization_codes;\n\n-- user_authorized_oidc_clients: add FK user_id, cascade client_id\nCREATE TABLE user_authorized_oidc_clients_new\n(\n scope TEXT,\n user_id TEXT NOT NULL REFERENCES users ON DELETE CASCADE,\n client_id TEXT NOT NULL REFERENCES oidc_clients ON DELETE CASCADE,\n last_used_at DATETIME NOT NULL,\n PRIMARY KEY (user_id, client_id)\n);\nINSERT INTO user_authorized_oidc_clients_new (scope, user_id, client_id, last_used_at)\nSELECT scope, user_id, client_id, last_used_at\nFROM user_authorized_oidc_clients;\nDROP TABLE user_authorized_oidc_clients;\nALTER TABLE user_authorized_oidc_clients_new RENAME TO user_authorized_oidc_clients;\n\n-- audit_logs: user_id → CASCADE\nCREATE TABLE audit_logs_new\n(\n id TEXT PRIMARY KEY,\n created_at DATETIME NOT NULL,\n event TEXT NOT NULL,\n ip_address TEXT,\n user_agent TEXT NOT NULL,\n data BLOB NOT NULL,\n user_id TEXT REFERENCES users ON DELETE CASCADE,\n country TEXT,\n city TEXT\n);\nINSERT INTO audit_logs_new\n (id, created_at, event, ip_address, user_agent, data, user_id, country, city)\nSELECT id, created_at, event, ip_address, user_agent, data, user_id, country, city\nFROM audit_logs;\nDROP TABLE audit_logs;\nALTER TABLE audit_logs_new RENAME TO audit_logs;\nCREATE INDEX idx_audit_logs_client_name ON audit_logs((json_extract(data, '$.clientName')));\nCREATE INDEX idx_audit_logs_country ON audit_logs (country);\nCREATE INDEX idx_audit_logs_created_at ON audit_logs (created_at);\nCREATE INDEX idx_audit_logs_event ON audit_logs (event);\nCREATE INDEX idx_audit_logs_user_agent ON audit_logs (user_agent);\nCREATE INDEX idx_audit_logs_user_id ON audit_logs (user_id);\n\n-- oidc_clients: created_by_id → SET NULL\nCREATE TABLE oidc_clients_new\n(\n id TEXT PRIMARY KEY,\n created_at DATETIME NOT NULL,\n name TEXT,\n secret TEXT,\n callback_urls BLOB,\n image_type TEXT,\n created_by_id TEXT REFERENCES users ON DELETE SET NULL,\n is_public BOOLEAN DEFAULT FALSE,\n pkce_enabled BOOLEAN DEFAULT FALSE,\n logout_callback_urls BLOB,\n credentials TEXT,\n launch_url TEXT,\n requires_reauthentication BOOLEAN DEFAULT FALSE NOT NULL\n);\nINSERT INTO oidc_clients_new\n (id, created_at, name, secret, callback_urls, image_type, created_by_id,\n is_public, pkce_enabled, logout_callback_urls, credentials, launch_url, requires_reauthentication)\nSELECT id, created_at, name, secret, callback_urls, image_type, created_by_id,\n is_public, pkce_enabled, logout_callback_urls, credentials, launch_url, requires_reauthentication\nFROM oidc_clients;\nDROP TABLE oidc_clients;\nALTER TABLE oidc_clients_new RENAME TO oidc_clients;\n\n-- one_time_access_tokens: user_id → CASCADE\nCREATE TABLE one_time_access_tokens_new\n(\n id TEXT PRIMARY KEY,\n created_at DATETIME NOT NULL,\n token TEXT NOT NULL UNIQUE,\n expires_at DATETIME NOT NULL,\n user_id TEXT NOT NULL REFERENCES users ON DELETE CASCADE\n);\nINSERT INTO one_time_access_tokens_new\n (id, created_at, token, expires_at, user_id)\nSELECT id, created_at, token, expires_at, user_id\nFROM one_time_access_tokens;\nDROP TABLE one_time_access_tokens;\nALTER TABLE one_time_access_tokens_new RENAME TO one_time_access_tokens;\n\n-- webauthn_credentials: user_id → CASCADE\nCREATE TABLE webauthn_credentials_new\n(\n id TEXT PRIMARY KEY,\n created_at DATETIME NOT NULL,\n name TEXT NOT NULL,\n credential_id TEXT NOT NULL UNIQUE,\n public_key BLOB NOT NULL,\n attestation_type TEXT NOT NULL,\n transport BLOB NOT NULL,\n user_id TEXT REFERENCES users ON DELETE CASCADE,\n backup_eligible BOOLEAN DEFAULT FALSE NOT NULL,\n backup_state BOOLEAN DEFAULT FALSE NOT NULL\n);\nINSERT INTO webauthn_credentials_new\n (id, created_at, name, credential_id, public_key, attestation_type,\n transport, user_id, backup_eligible, backup_state)\nSELECT id, created_at, name, credential_id, public_key, attestation_type,\n transport, user_id, backup_eligible, backup_state\nFROM webauthn_credentials;\nDROP TABLE webauthn_credentials;\nALTER TABLE webauthn_credentials_new RENAME TO webauthn_credentials;" ``` Good news is I was able to rollback to 1.7 after running these queries again, so at least I'm back up. thanks
OVERLORD commented 2025-10-07 23:52:02 +03:00
Author
Owner
Copy Link

@stanrc85 commented on GitHub:

So does the fix need to be done manually or will a new patch correct everything automatically?

@stanrc85 commented on GitHub: So does the fix need to be done manually or will a new patch correct everything automatically?
OVERLORD commented 2025-10-07 23:52:02 +03:00
Author
Owner
Copy Link

@Ulrar commented on GitHub:

Yep, that did the trick, all good on 1.8.1 with that env var defined.
Thank you very much !

@Ulrar commented on GitHub: Yep, that did the trick, all good on 1.8.1 with that env var defined. Thank you very much !
OVERLORD commented 2025-10-07 23:52:02 +03:00
Author
Owner
Copy Link

@johnnycube commented on GitHub:

I am running on Postgres, upgrading from 1.7.0 to 1.8.0 fails with the error above.

However I can confirm that upgrading from 1.7.0 to 1.8.1 works without error and without the need to use the above mentioned workaround

@johnnycube commented on GitHub: I am running on Postgres, upgrading from 1.7.0 to 1.8.0 fails with the error above. However I can confirm that upgrading from 1.7.0 to 1.8.1 works without error and without the need to use the above mentioned workaround
OVERLORD commented 2025-10-07 23:52:03 +03:00
Author
Owner
Copy Link

@Taywee commented on GitHub:

I can confirm that after running remediation in this comment (I know it's for SQLite, but I figured it wouldn't hurt on Postgres) and upgrading to v1.8.1, everything seems to work fine for me! Thank you.

From now on, I will always make a database backup before upgrades. I probably should have been doing that anyway. 😅

@Taywee commented on GitHub: I can confirm that after running remediation in [this comment](https://github.com/pocket-id/pocket-id/issues/866#issuecomment-3217887941) (I know it's for SQLite, but I figured it wouldn't hurt on Postgres) and upgrading to v1.8.1, everything seems to work fine for me! Thank you. From now on, I will always make a database backup before upgrades. I probably should have been doing that anyway. 😅
OVERLORD commented 2025-10-07 23:52:03 +03:00
Author
Owner
Copy Link

@ItalyPaleAle commented on GitHub:

Great, thanks for confirming!! I'll have a PR to fix this issue hopefully for good

@ItalyPaleAle commented on GitHub: Great, thanks for confirming!! I'll have a PR to fix this issue hopefully for good
OVERLORD commented 2025-10-07 23:52:04 +03:00
Author
Owner
Copy Link

@poudenes commented on GitHub:

I have this same issue with Postgres dB... how can I solve this?

pocketid  | addgroup: gid '100' in use
pocketid  | Creating group 100...
pocketid  | Creating user 1026...
pocketid  | addgroup: gid '100' in use
pocketid  | time=2025-08-24T20:23:15.793+02:00 level=INFO msg="Pocket ID is starting" app=pocket-id version=1.8.1
pocketid  | time=2025-08-24T20:23:15.814+02:00 level=INFO msg="Connected to database" app=pocket-id version=1.8.1 provider=postgres
pocketid  | time=2025-08-24T20:23:15.820+02:00 level=ERROR msg="Failed to run pocket-id" app=pocket-id version=1.8.1 error="failed to initialize database: failed to run migrations: failed to apply migrations: Dirty database version 20250814121300. Fix and force version."
@poudenes commented on GitHub: I have this same issue with Postgres dB... how can I solve this? ``` pocketid | addgroup: gid '100' in use pocketid | Creating group 100... pocketid | Creating user 1026... pocketid | addgroup: gid '100' in use pocketid | time=2025-08-24T20:23:15.793+02:00 level=INFO msg="Pocket ID is starting" app=pocket-id version=1.8.1 pocketid | time=2025-08-24T20:23:15.814+02:00 level=INFO msg="Connected to database" app=pocket-id version=1.8.1 provider=postgres pocketid | time=2025-08-24T20:23:15.820+02:00 level=ERROR msg="Failed to run pocket-id" app=pocket-id version=1.8.1 error="failed to initialize database: failed to run migrations: failed to apply migrations: Dirty database version 20250814121300. Fix and force version." ```
OVERLORD commented 2025-10-07 23:52:04 +03:00
Author
Owner
Copy Link

@Collectathon commented on GitHub:

Hi there 👋

When using PostgreSQL, it seems the issue is caused by a type mismatch in the migration’s CREATE TABLE statement:

20250814121300_requires_reauthentication.up.sql

ALTER TABLE oidc_clients ADD COLUMN requires_reauthentication BOOLEAN NOT NULL DEFAULT FALSE;

CREATE TABLE reauthentication_tokens (
id TEXT PRIMARY KEY,
created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
token TEXT NOT NULL UNIQUE,
expires_at TIMESTAMPTZ NOT NULL,
-- user_id should be of type UUID instead of TEXT
user_id UUID NOT NULL REFERENCES users ON DELETE CASCADE
);

CREATE INDEX idx_reauthentication_tokens_token ON reauthentication_tokens(token);

TLDR: Switching reauthentication_tokens.user_id to UUID resolved the issue for me ✅

On PostgreSQL this fails because users.id is UUID, but reauthentication_tokens.user_id was created as TEXT. FK columns must match the referenced type; TEXT ↔ UUID isn’t implicitly castable. Changing user_id to UUID fixes it.

Not sure if this is still relevant or already fixed, but I figured I’d share in case it helps someone.

Thank you so much for posting this anyway. This helped me recover my database.

@Collectathon commented on GitHub: > Hi there 👋 > > When using PostgreSQL, it seems the issue is caused by a type mismatch in the migration’s CREATE TABLE statement: > > [20250814121300_requires_reauthentication.up.sql](https://github.com/pocket-id/pocket-id/blob/9c54e2e6b0f531c56fa152c1226deceb9d2b264e/backend/resources/migrations/postgres/20250814121300_requires_reauthentication.up.sql) > > ALTER TABLE oidc_clients ADD COLUMN requires_reauthentication BOOLEAN NOT NULL DEFAULT FALSE; > > CREATE TABLE reauthentication_tokens ( > id TEXT PRIMARY KEY, > created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP, > token TEXT NOT NULL UNIQUE, > expires_at TIMESTAMPTZ NOT NULL, > -- user_id should be of type UUID instead of TEXT > user_id UUID NOT NULL REFERENCES users ON DELETE CASCADE > ); > > CREATE INDEX idx_reauthentication_tokens_token ON reauthentication_tokens(token); > > TLDR: Switching `reauthentication_tokens.user_id` to `UUID` resolved the issue for me ✅ > > On PostgreSQL this fails because `users.id` is `UUID`, but `reauthentication_tokens.user_id` was created as `TEXT`. FK columns must match the referenced type; `TEXT` ↔ `UUID` isn’t implicitly castable. Changing `user_id` to `UUID` fixes it. > > Not sure if this is still relevant or already fixed, but I figured I’d share in case it helps someone. Thank you so much for posting this anyway. This helped me recover my database.
OVERLORD commented 2025-10-07 23:52:05 +03:00
Author
Owner
Copy Link

@poudenes commented on GitHub:

Dirty database version 20250814121300. Fix and force version.

With some help of ChatGPT fixed it myself:

SELECT * FROM schema_migrations;

There was one record. Changed TRUE to FALSE

@poudenes commented on GitHub: > Dirty database version 20250814121300. Fix and force version. With some help of ChatGPT fixed it myself: ``` SELECT * FROM schema_migrations; ``` There was one record. Changed TRUE to FALSE
Sign in to join this conversation.
No Branch/Tag Specified
Branches Tags
Labels
Clear labels
breaking
bug
documentation
feature
needs more upvotes
open to pull requests
pull-request
Mirrored from GitHub Pull Request
No Label
Milestone
No items
No Milestone
Projects
Clear projects
No project
Assignees
Clear assignees
OVERLORD
No Assignees
1 Participants
Notifications
Due Date
No due date set.
Dependencies

No dependencies set.

Reference: starred/pocket-id-pocket-id-1#70
Delete Branch "%!s()"

Deleting a branch is permanent. Although the deleted branch may continue to exist for a short time before it actually gets removed, it CANNOT be undone in most cases. Continue?