Bug report #8923

SpatiaLite 4: added attributes disappear

Added by Matthias Kuhn about 6 years ago. Updated over 2 years ago.

Status:Closed
Priority:Normal
Assignee:-
Category:Data Provider/SpatiaLite
Affected QGIS version:2.2.0 Regression?:No
Operating System:Linux Easy fix?:No
Pull Request or Patch supplied:No Resolution:not reproducable
Crashes QGIS or corrupts data:No Copied to github as #:17595

Description

How to reproduce:
  • Add a spatialite layer (e.g. OSM import or create a new one)
  • Turn on editing
  • Open the field calculator
  • Add a new column. ( I took: Data type: double, expression: "randf(5,10)" )
  • Turn off editing (Save changes)
  • Result: New column shown in the attribute table
  • Remove the layer from the project
  • Re-load the layer into the project
  • Result: New column disappeared

Checking in the sqlite data itself, the column is actually added and the expression data is present.

Expected behavior:
The newly added column should be visible

Note:
The problems seems to be the table geometry_columns_field_infos which does not contain the new column, but the spatialite provider loads the information from there. I think, that it should be updated by a trigger, but my spatialite knowledge ends pretty much there.

Caveat:
Make sure you have a more recent build than 249526b (Oct, 20) to be able to add columns

Can somebody verify this behavior. I want to be sure it is not a local issue before reporting it upstream.

test_splite_8923.zip (683 KB) Regis Haubourg, 2014-06-23 01:31 AM

Screenshot_20170306_133951.png (674 KB) Giovanni Manghi, 2017-03-07 03:13 AM


Related issues

Related to QGIS Application - Bug report #8834: Style for layer spatialite Closed 2013-10-14
Duplicated by QGIS Application - Bug report #10381: Attribute table does not show new columns of spatialite l... Closed 2014-05-28

Associated revisions

Revision 0d7204cd
Added by Matthias Kuhn over 5 years ago

[spatialite] Update layer statistics after adding new columns
Fix #8923

History

#1 Updated by Josef Källgården about 6 years ago

I tested using qgis 2.0.1 and 2.1 on windows 7 and also qgis 2.0.1 in ubuntu 12.04.
I could not reproduce this issue. The new column is shown after re-adding to qgis.
/Josef

#2 Updated by Matthias Kuhn about 6 years ago

Thank you for testing Josef,

I opened a bug upstream and there seems to be a problem indeed (I'm working on it). I don't know why it works in your case. Are you sure you have spatialite 4?

https://groups.google.com/forum/#!topic/spatialite-users/FOq1DPRDkJw

#3 Updated by Josef Källgården about 6 years ago

Yes, I am definitely sure about using splite 4.1.1.
(And I am glad you are working on the problem although I could not verify this specific issue.)

#4 Updated by Alexander Bruy over 5 years ago

  • Operating System changed from Linux/Fedora to all
  • OS version deleted (19)

Also confirmed on QGIS 2.2 under Windows and Linux

#5 Updated by Matthias Kuhn over 5 years ago

This is a tricky issue. Just spent a good amount of time to reproduce it. It seems not to affect every file and it seems also to work always for the first added column and only affect subsequent columns.

As a workaround, I think the following SQL commands executed should work:

UPDATE geometry_columns_statistics set last_verified = 0;
SELECT UpdateLayerStatistics('geometry_table_name');

I would be happy to get a feedback if this workaround works for others as well. If yes, it could be implemented into the spatialite provider.

#6 Updated by Alexander Bruy over 5 years ago

Just tested proposed workaround on several databases and it works fine for me.

#7 Updated by Matthias Kuhn over 5 years ago

I've got a preliminary implementation containing a workaround for spatialite <= 4.1.1 and a proper implementation for spatialite > 4.1.1.

However it seems it suffers from a flaw that any new column is treated as TEXT, so no INTEGER, FLOAT or BLOB support at the moment...

https://github.com/m-kuhn/QGIS/tree/spatialite-fix

#8 Updated by René-Luc ReLuc over 5 years ago

I confirme the bug on Ubuntu and Windows.
The workaround works fine.

Some one is reviewing the proposed patch ?
Can we have it backported to release_2_2 ?

#9 Updated by Matthias Kuhn over 5 years ago

I think that (under certain circumstances?) spatialite determines the column type from the content. That means, as long as there is no content it cannot properly determine the column data type (and falls back to TEXT). We normally just create a new column which is then initialized with NULL values and only filled with data afterwards, so QGIS loads the new column while there are still only NULL values in there.

ToDo:
  • Verify this hypothesis
  • Fix it upstream
  • Implement a workaround for affected versions

#10 Updated by Matthias Kuhn over 5 years ago

Created a pull request:

https://github.com/qgis/QGIS/pull/1406

While the patch definitely is not perfect, it is the best we have at the moment and it shouldn't make things worse.

#11 Updated by Matthias Kuhn over 5 years ago

  • Affected QGIS version changed from 2.0.1 to 2.2.0

https://github.com/qgis/QGIS/pull/1406 has been merged. Please verify with different spatialite versions.

#12 Updated by Regis Haubourg over 5 years ago

Hi,
I tested the following with osgeo4w this morning d62a498 , with many problems:

- convert a postgis table in spatialite 4.1 via "save as"
- add int field, save, populate it with an expression (random): All lines are not populated, even if no selection was pending. Impossible to me to populate those cells by hand
- add a text fied, populate it by expression: other cells can't be filled and stay NULL
- select null lines, and delete them > QGIS Hangs...

Spatialite is currently not usable.. Will keep testing.

Régis

#13 Updated by Regis Haubourg over 5 years ago

Joining the sample sqlite file, I suspect something wrong inside sqlite table, since oids from postgis are the same on the lines that I can't fill with values..

#14 Updated by Regis Haubourg over 5 years ago

Previous bug is probably only for our postgis 9.0 instance that is having serious trouble in system tables and oid's.
Please do not take my previous report into account.

#15 Updated by Jürgen Fischer over 5 years ago

Regis Haubourg wrote:

- convert a postgis table in spatialite 4.1 via "save as"

Side note: "Save as" use the OGR to produce the spatialite database, while d&d in the browser would use the spatialite provider to create table table - which also is used to access the database later. Does the latter behave the same?

#16 Updated by Regis Haubourg over 5 years ago

with dbmanager d&d, issue of null uneditable values is not reproduced. Still my issue with source oid's remains, since now see negative oids ! (my postgres instance is dying, migration urges now. postgis 2 and pg 9.3 is not affected)

#17 Updated by Regis Haubourg over 5 years ago

with file datasources, I could successfully add several fields and fill them. Patch seems ok here.
Régis

#18 Updated by Jürgen Fischer over 5 years ago

Regis Haubourg wrote:

with dbmanager d&d, issue of null uneditable values is not reproduced. Still my issue with source oid's remains, since now see negative oids ! (my postgres instance is dying, migration urges now. postgis 2 and pg 9.3 is not affected)

Another side note: I meant d&d the browser and not in dbmanager - not sure if dbmanager uses the spatialite provider or has yet another way to create databases, tables and/or insert into sl databases.

#19 Updated by Matthias Kuhn over 5 years ago

Régis, thank you for testing.

  • If you reopen the project/readd the layer in a new project, does still everything work properly?
  • If the type is not TEXT, does it work? (E.g. field calculator to fill a newly created INT column with rand() values)

#20 Updated by Regis Haubourg over 5 years ago

Reopening , readding seems ok with int and decimal, reading sqlite with ogr file or database connection gui.

I have one error when creating a new int or decimal field, and trying to save before having it filled with data. The error message is strange, it seems to get the wrong field (here field 58 when i have 70 fields.)

Commit errors:
  SUCCESS: 1 attribute(s) added.
  ERROR: field with index 58 is not the same!
  Provider: spatialite
  Storage: SQLite database with SpatiaLite extension
  expected field: name=vol_gpt_soutien_etiage type=QString typeName=TEXT len=0 precision=0
  retrieved field: name=vol_gpt_soutien_etiage type=double typeName=FLOAT len=0 precision=0

#21 Updated by Matthias Kuhn over 5 years ago

May that be leftovers of experiments performed with QGIS without this patch added?

See #8923-5 for how to fix the file.
Can you try to fix the DB and then check if you can reproduce the bug afterwards?

#22 Updated by Hugo Mercier almost 5 years ago

Hi,
Thanks for the workaround.

There is still a problem when loading a corrupted sqlite file right ? (I stumbled upon this when trying to add fields to an open sqlite file from a plugin).
In the loadFieldsAbstractInterface function (spatialite provider), if I am correct the statistics table is only used to get name and type of fields, couldn't it be safely replaced by PRAGMA table_info() ?

#23 Updated by Matthias Kuhn almost 5 years ago

In loadFieldsAbstractInterface there is a PRAGMA table_info() call and a gaiaVectorLayerPtr is checked.
I think the second one is initialized by the spatialite driver. With information from the statistics table. This seems to be a conceptual decision.

You can refer to this discussion here https://groups.google.com/forum/#!topic/spatialite-users/FOq1DPRDkJw for some more information.

#24 Updated by Micha Silver about 4 years ago

Matthias Kuhn wrote:

I've got a preliminary implementation containing a workaround for spatialite <= 4.1.1 and a proper implementation for spatialite > 4.1.1.

However it seems it suffers from a flaw that any new column is treated as TEXT, so no INTEGER, FLOAT or BLOB support at the moment...

https://github.com/m-kuhn/QGIS/tree/spatialite-fix

I can verify that the above issue, new columns disappearing in QGIS, is still a problem in 2.12. New TEXT columns seem to appear OK, but new INTEGER do not even after Matthias' workaround.

#25 Updated by Jukka Rahkonen about 4 years ago

Read this discussion from the spatialite-users forum with a proposed way to fix it properly.
https://groups.google.com/forum/#!topic/spatialite-users/uGDZr1TCTn8

#26 Updated by Giovanni Manghi over 2 years ago

  • File Screenshot_20170306_133951.png added
  • Status changed from Open to Feedback
  • Priority changed from High to Normal
  • Target version deleted (Future Release - High Priority)
  • Operating System deleted (all)

Matthias, this is a lengthy thread and I have only tested after the original description.

On 2.18.4 I can't see anymore the issue you described in detail.

What I still see is that if I repeat the operations in order to add other columns, then on save messages like

Errors: SUCCESS: 1 attribute(s) added.
ERROR: field with index 2 is not the same!
Provider: spatialite
Storage: SQLite database with SpatiaLite extension
expected field: name=dede type=QString typeName=TEXT len=0 precision=0
retrieved field: name=dede type=double typeName=float len=0 precision=0

pop up. every time, but this does not stop the column to be saved correctly.

What is the status of this issue on recent qgis releases as 2.18.4?

#27 Updated by Micha Silver over 2 years ago

  • Operating System set to Linux

I also do not see this problem any longer (Linux Mint with QGIS 2.18.4)

#28 Updated by Giovanni Manghi over 2 years ago

  • Description updated (diff)
  • Status changed from Feedback to Closed
  • Resolution set to not reproducable

Giovanni Manghi wrote:

Matthias, this is a lengthy thread and I have only tested after the original description.

On 2.18.4 I can't see anymore the issue you described in detail.

What I still see is that if I repeat the operations in order to add other columns, then on save messages like

Errors: SUCCESS: 1 attribute(s) added.
ERROR: field with index 2 is not the same!
Provider: spatialite
Storage: SQLite database with SpatiaLite extension
expected field: name=dede type=QString typeName=TEXT len=0 precision=0
retrieved field: name=dede type=double typeName=float len=0 precision=0

pop up. every time, but this does not stop the column to be saved correctly.

What is the status of this issue on recent qgis releases as 2.18.4?

both the original issue and the one I reported here above seems gone in recent qgis releases.

Also available in: Atom PDF