No garbage collection of stale oc_talk_sessions rows for logged-in users #18445

Component: spreed (server) — affects deployments with a High Performance Backend (HPB)

Summary

On a long-running instance with an HPB, the oc_talk_sessions table accumulates
"zombie" session rows that are never garbage-collected. We have a periodic cleanup
path for guest participants, but no equivalent for logged-in users. Over
time the table grows with rows whose attendee no longer exists (orphans) and rows
whose last_ping is extremely old.

We suspect (see "What we did not observe" below — this part is derived from reading
the code, not an observed trace) that these stale rows widen the window in which a
participant lookup fails and the backend returns the generic no_such_room
("The user is not invited to this room.") on re-join. The web client tends to
recover; a mobile client that re-registers a fresh session can get stuck.

Environment

• Nextcloud 33.x, spreed 24.0.0-dev (built from the 24.x line)
• HPB: nextcloud-spreed-signaling v2.1.1
• Single small community instance, in use for ~2 years

What we measured (this part is observed data)

A direct count of oc_talk_sessions on our instance:

• 120 session rows total
• 72 orphaned (no matching attendee — the session → attendee join finds nothing)
• 116 rows with last_ping older than 1 day
• oldest last_ping: 2024-02-12 (over two years old at time of writing)

These rows are never deleted. We confirmed there is no periodic job touching the table:

• lib/BackgroundJob/ contains 14 timed jobs (CheckCertificates, ExpireChatMessages,
  ExpireObjectRooms, ExpireSignalingMessage, LockInactiveRooms, MaximumCallDuration,
  RemoveEmptyRooms, Reminder, …). None of them deletes from talk_sessions by
  last_ping. (RemoveEmptyRooms removes empty rooms, not stale sessions; a room
  with an owner attendee is never "empty".)
• The only last_ping-based session cleanup is ParticipantService::cleanGuestParticipants(),
  which only deletes sessions where the attendee is actor_type = guests and
  last_ping <= now - SESSION_TIMEOUT_KILL (100s). It is triggered event-driven
  (a participant-list GET), not by a cron. There is no equivalent path for
  actor_type = users.

So the session lifecycle for logged-in users is purely event-driven:

• created at join (SessionService::createSessionForAttendee(), including from the HPB
  backendRoom() path when the HPB joins an unknown session)
• deleted only on attendee removal (SessionMapper::deleteByAttendeeId())
• last_ping bumped in bulk by SessionService::updateMultipleLastPings() (HPB backendPing)

When the HPB does not signal a clean leave (network drop, 1006, aborted WebRTC setup),
no delete is emitted and the row leaks indefinitely.

What we did not observe (honest framing)

We did not capture a step-by-step signaling/WebRTC trace tying a specific stale row
to a specific no_such_room. The no_such_room ("The user is not invited to this room.")
message is intentionally generic / anti-leak — there are three identical return sites in
SignalingController::backendRoom(), and the same message covers "room does not exist",
"attendee absent" and "session not resolvable". So it does not by itself prove the
"stale session → getParticipantBySession() returns the catch-all → no_such_room"
chain. That chain is consistent with the code we read, but we present it only as a
plausible hypothesis, not as a captured fact.

(For context, the generic message coming straight from the backend on a re-join without
a clean leave is documented behavior — cf. signaling#512, where the HPB logs the backend's
code:no_such_room message:The user is not invited to this room. on a disinvite.)

Related issues

• Call is not joined again on reconnection with HPB if the session expired #5337 — "Call is not joined again on reconnection with HPB if the session
  expired" (open; same no_such_room line; client-side fix direction). This issue is the
  symptom; ours is the durability angle (the table leak), which Call is not joined again on reconnection with HPB if the session expired #5337 does not cover.
• Participants dropped out of call after session.gc_maxlifetime #12449 — "Participants dropped out of call after session.gc_maxlifetime"
  (closed; that was PHP/redis session expiry, reported fixed in NC 30 / spreed 20 — a
  different root cause from the oc_talk_sessions leak, same observable symptom).
• Talk Desktop: First call after idle fails – signaling token expired, stuck in "Connecting" until restart #17204 / Laptop resume after suspend overnight sometimes doesn't recover automatically. #9382 — reconnection-after-idle work, experiments_users flag.

Expected behavior

Stale oc_talk_sessions rows for logged-in users should be garbage-collected
periodically, the way guest sessions already are — instead of leaking forever when the
HPB does not signal a clean leave.

Proposed direction (for maintainers to accept or reject)

A small "backstop" TimedJob that periodically deletes:

1. rows whose last_ping is older than a conservative threshold (well beyond
   SESSION_TIMEOUT_KILL = 100s — e.g. >= 1 day — so a live session is never touched),
   restricted to sessions not in a call (in_call = 0); and
2. orphaned rows (session with no matching attendee).

This can reuse SessionService::deleteSessionsById() and the existing ts_last_ping
index (Version10000Date20201015134000), so the scan stays cheap.

Open questions for maintainers

1. spreed or HPB? We believe this belongs in spreed (it owns oc_talk_sessions; the
   HPB never deletes from it). Is that the right home, or do you prefer hardening the HPB
   leave path so the row is deleted at disconnect instead of swept later?
2. What threshold is safe for a global backstop? SESSION_TIMEOUT_KILL = 100s feels too
   aggressive for a periodic job (risk of cutting a live-but-quiet session). We suggested
   >= 1 day but defer to you.
3. Already addressed? Does the newer experiments_users reconnection logic (Talk Desktop: First call after idle fails – signaling token expired, stuck in "Connecting" until restart #17204)
   already make the server stop re-validating stale sessions aggressively, such that this
   leak no longer produces no_such_room in practice? If so, is the table growth still
   worth a backstop on storage grounds alone, or considered benign?

Happy to provide the exact (anonymized) row counts / occ snippet we used to measure this,
and to test a candidate patch on our instance.