* feat(db): add repair command to rebuild a corrupted FTS5 search index
A corrupted media_file_fts index made every scan fail with 'database disk
image is malformed', and sqlite3's built-in 'rebuild' command cannot repair
contentless FTS5 tables, leaving users to hand-drop tables and triggers.
Add 'navidrome db repair': it runs PRAGMA integrity_check, and when the
reported corruption is confined to the FTS5 search tables, drops and
recreates the three tables and their nine triggers and repopulates them
from the base tables (which hold all the data, so nothing is lost). The
result is verified with the FTS5-native 'integrity-check' command, which
reads only the rebuilt indexes instead of re-scanning the whole database
(on a 761MB production copy: ~9s full check, ~1s rebuild, sub-second
verify). A --rebuild flag forces the rebuild even when the check passes,
for silently desynced indexes. The rebuild refuses to run while migrations
are pending, and a schema-comparison test guards the duplicated DDL against
drifting from the migration.
The DbPath existence check and the YES confirmation prompt, previously
copy-pasted across the backup commands, are extracted into shared cmd
helpers used by both backup and repair.
Part of #6067
* fix(db): type the FTS migration version as int64 for 32-bit builds
The untyped constant defaults to int, which overflows on arm/v7 and 386.
* feat(db): split repair into 'db doctor' and 'search rebuild' commands
A single 'db repair' command promised more than it delivered: the only thing
it could actually repair was the search index, and its diagnosis and its fix
were welded together, so a forced rebuild paid the full integrity check twice.
Split it: 'navidrome db doctor' is strictly read-only, runs both PRAGMA
integrity_check and PRAGMA foreign_key_check, and routes the user (to
'search rebuild' when corruption is FTS-only, to backup/.recover otherwise).
'navidrome search rebuild' just rebuilds and verifies the FTS index, which
takes ~2s on a prod-size library instead of ~19s.
* refactor(cmd): extract a testable doctor function and bound foreign key output
Extract the doctor routing (check, classify, advise) into a function that
takes an io.Writer, so the advice paths are unit-tested and the process exit
happens in the cobra wrapper after the DB is closed (os.Exit was skipping the
deferred close, leaving WAL/SHM files behind on the unhealthy paths).
Aggregate foreign_key_check by (table, parent): the raw pragma emits one row
per orphan, which is unbounded output on a large corrupted library. Also
make confirmYES take an io.Reader, drop the unused return from the renamed
requireExistingDB, share the FTS table list with the tests, and stop the
schema-guard specs from paying for a seeded database they never use.
* docs(cmd): promise 'never alters your data' instead of 'never modifies the database'
Closing the doctor's connection can checkpoint a stale WAL into the main
file (as any SQLite tool does), so the byte-level claim was too strong. The
checks themselves are read-only and no logical content ever changes.
* fix(cmd): make 'db doctor' advice honest when checks are inconclusive
PRAGMA integrity_check stops at 100 errors and emits no marker row, so a
saturated result was being read as the whole picture. IntegrityCheck now sets
the limit itself and reports saturation as a truncated list, and doctor no
longer claims corruption is limited to the search index in that case.
Foreign key violations now print a next step instead of only flipping the
exit code: migrations run with foreign_keys off, so orphan rows are a
realistic leftover on a database that is not corrupt.
Also corrects the 'search rebuild' help, which promised that 'db doctor'
detects when a rebuild is needed -- integrity_check cannot see an index that
is merely out of sync; gives the never-migrated case its intended message
instead of a raw 'no such table: goose_db_version'; and extracts
rebuildSearchIndex so the database is closed before log.Fatal exits.
* refactor(cmd): promote 'db doctor' to a top-level 'doctor' command
The 'db' group held a single subcommand, and the checks planned for it reach
past the database: config, music folder permissions, external tools. None of
those belong under 'db'.
Promoting it also evens out the shape of the pair. The command that finds the
problem is now top-level alongside 'search rebuild', the command that fixes
it, matching the 'brew doctor' convention users already expect.
'db doctor' has never been released, so no alias or deprecation is needed.
* refactor(db): tighten the doctor and search rebuild internals
Follow-up cleanup with no behaviour change except where noted.
integrity_check now asks the pragma for one row beyond the reported limit and
treats that extra row as the proof it truncated, instead of inferring truncation
from a saturated count. That distinguishes a list of exactly 100 issues from one
that was cut short -- the old test could not, and 100 was SQLite's own default,
so passing it was a no-op.
ForeignKeyCheck returns []FKViolation instead of pre-formatted English, moving
the prose to the layer that already owns the CLI vocabulary. The goose table
probe shared with isSchemaEmpty becomes hasGooseTable, so 'has this database
ever been migrated' has one spelling. Also folds ftsMigrationApplied into
requireFTSMigration, lifts printFindings out of a closure that captured nothing,
names the FTS trigger suffixes once, and corrects the ftsSchemaDDL comment: the
drift test compares against the full migration chain, not the single frozen
migration it claimed.
* fix(db): verify the rebuilt search index before committing it
RebuildFTS committed its transaction and only then ran the FTS5 integrity
check, from the caller. A rebuild that produced a bad index was therefore
already persisted by the time anyone noticed, leaving the user worse off than
before they ran the command.
The check now runs inside the transaction, so a rebuild that does not verify
rolls back and leaves the original index in place. VerifyFTS keeps its *sql.DB
signature for callers outside a transaction; the shared body takes the small
execer interface that both *sql.DB and *sql.Tx satisfy.
Adds a spec for the rollback: it removes a column the repopulating SELECT
reads, so the transaction fails after the drops, and asserts the old index
still answers queries.
* refactor(cmd): drop the unused io.Reader parameter from confirmYES
The reader was added as a test seam that no test ever used: all three callers
pass os.Stdin. Back to fmt.Scanln, which drops the parameter and the now-unused
os import from backup.go and search.go.
* fix(cmd): stop promising a scan clears every foreign key violation
doctor told the user to run 'navidrome scan -f' for any foreign key
violation. SQLStore.GC only purges albums, artists, folders, annotations,
bookmarks, tags and playlist tracks, so orphans elsewhere survive it and the
next doctor run still reports them. player.user_id references user(id) and no
scan phase touches that table at all.
The advice now says a scan clears some of them and the rest have to be removed
by hand, which keeps the next step the earlier round asked for without claiming
a cleanup that does not happen.
* docs(db): trim over-long comments on the doctor and rebuild paths
Six comments ran past two lines or repeated something already stated nearby.
The RebuildFTS doc claimed the rebuild rolls back on a column mismatch, which
the new 'verifies before committing' sentence already implies, and a spec
comment restated that same rationale a second time.
* docs: drop em dashes from the comments added in this branch
* fix(db): fail restore when the backup file does not exist instead of wiping the database
`navidrome backup restore -b <file>` passed the flag value straight to the
SQLite driver, which opens databases with SQLITE_OPEN_CREATE by default. If
the file was not found (for example a file name relative to the working
directory instead of the backup directory), the driver silently created an
empty database and the backup API copied that emptiness over the live
database, reporting 'Restore complete' with an empty instance afterwards.
Two changes:
- db.Restore now opens the backup file read-only, so a missing file is an
error and nothing gets created or overwritten.
- A relative --backup-file is resolved against Backup.Path, the same folder
'backup create' writes to; absolute paths keep working as before.
Fixes#6083
* fix(db): stat the backup file instead of opening it read-only
The read-only DSN added in the previous commit works for the reported case but
breaks on other paths: 'file:' + path is parsed as a URI, so a '#' truncates the
path and a '%' sequence is percent-decoded, and a read-only open of a WAL
database leaves '-shm'/'-wal' sidecars next to the backup. Those sidecars then
matched the unanchored prune regex, so 'backup prune -k 3' right after a restore
deleted real backups and kept one.
Stat the file before opening it and keep passing the plain path to the driver.
Paths containing '?' are rejected, since go-sqlite3 splits the DSN there and
would otherwise open (and create) a different file. The prune regex is anchored
so sidecars are never counted as backups.
Also fixes the restore/backup/prune error logs, which printed BasePath (the web
URL prefix) instead of the backup location.
---------
Co-authored-by: Deluan <deluan@navidrome.org>
* feat(conf): add Dir type with lazy directory creation
Introduces the Dir type that wraps a directory path string and defers
os.MkdirAll until the first call to Path() or MustPath(), using sync.Once
to ensure the creation happens exactly once. Implements fmt.Stringer,
encoding.TextMarshaler, and encoding.TextUnmarshaler for config integration.
Includes Ginkgo/Gomega tests covering all methods and error paths.
* refactor(conf): replace eager dir creation with lazy Dir type
Change DataFolder, CacheFolder, Plugins.Folder, and Backup.Path from
string to Dir. Remove all os.MkdirAll calls from Load() so directories
are created lazily on first Path()/MustPath() call. Artwork folder
creation was already handled at point-of-use in image_upload.go.
Add SnapshotConfig() to conf package for safe test config save/restore
that avoids copying sync.Once inside Dir fields. Fix copy-lock vet
warning in nativeapi/config.go by marshalling pointer instead of value.
* refactor(conf): migrate tests and db init to lazy Dir type
Update all test files to use conf.NewDir() for Dir field assignments.
Ensure DataFolder is created lazily when the database is first opened
in db.Db(). Remove eager directory creation from conf.Load() tests.
* fix(conf): address review findings for Dir type
- Use os.ModePerm for DataFolder/CacheFolder (was 0700, should match
original behavior). Add NewDirWithPerm for PluginsFolder (0700).
- Use Path() instead of MustPath() in db.Prune() to avoid logFatal
from background cron job.
- Panic on marshal/unmarshal errors in SnapshotConfig (test helper).
- Clean up redundant String()/MustPath() calls in plugin manager.
- Remove dead code in dir_test.go.
Signed-off-by: Deluan <deluan@navidrome.org>
* fix(conf): add GoString to Dir for clean config dump output
Implement fmt.GoStringer on Dir so pretty.Sprintf shows the path
string instead of internal struct fields (sync.Once, perm, err).
Also add TODO comment to configtest about removing the indirection.
* fix(dir): improve error logging in MustPath method
Signed-off-by: Deluan <deluan@navidrome.org>
* refactor(tests): remove redundant tests for unwritable DataFolder and CacheFolder
Signed-off-by: Deluan <deluan@navidrome.org>
* fix(conf): address PR review feedback
- Ensure Plugins.Folder always uses 0700, even when user-configured
(previously only the derived default got restrictive permissions).
- Create LogFile parent directory before opening, so LogFile paths
inside a not-yet-created DataFolder work correctly.
---------
Signed-off-by: Deluan <deluan@navidrome.org>
* fix(server): backup not working from cli
Signed-off-by: Deluan <deluan@navidrome.org>
* fix(server): make backup-file required for restore
Signed-off-by: Deluan <deluan@navidrome.org>
---------
Signed-off-by: Deluan <deluan@navidrome.org>
* [enhancement]: Provide native backup/restore mechanism
- db.go: add Backup/Restore functions that utilize Sqlite's built-in online backup mechanism
- support automatic backup with schedule, limit number of files
- provide commands to manually backup/restore Navidrome
Notes:
`Step(-1)` results in a read-only lock being held for the entire duration of the backup.
This will block out any other write operation (and may hold additional locks.
An alternate implementation that doesn't block but instead retries is available at https://www.sqlite.org/backup.html#:~:text=of%20a%20Running-,database,-%2F*%0A**%20Perform%20an%20online (easily adaptable to go), but has the potential problem of continually getting restarted by background writes.
Additionally, the restore should still only be called when Navidrome is offline, as the restore process does not run migrations that are missing.
* remove empty line
* add more granular backup schedule
* do not remove files when bypass is set
* move prune
* refactor: small nitpicks
* change commands and flags
* tests, return path from backup
* refactor: small nitpicks
---------
Co-authored-by: Deluan <deluan@navidrome.org>
