7

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?

Improve this question
2
  • The -wal and -shm files should be automatically processed and deleted when the last connection to the database file is closed. But perhaps the error means another thing. Is your gpkg file on a network drive?
    – user30184
    Commented Jan 12, 2017 at 6:35
  • Yes, the gpkg is on a network drive.
    – Colin
    Commented Jan 13, 2017 at 1:51

1 Answer 1

Reset to default
7

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

PRAGMA journal_mode;

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

PRAGMA journal_mode=DELETE;

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:

  • Open a gpkg that is originally in journal_mode=delete with QGIS -> journal mode turns into WAL
  • Open the same gpkg with spatialite-gui -> journal mode is still WAL
  • Close the layer from QGIS -> journal mode is WAL in spatialite-gui
  • Close gpkg from spatialite-gui -> the -wal and -shm files disappear but journal mode remains WAL

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:

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.

Improve this answer
11
  • Thanks User30184. I guess the main cause here is that I wasn't aware of Write Ahead Logs and didn't understand how they worked (PEBKAC :-) ). While my file was on a network drive there were no other connections but I do have a (bad) habit of leaving QGIS running and I'm sure this is why the journal_mode was set to WAL and the two temporary files existed. I will write a warning into my doco about this! :-)
    – Colin
    Commented Jan 13, 2017 at 2:19
  • BTW I was able to get ArcCatelog to read the gpkg using DB Browser for SQLite and change journal_mode to DELETE, similar to your suggestion to run the journal_mode=DELETE command. Seems like this could be a work around but better to ensure everything is doconnected before copying the file. ;-)
    – Colin
    Commented Jan 13, 2017 at 2:19
  • If the size of the wal file is more than zero then it contains uncommitted changes. By copying gpkg alone you would get an older version of your data.
    – user30184
    Commented Jan 13, 2017 at 5:27
  • is there a reason that the WAL journal mode is now turned on by default for GeoPackage and SpatiaLite (i.e., automatically creating the -wal and -shm files, even if just temporary). Is there a way to change the default back or does one have to issue the PRAGMA journal_mode=DELETE; command to each new GeoPackage created if one wants to have single files, i.e., *gpkg as was the previous behaviour?
    – Rob Willson
    Commented Jan 18, 2017 at 17:26
  • 1
    There seems to be two workarounds suggested by a QGIS developer lists.osgeo.org/pipermail/qgis-user/2017-January/038830.html. If you think that journal_mode=DELETE should be the default it could be worth telling it to the list. I believe that in desktop use the gain of using WAL is rather minimal.
    – user30184
    Commented Jan 18, 2017 at 19:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.