Bug report #16935

Editing a Spatiallite/GPKG layer added by d&d (or DB Manager) leads to error and data corruption

Added by Giovanni Manghi about 3 years ago. Updated almost 3 years ago.

Status:Closed
Priority:High
Assignee:Alessandro Pasotti
Category:Vectors
Affected QGIS version:master Regression?:No
Operating System: Easy fix?:No
Pull Request or Patch supplied:Yes Resolution:fixed/implemented
Crashes QGIS or corrupts data:Yes Copied to github as #:24834

Description

1) create a new spatialite db and a layer in it (layer > create layer > new spatialite layer), for example a polygon one in WGS84 CRS

2) digitize a polygon and save

3) start a new project

4) add the above layer in the new project by dragging and dropping the Spatialite DB from outside QGIS within the QGIS main window

5) put the layer in edit mode, split the polygon with the "split features" tool, try to save

Could not commit changes to layer teste aaa Polygon

Errors: SUCCESS: 1 geometries were changed.
ERROR: 1 feature(s) not added.

Provider errors:
OGR error creating feature -2: sqlite3_step() failed:
UNIQUE constraint failed: aaa.pkuid (19)

6) discard changes > one of the polygons result of the split will vanishes

7) try all the about from the "add spatialite layer" dialog, the problem will not occur

Associated revisions

Revision 73d6765e
Added by Alessandro Pasotti almost 3 years ago

[bugfix] Editing a GPKG layer added by d&d leads

... to error and data corruption

Fixes #16935 for GPKG (OGR), still needs to check spatialite

Thanks to Matthias Kuhn for pointing me into the right direction

History

#1 Updated by Giovanni Manghi about 3 years ago

  • Category changed from Data Provider/SpatiaLite to Vectors
  • Subject changed from Editing a Spatiallite layer added by d&d leads to error and data corruption to Editing a Spatiallite/GPKG layer added by d&d leads to error and data corruption

Correction, this is not specific of Spatialite, it happens the same with GPKGs

#2 Updated by Giovanni Manghi about 3 years ago

  • Subject changed from Editing a Spatiallite/GPKG layer added by d&d leads to error and data corruption to Editing a Spatiallite/GPKG layer added by d&d (or DB Manager) leads to error and data corruption

It affects also GPKGs added via DB Manager, while Spatialite layers seem ok (from DB Manager).

#3 Updated by Alessandro Pasotti almost 3 years ago

  • Assignee set to Alessandro Pasotti

#4 Updated by Alessandro Pasotti almost 3 years ago

  • Status changed from Open to Feedback
  • Affected QGIS version changed from 2.18.11 to master

What's happening here is pretty clear: by splitting the polygon you create a new geometry, but the primary key of the table is just copied from the original feature attribute, when you commit the DB refuses to save due to a UNIQUE constraint in the primary key.

I think this is the expected behavior: what do you think should happen? (what is the expected behavior for the the primary key value?)

#5 Updated by Alessandro Pasotti almost 3 years ago

Ok, got the error but I'm still unsure about the solution: what happen here is:

- the split geometry operation is done within 2 distinct operations:
- 1) change the original geometry
- 2) insert the new splitted geometry
- the order is clearly wrong, because if the insert fails (which it does) you end up with the original geometry splitted but without the new splitted part
- even if you change the order and commit changes, it will fail because (and here comes the real ugly issue) the order of various operations in the commitChanges is completely ignored (the order is always the same: first change all the geometries then insert all the features)

#7 Updated by Alessandro Pasotti almost 3 years ago

  • Pull Request or Patch supplied changed from No to Yes
  • Status changed from Feedback to In Progress

for gpkg: https://github.com/qgis/QGIS/pull/5679

Now checking spatialite ...

#8 Updated by Anonymous almost 3 years ago

  • Status changed from In Progress to Closed
  • % Done changed from 0 to 100

#9 Updated by Giovanni Manghi almost 3 years ago

Hi Alessandro, is #17185 related?

#10 Updated by Alessandro Pasotti almost 3 years ago

@giovanni, not sure: probably slightly related, if you want I can have a look to see if I can fix it in the same way I fixed this one.

Btw, there is a more general problem about rollbacks on complex editing operations, by wrapping the editing commands in transactions for the data sources that supports them. Maybe worthwhile to file a separate ticket for that.

#11 Updated by Giovanni Manghi almost 3 years ago

  • Resolution set to fixed/implemented

Also available in: Atom PDF