V2.1.0-alpha.1 SQLITE_CORRUPT

A while back I reported the SQLITE_CORRUPT I get on unraid using a cache drive. Even though this was not a problem with 2.0 on the same setup, the working theory discussing with @mrm is that it possibly had to do with UNRAID cache drive not being great with SQLITE (indeed, some googling indicates a lot of similar problems, although with older version of UNRAID).

{"toast":{"text":"Well, darn, that didn't work","uuid":"SqliteError: SQLITE_CORRUPT: database disk image is malformed","details":"SqliteError: SQLITE_CORRUPT: database disk image is malformed","type":"warning","button1Text":"Return home","button1Url":"/tag#dismiss","button1Class":"info","onClickUrl":"#dismiss"}}

I’ve recently setup another server to play with. This time, it’s Ubuntu Server 22.04 LTS, with docker-ce 22.10.16. I’ve tried putting the library folder on a plain nvme SSD (ext4), and on a ZFS array. Both setup result in the exact same SQLITE error.

So I think it’s fair to say UNRAID is not the problem. Possibly something in my picture library? Or some other not discovered bug? This is very easy to replicate - this happens 100% of the time I do a scratch setup (empty library). So let me know what I can do to help find the issue?

The only occurences of the word “Error” or “sql” in all the logs are below:

