starred/pocket-id
1
0
Fork 0
mirror of https://github.com/pocket-id/pocket-id.git synced 2026-02-05 00:39:38 +03:00
Code Issues 61 Packages Projects Releases 10 Wiki Activity

🐛 Bug Report: Failing v2 migration when a user has no email #582

New Issue
Closed
opened 2026-02-04 20:33:40 +03:00 by OVERLORD · 3 comments
OVERLORD commented 2026-02-04 20:33:40 +03:00
Owner
Copy Link

Originally created by @Petersmit27 on GitHub (Jan 3, 2026).

Reproduction steps

I upgraded to v2, and have some users in my database without an email.

Expected behavior

The migrations should run as normal

Actual Behavior

The sqlite/20251217141000_v2_export_normalization.up.sql migration failed, leaving a dirty db. Seems like the migration adds the NOT NULL constraint to the users_new table which causes the error.

Pocket ID Version

v2.0.1

Database

sqlite

OS and Environment

Docker containers in docker compose on Ubuntu.

Log Output

pocket-id-1  | Jan  3 07:43:44 INF Pocket ID is starting app=pocket-id version=2.0.1
pocket-id-1  | Jan  3 07:43:45 INF Connected to database app=pocket-id version=2.0.1 provider=sqlite
pocket-id-1  | Jan  3 07:43:45 ERR Failed to run pocket-id app=pocket-id version=2.0.1 error="failed to initialize database: failed to run migrations: failed to apply embedded migrations: constraint failed: NOT NULL constraint failed: users_new.email (1299) in line 0: -- This migration is part of v2\n\nPRAGMA foreign_keys = OFF;\n\nBEGIN;\n\n-- 1. Create a new table with BOOLEAN columns\nCREATE TABLE users_new\n(\n    id           TEXT                  NOT NULL PRIMARY KEY,\n    created_at   DATETIME,\n    username     TEXT COLLATE NOCASE   NOT NULL UNIQUE,\n    email        TEXT                  NOT NULL UNIQUE,\n    first_name   TEXT,\n    last_name    TEXT                  NOT NULL,\n    display_name TEXT                  NOT NULL,\n    is_admin     BOOLEAN DEFAULT FALSE NOT NULL,\n    ldap_id      TEXT,\n    locale       TEXT,\n    disabled     BOOLEAN DEFAULT FALSE NOT NULL\n);\n\n-- 2. Copy all existing data, converting numeric bools to real booleans\nINSERT INTO users_new (\n    id,\n    created_at,\n    username,\n    email,\n    first_name,\n    last_name,\n    display_name,\n    is_admin,\n    ldap_id,\n    locale,\n    disabled\n)\nSELECT\n    id,\n    created_at,\n    username,\n    email,\n    first_name,\n    last_name,\n    display_name,\n    CASE WHEN is_admin != 0 THEN TRUE ELSE FALSE END,\n    ldap_id,\n    locale,\n    CASE WHEN disabled != 0 THEN TRUE ELSE FALSE END\nFROM users;\n\n-- 3. Drop old table\nDROP TABLE users;\n\n-- 4. Rename new table to original name\nALTER TABLE users_new RENAME TO users;\n\n-- 5. Recreate index\nCREATE UNIQUE INDEX users_ldap_id ON users (ldap_id);\n\n-- 6. Create temporary table with changed credential_id type to BLOB\nCREATE TABLE webauthn_credentials_dg_tmp\n(\n    id               TEXT PRIMARY KEY,\n    created_at       DATETIME             NOT NULL,\n    name             TEXT                 NOT NULL,\n    credential_id    BLOB                 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);\n\n-- 7. Copy existing data into the temporary table\nINSERT INTO webauthn_credentials_dg_tmp (\n    id,\n    created_at,\n    name,\n    credential_id,\n    public_key,\n    attestation_type,\n    transport,\n    user_id,\n    backup_eligible,\n    backup_state\n)\nSELECT\n    id,\n    created_at,\n    name,\n    credential_id,\n    public_key,\n    attestation_type,\n    transport,\n    user_id,\n    backup_eligible,\n    backup_state\nFROM webauthn_credentials;\n\n-- 8. Drop old table\nDROP TABLE webauthn_credentials;\n\n-- 9. Rename temporary table to original name\nALTER TABLE webauthn_credentials_dg_tmp\n    RENAME TO webauthn_credentials;\n\n-- 10. Create temporary table with credential_params type changed to BLOB\nCREATE TABLE webauthn_sessions_dg_tmp\n(\n    id                TEXT              NOT NULL PRIMARY KEY,\n    created_at        DATETIME,\n    challenge         TEXT              NOT NULL UNIQUE,\n    expires_at        DATETIME          NOT NULL,\n    user_verification TEXT              NOT NULL,\n    credential_params BLOB DEFAULT '[]' NOT NULL\n);\n\n-- 11. Copy existing data into the temporary sessions table\nINSERT INTO webauthn_sessions_dg_tmp (\n    id,\n    created_at,\n    challenge,\n    expires_at,\n    user_verification,\n    credential_params\n)\nSELECT\n    id,\n    created_at,\n    challenge,\n    expires_at,\n    user_verification,\n    credential_params\nFROM webauthn_sessions;\n\n-- 12. Drop old table\nDROP TABLE webauthn_sessions;\n\n-- 13. Rename temporary sessions table to original name\nALTER TABLE webauthn_sessions_dg_tmp\n    RENAME TO webauthn_sessions;\n\nCOMMIT;\n\nPRAGMA foreign_keys = ON;"
pocket-id-1  | Jan  3 07:43:49 INF Pocket ID is starting app=pocket-id version=2.0.1
pocket-id-1  | Jan  3 07:43:49 INF Connected to database app=pocket-id version=2.0.1 provider=sqlite
pocket-id-1  | Jan  3 07:43:49 ERR Failed to run pocket-id app=pocket-id version=2.0.1 error="failed to initialize database: failed to run migrations: failed to apply embedded migrations: Dirty database version 20251217141000. Fix and force version."
Originally created by @Petersmit27 on GitHub (Jan 3, 2026). ### Reproduction steps I upgraded to v2, and have some users in my database without an email. ### Expected behavior The migrations should run as normal ### Actual Behavior The `sqlite/20251217141000_v2_export_normalization.up.sql` migration failed, leaving a dirty db. Seems like the migration adds the `NOT NULL` constraint to the `users_new` table which causes the error. ### Pocket ID Version v2.0.1 ### Database sqlite ### OS and Environment Docker containers in docker compose on Ubuntu. ### Log Output ``` pocket-id-1 | Jan 3 07:43:44 INF Pocket ID is starting app=pocket-id version=2.0.1 pocket-id-1 | Jan 3 07:43:45 INF Connected to database app=pocket-id version=2.0.1 provider=sqlite pocket-id-1 | Jan 3 07:43:45 ERR Failed to run pocket-id app=pocket-id version=2.0.1 error="failed to initialize database: failed to run migrations: failed to apply embedded migrations: constraint failed: NOT NULL constraint failed: users_new.email (1299) in line 0: -- This migration is part of v2\n\nPRAGMA foreign_keys = OFF;\n\nBEGIN;\n\n-- 1. Create a new table with BOOLEAN columns\nCREATE TABLE users_new\n(\n id TEXT NOT NULL PRIMARY KEY,\n created_at DATETIME,\n username TEXT COLLATE NOCASE NOT NULL UNIQUE,\n email TEXT NOT NULL UNIQUE,\n first_name TEXT,\n last_name TEXT NOT NULL,\n display_name TEXT NOT NULL,\n is_admin BOOLEAN DEFAULT FALSE NOT NULL,\n ldap_id TEXT,\n locale TEXT,\n disabled BOOLEAN DEFAULT FALSE NOT NULL\n);\n\n-- 2. Copy all existing data, converting numeric bools to real booleans\nINSERT INTO users_new (\n id,\n created_at,\n username,\n email,\n first_name,\n last_name,\n display_name,\n is_admin,\n ldap_id,\n locale,\n disabled\n)\nSELECT\n id,\n created_at,\n username,\n email,\n first_name,\n last_name,\n display_name,\n CASE WHEN is_admin != 0 THEN TRUE ELSE FALSE END,\n ldap_id,\n locale,\n CASE WHEN disabled != 0 THEN TRUE ELSE FALSE END\nFROM users;\n\n-- 3. Drop old table\nDROP TABLE users;\n\n-- 4. Rename new table to original name\nALTER TABLE users_new RENAME TO users;\n\n-- 5. Recreate index\nCREATE UNIQUE INDEX users_ldap_id ON users (ldap_id);\n\n-- 6. Create temporary table with changed credential_id type to BLOB\nCREATE TABLE webauthn_credentials_dg_tmp\n(\n id TEXT PRIMARY KEY,\n created_at DATETIME NOT NULL,\n name TEXT NOT NULL,\n credential_id BLOB 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);\n\n-- 7. Copy existing data into the temporary table\nINSERT INTO webauthn_credentials_dg_tmp (\n id,\n created_at,\n name,\n credential_id,\n public_key,\n attestation_type,\n transport,\n user_id,\n backup_eligible,\n backup_state\n)\nSELECT\n id,\n created_at,\n name,\n credential_id,\n public_key,\n attestation_type,\n transport,\n user_id,\n backup_eligible,\n backup_state\nFROM webauthn_credentials;\n\n-- 8. Drop old table\nDROP TABLE webauthn_credentials;\n\n-- 9. Rename temporary table to original name\nALTER TABLE webauthn_credentials_dg_tmp\n RENAME TO webauthn_credentials;\n\n-- 10. Create temporary table with credential_params type changed to BLOB\nCREATE TABLE webauthn_sessions_dg_tmp\n(\n id TEXT NOT NULL PRIMARY KEY,\n created_at DATETIME,\n challenge TEXT NOT NULL UNIQUE,\n expires_at DATETIME NOT NULL,\n user_verification TEXT NOT NULL,\n credential_params BLOB DEFAULT '[]' NOT NULL\n);\n\n-- 11. Copy existing data into the temporary sessions table\nINSERT INTO webauthn_sessions_dg_tmp (\n id,\n created_at,\n challenge,\n expires_at,\n user_verification,\n credential_params\n)\nSELECT\n id,\n created_at,\n challenge,\n expires_at,\n user_verification,\n credential_params\nFROM webauthn_sessions;\n\n-- 12. Drop old table\nDROP TABLE webauthn_sessions;\n\n-- 13. Rename temporary sessions table to original name\nALTER TABLE webauthn_sessions_dg_tmp\n RENAME TO webauthn_sessions;\n\nCOMMIT;\n\nPRAGMA foreign_keys = ON;" pocket-id-1 | Jan 3 07:43:49 INF Pocket ID is starting app=pocket-id version=2.0.1 pocket-id-1 | Jan 3 07:43:49 INF Connected to database app=pocket-id version=2.0.1 provider=sqlite pocket-id-1 | Jan 3 07:43:49 ERR Failed to run pocket-id app=pocket-id version=2.0.1 error="failed to initialize database: failed to run migrations: failed to apply embedded migrations: Dirty database version 20251217141000. Fix and force version." ```
OVERLORD commented 2026-02-04 20:33:58 +03:00
Author
Owner
Copy Link

