Upgrading an existing database leaves the repeater silently off-air (idx_packets_upstream_time created before its columns) #148

New Issue

2026-10-27T09:35:29+01:00

adam commented

2026-10-27 09:35:29 +01:00

Summary

Upgrading an existing installation to current dev leaves the daemon running but recording nothing. It reports itself completely healthy — systemctl is-active green, radio initialised, companion clients connected and served, HTTP up — while doing no RX/TX and writing no packets.

Cause

SQLiteHandler.__init__ runs _init_database() and then _run_migrations().

_init_database creates idx_packets_upstream_time, which spans upstream_hash and upstream_hash_size. On a database predating migration 13 those columns do not exist yet — add_upstream_hash_to_packets adds them in the migration step that runs afterwards. So:

SQLiteHandler - ERROR - Failed to initialize SQLite: no such column: upstream_hash

_init_database catches and logs that, then returns. Every statement after the failing index is skipped, including the room_messages and room_client_sync tables, and startup continues on a partially-created schema.

Fresh installs never hit it, because the CREATE TABLE carries both columns.

Why it is easy to miss

It self-heals on the second start: the migration still runs after the failed init, so by the next boot the columns exist and the index succeeds. That makes it look transient. It is not — it costs one full run, and during that run the node is off-air.

The other trap is that the obvious health checks all pass. Diagnosing it needs select max(timestamp) from packets.

Reproduce

Take any repeater.db created before migration 13 (no upstream_hash column on packets).
Start current dev against it.
journalctl -u openhop-repeater | grep "Failed to initialize SQLite" → the error.
sqlite3 repeater.db "select name from sqlite_master where type='table' and name='room_messages'" → empty; schema init never got that far.
Watch max(timestamp) on packets — it does not advance.

Observed on a Raspberry Pi 4, Python 3.13, ~13 MB production database upgrading from 1.1.1. Four minutes off-air before the cause was found.

Fix

PR to follow. Two parts, separable:

Remove the index from _init_database. The add_upstream_hash_to_packets migration already creates it immediately after adding the columns, so the copy in _init_database is both redundant and too early. All three paths still end up with the index.
Make _init_database raise instead of swallowing. A daemon that cannot record packets is not usable, and a hard failure at upgrade time is far cheaper to diagnose than a silently off-air repeater. Happy to drop this half if you would rather keep startup non-fatal.

Includes tests/test_sqlite_upgrade_existing_db.py, which builds a pre-migration-13 database and asserts schema init runs to completion. It is keyed on tables created after the old failure point — asserting on the upstream columns alone passes either way, since the migration adds them regardless.

### Summary Upgrading an existing installation to current `dev` leaves the daemon running but recording nothing. It reports itself completely healthy — `systemctl is-active` green, radio initialised, companion clients connected and served, HTTP up — while doing no RX/TX and writing no packets. ### Cause `SQLiteHandler.__init__` runs `_init_database()` and then `_run_migrations()`. `_init_database` creates `idx_packets_upstream_time`, which spans `upstream_hash` and `upstream_hash_size`. On a database predating migration 13 those columns do not exist yet — `add_upstream_hash_to_packets` adds them in the migration step that runs *afterwards*. So: ``` SQLiteHandler - ERROR - Failed to initialize SQLite: no such column: upstream_hash ``` `_init_database` catches and logs that, then returns. Every statement after the failing index is skipped, including the `room_messages` and `room_client_sync` tables, and startup continues on a partially-created schema. Fresh installs never hit it, because the `CREATE TABLE` carries both columns. ### Why it is easy to miss It **self-heals on the second start**: the migration still runs after the failed init, so by the next boot the columns exist and the index succeeds. That makes it look transient. It is not — it costs one full run, and during that run the node is off-air. The other trap is that the obvious health checks all pass. Diagnosing it needs `select max(timestamp) from packets`. ### Reproduce 1. Take any `repeater.db` created before migration 13 (no `upstream_hash` column on `packets`). 2. Start current `dev` against it. 3. `journalctl -u openhop-repeater | grep "Failed to initialize SQLite"` → the error. 4. `sqlite3 repeater.db "select name from sqlite_master where type='table' and name='room_messages'"` → empty; schema init never got that far. 5. Watch `max(timestamp)` on `packets` — it does not advance. Observed on a Raspberry Pi 4, Python 3.13, ~13 MB production database upgrading from 1.1.1. Four minutes off-air before the cause was found. ### Fix PR to follow. Two parts, separable: 1. Remove the index from `_init_database`. The `add_upstream_hash_to_packets` migration already creates it immediately after adding the columns, so the copy in `_init_database` is both redundant and too early. All three paths still end up with the index. 2. Make `_init_database` raise instead of swallowing. A daemon that cannot record packets is not usable, and a hard failure at upgrade time is far cheaper to diagnose than a silently off-air repeater. Happy to drop this half if you would rather keep startup non-fatal. Includes `tests/test_sqlite_upgrade_existing_db.py`, which builds a pre-migration-13 database and asserts schema init runs to completion. It is keyed on tables created after the old failure point — asserting on the upstream columns alone passes either way, since the migration adds them regardless.

adam closed this issue

2026-10-27 09:35:29 +01:00

Sign in to join this conversation.

1 Participants

Notifications

Due Date

No due date set.

Dependencies

No dependencies set.

Reference: adam/pyMC_Repeater#148