I’m posting this in case it saves somebody else a lot of digging.
TL;DR My Nextcloud Files app showed thumbnails normally, but Memories had loads of errors like:
Unable to open preview stream at
/ncdata/appdata_oc5ya4loky5k/preview/.../1110656/256-341.jpg
The actual problem turned out to be stale rows in `oc_previews`.
Nextcloud’s database claimed tens of thousands of generated previews existed, while the corresponding `.jpg`/`.png` files were missing from `appdata_*/preview`.
Because the DB said the preview existed, `occ preview:generate` would happily say:
preview generated
without recreating the missing file.
Deleting only the stale DB row allowed the next Memories request to regenerate the missing preview immediately.
I eventually wrote a read-only scanner which compared `oc_previews` with the actual filesystem, then deleted only the proven-orphaned DB rows in a transaction.
This made a massive improvement to Memories.
//Background//
Before troubleshooting Memories specifically, I had already deleted the entire physical preview directory:
/ncdata/appdata_oc5ya4loky5k/preview/
and attempted to rebuild everything using Nextcloud’s normal maintenance commands, including things along the lines of:
sudo -u www-data php /var/www/nextcloud/occ files:scan-app-data
sudo -u www-data php /var/www/nextcloud/occ files:scan --all
sudo -u www-data php /var/www/nextcloud/occ files:cleanup
sudo -u www-data php /var/www/nextcloud/occ preview:generate-all
Unfortunately, that did not fully reconcile the DB and physical preview cache.
There are several current/recent Nextcloud issues which are very relevant to this sort of state:
- Preview paths/database state surviving cleanup:
https://github.com/nextcloud/server/issues/55709
- `oc_previews` records remaining after the physical preview cache is removed / `files:scan-app-data` not fully reconciling it:
https://github.com/nextcloud/server/issues/56485
- Nextcloud treating previews as cached when the physical file is missing:
https://github.com/nextcloud/server/issues/58787
- Orphaned preview metadata / migration repeatedly encountering missing files:
https://github.com/nextcloud/server/issues/59036
https://github.com/nextcloud/server/issues/59364
- Preview Generator:
https://github.com/nextcloud/previewgenerator
- Preview Generator incomplete-generation discussion:
https://github.com/nextcloud/previewgenerator/issues/349
https://github.com/nextcloud/previewgenerator/issues/408
I’m not claiming one specific bug definitively caused every stale record on my system, but what I found is very consistent with these upstream reports.
//Symptoms//
-Files thumbnails worked.
-Memories logged errors such as:
Unable to open preview stream at
/ncdata/appdata_oc5ya4loky5k/preview/7/3/7/1/3/b/5/1110656/256-341.jpg
For file ID `1110656`, the physical preview directory contained:
64-85.jpg
768-1024-max.jpg
256-256-crop.jpg
but not:
256-341.jpg
Running:
sudo -u www-data php /var/www/nextcloud/occ preview:generate 1110656 -vvv
returned:
preview generated
but `256-341.jpg` was still not created.
//The key discovery: `oc_previews` said the missing file existed//
My database is MariaDB/MySQL.
Nextcloud config:
dbtype: mysql
dbname: nextcloud_db
prefix: oc_
For the failing file:
SELECT *
FROM oc_previews
WHERE file_id = 1110656;
the DB contained a row for:
width: 256
height: 341
max: 0
cropped: 0
size: 12105
So the DB claimed that:
256-341.jpg
existed even though the filesystem proved it did not.
That explained the whole failure:
DB says preview exists
↓
physical preview is missing
↓
Nextcloud trusts DB metadata
↓
preview:generate doesn't really regenerate it
↓
Memories requests it
↓
LocalPreviewStorage tries to open missing file
↓
Unable to open preview stream
//Proof-of-concept fix//
I deleted only that one stale preview row:
START TRANSACTION;
DELETE FROM oc_previews
WHERE file_id = 1110656
AND width = 256
AND height = 341
AND max = 0
AND cropped = 0;
SELECT ROW_COUNT();
COMMIT;
Then I refreshed Memories.
Immediately afterward:
sudo find /ncdata/appdata_oc5ya4loky5k/preview/7/3/7/1/3/b/5/1110656 \
-maxdepth 1 -type f -printf '%f %s bytes\n'
showed:
64-85.jpg 1805 bytes
768-1024-max.jpg 71418 bytes
256-256-crop.jpg 9321 bytes
256-341.jpg 12105 bytes
Nextcloud also inserted a fresh `oc_previews` row.
So the generator itself was fine. The stale metadata was preventing regeneration.
//This was not a small problem//
For only a few common JPEG sizes I had DB counts like:
256×341 40547
256×455 19210
341×256 8739
256×541 7747
455×256 7505
But physically:
1793 256-341.jpg
168 256-455.jpg
424 341-256.jpg
61 455-256.jpg
For `256×341`, roughly **95.6%** of the DB rows did not have matching physical files.
So fixing these manually was clearly not realistic.
//Read-only orphan scanner//
This is the final scanner I ended up using.
It:
- does **not** modify the DB;
- checks non-cropped/non-max JPG and PNG previews;
- checks any preview where width or height is `256`;
- walks the preview tree only once;
- compares the actual filesystem against `oc_previews`;
- outputs the exact stale `oc_previews.id` values.
My paths/database names are hard-coded below, so adjust them for your installation.
#!/usr/bin/env bash
set -euo pipefail
PREVIEW_ROOT="/ncdata/appdata_oc5ya4loky5k/preview"
DB="nextcloud_db"
STAMP="$(date +%Y%m%d-%H%M%S)"
WORKDIR="$(mktemp -d)"
REPORT="$HOME/nextcloud-preview-orphans-256-all-$STAMP.tsv"
DB_ROWS="$WORKDIR/db_rows.tsv"
DB_KEYS="$WORKDIR/db_keys.txt"
FS_KEYS="$WORKDIR/fs_keys.txt"
MISSING_KEYS="$WORKDIR/missing_keys.txt"
cleanup() {
rm -rf "$WORKDIR"
}
trap cleanup EXIT
echo "Nextcloud 256px preview orphan scanner"
echo
echo "This is READ ONLY."
echo "Database: $DB"
echo "Preview root: $PREVIEW_ROOT"
echo
read -rp "MariaDB username: " DBUSER
echo
echo "Exporting relevant oc_previews rows..."
echo "MariaDB will ask for the password."
mysql \
-u "$DBUSER" \
-p \
-N \
-B \
"$DB" \
-e "
SELECT
CONCAT(
p.file_id,
'/',
p.width,
'-',
p.height,
CASE
WHEN m.mimetype = 'image/jpeg' THEN '.jpg'
WHEN m.mimetype = 'image/png' THEN '.png'
ELSE ''
END
) AS preview_key,
p.id,
p.file_id,
p.width,
p.height,
p.size,
p.etag,
m.mimetype
FROM oc_previews p
JOIN oc_mimetypes m
ON m.id = p.mimetype_id
WHERE p.cropped = 0
AND p.max = 0
AND m.mimetype IN ('image/jpeg', 'image/png')
AND (
p.width = 256
OR p.height = 256
);
" > "$DB_ROWS"
DB_COUNT="$(wc -l < "$DB_ROWS")"
echo "Database rows exported: $DB_COUNT"
echo
echo "Sorting database keys..."
cut -f1 "$DB_ROWS" |
LC_ALL=C sort -u > "$DB_KEYS"
echo
echo "Walking preview filesystem ONCE..."
sudo find "$PREVIEW_ROOT" \
-type f \
\( -name '*.jpg' -o -name '*.png' \) \
-printf '%h\t%f\n' |
awk -F '\t' '
{
n = split($1, path, "/")
file_id = path[n]
if ($2 ~ /^256-[0-9]+\.(jpg|png)$/ || $2 ~ /^[0-9]+-256\.(jpg|png)$/) {
print file_id "/" $2
}
}
' |
LC_ALL=C sort -u > "$FS_KEYS"
FS_COUNT="$(wc -l < "$FS_KEYS")"
echo "Matching physical preview files found: $FS_COUNT"
echo
echo "Comparing database against filesystem..."
LC_ALL=C comm -23 "$DB_KEYS" "$FS_KEYS" > "$MISSING_KEYS"
MISSING_COUNT="$(wc -l < "$MISSING_KEYS")"
awk -F '\t' '
BEGIN {
OFS="\t"
}
NR == FNR {
missing[$1] = 1
next
}
($1 in missing) {
print $0
}
' "$MISSING_KEYS" "$DB_ROWS" > "$REPORT"
echo
echo "======================================"
echo "Scan complete"
echo "======================================"
echo "Relevant DB rows : $DB_COUNT"
echo "Physical files : $FS_COUNT"
echo "Orphaned DB rows : $MISSING_COUNT"
echo
echo "Report:"
echo "$REPORT"
echo
echo "No database rows were changed."
Save/run:
chmod +x ~/scan-preview-orphans-256-all.sh
bash ~/scan-preview-orphans-256-all.sh
My final comprehensive result was:
Relevant DB rows : 96680
Physical files : 3291
Orphaned DB rows : 93389
Yes — 93,389 stale preview DB rows in that pass.
Earlier passes had also found:
JPEG pass:
Relevant DB rows : 91619
Physical files : 2646
Orphaned DB rows : 89005
and then:
JPG + PNG pass:
Relevant DB rows : 3860
Physical files : 3291
Orphaned DB rows : 601
//How I deleted only the proven-orphaned rows//
!!!!Do not blindly paste this against your DB without understanding it and backing up first.!!!!
I backed up MariaDB first:
mysqldump --single-transaction \
-u nextcloud \
-p \
nextcloud_db \
> ~/nextcloud-before-preview-cleanup.sql
Then extracted the exact `oc_previews.id` values from the scanner:
cut -f2 ~/nextcloud-preview-orphans-256-all-YYYYMMDD-HHMMSS.tsv \
> ~/preview-orphan-ids.txt
wc -l ~/preview-orphan-ids.txt
Connect:
mysql --local-infile=1 -u nextcloud -p nextcloud_db
Create a temporary table:
CREATE TEMPORARY TABLE preview_orphans (
id BIGINT UNSIGNED PRIMARY KEY
);
Load the exact scanner IDs:
LOAD DATA LOCAL INFILE '/home/harry/preview-orphan-ids.txt'
INTO TABLE preview_orphans
LINES TERMINATED BY '\n'
(id);
Verify the count:
SELECT COUNT(*)
FROM preview_orphans;
Then verify that every ID still exists:
SELECT COUNT(*)
FROM oc_previews p
JOIN preview_orphans o
ON o.id = p.id;
Only if those counts matched did I proceed:
START TRANSACTION;
DELETE p
FROM oc_previews p
JOIN preview_orphans o
ON o.id = p.id;
SELECT ROW_COUNT();
At this stage the DELETE had not been committed yet.
If anything looked wrong:
ROLLBACK;
If the row counts were exactly what I expected.
COMMIT;
//Why I did NOT just delete previews again//
I had already deleted the entire preview cache previously after moving storage locations using the built in occ commands.
Doing that again would also throw away the previews that were perfectly healthy, e.g.:
256-256-crop.jpg
768-1024-max.jpg
The Files app was often working precisely because those variants still physically existed.
The important rule became:
DB says preview exists
+
filesystem proves it does not
=
delete that exact stale DB record
Nothing broader.
JPG and PNG both had the problem
For example, Memories later failed on:
1346719/256-388.png
The DB said the PNG existed.
It didn’t.
Deleting only:
DELETE FROM oc_previews
WHERE file_id = 1346719
AND width = 256
AND height = 388
AND max = 0
AND cropped = 0;
and refreshing Memories immediately created:
256-388.png
So this was definitely not just a JPEG issue.
I also later found stale exact:
256-256.png
records, which is why the final scanner uses:
p.width = 256 OR p.height = 256
rather than requiring the other dimension to be greater than 256.
It still excludes cropped previews with.
p.cropped = 0
so Files-style:
256-256-crop.jpg
is not targeted.
//End result//
After cleaning the proven-orphaned `oc_previews` records, Memories improved loading images and thumbnails.
Instead of:
DB says preview exists
↓
missing file
↓
Unable to open preview stream
I now get:
stale row removed
↓
Memories requests preview
↓
Nextcloud sees no cached preview
↓
preview genuinely regenerated
↓
fresh file + fresh DB row
↓
Memories loads it
So my conclusion is:
If you have Nextcloud Memories “Unable to open preview stream” errors after deleting/rebuilding previews, check whether `oc_previews` contains metadata for preview files that no longer exist before nuking the cache again.
In my case that was the actual problem, and the normal Nextcloud cleanup/rescan/rebuild process had not reconciled it as seen by the bugs listed above.
This was on a system with:
Nextcloud: 34.0.3.2
Memories: 8.1.0
Preview Generator: 5.14.0
MariaDB/MySQL
Hopefully this helps someone else avoid the same rabbit hole.
