opengisch / QgisModelBaker

Create QGIS projects from database schemas or Interlis models
https://opengisch.github.io/QgisModelBaker/
GNU Lesser General Public License v3.0
55 stars 17 forks source link

OGR error creating feature -7: failed to execute insert : database is locked #807

Open domi4484 opened 1 year ago

domi4484 commented 1 year ago

When editing some geopackage generated projects which use relations it can easily come to database locked situations when editing.

Steps to reproduce:

When using the buffered transaction mode the lock happens at the time of saving edits.

Tried paths:

sqlite_fetch_and_increment

One though was that the default value created by model baker for T_id fields was causing the lock because it accesses and write the DB for fetching and increment the index:

sqlite_fetch_and_increment(@layer, 'T_KEY_OBJECT', 'T_LastUniqueId', 'T_Key', 'T_Id', map('T_LastChange','date(''now'')','T_CreateDate','date(''now'')','T_User','''' || @user_account_name || ''''))

But this was not the case because after changing the QGIS implementation of sqlite_fetch_and_increment with a simple counter for testing the database locked error still occours.

GDAL SQL logs

When built against GDAL version 3.7.0 or above, QGIS has the ability to log the SQL statements in the Debug Window. But only for SELECT statements. Complete the functionality to log also INSERT, UPDATE and DELETE statement could help to debug this issue. Related QGIS code here Note: the function sqlite_fetch_and_increment doesn't use GDAL so its activity will not be logged with this mechanism.

signedav commented 1 year ago

Note: the function sqlite_fetch_and_increment doesn't use GDAL so its activity will not be logged with this mechanism.

But as you write above the lock occurs as well when this function is disabled, so we don't need to put the main focus on this.

When you create a db / project manually. Are there no locks? Means, can we say it's something concerning Model Baker only?

domi4484 commented 1 year ago

After so many tests I was not sure anymore, so I built the following table. No the issue is not model baker only, it is possible to build a minimal QGIS project to reproduce it. I am quite sure I had locks also in buffered transaction mode but I can't arrange to reproduce them anymore.

Automatic Buffered (with bugfix qgis #53098)
Simple non MB project Lock ❌ No lock ✅
Model baker Lock ❌ No lock ✅
Mb / fake fetch&inc Lock ❌ No lock ✅
signedav commented 1 year ago

This would be awesome news. I remember a situation where I had this issue, I tried buffered and it crashed but because of being very busy I didn't made further investigation...

signedav commented 1 year ago

I would like to enable auto transaction groups per default - as well in GeoPackages. This because people test it out often with GeoPackage. Then they have errors - not because of a bug, but because they do not save in the right order. As well, mostly in productive work people use the automatic transaction group anyway. See Mostly, in productive work people use the automatic transaction group. See #808

But I have concerns because of this issue. I tried to investigate a little. But I cannot reproduce any database locks. I cannot access the model you mentioned @domi4484 Maybe you have more use cases?

As soon as I tested out 3.28.7 we can set the buffered transaction mode per default.

domi4484 commented 1 year ago

@signedav to access the model I mentioned you can add the repository https://models.geo.tg.ch/ and search for naturgefahren

image

signedav commented 1 year ago

Ah it's not in the sites...

Btw. do you still have the minimal project? Just in case. Otherwise nevermind...

domi4484 commented 1 year ago

This is the project, just model baker + buffered transaction: geopackage-lock.zip

The easiest way to reproduce is:

  1. Open a new "Intervention" feature form
  2. Add a point children
  3. Add a line children
  4. Save edits

Note: this patch must be included otherwise QGIS will just crash

signedav commented 1 year ago

Thanks. But I thought about the project, that is not based on an INTERLIS model.

signedav commented 1 year ago

I can reproduce the crash but I'm more curious about the lock in automatic transaction group.

With your project I can set the "automatic transaction groups" and it seems to work fine.