@rsaffi commented on GitHub (Jan 3, 2026):

I was already on v2 but when bumping pocket-id from v2.0.0 to v2.0.1 I get the same:

pocket-id-1  | Jan  3 12:49:26 INF Pocket ID is starting app=pocket-id version=2.0.1
pocket-id-1  | Jan  3 12:49:26 INF Connected to database app=pocket-id version=2.0.1 provider=sqlite
pocket-id-1  | Jan  3 12:49:26 ERR Failed to run pocket-id app=pocket-id version=2.0.1 error="failed to initialize database: failed to run migrations: failed to apply embedded migrations: Dirty database version 20260102231700. Fix and force version."
pocket-id-1 exited with code 1 (restarting)

Trying to downgrade back to v2.0.0 gives a warning:

pocket-id-1  | Jan  3 12:52:31 INF Pocket ID is starting app=pocket-id version=2.0.0
pocket-id-1  | Jan  3 12:52:31 INF Connected to database app=pocket-id version=2.0.0 provider=sqlite
pocket-id-1  | Jan  3 12:52:31 WRN Database version is newer than the application supports, possible downgrade detected app=pocket-id version=2.0.0 db_version=20260102231700 app_version=20251229173100
pocket-id-1  | Jan  3 12:52:31 ERR Failed to run pocket-id app=pocket-id version=2.0.0 error="failed to initialize database: failed to run migrations: database version (20260102231700) is newer than application version (20251229173100), downgrades are not allowed (set ALLOW_DOWNGRADE=true to enable)"

