AI Gen: GET /api/timeline/segments fails with "disk I/O error" at sqlite3_prepare(), persists until restart — correlates with backup_interval_minutes, not reproduced on standalone install

[netmms]

Environment

• LightNVR installed as a Home Assistant OS add-on
• Not observed on a separate, standalone Ubuntu/Docker install of LightNVR by the same reporter — this points toward something specific to the HA add-on environment (exact mechanism unconfirmed)
• 7 camera streams (5 ESP32/MJPEG via ffmpeg transcode, 2 native RTSP IP cameras)
• backup_interval_minutes = 60 (default-ish setting)

Bug

GET /api/timeline/segments fails for every stream simultaneously with:

[ERROR] [RecordingsAPI] Failed to prepare timeline segments query: disk I/O error
[ERROR] [RecordingsAPI] Failed to get timeline segments

This repeats identically for every subsequent request (~every 10s, matching UI polling) until the LightNVR process is restarted, at which point it works again — for a while.

What's been ruled out

• Not disk/hardware. dmesg shows no I/O errors, no remounts, no ATA errors at the failure time.
• Not DB corruption. PRAGMA quick_check passes cleanly on the live DB.
• Specific to prepare(), not execution. Ordinary SELECT queries against the same recordings table via the sqlite3 CLI succeed fine during the failure window.
• WAL file is not bloated (lightnvr.db-wal was 0 bytes when checked) — rules out a naive "WAL grows huge, eventually fails" theory.
• Not a temp-storage issue. /tmp inside the container has ample free space.

Reproducible correlation

• Process started 11:47. First failure hit 13:47 — exactly two 60-minute backup cycles later.
• Test: set backup_interval_minutes = 0, restarted once. Timeline queries succeeded cleanly for 3+ hours straight, well past where failures previously always occurred.
• Re-enabling backups afterward is the natural next test, to confirm the failure reproduces again on schedule.

Hypothesis (unconfirmed — flagging clearly as speculative)

Periodic backup likely requires a WAL checkpoint to produce a consistent snapshot. If a timeline prepare() call executes during that checkpoint, it may hit the shared WAL index (-shm) in an inconsistent state, surfacing as "disk I/O error."

What I could NOT confirm, flagging explicitly: why the failure persists across every subsequent request instead of resolving after the one moment of actual contention. I don't have evidence for a specific mechanism (e.g., a stuck connection handle, an unreleased lock, a bad cached prepared statement) — this needs maintainer insight or source-level review of the backup implementation's interaction with concurrent connections.

Question for maintainers

Is there any known interaction between the scheduled backup (maybe_run_scheduled_database_backup() in db_core.c) and concurrently-open read connections that could produce a persistent (not self-recovering) SQLITE_IOERR on prepare() for unrelated queries, until process restart? Separately: is there anything in the HA add-on packaging/environment that could plausibly explain why this reproduces there but not on a standalone install of the same LightNVR version?

[matteius]

Diagnostic instrumentation has landed on main in 2f7c6c8d.

When timeline statement preparation fails, the log now reports both the primary SQLite result and sqlite3_extended_errcode(). On the next Home Assistant reproduction, that extended code should distinguish cases such as SQLITE_IOERR_SHMOPEN, SQLITE_IOERR_SHMMAP, or another specific I/O failure instead of only reporting the generic disk I/O message.

This commit is diagnostic rather than a claimed fix. The focused timeline/API and database-backup tests pass. Please attach the new full error line from a build containing this commit when the failure recurs.

[netmms]

Thank you very much for the fast turnaround and the regression test — will retest once it's in a tagged release and report back.

[RMG112096]

Follow-up with a clean confirmation test, replicating what the original reporter did.

Test: backup_interval_minutes = 0 for 3 hours, sampled every 5 minutes

