Bug report #20765

Delete field in table delete all features

Added by Lene Fischer over 1 year ago. Updated over 1 year ago.

Status:Closed
Priority:High
Assignee:-
Category:Data Provider/OGR
Affected QGIS version:3.5(master) Regression?:Yes
Operating System: Easy fix?:No
Pull Request or Patch supplied:No Resolution:up/downstream
Crashes QGIS or corrupts data:Yes Copied to github as #:28585

Description

To in a lecture today we should delete a field in a table. Geopackage.

When trying to save all data vanished. We were not able to undo.
When closing and opening again the open dialog showed 160 features - but none in the attribute tabel.

Error message was something about OGR..

This happend for all the students with version 3.4.2 Windows
Students with 3.4.1 had no problem - and the same for students with 3.4.2 Mac - (great fun because it´s alwas the Mac users who are having difficulties..)

Not sure if it´s the same issue as #20507

skovkort.gpkg (572 KB) Lene Fischer, 2018-12-11 07:42 AM

History

#1 Updated by Nyall Dawson over 1 year ago

  • Status changed from Open to Feedback

Error message was something about OGR..

Have you got the actual error?

Can you share the original file and steps to reproduce?

#2 Updated by Lene Fischer over 1 year ago

I have tried to replicate the error message but it does not appear any longer.

#3 Updated by Lene Fischer over 1 year ago

When I close the file and re-open I get a message : WARNING CRS was undefined : defaulting to CRS EPSG:25832 - ETRS89 / UTM zone 32N

#4 Updated by Jérôme Guélat over 1 year ago

I'm experiencing the same bug... Suprisingly this doesn't happen if you save the layer to a new GeoPackage and then delete one column in the new GeoPackage.

The problem is also occurring with QGIS 2.18.26 (and QGIS is even crashing).

I've also checked on my Mac and it works perfectly, using the same GDAL version as the Windows version (2.3.2).

#5 Updated by Jérôme Guélat over 1 year ago

Exporting the whole GeoPackage with ogr2ogr using the following command also helps: ogr2ogr newgeopackage.gpkg skovkort.gpkg

I've first thought the problem was related to the GeoPackage version since I've only had problems with GeoPackages using version 1.0 (and not with version 1.2 which is now the default when using GDAL 2.3.2) but this doesn't seem to be the cause since the problem is also solved if I force ogr2ogr to export in version 1.0 (ogr2ogr newgeopackage.gpkg skovkort.gpkg -dsco VERSION=1.0).

#6 Updated by Jérôme Guélat over 1 year ago

It is fortunately possible to recover the data... Open the GeoPackage with an SQLite editor (e.g., https://sqlitebrowser.org) and look for the table with the removed column.

If your table/layer was called foo, then look for a table named foo_ogr_tmp. Rename this table using the original name (foo). QGIS will again be able to open the GeoPackage layer.

#7 Updated by Giovanni Manghi over 1 year ago

Lene Fischer wrote:

I have tried to replicate the error message but it does not appear any longer.

so you can't replicate the problem in a consistent manner?

#8 Updated by Lene Fischer over 1 year ago

I can easily replicate the problem - but not the error message. I think that it initially was a multiple session of deleting and creating fields that caused the OGR error message.

#9 Updated by Giovanni Manghi over 1 year ago

  • Affected QGIS version changed from 3.4.2 to 3.5(master)
  • Status changed from Feedback to Open

Confirmed on master on clean Windows 10 and QGIS installations. This is VERY bad.

#10 Updated by Jürgen Fischer over 1 year ago

  • Description updated (diff)

#11 Updated by Alessandro Pasotti over 1 year ago

This is probably an upstream bug in OGR/GDAL or the underlying sqlite libraries.

I checked with a debugger on windows and QGIS is not doing anything strange: it calls the right OGR function with the right parameters, the call returns no errors but the table is completely removed from the DB (that's why QGIS asks for the CRS when re-opened).

#12 Updated by Jérôme Guélat over 1 year ago

You're right... I think I remember that SQLite was updated with QGIS 3.4.2.

Using OSGeo4W I downgraded SQLite from 3.25 to 3.17 and the bug is gone.

#13 Updated by Alessandro Pasotti over 1 year ago

  • Resolution set to up/downstream

@Jérôme, I can confirm: downgrading sqlite to 3.17 solved the issue, I'm not sure what you should do now, perhaps file a bug report for sqlite?

#14 Updated by Jürgen Fischer over 1 year ago

Alessandro Pasotti wrote:

@Jérôme, I can confirm: downgrading sqlite to 3.17 solved the issue, I'm not sure what you should do now, perhaps file a bug report for sqlite?

Or try 3.26.0-1

#15 Updated by Jérôme Guélat over 1 year ago

Unfortunately the bug is also occurring with this version.

#16 Updated by Even Rouault over 1 year ago

OK, I believe this is the issue that was fixed per https://github.com/OSGeo/gdal/commit/556738b51b0b7b837544968199d359395a71e2ad in GDAL 2.4.0
The GeoPackage specification had an error in the formulation of a trigger, but before SQLite 3.25, SQLite was not sensitive to this error.
So GeoPackage generated with GDAL 2.4.0 should now work fine, but files generated by older versions will cause the issue.

#17 Updated by Jérôme Guélat over 1 year ago

Thanks for having a look at it.

Indeed this seeems to be the problem... If I correct the trigger in the "older" GeoPackages, then the issue doesn't occur. Would it be possible to correct this trigger automatically when the incorrect one is detected in a GeoPackage used in QGIS?

#18 Updated by Giovanni Manghi over 1 year ago

  • Status changed from Open to Closed

Jérôme Guélat wrote:

Thanks for having a look at it.

Indeed this seeems to be the problem... If I correct the trigger in the "older" GeoPackages, then the issue doesn't occur. Would it be possible to correct this trigger automatically when the incorrect one is detected in a GeoPackage used in QGIS?

I think this should be filed as feature request.

Also available in: Atom PDF