1

I am a GIS professional who has recently migrated over to QGIS from MapInfo and have been experimenting with the use of SpatiaLite and Geopackage databases to store our ecology data. I have structured the database so it contains approximately 8 layers comprised of points, lines and polygons and associated attribute fields.

The database is stored on our server and accessed by multiple people across mulitple PCs (although not at the same time). It loads into QGIS fine, but after a period of 30-60 minutes seems to lock itself and won't allow any changes (editing/adding features or attribute information) to be written to the database and shows the below image: error accessing database

Any thoughts on what might be causing this? The error doesn't show when the database is stored on a C: drive so must be a server related issue. Can Spatialite/Geopackage databases be stored on servers in the same way a shapefile can?

3
  • It sounds as if anti-virus software on the fileshare server is conflicting with your use pattern.
    – Vince
    May 20 '21 at 4:16
  • 4
    SQLite developers do not recommend SQLite for such use. See sqlite.org/whentouse.html and sqlite.org/lockingv3.html.
    – user30184
    May 20 '21 at 6:57
  • 2
    If possible, you should better switch to use a PostgreSQL/PostGIS instance instead of Geopackage considering your context
    – ThomasG77
    May 20 '21 at 8:57
4

I have a similar setup and have in the past experienced similar issues. It is hard to say exactly what the cause of this is, but as per the comments, geopackages are SQLite DB's and aren't really meant for concurrent usage.

However here are some of my experiences and comments.

"OGR Error Changing Geometry; Feature 21 not found" -

In my experience, this has occured when two separate transactions occured, but the first transaction has not completed or committed successfully for whatever reason.
eg: You insert Record 21. It appears on the map. its attributes are there. you save the edit on the geopackage. However when attempting to now edit the record, you are presented with that error. (thats usually what as occured for me).

One thing to keep an eye for is - during your first transaction save, if there is an issue with the transaction, QGIS will present a popup just above the map window. Sometimes I miss this, BUT then on the second transaction, I see the error. It now becomes less about the second error, and more about the first. In other words - Check that there are no errors with your first transaction!

The way I have generally resolved this, is to perform transactions in a way that simulates what is referred to in other DB systems as a 'short transaction'. That is - ensure the database is only writable for the duration of the write activity, then put the DB back into Readonly mode with no further action on the database. (Easy to do when im the only user). In other words, inside of QGIS:

  • Switch to writable
  • Insert/modify/delete
  • Save
  • Switch back to readonly

I realize this isn't ideal if say you are editing the vertexes on a single geometry, and want it set to writable for an extended period of time. I have personally implemented a 'work offline, implement online' type of model for edits, which is ok for me, but might not float your boat and certainly will cause issues with multiple users. However working in this manner has helped eliminate a lot of these types of transaction errors. BUT again - I am a single user.

The root cause of your problems are most likely the journal files being corrupted, or similar, due to concurrent access. Geopackages aren't meant for concurrent usage as per the other comments, hence the problem.

Its less about the fact that its on a server - and more about the multiple users.

You could setup some kind of publishing mechanism via a copy of the database, such that you have a 'master writable database', whilst everyone else accesses the published replica or similar, but this may depend on your user requirements for up to date data and is almost more of a band-aid solution that anything else.

Your next question really should be - What is an appropriate solution I can migrate my datasets to.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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