[QGIS-Developer] 3.40 mac binary version of qgis spuriously writing geopackage files, old sqlite3?

[Even Rouault]

Greg,

what is the GDAL version of the Mac binary? If it is older than 3.4.2 
that could explain it.

For the issue with IsLocalFile() implementation for 
non-Windows/non-Linux OS, that should be fixed per 
https://github.com/qgis/QGIS/pull/62284

Even

Le 14/06/2025 à 22:11, Greg Troxel via QGIS-Developer a écrit :
> This is a little long, but the summary is
>
>    3.40.7, mac binary from qgis.org, appear to write geopackage files
>    when qgis is merely reading them and no layer write operation is
>    invoked.  This is "obviously wrong" from my viewpoint, but I can't
>    find any documentation.
>
>    Not that I think it really matters to the above, but the sqlite
>    version included in the 3.40 mac installer is much older than I would
>    have expected.
>
>    The qgis user manual doesn't explain how this is supposed to work, and
>    the code doesn't have enough comments to see if it is doing what it is
>    supposed to be doing.
>
>    The "isLocal" code seems wrong in the general case.
>
> For background -- since I know it's unusual -- I've been mostly using
> qgis on NetBSD, built via pkgsrc.  It's been working well.  I just
> updated from 3.34.x to 3.40.7, and yes, I know that was overdue.  I have
> several of project files, and data, which I believe is pretty normal:
>    - several gpkg files, usually with multiple tables
>    - postgis
>    - a few csv files (that I treat as read only)
>    - geodatabases and shp from MassGIS (that I treat as read only)
>    - jp2/tif tiles of imagery
>    - online WMTS/TMS
>
> I use unison (a user-mode synchronization program, and I happen to be
> the maintainer) as a replicated filesystem across machines, mostly for
> sort-of-backup and partially for editing on secondary machines.  As a
> side effect unison alerts me to changes, so I notice things others might
> not.
>
> Long ago I remember a problem where qgis (via gdal) would write (in a
> filesystem sense) a gpkg file when it was merely opened for reading.  A
> fix was made to gdal to enable reading a database to simply.... read the
> database file, apparently in gdal 3.4.2, now long-ago history,
>    https://github.com/OSGeo/gdal/pull/5207
> and then used in qgis:
>    https://github.com/qgis/QGIS/pull/47098
> There is also
>    https://github.com/qgis/QGIS/pull/48059
>
> (Separately from unison, whenever there is any kind of sync going on,
> writing files when reading datasets turns a "read read" operation, which
> is not a sync conflict, into a "write write" operation, which will be a
> conflict if the first write does not get synced to the machine that does
> the second read before that second read.  Thus, from long experience
> with distributed filesystems including disconnected operation, I'm of
> the opinion that logically reading should ~never result in a write.)
>
> Since those changes, I have been able to start qgis on a project file
> with geopackage layers, look at the project (and perhaps edit other
> layers), and exit, without any fileysystem writes to the gpkg file.  I
> think it even refrains from opening the file for write and not writing
> -- which on most local filesystems does not cause a modification but can
> interact badly with distributed filesystems such as coda.  I have no
> memory of having to set anything; my memory is that with fixes to
> qgis/gdal, it just worked correctly (where correctly means reading is
> reading, not reading is writing).
>
> Now, I have a project that had two gpkg layers, that I added a shapefile
> to, just to visualize the shapefile and to load it into QField.  I was
> planning to ignore the other layers when looking at the new data, and to
> omit the older layers in QField export.  And, I wanted to access this
> project on a mac notebook, so I installed 3.40 on it, via qgis.org.
>
> On the mac, I started up qgis, and looked at the shapefile, panning and
> zooming, querying features, but not enabling write in any layer.
>
> Later, I noticed that the gpkg files had been written.
>
> Comparing a copy from [before (the -back and the modified file:
>
> foobar-backup-baz.gpkg: SQLite 3.x database (OGC GeoPackage file), user version 10200, last written using SQLite version 3048000, file counter 29, database pages 24, cookie 0x2f, schema 4, UTF-8, version-valid-for 29
> foobar.gpkg:            SQLite 3.x database (OGC GeoPackage file), user version 10200, last written using SQLite version 3035002, file counter 35, database pages 24, cookie 0x2f, schema 4, UTF-8, version-valid-for 35
>
> I see that the file counter has been incremented 6 times (and
> version-valid-for, correspondingly), and the last-written version
> changed from 3.48.0 to 3.35.2.
>
> sqlite3 versions:
>
>    3.49.2	NetBSD/pkgsrc machine (but plausibly 3.48.0 when the
>                  gpkg was last written)
>
>    3.35.2	apparently the version in the qgis mac binary
>    3.39.5	/usr/bin/sqlite3 on mac
>    3.48.0	pkgsrc version on mac
>
> This is surprising; it makes sense that Apple is behind (macOS 13)
> because that's what they do, but 3.35 seems much older than I'd expect.
>
> Doing cmp -l backup current:
>      28  35  43
>      96  35  43
>      99 202 117
>     100 100 172
>
>
> I found
>    https://torymur.github.io/sqlite-repr/
> which is really useful, when used with emacs hexl mode, as "sqlite3
> foobar.gpkg dump" omits metadata and sqlite3 doesn't seem to have a full
> deconstruct option.
>
> So my sqlite file change is read version +=8, valid version same, and
> last write sqlite version changed.  .dump is the same for both, and
> "file" above shows all the differences.
>
> This is repeatable; open/view/close results in sqlite version change and
> +2 in the database version.  Today, with 3.40.7 built under pkgsrc
> (really a pretty straightforward build), and 3.40.7 mac official, I find
> that
>
>    NetBSD pkgsrc: read is read
>    macOS official: read is write (modified and I see wal when qgis is open)
>
> So, the bug is that read is opening a journal, on macOS.
>
> Looking at 3.40.7:
>
>    src/core/providers/ogr/qgsogrproviderutils.cpp : QgsOgrProviderUtils::GDALOpenWrapper
>
> It looks like QGIS_FORCE_WAL is set (to nullptr) for any geopackage,
> even those that are unrelated to offline editing (a feature I've never
> used).  This doesn't seem to be from PR #48059.  It seems that databases
> for offline storage should have some  sidecar way to force journaling
> for read, but naively I'd expect those to be opened for write anyway.
>
> Then there seems to be code to detect local and set NOLOCK on reading.
>
> And, if not local, to say no journal period.  (I have no idea if sqlite
> really works correctly on remote filesystems but I'm in the "don't do
> that" camp.)
>
> Reading the isLocal code, I notice there are three implementations: one
> for Windows, one for Linux, and one for POSIX except Linux (and maybe
> android?).  I can't understand the code.  It refers to "path" which
> might be relative and not to "dirName" which is absolute (but a path,
> not a directory name, if the qt code names make sense).  And then it
> outright compares path to "nfs" and "smbfs" which appear to intend to be
> filesystem *types*, not pathnames, not using info.fileSystemType which
> was just computed.
>
> But, that means isLocal should always return true, unless the data
> source is literally a relative path to a file named nfs or smbfs.
> _______________________________________________
> QGIS-Developer mailing list
> QGIS-Developer at lists.osgeo.org
> List info: https://lists.osgeo.org/mailman/listinfo/qgis-developer
> Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-developer

-- 
http://www.spatialys.com
My software is free, but my time generally not.