SpatiaLite 4: added attributes disappear

[qgib]

Author Name: Matthias Kuhn (@m-kuhn)
Original Redmine Issue: 8923
Affected QGIS version: 2.2.0
Redmine category:data_provider/spatialite

---

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 (Regis Haubourg)
• Screenshot_20170306_133951.png (Giovanni Manghi)

---

Related issue(s): #17524 (relates), #18799 (duplicates)
Redmine related issue(s): 8834, 10381

---

[qgib]

Author Name: Josef Källgården (Josef Källgården)

---

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

[qgib]

Author Name: Matthias Kuhn (@m-kuhn)

---

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

[qgib]

Author Name: Josef Källgården (Josef Källgården)

---

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.)

[qgib]

Author Name: Alexander Bruy (@alexbruy)

---

Also confirmed on QGIS 2.2 under Windows and Linux

---

• operating_system was changed from Linux/Fedora to all
• os_version was changed from 19 to

[qgib]

Author Name: Matthias Kuhn (@m-kuhn)

---

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.

[qgib]

Author Name: Alexander Bruy (@alexbruy)

---

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

[qgib]

Author Name: Matthias Kuhn (@m-kuhn)

---

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

[qgib]

Author Name: René-Luc ReLuc (@rldhont)

---

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 ?

[qgib]

Author Name: Matthias Kuhn (@m-kuhn)

---

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

[qgib]

Author Name: Matthias Kuhn (@m-kuhn)

---

Created a pull request:

#1406

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

[qgib]

Author Name: Matthias Kuhn (@m-kuhn)

---

#1406 has been merged. Please verify with different spatialite versions.

---

• version was changed from 2.0.1 to 2.2.0

[qgib]

Author Name: Regis Haubourg (@haubourg)

---

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

[qgib]

Author Name: Regis Haubourg (@haubourg)

---

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..

---

• 7486 was configured as test_splite_8923.zip

---

• test_splite_8923.zip (Regis Haubourg)

[qgib]

Author Name: Regis Haubourg (@haubourg)

---

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.

[qgib]

Author Name: Jürgen Fischer (@jef-n)

---

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?

[qgib]

Author Name: Regis Haubourg (@haubourg)

---

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)

[qgib]

Author Name: Regis Haubourg (@haubourg)

---

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

[qgib]

Author Name: Jürgen Fischer (@jef-n)

---

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.

[qgib]

Author Name: Matthias Kuhn (@m-kuhn)

---

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)

[qgib]

Author Name: Regis Haubourg (@haubourg)

---

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

[qgib]

Author Name: Matthias Kuhn (@m-kuhn)

---

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

See #17595 (comment) for how to fix the file.
Can you try to fix the DB and then check if you can reproduce the bug afterwards?

[qgib]

Author Name: Hugo Mercier (@mhugo)

---

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() ?

[qgib]

Author Name: Matthias Kuhn (@m-kuhn)

---

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.

[qgib]

Author Name: Micha Silver (@micha-silver)

---

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.

[qgib]

Author Name: Jukka Rahkonen (Jukka Rahkonen)

---

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

[qgib]

Author Name: Giovanni Manghi (@gioman)

---

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?

---

• 10800 was configured as Screenshot_20170306_133951.png
• status_id was changed from Open to Feedback
• priority_id was changed from High to Normal
• fixed_version_id removed Future Release - High Priority
• operating_system was changed from all to

---

• Screenshot_20170306_133951.png (Giovanni Manghi)

[qgib]

Author Name: Micha Silver (@micha-silver)

---

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

---

• operating_system was changed from to Linux

[qgib]

Author Name: Giovanni Manghi (@gioman)

---

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.

---

• description was changed from 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 commit: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. to 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 commit: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.

• status_id was changed from Feedback to Closed
• resolution was changed from to not reproducable