And even with adding the ALLOW_DOWNGRADE=true to the env variables, it still doesn't fix the issue:

pocket-id-1  | Jan  3 12:54:28 INF Pocket ID is starting app=pocket-id version=2.0.0
pocket-id-1  | Jan  3 12:54:28 INF Connected to database app=pocket-id version=2.0.0 provider=sqlite
pocket-id-1  | Jan  3 12:54:28 WRN Database version is newer than the application supports, possible downgrade detected app=pocket-id version=2.0.0 db_version=20260102231700 app_version=20251229173100
pocket-id-1  | Jan  3 12:54:28 INF Fetching migrations from GitHub to handle possible downgrades app=pocket-id version=2.0.0
pocket-id-1  | Jan  3 12:54:28 ERR Failed to run pocket-id app=pocket-id version=2.0.0 error="failed to initialize database: failed to run migrations: failed to apply GitHub migrations: Dirty database version 20260102231700. Fix and force version."
@rsaffi commented on GitHub (Jan 3, 2026): I was already on v2 but when bumping pocket-id from `v2.0.0` to `v2.0.1` I get the same: ```log pocket-id-1 | Jan 3 12:49:26 INF Pocket ID is starting app=pocket-id version=2.0.1 pocket-id-1 | Jan 3 12:49:26 INF Connected to database app=pocket-id version=2.0.1 provider=sqlite pocket-id-1 | Jan 3 12:49:26 ERR Failed to run pocket-id app=pocket-id version=2.0.1 error="failed to initialize database: failed to run migrations: failed to apply embedded migrations: Dirty database version 20260102231700. Fix and force version." pocket-id-1 exited with code 1 (restarting) ``` Trying to downgrade back to `v2.0.0` gives a warning: ```log pocket-id-1 | Jan 3 12:52:31 INF Pocket ID is starting app=pocket-id version=2.0.0 pocket-id-1 | Jan 3 12:52:31 INF Connected to database app=pocket-id version=2.0.0 provider=sqlite pocket-id-1 | Jan 3 12:52:31 WRN Database version is newer than the application supports, possible downgrade detected app=pocket-id version=2.0.0 db_version=20260102231700 app_version=20251229173100 pocket-id-1 | Jan 3 12:52:31 ERR Failed to run pocket-id app=pocket-id version=2.0.0 error="failed to initialize database: failed to run migrations: database version (20260102231700) is newer than application version (20251229173100), downgrades are not allowed (set ALLOW_DOWNGRADE=true to enable)" ``` And even with adding the `ALLOW_DOWNGRADE=true` to the env variables, it still doesn't fix the issue: ```log pocket-id-1 | Jan 3 12:54:28 INF Pocket ID is starting app=pocket-id version=2.0.0 pocket-id-1 | Jan 3 12:54:28 INF Connected to database app=pocket-id version=2.0.0 provider=sqlite pocket-id-1 | Jan 3 12:54:28 WRN Database version is newer than the application supports, possible downgrade detected app=pocket-id version=2.0.0 db_version=20260102231700 app_version=20251229173100 pocket-id-1 | Jan 3 12:54:28 INF Fetching migrations from GitHub to handle possible downgrades app=pocket-id version=2.0.0 pocket-id-1 | Jan 3 12:54:28 ERR Failed to run pocket-id app=pocket-id version=2.0.0 error="failed to initialize database: failed to run migrations: failed to apply GitHub migrations: Dirty database version 20260102231700. Fix and force version." ```
OVERLORD commented 2026-02-04 20:34:04 +03:00
Author
Owner
Copy Link

@rsaffi commented on GitHub (Jan 3, 2026):

Had to brush up my sqlite3 skills, but managed to get the app running again (v2.0.1) by:

0. Make a backup copy of the pocket-id.db file
1. sqlite3 pocket-id.db
2. UPDATE schema_migrations SET dirty=0;

After this, the applications starts. I don't know if I'm risking any issues or inconsistent states of the database, but at least it got me back on track (for now).

EDIT: yeah, celebrated too early. The application is indeed running, but the failed migration left the db with issues. Upon trying to login to Pocket ID, I get this:

Image

I have for now restored an earlier backup from my archive, while v2.0.0 was still running with its own database version (pre migration).

@rsaffi commented on GitHub (Jan 3, 2026): ~Had to brush up my sqlite3 skills, but managed to get the app running again (`v2.0.1`) by:~ ~0. Make a backup copy of the `pocket-id.db` file~ ~1. `sqlite3 pocket-id.db`~ ~2. `UPDATE schema_migrations SET dirty=0;`~ ~After this, the applications starts. I don't know if I'm risking any issues or inconsistent states of the database, but at least it got me back on track (for now).~ --- EDIT: yeah, celebrated too early. The application is indeed running, but the failed migration left the db with issues. Upon trying to login to Pocket ID, I get this: <img width="382" height="312" alt="Image" src="https://github.com/user-attachments/assets/725847db-ee37-4454-96ab-16e3d35991e7" /> --- I have for now restored an earlier backup from my archive, while `v2.0.0` was still running with its own database version (pre migration).
OVERLORD commented 2026-02-04 20:34:16 +03:00
Author
Owner
Copy Link

@stonith404 commented on GitHub (Jan 3, 2026):

Thanks for reporting this. v2.0.2 should fix this issue. Unfortunately you have to make a small adjustment in the database manually:

If you upgrade from v2.0.0 run the following command on the database:

UPDATE schema_migrations SET version=20251229173100, dirty=false

If you upgrade from v1.x.x run the following command on the database:

UPDATE schema_migrations SET version=20251217000000, dirty=false
@stonith404 commented on GitHub (Jan 3, 2026): Thanks for reporting this. `v2.0.2` should fix this issue. Unfortunately you have to make a small adjustment in the database manually: If you upgrade from `v2.0.0` run the following command on the database: ``` UPDATE schema_migrations SET version=20251229173100, dirty=false ``` If you upgrade from `v1.x.x` run the following command on the database: ``` UPDATE schema_migrations SET version=20251217000000, dirty=false ```
Sign in to join this conversation.
No Branch/Tag Specified
Branches Tags
Labels
Clear labels
breaking
bug
bug
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#582
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?