Database migration error

Hi all, my self-hosted Windmill stopped working and shows this error before failing.

INFO Binary is in 'server' mode | timestamp=2024-04-12T14:04:18.792315Z target=windmill 
INFO Connecting to database... | timestamp=2024-04-12T14:04:18.792375Z target=windmill 
INFO Database connected | timestamp=2024-04-12T14:04:19.018736Z target=windmill 
INFO PostgreSQL version: PostgreSQL 16.1 on aarch64-unknown-linux-gnu, compiled by gcc (GCC) 7.3.1 20180712 (Red Hat 7.3.1-6), 64-bit (windmill require PG >= 14) | timestamp=2024-04-12T14:04:19.037634Z target=windmill 
INFO Acquiring global PG lock for potential migration with pid: Some(8859) | timestamp=2024-04-12T14:04:19.040425Z target=windmill_api::db 
INFO Acquired global PG lock | timestamp=2024-04-12T14:04:19.043802Z target=windmill_api::db 
INFO Started applying migration 20240209103543: update app new | timestamp=2024-04-12T14:04:19.050553Z target=windmill_api::db 
INFO Finished applying migration 20240209103543 | timestamp=2024-04-12T14:04:19.054929Z target=windmill_api::db 
Error: Migrating database: while executing migrations: error returned from database: more than one row returned by a subquery used as an expression
Caused by:
    0: while executing migrations: error returned from database: more than one row returned by a subquery used as an expression
    1: error returned from database: more than one row returned by a subquery used as an expression
    2: more than one row returned by a subquery used as an expression

INFO Binary is in 'server' mode | timestamp=2024-04-12T14:04:18.792315Z target=windmill 
INFO Connecting to database... | timestamp=2024-04-12T14:04:18.792375Z target=windmill 
INFO Database connected | timestamp=2024-04-12T14:04:19.018736Z target=windmill 
INFO PostgreSQL version: PostgreSQL 16.1 on aarch64-unknown-linux-gnu, compiled by gcc (GCC) 7.3.1 20180712 (Red Hat 7.3.1-6), 64-bit (windmill require PG >= 14) | timestamp=2024-04-12T14:04:19.037634Z target=windmill 
INFO Acquiring global PG lock for potential migration with pid: Some(8859) | timestamp=2024-04-12T14:04:19.040425Z target=windmill_api::db 
INFO Acquired global PG lock | timestamp=2024-04-12T14:04:19.043802Z target=windmill_api::db 
INFO Started applying migration 20240209103543: update app new | timestamp=2024-04-12T14:04:19.050553Z target=windmill_api::db 
INFO Finished applying migration 20240209103543 | timestamp=2024-04-12T14:04:19.054929Z target=windmill_api::db 
Error: Migrating database: while executing migrations: error returned from database: more than one row returned by a subquery used as an expression
Caused by:
    0: while executing migrations: error returned from database: more than one row returned by a subquery used as an expression
    1: error returned from database: more than one row returned by a subquery used as an expression
    2: more than one row returned by a subquery used as an expression

Di you have any suggestion on how tò solve this? Many thanks for your support.

12 Replies

rubenf•7mo ago

What does:

(
            SELECT
                id
            FROM
                app
            WHERE
                workspace_id = 'admins'
                AND path = 'g/all/setup_app'
        )

(
            SELECT
                id
            FROM
                app
            WHERE
                workspace_id = 'admins'
                AND path = 'g/all/setup_app'
        )

return ?

RBlhaze•7mo ago

|id| |--| |1| |1| Is this duplication the issue? What should i do then? Thanks for your support

rubenf•7mo ago

I don't know how you arrived to that state but you need to delete one of them

RBlhaze•7mo ago

@rubenf i've done so, but now i get

