8000 [0.29.1] DB Migration fails · Issue #917 · sebadob/rauthy · GitHub
[go: up one dir, main page]
More Web Proxy on the site http://driver.im/
Skip to content

[0.29.1] DB Migration fails #917

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom
Closed
cocoon opened this issue May 8, 2025 · 12 comments · Fixed by #919
Closed

[0.29.1] DB Migration fails #917

cocoon opened this issue May 8, 2025 · 12 comments · Fixed by #919
Labels
bug Something isn't working

Comments

@cocoon
Copy link
Contributor
cocoon commented May 8, 2025

Today I tried to upgrade to v0.29.1 and it worked.

I had an old postgres db from v0.28.3.

First I tried to directly use to migrate into a new empty db from it, but it rejected it, because different major version.

I had created the db with:
createdb.exe -p 5412 -O rauthy -U postgres -w rauthy0291

So instead I dumped the current 0.28.3 AS BACKUP and THEN used this db and started v0.29.1 and it worked and is running, I can login, all OK.

Now I wanted to move to a new DB and again tried to set:

MIGRATE_DB_FROM=postgres
MIGRATE_PG_PORT=5412
MIGRATE_PG_USER=rauthy
MIGRATE_PG_PASSWORD=...xyz
MIGRATE_PG_DB_NAME=<MYOLDDB_from_0.28.3_now_new_0.29.1>

but it fails with:

2025-05-08T09:35:11.228369Z  INFO rauthy_models::migration::db_migrate: Starting migration from Postgres to another DB
2025-05-08T09:35:11.247477Z  INFO rauthy_models::database: Database Connection Pool created successfully
2025-05-08T09:35:11.248486Z DEBUG rauthy_models::migration::db_migrate: Migrating table: config
2025-05-08T09:35:11.250217Z DEBUG rauthy_models::migration::db_migrate: Migrating table: api_keys
2025-05-08T09:35:11.250982Z DEBUG rauthy_models::migration::db_migrate: Migrating table: auth_providers
2025-05-08T09:35:11.252004Z DEBUG rauthy_models::migration::db_migrate: Migrating table: auth_provider_logos
2025-05-08T09:35:11.252745Z DEBUG rauthy_models::migration::db_migrate: Migrating table: pictures
2025-05-08T09:35:11.253462Z DEBUG rauthy_models::migration::db_migrate: Migrating table: users
2025-05-08T09:35:11.254208Z DEBUG rauthy_models::migration::db_migrate: before users insert
2025-05-08T09:35:11.261829Z DEBUG rauthy_models::migration::db_migrate: after users insert
2025-05-08T09:35:11.261916Z DEBUG rauthy_models::migration::db_migrate: Migrating table: passkeys
2025-05-08T09:35:11.262759Z DEBUG rauthy_models::migration::db_migrate: Migrating table: clients
2025-05-08T09:35:11.268752Z DEBUG rauthy_models::migration::db_migrate: Migrating table: clients_dyn
2025-05-08T09:35:11.269549Z DEBUG rauthy_models::migration::db_migrate: Migrating table: client_logos

thread 'main' panicked at src\models\src\entity\logos.rs:82:26:
error retrieving column updated: invalid column `updated`
stack backtrace:
note: Some details are omitted, run with `RUST_BACKTRACE=full` for a verbose backtrace.

Note:
This is running on windows compiled from commit 4ec5cae tag/v0.29.1
@sebadob
Copy link
Owner
sebadob commented May 8, 2025 •
edited
Loading

First I tried to directly use to migrate into a new empty db from it, but it rejected it, because different major version.

Glad that worked fine. I added this additional check with 0.29.

Yeah I took at look at the migration query and the newly added updated column has been missing in the migration SELECT. That slipped through in testing, because there were no custom logos during migration tests.

The new updated column is used to make efficient use of logo caching. After taking a look, it was missing for the aurh_provider_logos table as well.

Edit: Your migration will probably be fine if you delete the custom logos, right? Could you please test it?

@sebadob sebadob added the bug Something isn't working label May 8, 2025
@cocoon
Copy link
Contributor Author
cocoon commented May 8, 2025

ok deleted 2 rows from "client_logo", now it stops here:

2025-05-08T10:13:24.678556Z  INFO rauthy_models::migration::db_migrate: Starting migration from Postgres to another DB
2025-05-08T10:13:24.697056Z  INFO rauthy_models::database: Database Connection Pool created successfully
2025-05-08T10:13:24.698150Z DEBUG rauthy_models::migration::db_migrate: Migrating table: config
2025-05-08T10:13:24.700766Z DEBUG rauthy_models::migration::db_migrate: Migrating table: api_keys
2025-05-08T10:13:24.701613Z DEBUG rauthy_models::migration::db_migrate: Migrating table: auth_providers
2025-05-08T10:13:24.702732Z DEBUG rauthy_models::migration::db_migrate: Migrating table: auth_provider_logos
2025-05-08T10:13:24.703343Z DEBUG rauthy_models::migration::db_migrate: Migrating table: pictures
2025-05-08T10:13:24.703884Z DEBUG rauthy_models::migration::db_migrate: Migrating table: users
2025-05-08T10:13:24.704460Z DEBUG rauthy_models::migration::db_migrate: before users insert
2025-05-08T10:13:24.712883Z DEBUG rauthy_models::migration::db_migrate: after users insert
2025-05-08T10:13:24.712974Z DEBUG rauthy_models::migration::db_migrate: Migrating table: passkeys
2025-05-08T10:13:24.713714Z DEBUG rauthy_models::migration::db_migrate: Migrating table: clients
2025-05-08T10:13:24.720860Z DEBUG rauthy_models::migration::db_migrate: Migrating table: clients_dyn
2025-05-08T10:13:24.721507Z DEBUG rauthy_models::migration::db_migrate: Migrating table: client_logos
2025-05-08T10:13:24.722167Z DEBUG rauthy_models::migration::db_migrate: Migrating table: groups
2025-05-08T10:13:24.727588Z DEBUG rauthy_models::migration::db_migrate: Migrating table: jwks
2025-05-08T10:13:24.733055Z DEBUG rauthy_models::migration::db_migrate: Migrating table: magic_links
2025-05-08T10:13:24.769561Z DEBUG rauthy_models::migration::db_migrate: Migrating table: password_policy

thread 'main' panicked at src\models\src\entity\config.rs:12:21:
error retrieving column id: invalid column `id`
stack backtrace:
note: Some details are omitted, run with `RUST_BACKTRACE=full` for a verbose backtrace.

@sebadob
Copy link
Owner
sebadob commented May 8, 2025

Oh yeah, and that's because of late query / memory optimizations.

I will fix it and do some more tests, thanks.

@sebadob sebadob mentioned this issue May 8, 2025
Merged
@sebadob
Copy link
Owner
sebadob commented May 8, 2025 •
edited
Loading

If you have the DB available locally, you could test with #919

Edit: Oh, not yet. Let me set up some test DBs first.

@sebadob
Copy link
Owner
sebadob commented May 8, 2025

Now please test with #919.

I can successfully migrate Hiqlite -> Postgres -> Hiqlite with this. I also added a custom logo for a client and made sure there is data in that table. This PR should make it work.

@cocoon
Copy link
Contributor Author
cocoon commented May 8, 2025

Great thanks, will test and share results 👍

@cocoon
Copy link
Contributor Author
cocoon commented May 8, 2025 •
edited
Loading

Fails:

