ai-collab/bulk-image-organizer

Watch

Fork 0

[Bug]: SQLite variable limit silently disables grid preload, cached-thumbnail hydrate, and prune at ~33k+ images #191

New issue

Open

opened 2026-08-29 00:18:40 +00:00 by Claude · 1 comment

Claude commented

2026-08-29 00:18:40 +00:00

Member

Affected area

Directory open — ui/main_window.py::open_directory() preload block (lines 4692-4720), db/working_db.py (get_tags_for_images, prune_images_not_in, and five sibling methods).

Summary

Every WorkingDB method that expands one SQL parameter per caller-supplied id/path breaks once the workspace exceeds SQLite's SQLITE_LIMIT_VARIABLE_NUMBER. On builds where that limit is the SQLite compile-time default of 32766 (which the official CPython-for-Windows amalgamation ships), a workspace above ~33k images silently loses:

the cached grid preload (placeholders + tags),
the cached-thumbnail hydrate worker — it is never started at all,
prune of rows whose files no longer exist.

All three fail to a logger.warning, with no user-visible error.

This is not reproducible in CI or on Debian/Ubuntu dev machines: those ship a patched SQLite with the limit raised to 250,000, so 125k parameters succeed. Verified on this sandbox: sqlite3.connect(':memory:').getlimit(sqlite3.SQLITE_LIMIT_VARIABLE_NUMBER) → 250000.

Root cause

open_directory()'s preload block wraps the tag fetch and the hydrate-worker start in one try:

try:
    if existing:
        mgr = TagManager(self._working_db)
        plains = mgr.image_dicts_with_tags(existing)   # 4700 — raises here
        self.model.set_images(plains)                  # 4701 — never runs
        ...
    ...
    self._start_cache_hydrate_worker()                 # 4718 — never runs
except Exception as exc:
    logger.warning("Preload from DB failed (non-fatal): %s", exc)   # 4719-4720

image_dicts_with_tags() calls WorkingDB.get_tags_for_images(ids), which builds WHERE it.image_id IN (?, ?, ...) with one parameter per image. get_all_images(include_thumbnails=False) selects id (see _IMAGE_COLUMNS_NO_BLOB), so all 125,000 ids are bound. Above the limit this raises sqlite3.OperationalError: too many SQL variables, and the single except takes the hydrate worker down with it.

Separately, prune_images_not_in() builds DELETE FROM images WHERE relative_path NOT IN (?, ?, ...) with one parameter per surviving path, so prune raises too — caught in scan_metadata_ordered_and_upsert() as errors.append(f"prune failed: {exc}").

Same unbounded expansion, triggered whenever their argument set is large: get_images_by_paths, get_cache_metadata_for_paths, get_images_with_thumbnails_for_paths, get_image_dicts_for_dupe_tags, delete_images_by_paths.

Reproduction (mechanism proven against the real code)

Lowering the limit on every connection and running the actual WorkingDB / TagManager path, with 250 images against a simulated limit of 100:

rows preloaded from DB      : 250
simulated variable limit    : 100

--- step 1: TagManager.image_dicts_with_tags(existing) ---
  RAISED OperationalError: too many SQL variables

--- step 2: prune_images_not_in(all_paths) ---
  RAISED OperationalError: too many SQL variables

--- step 3: replicate open_directory()'s try/except ---
  logger.warning("Preload from DB failed (non-fatal): too many SQL variables")
  cache hydrate worker started: False

Against a real (unpatched-default) limit of 32766, IN with 32767+ parameters raises; 32766 succeeds.

Relationship to #190

#190 (cached-thumbnail hydrate paginating with unindexed OFFSET) is a genuine, separately-measured O(n²) bug and its fix stands. But on a build whose variable limit is below the image count, the hydrate worker never starts, so #190's fix cannot affect the reported symptom on that platform. This issue is the more direct explanation of "125,000-image workspace shows placeholders with zero thumbnails": the placeholders come from the discovery scan's images_added, and the cached thumbnails never load because the worker was never launched.

Confirmation needed on the reporting platform

One line in a Python console on the affected Windows build:

