From 069eb40ebfdca4030d0c87d56f4398f6f5d1b55e Mon Sep 17 00:00:00 2001
From: "Joshua M. Boniface" <joshua@boniface.me>
Date: Sun, 21 Jun 2026 22:49:46 -0400
Subject: Fix too many SQL variables in DeleteItem for large batch deletes

The FixIncorrectOwnerIdRelationships migration deletes all duplicate
items in a single DeleteItemsUnsafeFast -> DeleteItem(ids) call. Inside
DeleteItem, the owned-extras lookup used a raw HashSet.Contains, which EF
inlines as one SQL variable per id and overflows SQLite's variable limit
on large libraries. Use WhereOneOrMany so the id set is bound as a single
json_each parameter, like the rest of the method, making bulk deletes
work for unlimited library sizes.
---
 Jellyfin.Server.Implementations/Item/ItemPersistenceService.cs | 7 ++++++-
 1 file changed, 6 insertions(+), 1 deletion(-)

diff --git a/Jellyfin.Server.Implementations/Item/ItemPersistenceService.cs b/Jellyfin.Server.Implementations/Item/ItemPersistenceService.cs
index 7c0cfe7c15..b10f7c527e 100644
--- a/Jellyfin.Server.Implementations/Item/ItemPersistenceService.cs
+++ b/Jellyfin.Server.Implementations/Item/ItemPersistenceService.cs
@@ -65,8 +65,13 @@ public class ItemPersistenceService : IItemPersistenceService
             descendantIds.Add(id);
         }
 
+        // Use WhereOneOrMany instead of a raw HashSet.Contains so large id sets are bound as a
+        // single parameter (json_each) rather than one SQL variable per id, which would otherwise
+        // overflow SQLite's variable limit when deleting many items at once (e.g. migrations).
+        var ownerIds = descendantIds.ToArray();
         var extraIds = context.BaseItems
-            .Where(e => e.OwnerId.HasValue && descendantIds.Contains(e.OwnerId.Value))
+            .Where(e => e.OwnerId.HasValue)
+            .WhereOneOrMany(ownerIds, e => e.OwnerId!.Value)
             .Select(e => e.Id)
             .ToArray();
 
-- 
cgit v1.2.3


From b60c535c84a382d06eab5b0c38ee279103b06cf2 Mon Sep 17 00:00:00 2001
From: "Joshua M. Boniface" <joshua@boniface.me>
Date: Sun, 21 Jun 2026 23:06:45 -0400
Subject: Add progress logging and batch deletion for logs

After resolving duplicates the migration deleted all items in one silent
pass (per-id GetItemById plus a single DeleteItemsUnsafeFast), which looks
hung for minutes on large libraries. Delete in batches of 500 and log
progress per batch, which also avoids one oversized delete transaction.
---
 ...60115120000_FixIncorrectOwnerIdRelationships.cs | 43 ++++++++++++++++------
 1 file changed, 31 insertions(+), 12 deletions(-)

diff --git a/Jellyfin.Server/Migrations/Routines/20260115120000_FixIncorrectOwnerIdRelationships.cs b/Jellyfin.Server/Migrations/Routines/20260115120000_FixIncorrectOwnerIdRelationships.cs
index 0baf261a2e..e34182fd5d 100644
--- a/Jellyfin.Server/Migrations/Routines/20260115120000_FixIncorrectOwnerIdRelationships.cs
+++ b/Jellyfin.Server/Migrations/Routines/20260115120000_FixIncorrectOwnerIdRelationships.cs
@@ -136,19 +136,38 @@ public class FixIncorrectOwnerIdRelationships : IAsyncMigrationRoutine
 
         if (allIdsToDelete.Count > 0)
         {
-            // Batch-resolve items for metadata path cleanup, then delete all at once
-            var itemsToDelete = allIdsToDelete
-                .Select(id => _libraryManager.GetItemById(id))
-                .Where(item => item is not null)
-                .ToList();
-            _libraryManager.DeleteItemsUnsafeFast(itemsToDelete!);
-
-            // Fall back to direct DB deletion for any items that couldn't be resolved via LibraryManager
-            var deletedIds = itemsToDelete.Select(i => i!.Id).ToHashSet();
-            var unresolvedIds = allIdsToDelete.Where(id => !deletedIds.Contains(id)).ToList();
-            if (unresolvedIds.Count > 0)
+            _logger.LogInformation("Deleting {Count} duplicate database entries...", allIdsToDelete.Count);
+
+            // Delete in batches so progress is visible (item resolution and deletion can take a
+            // long time on large libraries) and so we never issue one massive delete transaction.
+            const int deleteBatchSize = 500;
+            var deletedSoFar = 0;
+            for (var offset = 0; offset < allIdsToDelete.Count; offset += deleteBatchSize)
             {
-                _persistenceService.DeleteItem(unresolvedIds);
+                cancellationToken.ThrowIfCancellationRequested();
+
+                var batchIds = allIdsToDelete.GetRange(offset, Math.Min(deleteBatchSize, allIdsToDelete.Count - offset));
+
+                // Resolve items for metadata path cleanup, then delete this batch
+                var itemsToDelete = batchIds
+                    .Select(id => _libraryManager.GetItemById(id))
+                    .Where(item => item is not null)
+                    .ToList();
+                if (itemsToDelete.Count > 0)
+                {
+                    _libraryManager.DeleteItemsUnsafeFast(itemsToDelete!);
+                }
+
+                // Fall back to direct DB deletion for any items that couldn't be resolved via LibraryManager
+                var deletedIds = itemsToDelete.Select(i => i!.Id).ToHashSet();
+                var unresolvedIds = batchIds.Where(id => !deletedIds.Contains(id)).ToList();
+                if (unresolvedIds.Count > 0)
+                {
+                    _persistenceService.DeleteItem(unresolvedIds);
+                }
+
+                deletedSoFar += batchIds.Count;
+                _logger.LogInformation("Deleting duplicates: {Deleted}/{Total} items", deletedSoFar, allIdsToDelete.Count);
             }
         }
 
-- 
cgit v1.2.3