INFO Binary is in 'server' mode | timestamp=2024-04-12T16:11:43.197934Z target=windmill 
INFO Connecting to database... | timestamp=2024-04-12T16:11:43.202512Z target=windmill 
INFO Database connected | timestamp=2024-04-12T16:11:43.368182Z target=windmill 
INFO PostgreSQL version: PostgreSQL 16.1 on aarch64-unknown-linux-gnu, compiled by gcc (GCC) 7.3.1 20180712 (Red Hat 7.3.1-6), 64-bit (windmill require PG >= 14) | timestamp=2024-04-12T16:11:43.377295Z target=windmill 
INFO Acquiring global PG lock for potential migration with pid: Some(23537) | timestamp=2024-04-12T16:11:43.379968Z target=windmill_api::db 
INFO Acquired global PG lock | timestamp=2024-04-12T16:11:43.381494Z target=windmill_api::db 
INFO Started applying migration 20240209103543: update app new | timestamp=2024-04-12T16:11:43.385891Z target=windmill_api::db 
INFO Finished applying migration 20240209103543 | timestamp=2024-04-12T16:11:43.388612Z target=windmill_api::db 
Error: Migrating database: while executing migrations: error returned from database: null value in column "app_id" of relation "app_version" violates not-null constraint
Caused by:
    0: while executing migrations: error returned from database: null value in column "app_id" of relation "app_version" violates not-null constraint
    1: error returned from database: null value in column "app_id" of relation "app_version" violates not-null constraint
    2: null value in column "app_id" of relation "app_version" violates not-null constraint

INFO Binary is in 'server' mode | timestamp=2024-04-12T16:11:43.197934Z target=windmill 
INFO Connecting to database... | timestamp=2024-04-12T16:11:43.202512Z target=windmill 
INFO Database connected | timestamp=2024-04-12T16:11:43.368182Z target=windmill 
INFO PostgreSQL version: PostgreSQL 16.1 on aarch64-unknown-linux-gnu, compiled by gcc (GCC) 7.3.1 20180712 (Red Hat 7.3.1-6), 64-bit (windmill require PG >= 14) | timestamp=2024-04-12T16:11:43.377295Z target=windmill 
INFO Acquiring global PG lock for potential migration with pid: Some(23537) | timestamp=2024-04-12T16:11:43.379968Z target=windmill_api::db 
INFO Acquired global PG lock | timestamp=2024-04-12T16:11:43.381494Z target=windmill_api::db 
INFO Started applying migration 20240209103543: update app new | timestamp=2024-04-12T16:11:43.385891Z target=windmill_api::db 
INFO Finished applying migration 20240209103543 | timestamp=2024-04-12T16:11:43.388612Z target=windmill_api::db 
Error: Migrating database: while executing migrations: error returned from database: null value in column "app_id" of relation "app_version" violates not-null constraint
Caused by:
    0: while executing migrations: error returned from database: null value in column "app_id" of relation "app_version" violates not-null constraint
    1: error returned from database: null value in column "app_id" of relation "app_version" violates not-null constraint
    2: null value in column "app_id" of relation "app_version" violates not-null constraint

rubenf•7mo ago

did you delete both of them ?

RBlhaze•7mo ago

I've manually delete one of them, but now the app table appears to be empty

rubenf•7mo ago

try to recreate one at the right path

RBlhaze•7mo ago

Do you mean reinserting the row? I'm not sure which values to use aside from id and workspace_id. Thanks for your patience

rubenf•7mo ago

put blank where you can, just have it be right workspace id and path

RBlhaze•7mo ago

Which policy should i put in? It has non null constraint Update I've managed to retrieve data from a dump of the db, and recreated the rows in app table. Server Is now stuck with a 500 error

rubenf•7mo ago

that's likely not coming from windmill but you will have to share more than that what are the logs of the servers saying

RBlhaze•7mo ago

I got a load of 429 errors from the Page in fetching resources. As i'm not seeing errors in the server logs, i'm assuming this has something to do with my rev proxy rate limit. I'll keep you posted while i figure this out, many thanks 👍 Same error even disabling the rate limit, so i'm not quite understandg the 429s. I will try a few things an post a new update. Many thanks, your support Is really appreciated. It was the rate limit indeed. Consider this solved, many thanks for your time. Cheers