[Bug]: sqlite3 configured to only use mmap, no fallback to regular IO

trapexit commented 2 months ago

⚠️ This issue respects the following points: ⚠️

[X] This is a bug, not a question or a configuration/webserver/proxy issue.
[X] This issue is not already reported on Github OR Nextcloud Community Forum (I've searched it).
[X] Nextcloud Server is up to date. See Maintenance and Release Schedule for supported versions.
[X] I agree to follow Nextcloud's Code of Conduct.

Bug description

I'm the author of mergerfs. As a FUSE filesystem it is possible for individual to disable mmap support. Numerous Nextcloud + mergerfs users have run into an issue where they get IO errors from sqlite because (I'm guessing) nextcloud's usage of sqlite3 sets up mmap and when a query fails doesn't reset it to regular IO. As a result they get unclear messages about IO errors.

Steps to reproduce

Setup mergerfs with cache.files=off
Point Nextcloud to the mergerfs mount during install.

Expected behavior

Ideally the code managing sqlite3 could confirm the target filesystem supports mmap and falls back to regular IO.

Installation method

None

Nextcloud Server version

28

Operating system

None

PHP engine version

None

Web server

None

Database engine version

SQlite

Is this bug present after an update or on a fresh install?

None

Are you using the Nextcloud Server Encryption module?

None

What user-backends are you using?

[ ] Default user-backend (database)
[ ] LDAP/ Active Directory
[ ] SSO - SAML
[ ] Other

Configuration report

No response

List of activated Apps

No response

Nextcloud Signing status

No response

Nextcloud Logs

No response

Additional info

No response

kesselb commented 1 month ago

Thanks for your bug report.

I'm not sure if there's anything we can do.

We are using Doctrine and PHP's PDO as database abstraction: https://www.doctrine-project.org/projects/doctrine-dbal/en/4.0/reference/configuration.html#pdo-sqlite.

Moreover, we recommend for a while now to not use SQLite. There's nothing wrong with SQLite, but it usually don't work as soon as you connect one client due to the parallel write operations.

trapexit commented 1 month ago

I understand the advice and predicament. The reality is a number of homelab folks use nextcloud with sqlite from my anecdotal experience. Unfortunately, this situation of not having a fallback is pretty common across users of sqlite and bites my users pretty regularly. Perhaps we could submit this upstream?

Do you believe https://github.com/doctrine/dbal/issues would be the right place? Not familiar with the PHP space.

kesselb commented 1 month ago

Isn't that something for SQLite itself?

If the mode you are looking for is listed on https://www.sqlite.org/c3ref/open.html, then we need to check if that options are somehow exposed in https://github.com/php/php-src/tree/master/ext/pdo_sqlite, and we can, as api consumer, enable it.

trapexit commented 1 month ago

Not to my understanding. sqlite3 allows users to select mmap or regular io and these projects hard code mmap mode and don't handle errors related to mmap being unavailable. You could argue that sqlite3 could have a "automatic" mode but given mmap is not the default behavior and the user (the app dev) is setting this value IMO they should handle it.

To enable mmap

kesselb commented 1 month ago

I believe it's something for https://github.com/php/php-src then.

trapexit commented 1 month ago

Nothing stands out in that code regarding mmap. And honestly, since the value is set as a PRAGMA I'd be a little surprised if it were hard coded. Though it can be set via sqlite_config and compile time too. That said I'm also not seeing anything in nextcloud server code enabling it.

nextcloud / server