Found the setting exposed in the web UI under Configuración → Almacenamiento → "Intervalo de copia de seguridad de la base de datos (minutos)" (default was 60; also found backup_retention_count there, default 6 — which explains why I had exactly 6 rotating backup files, not an unbounded accumulation as I'd initially assumed in my last comment).

Set it to 0, restarted, then ran docker exec lightnvr top -bn1 every 5 minutes for 3 hours (36 samples total) via a background script, so I wasn't just eyeballing it in the moment.

Result: wa (I/O wait) stayed at 0.0% for essentially the entire window

Out of 36 samples spanning 3 hours, wa was 0.0 in 35 of them. The one exception was a single isolated sample at the 2h50m mark showing wa: 25.0% — not sustained, back to 0.0 five minutes later. User+system CPU stayed in the 5-15% range throughout (compared to the sustained ~48-55% "CPU" I was seeing before, which — per my earlier comment — was actually almost entirely wa, not real compute). Memory stayed flat (~600-620MB) the whole time too, no leak.

For reference, before this test, with backup_interval_minutes at its default of 60, LightNVR's own System Health panel had flagged this same period as:

io pressure — critical
Current value: 5.9s (threshold: warning 0.5s, critical 2s)

So on my end this is about as clean an A/B as I can get: default 60-minute interval → sustained I/O pressure hitting "critical" on a 262MB DB over a mechanical HDD; interval set to 0 → pressure essentially gone over a 3-hour window. This corroborates the original reporter's finding on a different environment (standalone Docker on Debian, not Home Assistant OS), which for what it's worth suggests the periodic-backup/checkpoint contention isn't HA-specific — it's just more likely to surface there first given more streams / bigger DBs, and easier to notice in my case because LightNVR's own health monitoring already labels it explicitly.

I still haven't hit the hard sqlite3_prepare() "disk I/O error" failure mode from the top of this issue — my case seems to top out at "expensive and flagged as critical by health monitoring" rather than an outright query failure. Might be a difference in DB size, disk speed, or query timing luck. Happy to leave backup_interval_minutes at 0 for a while longer and report back if the hard failure eventually shows up too.

[matteius]

Thanks — this is a useful A/B confirmation. It shows that a scheduled full-database backup can create substantial storage pressure outside Home Assistant as well, especially on a mechanical disk.

One important distinction: the scheduled backup path does not invoke PRAGMA wal_checkpoint. It uses the SQLite online backup API, flushes the destination in bounded batches, and then verifies the complete destination with PRAGMA integrity_check. A full copy plus a full verification scan can therefore generate significant I/O even without a checkpoint race. That performance effect is related to, but does not by itself reproduce, the persistent SQLITE_IOERR reported at the top of this issue.

There have been two relevant changes since the older default of 60 minutes:

• Since 0.41.2, periodic runtime backups default to disabled (db_backup_interval_minutes = 0); initial and clean-shutdown backups remain enabled. Existing installations can retain a previously saved value of 60 across upgrades.
• Since 0.41.8, a backup/WAL locking fix in a345a38 removes the raw descriptor previously opened on the live database (closing it could discard process-wide SQLite POSIX locks), preserves WAL sidecars, and releases the backup snapshot before destination verification. Current release 0.41.11 contains this fix and the extended SQLite error logging.

Could you confirm the exact LightNVR version used for this test? If it is older than 0.41.8, please update before any controlled re-test with periodic backups enabled. If it is already 0.41.8 or newer, that makes this a useful current-version performance result.

Keeping the interval at 0 is reasonable for normal operation. If you do re-enable it for one controlled cycle, logs from Starting scheduled database backup cycle through completion plus higher-frequency disk sampling such as iostat -xz 1 would help attribute the pressure precisely. If the hard timeline failure occurs, please include the complete error line containing both code and extended_code.

[RMG112096]

Confirming the LightNVR version from the earlier test, and following up with the precise timing data you asked for.

Version confirmation

The A/B test in my previous comments was run on v0.41.11, which per your note already includes the 0.41.8 backup/WAL locking fix (a345a38) and the extended SQLite error logging. So this should count as a current-version result, not a stale-locking-bug artifact.

Overnight logging: exact duration of each scheduled backup cycle

Set backup_interval_minutes = 60 and let it run overnight, capturing the full (unfiltered) LightNVR log plus iostat -xz 1 on the underlying disk continuously (no time limit, stopped manually in the morning — 267k iostat samples, 61k log lines over ~11 hours).

Grepping for Starting scheduled database backup cycle / Database backup completed successfully gave clean start/end timestamps for 10 consecutive cycles:

Cycle	Start	End	Duration
1	07:08:19	07:46:23	38m 04s
2	08:08:46	08:47:08	38m 22s
3	09:09:31	09:47:45	38m 14s
4	10:10:08	10:48:10	38m 02s
5	11:10:33	11:58:23	47m 50s
6	12:10:36	12:48:56	38m 20s
7	13:11:19	13:49:29	38m 10s
8	14:11:52	14:54:20	42m 28s
9	15:12:39	15:56:22	43m 43s
10	16:12:40	16:51:04	38m 24s

Each scheduled backup cycle takes 38-48 minutes against a backup_interval_minutes = 60 setting. That means the backup+verify process is active roughly 63-80% of every hour, with only a 12-22 minute gap between one cycle finishing and the next one starting. This isn't an occasional spike — on this hardware (mechanical HDD, SATA II-limited controller — see below), it's closer to a near-continuous background load.

The three longer cycles (5, 8, 9 — 43-48 min instead of the ~38 min baseline) don't have an obvious single cause in the logs; I'd guess they coincide with concurrent activity (live view sessions, detection events), but I don't have a clean way to confirm that correlation without additional instrumentation.

DB size at the time: ~305MB main file, growing slowly with continued use.

Storage specifics (relevant context I hadn't mentioned yet)

• Disk: Seagate ST500DM002 (500GB, 7200 RPM-class consumer mechanical HDD, SATA III-rated drive)
• Controller: confirmed via dmesg — SATA link up 3.0 Gbps on both onboard ports (i.e., the motherboard's SATA controller only negotiates SATA II, regardless of the drive's own capability). This is an Intel Celeron J1800 mini-ITX board (ASRock D1800M), so a fairly constrained embedded-class controller.
• SMART fully healthy (0 reallocated/pending/uncorrectable sectors) — ruled out failing hardware as a factor.

I'd guess this SATA II ceiling and the mechanical seek latency (relevant given PRAGMA integrity_check walks the whole B-tree with effectively random access, per your earlier explanation) make my setup close to a worst-case for this operation's duration, which might explain why my case tops out at "critical I/O pressure" rather than the outright SQLITE_IOERR from the top of this issue — a faster/SSD-backed setup would likely clear each cycle in a small fraction of this time.

What I didn't get right this round

I meant to timestamp the iostat output (forgot the -t flag) so I can't give you a clean per-second I/O profile correlated to these exact windows this time — happy to redo that capture with timestamps included if it would still be useful, now that I have confirmed cycle-duration data to align it against.

Also haven't hit the hard timeline failure yet — still just the sustained pressure/duration issue described above.

[matteius]

This is worth a recheck on latest release.