I am trying to find a replacement for shapefiles when sharing geospatial data. I am testing the geopackage format and with QGIS 2.18.2, I can create a geopackage and add additional layers to the geopackage using the 'save as' function.
I have created geopackage files in previous versions of QGIS (2.14.9), which creates a single gpkg file. This is attractive over shapefiles because it is one file instead of many. However, when I create a geopackage using 2.18.2 I get additonal two files – gpkg-wal, gpkg-shm.
The issue is when I try to open the geopackage created in 2.18.2 using ArcCatelog 10.4 I get an error "underlying DBMS error"…"is mounted and in WAL mode, this combination is not allowed".
My question is how do I create a single geopackage file that is not in WAL mode using QGIS?
Best Answer
QGIS is made to use Write Ahead Logging (WAL) by commit https://github.com/qgis/QGIS/commit/f939e9cff598b95e95b0de099d0c9a92eed0ea9c
The new behavior should be to open gpkg database file in WAL journal mode if file is on local disk but turn it back to the default journal mode when the connection is closed.
I made a test with QGIS 2.18.2 and that seems to work. I opened a gpkg database with "Add Vector Layer" and immediately -wal and -shm files appear in the same directory. When I removed the layer from QGIS both files disappear. I verified with spatialite-gui that the journal mode was really turned back by making a pragma request
You must not ever delete manually -wal and -shm files. If they still are there in the directory I would check if there are any other clients than QGIS having connections. If not, opening and closing db with QGIS may clear the temporary files and turn logging to default mode. If that does not help, try with the pragma command
It is not possible to turn the journal mode from WAL to anything else if there are any other open connections. Only the last connection can do the change. Also, journal mode is persistent and gpkg database that is set to WAL mode remains in WAL mode until it is explicitly set to something else. I guess that this may cause troubles sometimes. I made this simple test:
In this state the gpkg database will work from local disk but not from network drives if copies of it are given to end users!
Edit:
These are the two ways reported by Even Roualt for turning WAL mode off from QGIS: