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

[Greg Troxel]

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.