web-35-001.log:{"ts":1654458025349,"l":"error","ctx":"DbRequest","msg":".throw() database disk image is malformed","meta":{"stack":"SqliteError: database disk image is malformed\n    at /ps/app/bin/web.js:9:857469\n    at /ps/app/bin/web.js:9:880041\n    at sqliteTransaction (/ps/app/node_modules/better-sqlite3/lib/methods/transaction.js:65:24)\n    at maxR…st (/ps/app/bin/web.js:9:858776)","method":"select","options":{},"timeout":false,"cancelOnTimeout":false,"bindings":[1,0],"sql":"select count(distinct `Asset`.`id`) from `Asset` where `Asset`.`shown` = ? and `Asset`.`hidden` = ? and `Asset`.`excludedAt` is null and `Asset`.`deletedAt` is null"}}
worker-4648-001.log:{"ts":1654456973481,"l":"error","ctx":"AssetPreviewBuilder(644)","msg":".throw() Cannot build previews: none of the files are valid: Failed to extract video frame for /media/photos/2018/201812/MVIMG_20181218_160234.MP4: Failed to extract frame for /media/photos/2018/201812/MVIMG_20181218_160234.MP4: Cannot read /media/photos/2018/201812/MVIMG_20181218_160234.MP4 (video/mp4)²⁶","meta":{"stack":"Error: Failed to extract frame for /media/photos/2018/201812/MVIMG_20181218_160234.MP4\n    at t.ffmpegFrame_ (/ps/app/bin/worker.js:9:350967)\n    at /ps/app/bin/worker.js:9:345758\n    at L.applyWip_ (/ps/app/bin/worker.js:9… (/ps/app/bin/worker.js:9:88697)","doNotSend":true,"retriable":false,"files":["/media/photos/2018/201812/MVIMG_20181218_160234.MP4"],"causes":[{"name":"Error","causes":[{"name":"Error","causes":["Failed to extract video frame for /media/photos/2018/201812/MVIMG_20181218_160234.MP4",{}],"fatal":false},{"message":"Cannot read /media/photos/2018/201812/MVIMG_20181218_160234.MP4 (video/mp4)","causes":[{"name":"Error","causes":["Failed to extract video frame for /media/photos/2018/201812/MVIMG_20181218_160234.MP4",{}],"fatal":false}]}],"fatal":false}]}}
worker-4648-001.log:{"ts":1654457041567,"l":"error","ctx":"AssetPreviewBuilder(796)","msg":".throw() Cannot build previews: none of the files are valid: Failed to extract video frame for /media/photos/2018/201812/MVIMG_20181230_133725.MP4: Failed to extract frame for /media/photos/2018/201812/MVIMG_20181230_133725.MP4: Cannot read /media/photos/2018/201812/MVIMG_20181230_133725.MP4 (video/mp4)²⁶","meta":{"stack":"Error: Failed to extract frame for /media/photos/2018/201812/MVIMG_20181230_133725.MP4\n    at t.ffmpegFrame_ (/ps/app/bin/worker.js:9:350967)\n    at runNextTicks (node:internal/process/task_queues:61:5)\n    at processTimers… (/ps/app/bin/worker.js:9:88697)","doNotSend":true,"retriable":false,"files":["/media/photos/2018/201812/MVIMG_20181230_133725.MP4"],"causes":[{"name":"Error","causes":[{"name":"Error","causes":["Failed to extract video frame for /media/photos/2018/201812/MVIMG_20181230_133725.MP4",{}],"fatal":false},{"message":"Cannot read /media/photos/2018/201812/MVIMG_20181230_133725.MP4 (video/mp4)","causes":[{"name":"Error","causes":["Failed to extract video frame for /media/photos/2018/201812/MVIMG_20181230_133725.MP4",{}],"fatal":false}]}],"fatal":false}]}}
worker-4689-001.log:{"ts":1654457008188,"l":"error","ctx":"AssetPreviewBuilder(720)","msg":".throw() Cannot build previews: none of the files are valid: Failed to extract video frame for /media/photos/2018/201812/MVIMG_20181230_130715.MP4: Failed to extract frame for /media/photos/2018/201812/MVIMG_20181230_130715.MP4: Cannot read /media/photos/2018/201812/MVIMG_20181230_130715.MP4 (video/mp4)²⁶","meta":{"stack":"Error: Failed to extract frame for /media/photos/2018/201812/MVIMG_20181230_130715.MP4\n    at t.ffmpegFrame_ (/ps/app/bin/worker.js:9:350967)\n    at /ps/app/bin/worker.js:9:345758\n    at L.applyWip_ (/ps/app/bin/worker.js:9… (/ps/app/bin/worker.js:9:88697)","doNotSend":true,"retriable":false,"files":["/media/photos/2018/201812/MVIMG_20181230_130715.MP4"],"causes":[{"name":"Error","causes":[{"name":"Error","causes":["Failed to extract video frame for /media/photos/2018/201812/MVIMG_20181230_130715.MP4",{}],"fatal":false},{"message":"Cannot read /media/photos/2018/201812/MVIMG_20181230_130715.MP4 (video/mp4)","causes":[{"name":"Error","causes":["Failed to extract video frame for /media/photos/2018/201812/MVIMG_20181230_130715.MP4",{}],"fatal":false}]}],"fatal":false}]}}
worker-4689-001.log:{"ts":1654457019138,"l":"error","ctx":"AssetPreviewBuilder(746)","msg":".throw() Cannot build previews: none of the files are valid: Failed to extract video frame for /media/photos/2018/201812/MVIMG_20181230_130722.MP4: Failed to extract frame for /media/photos/2018/201812/MVIMG_20181230_130722.MP4: Cannot read /media/photos/2018/201812/MVIMG_20181230_130722.MP4 (video/mp4)²⁶","meta":{"stack":"Error: Failed to extract frame for /media/photos/2018/201812/MVIMG_20181230_130722.MP4\n    at t.ffmpegFrame_ (/ps/app/bin/worker.js:9:350967)\n    at /ps/app/bin/worker.js:9:345758\n    at L.applyWip_ (/ps/app/bin/worker.js:9… (/ps/app/bin/worker.js:9:88697)","doNotSend":true,"retriable":false,"files":["/media/photos/2018/201812/MVIMG_20181230_130722.MP4"],"causes":[{"name":"Error","causes":[{"name":"Error","causes":["Failed to extract video frame for /media/photos/2018/201812/MVIMG_20181230_130722.MP4",{}],"fatal":false},{"message":"Cannot read /media/photos/2018/201812/MVIMG_20181230_130722.MP4 (video/mp4)","causes":[{"name":"Error","causes":["Failed to extract video frame for /media/photos/2018/201812/MVIMG_20181230_130722.MP4",{}],"fatal":false}]}],"fatal":false}]}}
worker-4689-001.log:{"ts":1654457028376,"l":"error","ctx":"AssetPreviewBuilder(767)","msg":".throw() Cannot build previews: none of the files are valid: Failed to extract video frame for /media/photos/2018/201812/MVIMG_20181230_133715.MP4: Failed to extract frame for /media/photos/2018/201812/MVIMG_20181230_133715.MP4: Cannot read /media/photos/2018/201812/MVIMG_20181230_133715.MP4 (video/mp4)²⁶","meta":{"stack":"Error: Failed to extract frame for /media/photos/2018/201812/MVIMG_20181230_133715.MP4\n    at t.ffmpegFrame_ (/ps/app/bin/worker.js:9:350967)\n    at /ps/app/bin/worker.js:9:345758\n    at L.applyWip_ (/ps/app/bin/worker.js:9… (/ps/app/bin/worker.js:9:88697)","doNotSend":true,"retriable":false,"files":["/media/photos/2018/201812/MVIMG_20181230_133715.MP4"],"causes":[{"name":"Error","causes":[{"name":"Error","causes":["Failed to extract video frame for /media/photos/2018/201812/MVIMG_20181230_133715.MP4",{}],"fatal":false},{"message":"Cannot read /media/photos/2018/201812/MVIMG_20181230_133715.MP4 (video/mp4)","causes":[{"name":"Error","causes":["Failed to extract video frame for /media/photos/2018/201812/MVIMG_20181230_133715.MP4",{}],"fatal":false}]}],"fatal":false}]}}
web-35-001.log:{"ts":1654458025349,"l":"error","ctx":"DbRequest","msg":".throw() database disk image is malformed","meta":{"stack":"SqliteError: database disk image is malformed\n    at /ps/app/bin/web.js:9:857469\n    at /ps/app/bin/web.js:9:880041\n    at sqliteTransaction (/ps/app/node_modules/better-sqlite3/lib/methods/transaction.js:65:24)\n    at maxR…st (/ps/app/bin/web.js:9:858776)","method":"select","options":{},"timeout":false,"cancelOnTimeout":false,"bindings":[1,0],"sql":"select count(distinct `Asset`.`id`) from `Asset` where `Asset`.`shown` = ? and `Asset`.`hidden` = ? and `Asset`.`excludedAt` is null and `Asset`.`deletedAt` is null"}}

