Repository: lldap/lldap (language: rust)

Issue:
[BUG] Can't upgrade DB schema to version 8 when using postgres
**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
```




Discussion / hints:
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)

Fix patch:
diff --git a/server/src/domain/sql_migrations.rs b/server/src/domain/sql_migrations.rs
index 4bd54cf2..64630a1e 100644
--- a/server/src/domain/sql_migrations.rs
+++ b/server/src/domain/sql_migrations.rs
@@ -5,8 +5,8 @@ use crate::domain::{
 use itertools::Itertools;
 use sea_orm::{
     sea_query::{
-        self, all, Alias, BinOper, BlobSize::Blob, ColumnDef, Expr, ForeignKey, ForeignKeyAction,
-        Func, Index, Query, SimpleExpr, Table, Value,
+        self, all, BinOper, BlobSize::Blob, ColumnDef, Expr, ForeignKey, ForeignKeyAction, Func,
+        Index, Query, SimpleExpr, Table, Value,
     },
     ConnectionTrait, DatabaseTransaction, DbErr, DeriveIden, FromQueryResult, Iden, Order,
     Statement, TransactionTrait,
@@ -970,21 +970,15 @@ async fn migrate_to_v8(transaction: DatabaseTransaction) -> Result<DatabaseTrans
     let builder = transaction.get_database_backend();
     // Remove duplicate memberships.
     #[derive(FromQueryResult)]
-    #[allow(dead_code)]
     struct MembershipInfo {
         user_id: UserId,
         group_id: GroupId,
-        cnt: i64,
     }
-    let mut delete_queries = MembershipInfo::find_by_statement(
+    for MembershipInfo { user_id, group_id } in MembershipInfo::find_by_statement(
         builder.build(
             Query::select()
                 .from(Memberships::Table)
                 .columns([Memberships::UserId, Memberships::GroupId])
-                .expr_as(
-                    Expr::count(Expr::col((Memberships::Table, Memberships::UserId))),
-                    Alias::new("cnt"),
-                )
                 .group_by_columns([Memberships::UserId, Memberships::GroupId])
                 .cond_having(all![SimpleExpr::Binary(
                     Box::new(Expr::col((Memberships::Table, Memberships::UserId)).count()),
@@ -996,38 +990,29 @@ async fn migrate_to_v8(transaction: DatabaseTransaction) -> Result<DatabaseTrans
     .all(&transaction)
     .await?
     .into_iter()
-    .map(
-        |MembershipInfo {
-             user_id,
-             group_id,
-             cnt,
-         }| {
-            builder
-                .build(
+    {
+        transaction
+            .execute(
+                builder.build(
                     Query::delete()
                         .from_table(Memberships::Table)
                         .cond_where(all![
-                            Expr::col(Memberships::UserId).eq(user_id),
+                            Expr::col(Memberships::UserId).eq(&user_id),
                             Expr::col(Memberships::GroupId).eq(group_id)
-                        ])
-                        .limit(cnt as u64 - 1),
-                )
-                .to_owned()
-        },
-    )
-    .peekable();
-    if delete_queries.peek().is_some() {
-        match transaction.get_database_backend() {
-            sea_orm::DatabaseBackend::Sqlite => {
-                return Err(DbErr::Migration(format!(
-                    "The Sqlite driver does not support LIMIT in DELETE. Run these queries manually:\n{}" , delete_queries.map(|s| s.to_string()).join("\n"))));
-            }
-            sea_orm::DatabaseBackend::MySql | sea_orm::DatabaseBackend::Postgres => {
-                for query in delete_queries {
-                    transaction.execute(query).await?;
-                }
-            }
-        }
+                        ]),
+                ),
+            )
+            .await?;
+        transaction
+            .execute(
+                builder.build(
+                    Query::insert()
+                        .into_table(Memberships::Table)
+                        .columns([Memberships::UserId, Memberships::GroupId])
+                        .values_panic([user_id.into(), group_id.into()]),
+                ),
+            )
+            .await?;
     }
     transaction
         .execute(


Test patch: