[Qgis-user] Geopackage file data loss issue

[Patrick Dunford]

Good day

In most of my file-based projects I have converted all the shapefiles 
into geopackages as this is supposed to be better these days and I am 
sure with a database based format there should be significant overall 
benefits of stability etc of the data stored within these layers.

However there does seem to be a major problem with Qgis in the way it 
handles errors from rejecting a change in the database table structure, 
specifically adding and removing columns. This appears to consistently 
result in all of the records of the table disappearing from view into 
temporary files associated with the database, from which they cannot 
actually be retrieved by the user.

In the case of the layer shown in the attached picture, this has SHM and 
WAL files open that, according to the Sqlite specification, should 
contain unposted data from the table, but the end user has no way of 
forcing this data to be committed in this situation in which, in this 
case, an alter table or similar type of operation was rejected by the 
Sqlite library. Leaving 181 features in what appears to be a temporary 
table within the database and rendering the main data table (the first 
one in the list) in practice having no rows in it.

There appear to be two issues: 1. the rejected data processing operation 
on the table, 2. the error recovery coded into Qgis when a situation 
like this comes about, in this case I have cancelled the post (save) 
operation on the geopackage, this should have rolled back the database 
to its previous state prior to attempting the data processing operation, 
but instead of this roll back taking place the table is left in this 
inconsistent state from which the user is unable to retrieve their data.

Specifically (1)

a. Qgis will treat a table's field names as case sensitive so that 
styles that apply to a field that are copied between otherwise similar 
tables will not apply to the field in the table they are copied to if 
that field has one letter that is different case from the original. This 
behaviour is not consistent with the Sqlite data engine behaviour which 
does not distinguish between case sensitive names of fields so that 
attempting to rename the field will be rejected by the Sqlite layer 
resulting in potential data loss in the table due to Qgis's failure to 
handle the error situation properly.

b. I have seen a number of situations where attempting just to delete 
some columns out of a table will be rejected by the Sqlite layer for 
unknown reason, resulting in the same problem.



-------------- next part --------------
A non-text attachment was scrubbed...
Name: Screenshot_2020-10-14_19-33-23.png
Type: image/png
Size: 32554 bytes
Desc: not available
URL: <http://lists.osgeo.org/pipermail/qgis-user/attachments/20201014/3a3a662b/attachment.png>