2025-05-08T12:07:40.641555Z  INFO rauthy_models::migration::db_migrate: Starting migration from Postgres to another DB
2025-05-08T12:07:40.664090Z  INFO rauthy_models::database: Database Connection Pool created successfully
2025-05-08T12:07:40.665200Z DEBUG rauthy_models::migration::db_migrate: Migrating table: config
2025-05-08T12:07:40.667024Z DEBUG rauthy_models::migration::db_migrate: Migrating table: api_keys
2025-05-08T12:07:40.668265Z DEBUG rauthy_models::migration::db_migrate: Migrating table: auth_providers
2025-05-08T12:07:40.669208Z DEBUG rauthy_models::migration::db_migrate: Migrating table: auth_provider_logos
2025-05-08T12:07:40.669802Z DEBUG rauthy_models::migration::db_migrate: Migrating table: pictures
2025-05-08T12:07:40.670439Z DEBUG rauthy_models::migration::db_migrate: Migrating table: users
2025-05-08T12:07:40.674841Z DEBUG rauthy_models::migration::db_migrate: Migrating table: passkeys
2025-05-08T12:07:40.675657Z DEBUG rauthy_models::migration::db_migrate: Migrating table: clients
2025-05-08T12:07:40.679528Z DEBUG rauthy_models::migration::db_migrate: Migrating table: clients_dyn
2025-05-08T12:07:40.680080Z DEBUG rauthy_models::migration::db_migrate: Migrating table: client_logos
2025-05-08T12:07:40.682680Z DEBUG rauthy_models::migration::db_migrate: Migrating table: groups
2025-05-08T12:07:40.686260Z DEBUG rauthy_models::migration::db_migrate: Migrating table: jwks
2025-05-08T12:07:40.689227Z DEBUG rauthy_models::migration::db_migrate: Migrating table: magic_links
2025-05-08T12:07:40.689839Z DEBUG rauthy_models::migration::db_migrate: Migrating table: refresh_tokens
2025-05-08T12:07:40.690541Z DEBUG rauthy_models::migration::db_migrate: Migrating table: roles
2025-05-08T12:07:40.693778Z DEBUG rauthy_models::migration::db_migrate: Migrating table: scopes
2025-05-08T12:07:40.698072Z DEBUG rauthy_models::migration::db_migrate: Migrating table: events
2025-05-08T12:07:40.700429Z DEBUG rauthy_models::migration::db_migrate: Migrating table: user_attr_config
2025-05-08T12:07:40.711384Z DEBUG rauthy_error::error_impls: db error: FEHLER: Syntaxfehler bei »desc«
2025-05-08T12:07:40.711495Z ERROR rauthy_error::error_impls: SqlState(42601) -> db error: FEHLER: Syntaxfehler bei »desc«

thread 'main' panicked at \src\models\src\database.rs:266:25:
Error during db migration: ErrorResponse { timestamp: 1746706060, error: Database, message: "db error: FEHLER: Syntaxfehler bei »desc«" }
stack backtrace:
note: Some details are omitted, run with `RUST_BACKTRACE=full` for a verbose backtrace.

not much inside:

-- Exportiere Daten aus Tabelle public.user_attr_config: 2 rows
/*!40000 ALTER TABLE "user_attr_config" DISABLE KEYS */;
REPLACE INTO "user_attr_config" ("name", "desc") VALUES
	('uid', 'user id'),
	('role', 'Role');
/*!40000 ALTER TABLE "user_attr_config" ENABLE KEYS */;

@cocoon
Copy link
Contributor Author
cocoon commented May 8, 2025

"desc" might be a reserved word?

https://www.postgresql.org/docs/current/sql-keywords-appendix.html

@sebadob
Copy link
Owner
sebadob commented May 8, 2025 •
edited
Loading

"desc" might be a reserved word?

Oh yeah, of course it is. Today is not my day apparently. ^^

Pushed the fix after doing additional tests and adding a custom scope + attribute config + attribute for user.

Edit:

These migrations are quite annoying, because I cannot use the existing queries, as the source database can be either hiqlite or Postgres and I don't want to trigger caching.

@cocoon
Copy link
Contributor Author
cocoon commented May 8, 2025 •
edited
Loading

I am sure this will be the last one :)

