[Qgis-user] Geopackage file data loss issue

[Alessandro Pasotti]

On Wed, Oct 14, 2020 at 8:51 AM Patrick Dunford <enzedrailmaps at gmail.com> wrote:
>
> 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.
>
>
>

Hi Patrick,

I would recommend you to file an issue on the QGIS bug tracker, please
attach a small project + dataset and the step-by-step procedure to
trigger the issue.

Because you are reporting several maybe-related problems, please file
a separate issue for each of them.

Kind regards.


-- 
Alessandro Pasotti
QCooperative:  www.qcooperative.net
ItOpen:   www.itopen.it