[GH-ISSUE #1045] [BUG] Can't upgrade DB schema to version 8 when using postgres #374

New issue

Closed

opened 2026-02-27 08:16:56 +03:00 by kerem · 4 comments

kerem commented

2026-02-27 08:16:56 +03:00

Owner

Originally created by @tblaschke on GitHub (Nov 21, 2024).
Original GitHub issue: https://github.com/lldap/lldap/issues/1045

Describe the bug
Hi, it seems that upgrading the DB schema fails during the upgrade of version 0.5 to version 0.6 while using postgres as DB.

This causes my lldap instance to be inaccessible, as I can't startup v0.6 and reverting to v0.5 fails due to the schema beeing too new. (Had to restore the DB from a backup)

To Reproduce
Steps to reproduce the behavior:

Setup an lldap instance v0.5 using postgres as DB
Upgrade lldap to v0.6

Expected behavior
Update from v0.5 to v0.6 should work even when using Postgres

Logs
LLDAP logs

2024-11-21T17:06:18.705491180+00:00  INFO     set_up_server [ 4.39ms | 93.87% / 100.00% ]
2024-11-21T17:06:18.705513535+00:00  INFO     ┝━ ｉ [info]: Starting LLDAP version 0.6.0
2024-11-21T17:06:18.715020826+00:00  DEBUG    ┝━ get_schema_version [ 269µs | 6.13% ]
2024-11-21T17:06:18.721196526+00:00  DEBUG    │  ┕━ 🐛 [debug]:  | return: Some(SchemaVersion(5))
2024-11-21T17:06:18.721204849+00:00  INFO     ┝━ ｉ [info]: Upgrading DB schema from version 5
2024-11-21T17:06:18.721205922+00:00  INFO     ┝━ ｉ [info]: Upgrading DB schema to version 6
2024-11-21T17:06:18.751703272+00:00  INFO     ┝━ ｉ [info]: Upgrading DB schema to version 7
2024-11-21T17:06:18.757698108+00:00  INFO     ┕━ ｉ [info]: Upgrading DB schema to version 8
Error: while creating base tables

Caused by:
    0: Execution Error: error returned from database: syntax error at or near "LIMIT"
    1: error returned from database: syntax error at or near "LIMIT"
    2: error returned from database: syntax error at or near "LIMIT"
    3: syntax error at or near "LIMIT"

Postgres logs


PostgreSQL Database directory appears to contain a database; Skipping initialization
2024-11-21 17:06:08.272 GMT [1] LOG:  starting PostgreSQL 16.2 on x86_64-pc-linux-musl, compiled by gcc (Alpine 13.2.1_git20231014) 13.2.1 20231014, 64-bit
2024-11-21 17:06:08.272 GMT [1] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2024-11-21 17:06:08.272 GMT [1] LOG:  listening on IPv6 address "::", port 5432
2024-11-21 17:06:08.273 GMT [1] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2024-11-21 17:06:08.277 GMT [24] LOG:  database system was shut down at 2024-11-21 17:04:06 GMT
2024-11-21 17:06:08.284 GMT [1] LOG:  database system is ready to accept connections
2024-11-21 17:06:18.721 GMT [35] LOG:  execute sqlx_s_1: SELECT "version" FROM "metadata"
2024-11-21 17:06:18.725 GMT [36] LOG:  statement: BEGIN
2024-11-21 17:06:18.725 GMT [36] LOG:  execute sqlx_s_1: ALTER TABLE "groups" ADD COLUMN "lowercase_display_name" varchar(255) NOT NULL DEFAULT 'UNSET'
2024-11-21 17:06:18.737 GMT [36] LOG:  execute sqlx_s_2: ALTER TABLE "users" ADD COLUMN "lowercase_email" varchar(255) NOT NULL DEFAULT 'UNSET'
2024-11-21 17:06:18.740 GMT [36] LOG:  execute sqlx_s_3: UPDATE "groups" SET "lowercase_display_name" = LOWER("display_name")
2024-11-21 17:06:18.743 GMT [36] LOG:  execute sqlx_s_4: UPDATE "users" SET "lowercase_email" = LOWER("email")
2024-11-21 17:06:18.743 GMT [36] LOG:  execute sqlx_s_5: UPDATE "metadata" SET "version" = $1
2024-11-21 17:06:18.743 GMT [36] DETAIL:  parameters: $1 = '6'
2024-11-21 17:06:18.743 GMT [36] LOG:  statement: COMMIT
2024-11-21 17:06:18.751 GMT [35] LOG:  statement: BEGIN
2024-11-21 17:06:18.752 GMT [35] LOG:  execute sqlx_s_2: ALTER TABLE "metadata" ADD COLUMN "private_key_hash" bytea
2024-11-21 17:06:18.755 GMT [35] LOG:  execute sqlx_s_3: ALTER TABLE "metadata" ADD COLUMN "private_key_location" varchar(255)
2024-11-21 17:06:18.756 GMT [35] LOG:  execute sqlx_s_4: UPDATE "metadata" SET "version" = $1
2024-11-21 17:06:18.756 GMT [35] DETAIL:  parameters: $1 = '7'
2024-11-21 17:06:18.756 GMT [35] LOG:  statement: COMMIT
2024-11-21 17:06:18.757 GMT [36] LOG:  statement: BEGIN
2024-11-21 17:06:18.762 GMT [36] LOG:  execute sqlx_s_6: SELECT "user_id", "group_id", COUNT("memberships"."user_id") AS "cnt" FROM "memberships" GROUP BY "user_id", "group_id" HAVING COUNT("memberships"."user_id") > $1
2024-11-21 17:06:18.762 GMT [36] DETAIL:  parameters: $1 = '1'
2024-11-21 17:06:18.762 GMT [36] ERROR:  syntax error at or near "LIMIT" at character 68
2024-11-21 17:06:18.762 GMT [36] STATEMENT:  DELETE FROM "memberships" WHERE "user_id" = $1 AND "group_id" = $2 LIMIT $3
2024-11-21 17:06:18.762 GMT [36] LOG:  could not receive data from client: Connection reset by peer

Originally created by @tblaschke on GitHub (Nov 21, 2024). Original GitHub issue: https://github.com/lldap/lldap/issues/1045 **Describe the bug** Hi, it seems that upgrading the DB schema fails during the upgrade of version 0.5 to version 0.6 while using postgres as DB. This causes my lldap instance to be inaccessible, as I can't startup v0.6 and reverting to v0.5 fails due to the schema beeing too new. (Had to restore the DB from a backup) **To Reproduce** Steps to reproduce the behavior: 1. Setup an lldap instance v0.5 using postgres as DB 2. Upgrade lldap to v0.6 **Expected behavior** Update from v0.5 to v0.6 should work even when using Postgres **Logs** LLDAP logs ``` 2024-11-21T17:06:18.705491180+00:00 INFO set_up_server [ 4.39ms | 93.87% / 100.00% ] 2024-11-21T17:06:18.705513535+00:00 INFO ┝━ ｉ [info]: Starting LLDAP version 0.6.0 2024-11-21T17:06:18.715020826+00:00 DEBUG ┝━ get_schema_version [ 269µs | 6.13% ] 2024-11-21T17:06:18.721196526+00:00 DEBUG │ ┕━ 🐛 [debug]: | return: Some(SchemaVersion(5)) 2024-11-21T17:06:18.721204849+00:00 INFO ┝━ ｉ [info]: Upgrading DB schema from version 5 2024-11-21T17:06:18.721205922+00:00 INFO ┝━ ｉ [info]: Upgrading DB schema to version 6 2024-11-21T17:06:18.751703272+00:00 INFO ┝━ ｉ [info]: Upgrading DB schema to version 7 2024-11-21T17:06:18.757698108+00:00 INFO ┕━ ｉ [info]: Upgrading DB schema to version 8 Error: while creating base tables Caused by: 0: Execution Error: error returned from database: syntax error at or near "LIMIT" 1: error returned from database: syntax error at or near "LIMIT" 2: error returned from database: syntax error at or near "LIMIT" 3: syntax error at or near "LIMIT" ``` Postgres logs ``` PostgreSQL Database directory appears to contain a database; Skipping initialization 2024-11-21 17:06:08.272 GMT [1] LOG: starting PostgreSQL 16.2 on x86_64-pc-linux-musl, compiled by gcc (Alpine 13.2.1_git20231014) 13.2.1 20231014, 64-bit 2024-11-21 17:06:08.272 GMT [1] LOG: listening on IPv4 address "0.0.0.0", port 5432 2024-11-21 17:06:08.272 GMT [1] LOG: listening on IPv6 address "::", port 5432 2024-11-21 17:06:08.273 GMT [1] LOG: listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432" 2024-11-21 17:06:08.277 GMT [24] LOG: database system was shut down at 2024-11-21 17:04:06 GMT 2024-11-21 17:06:08.284 GMT [1] LOG: database system is ready to accept connections 2024-11-21 17:06:18.721 GMT [35] LOG: execute sqlx_s_1: SELECT "version" FROM "metadata" 2024-11-21 17:06:18.725 GMT [36] LOG: statement: BEGIN 2024-11-21 17:06:18.725 GMT [36] LOG: execute sqlx_s_1: ALTER TABLE "groups" ADD COLUMN "lowercase_display_name" varchar(255) NOT NULL DEFAULT 'UNSET' 2024-11-21 17:06:18.737 GMT [36] LOG: execute sqlx_s_2: ALTER TABLE "users" ADD COLUMN "lowercase_email" varchar(255) NOT NULL DEFAULT 'UNSET' 2024-11-21 17:06:18.740 GMT [36] LOG: execute sqlx_s_3: UPDATE "groups" SET "lowercase_display_name" = LOWER("display_name") 2024-11-21 17:06:18.743 GMT [36] LOG: execute sqlx_s_4: UPDATE "users" SET "lowercase_email" = LOWER("email") 2024-11-21 17:06:18.743 GMT [36] LOG: execute sqlx_s_5: UPDATE "metadata" SET "version" = $1 2024-11-21 17:06:18.743 GMT [36] DETAIL: parameters: $1 = '6' 2024-11-21 17:06:18.743 GMT [36] LOG: statement: COMMIT 2024-11-21 17:06:18.751 GMT [35] LOG: statement: BEGIN 2024-11-21 17:06:18.752 GMT [35] LOG: execute sqlx_s_2: ALTER TABLE "metadata" ADD COLUMN "private_key_hash" bytea 2024-11-21 17:06:18.755 GMT [35] LOG: execute sqlx_s_3: ALTER TABLE "metadata" ADD COLUMN "private_key_location" varchar(255) 2024-11-21 17:06:18.756 GMT [35] LOG: execute sqlx_s_4: UPDATE "metadata" SET "version" = $1 2024-11-21 17:06:18.756 GMT [35] DETAIL: parameters: $1 = '7' 2024-11-21 17:06:18.756 GMT [35] LOG: statement: COMMIT 2024-11-21 17:06:18.757 GMT [36] LOG: statement: BEGIN 2024-11-21 17:06:18.762 GMT [36] LOG: execute sqlx_s_6: SELECT "user_id", "group_id", COUNT("memberships"."user_id") AS "cnt" FROM "memberships" GROUP BY "user_id", "group_id" HAVING COUNT("memberships"."user_id") > $1 2024-11-21 17:06:18.762 GMT [36] DETAIL: parameters: $1 = '1' 2024-11-21 17:06:18.762 GMT [36] ERROR: syntax error at or near "LIMIT" at character 68 2024-11-21 17:06:18.762 GMT [36] STATEMENT: DELETE FROM "memberships" WHERE "user_id" = $1 AND "group_id" = $2 LIMIT $3 2024-11-21 17:06:18.762 GMT [36] LOG: could not receive data from client: Connection reset by peer ```

kerem

2026-02-27 08:16:56 +03:00

closed this issue
added the
bug

backend

rust
labels

kerem commented

2026-02-27 08:16:57 +03:00

Author

Owner

@nitnelave commented on GitHub (Nov 21, 2024):

Yeah, that's a bit unfortunate, I thought PostgreSQL supported that.
Anyway, the problem is that you have a user that was added multiple times to the same group. The migration attempts to correct that. I might try to change this to delete all the instances of this membership and then add a single one, that way you don't need LIMIT.

Right now, you can manually delete the instances of duplicated memberships. (In the 0.5 DB)

@nitnelave commented on GitHub (Nov 21, 2024): Yeah, that's a bit unfortunate, I thought PostgreSQL supported that. Anyway, the problem is that you have a user that was added multiple times to the same group. The migration attempts to correct that. I might try to change this to delete all the instances of this membership and then add a single one, that way you don't need LIMIT. Right now, you can manually delete the instances of duplicated memberships. (In the 0.5 DB)

kerem commented

2026-02-27 08:16:57 +03:00

Author

Owner

@nitnelave commented on GitHub (Nov 21, 2024):

Before you do anything manually, I'd like you to test a patch on your DB, if you don't mind. It should solve your problem.

@nitnelave commented on GitHub (Nov 21, 2024): Before you do anything manually, I'd like you to test a patch on your DB, if you don't mind. It should solve your problem.

kerem commented

2026-02-27 08:16:57 +03:00

Author

Owner

@nitnelave commented on GitHub (Nov 21, 2024):

Can you try the latest docker image? That's just 0.6 plus this patch, which should fix your migration problems.

@nitnelave commented on GitHub (Nov 21, 2024): Can you try the `latest` docker image? That's just 0.6 plus this patch, which should fix your migration problems.

kerem commented

2026-02-27 08:16:57 +03:00

Author

Owner

@tblaschke commented on GitHub (Nov 21, 2024):

I can confirm latest fixes the DB schema migration for me and the lldap starts up. Thanks for the quick fix!

@tblaschke commented on GitHub (Nov 21, 2024): I can confirm `latest` fixes the DB schema migration for me and the lldap starts up. Thanks for the quick fix!

kerem referenced this issue

2026-02-27 09:09:26 +03:00

[PR #374] [MERGED] Kanboard example config #704