import sqlite3; print(sqlite3.connect(':memory:').getlimit(sqlite3.SQLITE_LIMIT_VARIABLE_NUMBER))

32766 (or 999) confirms; 250000 would rule it out for that machine.

Suggested fix direction

Resolve the connection's real limit once via conn.getlimit(sqlite3.SQLITE_LIMIT_VARIABLE_NUMBER) (Python 3.11+), falling back to a conservative 999, and chunk every parameter-expanding query to that budget, merging results.
Rewrite prune_images_not_in() around a temp table (NOT IN cannot be fixed by splitting the list — each chunk would delete nearly everything): insert the keep-set with executemany, then select/delete via NOT IN (SELECT ... FROM temp). This also allows streaming pruned paths back to the UI in batches.
Narrow the open_directory() preload try so a preload failure cannot prevent the cached-thumbnail hydrate worker from starting.

Acceptance criteria

A workspace of 125k images preloads placeholders + tags and hydrates cached thumbnails on a build whose variable limit is 32766.
Prune removes rows for missing files at that scale.
No WorkingDB method binds an unbounded number of parameters.
A preload failure never prevents the hydrate worker from starting.
Tests exercise the chunking with an artificially lowered limit, so the sandbox's patched 250,000 limit cannot mask a regression.

## Affected area Directory open — `ui/main_window.py::open_directory()` preload block (lines 4692-4720), `db/working_db.py` (`get_tags_for_images`, `prune_images_not_in`, and five sibling methods). ## Summary Every `WorkingDB` method that expands one SQL parameter per caller-supplied id/path breaks once the workspace exceeds SQLite's `SQLITE_LIMIT_VARIABLE_NUMBER`. On builds where that limit is the SQLite compile-time default of **32766** (which the official CPython-for-Windows amalgamation ships), a workspace above ~33k images silently loses: - the **cached grid preload** (placeholders + tags), - the **cached-thumbnail hydrate worker** — it is never started at all, - **prune** of rows whose files no longer exist. All three fail to a `logger.warning`, with no user-visible error. This is **not** reproducible in CI or on Debian/Ubuntu dev machines: those ship a patched SQLite with the limit raised to 250,000, so 125k parameters succeed. Verified on this sandbox: `sqlite3.connect(':memory:').getlimit(sqlite3.SQLITE_LIMIT_VARIABLE_NUMBER)` → `250000`. ## Root cause `open_directory()`'s preload block wraps the tag fetch **and** the hydrate-worker start in one `try`: ```python try: if existing: mgr = TagManager(self._working_db) plains = mgr.image_dicts_with_tags(existing) # 4700 — raises here self.model.set_images(plains) # 4701 — never runs ... ... self._start_cache_hydrate_worker() # 4718 — never runs except Exception as exc: logger.warning("Preload from DB failed (non-fatal): %s", exc) # 4719-4720 ``` `image_dicts_with_tags()` calls `WorkingDB.get_tags_for_images(ids)`, which builds `WHERE it.image_id IN (?, ?, ...)` with **one parameter per image**. `get_all_images(include_thumbnails=False)` selects `id` (see `_IMAGE_COLUMNS_NO_BLOB`), so all 125,000 ids are bound. Above the limit this raises `sqlite3.OperationalError: too many SQL variables`, and the single `except` takes the hydrate worker down with it. Separately, `prune_images_not_in()` builds `DELETE FROM images WHERE relative_path NOT IN (?, ?, ...)` with one parameter per surviving path, so prune raises too — caught in `scan_metadata_ordered_and_upsert()` as `errors.append(f"prune failed: {exc}")`. Same unbounded expansion, triggered whenever their argument set is large: `get_images_by_paths`, `get_cache_metadata_for_paths`, `get_images_with_thumbnails_for_paths`, `get_image_dicts_for_dupe_tags`, `delete_images_by_paths`. ## Reproduction (mechanism proven against the real code) Lowering the limit on every connection and running the actual `WorkingDB` / `TagManager` path, with 250 images against a simulated limit of 100: ``` rows preloaded from DB : 250 simulated variable limit : 100 --- step 1: TagManager.image_dicts_with_tags(existing) --- RAISED OperationalError: too many SQL variables --- step 2: prune_images_not_in(all_paths) --- RAISED OperationalError: too many SQL variables --- step 3: replicate open_directory()'s try/except --- logger.warning("Preload from DB failed (non-fatal): too many SQL variables") cache hydrate worker started: False ``` Against a real (unpatched-default) limit of 32766, `IN` with 32767+ parameters raises; 32766 succeeds. ## Relationship to #190 #190 (cached-thumbnail hydrate paginating with unindexed `OFFSET`) is a genuine, separately-measured O(n²) bug and its fix stands. But on a build whose variable limit is below the image count, **the hydrate worker never starts**, so #190's fix cannot affect the reported symptom on that platform. This issue is the more direct explanation of "125,000-image workspace shows placeholders with zero thumbnails": the placeholders come from the discovery scan's `images_added`, and the cached thumbnails never load because the worker was never launched. ## Confirmation needed on the reporting platform One line in a Python console on the affected Windows build: ```python import sqlite3; print(sqlite3.connect(':memory:').getlimit(sqlite3.SQLITE_LIMIT_VARIABLE_NUMBER)) ``` `32766` (or `999`) confirms; `250000` would rule it out for that machine. ## Suggested fix direction - Resolve the connection's real limit once via `conn.getlimit(sqlite3.SQLITE_LIMIT_VARIABLE_NUMBER)` (Python 3.11+), falling back to a conservative 999, and chunk every parameter-expanding query to that budget, merging results. - Rewrite `prune_images_not_in()` around a temp table (`NOT IN` cannot be fixed by splitting the list — each chunk would delete nearly everything): insert the keep-set with `executemany`, then select/delete via `NOT IN (SELECT ... FROM temp)`. This also allows streaming pruned paths back to the UI in batches. - Narrow the `open_directory()` preload `try` so a preload failure cannot prevent the cached-thumbnail hydrate worker from starting. ## Acceptance criteria - [ ] A workspace of 125k images preloads placeholders + tags and hydrates cached thumbnails on a build whose variable limit is 32766. - [ ] Prune removes rows for missing files at that scale. - [ ] No `WorkingDB` method binds an unbounded number of parameters. - [ ] A preload failure never prevents the hydrate worker from starting. - [ ] Tests exercise the chunking with an artificially lowered limit, so the sandbox's patched 250,000 limit cannot mask a regression.

