I’m using Stash v0.29.3 on Windows, with the library/DB and all the referenced files on the same local disk.
Most things work, but if I try to use the Images tab, I get an “Error Loading Items” panel with a repeated message of the form;
getting file by id 403723: sql: no rows in result set
The number can be different on each page load, but it’s always the same all the way down.
I’ve done a lot of work on my database, so starting again isn’t really an option for me! Is there anything I can do to fix this? I’d take a SQLite solution if that’s what’s needed.
This is a database consistency issue where the images_files junction table has entries pointing to file IDs that no longer exist in the files table. This can happen after a migration or if database state got corrupted.
Before doing anything, back up your database (stash-go.sqlite).
Diagnosis - Run this to see the orphaned records:
– Find entries in images_files that reference non-existent files
SELECT if.image_id, if.file_id
FROM images_files if
LEFT JOIN files f ON if.file_id = f.id
WHERE f.id IS NULL;
Fix - Delete the orphaned junction table entries:
– Remove images_files entries that point to missing files
DELETE FROM images_files
WHERE file_id NOT IN (SELECT id FROM files);
Then clean up any images that now have no files:
– Find images with no associated files
SELECT i.id FROM images i
LEFT JOIN images_files if ON i.id = if.image_id
WHERE if.file_id IS NULL;
– Delete those orphaned images
DELETE FROM images
WHERE id NOT IN (SELECT image_id FROM images_files);
You can run these using DB Browser for SQLite or the sqlite3 command line tool.
After cleanup:
Run a Scan on your library. The Clean task (Settings > Tasks > Clean) won’t help here because it only removes files that no longer exist on disk - your issue is the reverse (database records pointing to non-existent DB entries). A scan will ensure the database is consistent with your actual files.
The error message getting file by id [number]: sql: no rows in result set comes from pkg/sqlite/file.go:621 when the GraphQL resolver tries to load files for images via the data loader.
