V2.1.0-alpha.1 SQLITE_CORRUPT

[avdp]

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?

[avdp]

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"}}

[mrm]

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

[mrm]

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?)

[avdp]

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

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

[mrm]

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.

[bvwelch]

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.

[avdp]

@bvwelch

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

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

Support mariadb, Mysql, etc

Has this been suggested before? Optional support for a databases other than sqlite, especially for server (docker) installs. Mariadb, mysql are popular options amongst more advanced users (like myself) that self-host applications like Home Assistant, etc. This might open the door to a whole new set of future capabilities, including perhaps concurrent access to the database from multiple instances of photostructure, or even new third-party capabilities to display library stats, etc. ( I un…

[mrm]

@bvwelch

switching away from sqlite and using mariadb

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)

[bvwelch]

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.

[mrm]

@bvwelch

my family is missing the photos while I struggle to get it running again

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.

How to restore your library from a database backup

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

[bvwelch]

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.

[bvwelch]

Well I am out of ideas, and will have to wait until I get more free time – back to my day job tomorrow. but here is my update-- I gave up on proxmox, and then realized my unraid trial had lapsed a few days ago, so I went with Ubuntu 22.04 lts desktop. I got the same sqlite malformed error. I then tried a different hard drive. same error. I suppose my next experiment will be to switch to a different computer entirely.

[mrm]

Can you DM me your About page, the filesystem type that hosted your library, any hard drive/ssd details, and maybe a library database backup so I can try to reproduce your error?

(Ideally I want local hardware and a process that results in sqlite errors, so I can then track the problem, try different resolutions, and not just be shooting in the dark)

Thanks for keeping me in the loop here, and apologies for the inconvenience!