Claude added the

Kind/Bug

Priority/High

Reviewed/Confirmed

labels

2026-08-29 00:18:49 +00:00

Claude referenced this issue from a commit

2026-08-29 00:26:24 +00:00

fix(db): chunk SQL parameters and stage prune in a temp table (issue #191)

Claude referenced this issue from a commit

2026-08-29 00:41:28 +00:00

perf(scan): rate-limit worker progress emissions (issue #191)

Claude referenced this issue from a commit

2026-08-29 00:41:28 +00:00

perf(grid): stop resorting the whole model on filter-only changes (issue #191)

Claude commented

2026-08-29 17:41:47 +00:00

Author

Member

Confirmed on the reporting platform

The reporter ran the check on the affected Windows 11 build:

import sqlite3; print(sqlite3.connect(':memory:').getlimit(sqlite3.SQLITE_LIMIT_VARIABLE_NUMBER))

Result: 32766 — SQLite's unpatched compile-time default.

That makes the diagnosis definitive rather than inferred. At 125,000 images every one of the affected queries was raising OperationalError: too many SQL variables on that machine, so the workspace opened with:

no cached grid preload (TagManager.image_dicts_with_tags → get_tags_for_images(125000 ids)),
no cached-thumbnail hydrate at all — _start_cache_hydrate_worker() shared the failing try,
no prune (prune_images_not_in with 125k NOT IN parameters).

The breakage threshold is ~32,766 images

This also retroactively explains the A/B comparison in #184: the reporter's ~3,900-image directory is below the limit and behaved normally, while the ~125,000-image directory on the same share did not. Any workspace above ~33k images on a build with the stock limit is affected; below it, nothing is.

Follow-up: dupe-path queries chunked too

Three further call sites scale with duplicate-group count rather than image count and were chunked in the same pass, since a 125k workspace can accumulate more than 32,766 dupe-group tags:

remove_dupe_tags_with_single_member() — highest risk, runs after every scan
get_image_dicts_for_dupe_tags()
get_dupe_group_representatives()

The remaining unbounded expansions are genuinely bounded by column count or by the user's active tag-filter selection, and are left alone.

Bug found in the fix itself

The first cut of _resolve_sql_variable_limit() clamped with max(64, min(limit - RESERVED, 20000)). That floor can return a chunk size larger than the reported limit when the limit is small, defeating the reserved headroom for the fixed parameters some queries bind alongside the chunked list (get_dupe_group_representatives binds the DELETE tag name plus the chunk). Caught by the new dupe test at a simulated limit of 64; corrected to min(max(1, limit - RESERVED), 20000).

Status

Fixed on 0.9.4/issue-179-184-dupe-pip-thumb-perf (dd9032e, plus the dupe-path chunking). 12 regression tests force a small variable limit on every connection so this project's Debian-patched 250,000 cannot mask a regression.

## Confirmed on the reporting platform The reporter ran the check on the affected Windows 11 build: ```python import sqlite3; print(sqlite3.connect(':memory:').getlimit(sqlite3.SQLITE_LIMIT_VARIABLE_NUMBER)) ``` Result: **`32766`** — SQLite's unpatched compile-time default. That makes the diagnosis definitive rather than inferred. At 125,000 images every one of the affected queries was raising `OperationalError: too many SQL variables` on that machine, so the workspace opened with: - no cached grid preload (`TagManager.image_dicts_with_tags` → `get_tags_for_images(125000 ids)`), - **no cached-thumbnail hydrate at all** — `_start_cache_hydrate_worker()` shared the failing `try`, - no prune (`prune_images_not_in` with 125k `NOT IN` parameters). ### The breakage threshold is ~32,766 images This also retroactively explains the A/B comparison in #184: the reporter's ~3,900-image directory is below the limit and behaved normally, while the ~125,000-image directory on the same share did not. Any workspace above ~33k images on a build with the stock limit is affected; below it, nothing is. ### Follow-up: dupe-path queries chunked too Three further call sites scale with *duplicate-group* count rather than image count and were chunked in the same pass, since a 125k workspace can accumulate more than 32,766 dupe-group tags: - `remove_dupe_tags_with_single_member()` — highest risk, runs after **every** scan - `get_image_dicts_for_dupe_tags()` - `get_dupe_group_representatives()` The remaining unbounded expansions are genuinely bounded by column count or by the user's active tag-filter selection, and are left alone. ### Bug found in the fix itself The first cut of `_resolve_sql_variable_limit()` clamped with `max(64, min(limit - RESERVED, 20000))`. That floor can return a chunk size *larger* than the reported limit when the limit is small, defeating the reserved headroom for the fixed parameters some queries bind alongside the chunked list (`get_dupe_group_representatives` binds the `DELETE` tag name plus the chunk). Caught by the new dupe test at a simulated limit of 64; corrected to `min(max(1, limit - RESERVED), 20000)`. ### Status Fixed on `0.9.4/issue-179-184-dupe-pip-thumb-perf` (`dd9032e`, plus the dupe-path chunking). 12 regression tests force a small variable limit on every connection so this project's Debian-patched 250,000 cannot mask a regression.

Claude referenced this issue from a commit

2026-08-29 17:46:40 +00:00

fix(db): chunk the dupe-path SQL parameter expansions too (issue #191)

Claude referenced this issue from a commit

2026-08-29 18:27:44 +00:00

perf(network): copy the workspace cache off the GUI thread (issue #191)

Claude referenced this issue from a commit

2026-08-29 18:27:44 +00:00

perf(scan): stream discovery instead of draining the walk first (issue #191)

Claude referenced this issue from a commit

2026-08-29 18:27:44 +00:00

feat(scan): reconcile at the end of discovery, not enrichment (DR-045, issue #191)