2025-05-08T13:12:53.552120Z  INFO rauthy_models::migration::db_migrate: Starting migration from Postgres to another DB
2025-05-08T13:12:53.570763Z  INFO rauthy_models::database: Database Connection Pool created successfully
2025-05-08T13:12:53.571759Z DEBUG rauthy_models::migration::db_migrate: Migrating table: config
2025-05-08T13:12:53.573628Z DEBUG rauthy_models::migration::db_migrate: Migrating table: api_keys
2025-05-08T13:12:53.574586Z DEBUG rauthy_models::migration::db_migrate: Migrating table: auth_providers
2025-05-08T13:12:53.575646Z DEBUG rauthy_models::migration::db_migrate: Migrating table: auth_provider_logos
2025-05-08T13:12:53.576285Z DEBUG rauthy_models::migration::db_migrate: Migrating table: pictures
2025-05-08T13:12:53.576865Z DEBUG rauthy_models::migration::db_migrate: Migrating table: users
2025-05-08T13:12:53.584515Z DEBUG rauthy_models::migration::db_migrate: Migrating table: passkeys
2025-05-08T13:12:53.585255Z DEBUG rauthy_models::migration::db_migrate: Migrating table: clients
2025-05-08T13:12:53.590970Z DEBUG rauthy_models::migration::db_migrate: Migrating table: clients_dyn
2025-05-08T13:12:53.591632Z DEBUG rauthy_models::migration::db_migrate: Migrating table: client_logos
2025-05-08T13:12:53.593976Z DEBUG rauthy_models::migration::db_migrate: Migrating table: groups
2025-05-08T13:12:53.597504Z DEBUG rauthy_models::migration::db_migrate: Migrating table: jwks
2025-05-08T13:12:53.601170Z DEBUG rauthy_models::migration::db_migrate: Migrating table: magic_links
2025-05-08T13:12:53.601821Z DEBUG rauthy_models::migration::db_migrate: Migrating table: refresh_tokens
2025-05-08T13:12:53.602517Z DEBUG rauthy_models::migration::db_migrate: Migrating table: roles
2025-05-08T13:12:53.605582Z DEBUG rauthy_models::migration::db_migrate: Migrating table: scopes
2025-05-08T13:12:53.610179Z DEBUG rauthy_models::migration::db_migrate: Migrating table: events
2025-05-08T13:12:53.612634Z DEBUG rauthy_models::migration::db_migrate: Migrating table: user_attr_config
2025-05-08T13:12:53.614570Z DEBUG rauthy_models::migration::db_migrate: Migrating table: user_attr_values
2025-05-08T13:12:53.635114Z DEBUG rauthy_models::migration::db_migrate: Migrating table: users_values
2025-05-08T13:12:53.635848Z DEBUG rauthy_models::migration::db_migrate: Migrating table: devices
2025-05-08T13:12:53.636454Z DEBUG rauthy_models::migration::db_migrate: Migrating table: refresh_tokens_devices
2025-05-08T13:12:53.637026Z DEBUG rauthy_models::migration::db_migrate: Migrating table: sessions
2025-05-08T13:12:53.638604Z DEBUG rauthy_models::migration::db_migrate: Migrating table: user_login_states
2025-05-08T13:12:53.657177Z DEBUG rauthy_models::migration::db_migrate: Migrating table: failed_backchannel_logouts
2025-05-08T13:12:53.657910Z DEBUG rauthy_models::migration::db_migrate: Migrating table: recent_passwords
2025-05-08T13:12:53.658416Z DEBUG rauthy_models::migration::db_migrate: Migrating table: themes
2025-05-08T13:12:53.659000Z DEBUG rauthy_error::error_impls: error serializing parameter 2: cannot convert between the Rust type `i64` and the Postgres type `int4`
2025-05-08T13:12:53.659067Z ERROR rauthy_error::error_impls: db-err-id(ctMxxW): error serializing parameter 2: cannot convert between the Rust type `i64` and the Postgres type `int4`

thread 'main' panicked at src\models\src\database.rs:266:25:
Error during db migration: ErrorResponse { timestamp: 1746709973, error: Database, message: "Database error without inner error, check logs for:  db-err-id(ctMxxW)" }
stack backtrace:
note: Some details are omitted, run with `RUST_BACKTRACE=full` for a verbose backtrace.

I might not be able to test more today, so I will update again as soon as possible ^^

@sebadob
Copy link
Owner
sebadob commented May 8, 2025

Sorry for all the confusion.

These migrations are a thing you cannot really integration-test easily.

I created custom themes and even a custom webid entry now to make sure that the last 2 are working fine a well.
The main issue was, that they were using custom database conversions which cannot be easily re-used, especially for the sqlite part of it.

@cocoon
Copy link
Contributor Author
cocoon commented May 9, 2025

No need to feel sorry, nice to see things improve!
And compiled from main today and now it had no problems anymore, thanks 👍

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
bug Something isn't working
Projects
None yet
Milestone
No milestone
Development

Successfully merging a pull request may close this issue.

Fix: DB migration queries sebadob/rauthy
2 participants
@cocoon @sebadob
0