[Qgis-user] Something to think about GeoPackage and WAL

[Even Rouault]

Hi,
http://gis.stackexchange.com/questions/224188/geopackage-error-is-mounted-an
d-in-wal-mode-this-combination-is-not-allowed
Could it mean issues which end users can't understand if GeoPackage is
left to WAL mode and user tries to use it from network drive or as
read-only? Is it even possible to change the journal mode in this case
without moving the db into local disk?

Jukka,

(Adding qgis-user in CC)

WAL will not enabled by QGIS if the GeoPackage is located on a network driver (as best as we
can detect that situation !) since the SQLite doc mentions that WAL isn't safe in that mode
(see https://www.sqlite.org/wal.html)
And I don't think WAL can be enabled either on a read-only file/directory (the opening will go
on even if we can't turn WAL on)

If moving a WAL enabled DB (the .gpkg plus the -wal and -shm) on a network share, then this
should probably work, but not in a reliable way in a concurrent use case. If moving on a read-
only location, according to the doc, the database shouldn't be openable. If moving only the
.gpkg file without the -wal and -shm, you'll probably get an outdated version of the database,
or will not be able to open it at all. Not sure.

WAL is turned off by QGIS on layer removal, but QGIS must be the last program to have the
file opened so that operation to be successful. If a GeoPackage is left in WAL mode (on a
local disk), a workaround is to close all connections to it, and open / close it again with QGIS.

If there are interoperability problems, people can either define the
OGR_SQLITE_JOURNAL environment variable to DELETE or set the QGIS setting "/qgis/
walForSqlite3" (in advanced mode) to false, and this will prevent QGIS from enabling WAL on
opening. The drawback is potential deadlocks in some situations where a reader and writer
would run concurrently.

If the current situation causes more harm than good, we can change the "/qgis/
walForSqlite3" setting default value to false. Together with enabling WAL, I made another
change in the OGR provider that prevented a reader that has finished from iterating over
features from being left in a ghost active state, but I guess there will be real read/write
concurrent situations were deadlocks will appear.

I'm afraid there's no perfect solution. SQLite is a file-based database. One cannot ask for it
the full power of server-based databases regarding concurrent use.

Even

[jratike80]

I believe that for most users and most of the time using WAL does not make
any harm. What I can imagine affects mostly eager power users who might

1) Take only the .gpkg file and copy it while GeoPackage is still open in
QGIS - Hey, I have been reading that GeoPackage is just one file! As a
results the copy of GeoPackage lacks the pending edits and it remains in WAL
mode.

2) Keep GeoPackage open in other programs (like spatialite-gui) while it is
also open in QGIS. If QGIS is closing the connection first it can't switch
the journal mode, and if the other program does not even try to switch the
mode the GeoPackage file will remain in WAL mode. Data loss will not happen,
though.

Some real harm may happen if data are delivered for end users as GeoPackage
that is in WAL mode. If the WAL-db is saved into read-only media (CD, DVD,
read-only memory card) it can't be opened at all before copying it into some
writable media. And if user is placing the WAL db on a network drive it may
lead to some other problems in multi-user environment.

Probably this kind of issues will not occur frequently and I can't say if
enabling WAL by default makes more harm than good.

-Jukka Rahkonen-

Post by Even Rouault
If the current situation causes more harm than good, we can change the
"/qgis/
walForSqlite3" setting default value to false. Together with enabling WAL, I made another
change in the OGR provider that prevented a reader that has finished from iterating over
features from being left in a ghost active state, but I guess there will be real read/write
concurrent situations were deadlocks will appear.

--
View this message in context: http://osgeo-org.1560.x6.nabble.com/Re-Something-to-think-about-GeoPackage-and-WAL-tp5303139p5303203.html
Sent from the Quantum GIS - User mailing list archive at Nabble.com.