Thanks for doing this research! I’ll check those logs as soon as I get back to my desk.

The “Failed to extract video frame” error may be due to using a newer version of ffmpeg: they removed the singlejpeg format, and I had to adjust PhotoStructure’s video frame extraction arguments. v2.1-alpha.2 has that fix.

I just did some net spelunking for database disk image is malformed and found this: SQLite Forum: FTS5 error: database disk image is malformed and looking into if that scenario is applicable/causal.

Update: I’m not using triggers, and have no incremental tag FTS updates (it’s currently fast enough to just drop and rebuild that entire FTS index on tag updates), so I’m looking elsewhere (perhaps the db backup process?)

Should it matter, here are my env variables. The only one as far as I know with impact to SQLITE is PS_FORCE_LOCAL_DB_REPLICA, but I have confirmed this happens wether I set or don’t set this variable. Happy to retest with alpha.2 to see if one of the issue you’ve fixed there accidentally fixed this as well. You just need to release the thing :slight_smile:

Name Value
PS_FORCE_LOCAL_DB_REPLICA false
PS_IS_DOCKER true
PS_KEYWORD_REPARENTING move
PS_TAG_CAMERA false
PS_TAG_FACE_REGIONS false
PS_TAG_JSON_FACES false
PS_WHO_TAGS [“People”]
TZ America/New_York

Thanks for sharing that–nothing there looks suspect.

I was reviewing my call to PRAGMA WAL_CHECKPOINT and realized I wasn’t verifying that the db wasn’t busy before calling PRAGMA OPTIMIZE. I’ve added a retry loop for the checkpoint, added logging, and added the checkpoint flavor as a new setting, so we can test this in the wild.

I am getting this error also - tried alpha.1 and alpha.3. My setup is ubuntu server 22.04 lts, snap docker, virtual machine under proxmox.

I am not a database guy but have noticed some projects switching away from sqlite and using mariadb.

i am re-running with ps-log-level=info. lemme know if you need debug level instead. thanks.

Go vote on this feature request to get mariadb support. I am beyond tired of the sqlite issues…

MariaDB is a reasonable RDBMS!

PhotoStructure needs something out of the box that supports macOS, Windows, and Linux, and the on-disk db needs to be cross-platform as well for libraries to be both self-contained and portable across platforms.

The download of MariaDB is 300MB compressed, which is 3x larger than any of PhotoStructure’s installers… If I could choose any RDBMS, I’d pick PostgreSQL (it can handle PhotoStructure’s recursive CTE SQL queries), but those installers are quite large as well.

Check out the link from @avdp – we discussed an option to use a dedicated RDBMS with PhotoStructure. Using an external RDBMS would mean those libraries aren’t portable and self-contained, but that can be a decision made by more advanced users.

(And yeah, I’m pretty done with the SQLite issues as well)

ok i will check out the other link – i had forgotten about postgresql – i used it many years ago - it had an api for python.

meanwhile i am about at my wits end with photostructure – on the one hand i want to help advance the program, but my family is missing the photos while I struggle to get it running again.

I had pretty good luck with unraid, but wanted to try proxmox – not such good luck with it, unless i stop and learn docker-compose and also how to make proxmox bind mounts work with containers – so much to learn, so little time.

Let’s get this fixed! Are you on :stable, or :alpha?

If your library db is broken, you should be able to restore to the latest version with these instructions.

Ping me on discord if you want real-time assistance.

About the installer sizes, it is hard to resist saying – if it doesnt have to actually work correctly, it can be very small! So bigger, but working/robust seems a reasonable thing to me.

1 Like