[Django] #35387: Django's Spatialite backend throws errors with Spatialite 5.0.1

[Django]

#35387: Django's Spatialite backend throws errors with Spatialite 5.0.1
-------------------------------------+-------------------------------------
Reporter: Nikolas N | Owner: nobody
Type: Bug | Status: new
Component: Database | Version: 5.0
layer (models, ORM) |
Severity: Normal | Keywords: spatialite
Triage Stage: | Has patch: 1
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 1
UI/UX: 0 |
-------------------------------------+-------------------------------------
Spatialite 5.0.1 contains a bug which causes the `SELECT
InitSpatialMetaDataFull(1)` statement called by django's database wrapper
to fail with the error:
{{{
django.db.utils.OperationalError: error in trigger
ISO_metadata_reference_row_id_value_insert: no such column: rowid
}}}

Although this is primarily a bug in Spatialite, it is expected for Django
to be compatible with this version of Spatialite, as it is shipping in
many current distributions, and upgrading to Spatialite 5.1 is not always
straightforward.

There is a patch for this issue here:
https://github.com/django/django/pull/18083

I was told patches aren't accepted for closed tickets
(https://code.djangoproject.com/ticket/32935), so I am opening this new
ticket, as the core issue here with Django + Spatialite 5.0.1 remains
unresolved.
--
Ticket URL: <https://code.djangoproject.com/ticket/35387>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

[Django]

#35387: Django's Spatialite backend throws errors with Spatialite 5.0.1
-------------------------------------+-------------------------------------
Reporter: Nikolas N | Owner: nobody
Type: Bug | Status: new

Component: Database layer | Version: 5.0
(models, ORM) |
Severity: Normal | Resolution:
Keywords: spatialite | Triage Stage:
| Unreviewed
Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 1 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Natalia Bidart):

Hello Nikolas, thank you for this ticket! Do you have a link to the
Spatialite bug or conversation about the issue you describe? Do we know
exactly which versions are affected? I'm a little concerned that the PR
hardcodes a single version and there may be more versions affected?
--
Ticket URL: <https://code.djangoproject.com/ticket/35387#comment:1>

[Django]

#35387: Django's Spatialite backend throws errors with Spatialite 5.0.1
-------------------------------------+-------------------------------------
Reporter: Nikolas N | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: 5.0
(models, ORM) |
Severity: Normal | Resolution:
Keywords: spatialite | Triage Stage:
| Unreviewed
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 1 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Natalia Bidart):

* cc: David Smith (added)

Comment:

David, would you have any advice on this report? Can you reproduce as
well? Thank you!
--
Ticket URL: <https://code.djangoproject.com/ticket/35387#comment:2>

[Django]

#35387: Django's Spatialite backend throws errors with Spatialite 5.0.1
-------------------------------------+-------------------------------------
Reporter: Nikolas N | Owner: nobody

Type: Bug | Status: closed

Component: Database layer | Version: 5.0
(models, ORM) |

Severity: Normal | Resolution: duplicate

Keywords: spatialite | Triage Stage:
| Unreviewed
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0

Easy pickings: 0 | UI/UX: 0

-------------------------------------+-------------------------------------
Changes (by Natalia Bidart):

* cc: David Smith (removed)
* easy: 1 => 0
* resolution: => duplicate
* status: new => closed

Comment:

Nikolas, I missed an important detail from the other Django ticket that
you linked (#32935). Thank you Sarah for pointing this out! And sorry
David for the unnecessary CC.

As per the conversation in #32935, this has been discussed and is not an
issue with Django and is instead, an issue with sqlite3 v.3.36+
compatibility with Spatialite 5.0.1 when not compiled with
`-DSQLITE_ALLOW_ROWID_IN_VIEW` option.

Django is using `InitSpatialMetaDataFull` in 5.0 [https://www.gaia-
gis.it/fossil/libspatialite/info/2ab2aeb5d1c9a0458b64b8afd4be4ce7527b059c
as per the docs for 5.0], specifically

Please remember that now in SpatiaLite-5.0.0 InitSpatialMetaDataFull
represents the preferred method for creating all metadata tables required
by this version.
InitSpatialMetaData is still maintained so to not break historical
compatibility, but should no longer be used.

Some workarounds have been discussed, including downgrading to Spatialite
4.2, upgrade to 5.1, or recomplie sqlite with the flag above.
This is a good discussion on the issue: https://groups.google.com/g
/spatialite-users/c/SnNZt4AGm_o

It seems that the people most impacted are using Ubuntu 22.04 which has
this troubling combination of sqlite and spatialite but Ubuntu 24.04 will
be released soon in a week and uses spatialite 5.1.

In summary, Django would not be incorporating a workaround such as the one
proposed. We might be able to accept a patch that resolves this
compatibility issue ONLY if that doesn't go against official doc
recommendations. But also please consider that any patch for Django would
be targeting Django 5.1 to be released (at the earliest) in August. I
don't think anyone should rely on a patch to Django to solve this issue
for them.
--
Ticket URL: <https://code.djangoproject.com/ticket/35387#comment:3>

[Django]

#35387: Django's Spatialite backend throws errors with Spatialite 5.0.1
-------------------------------------+-------------------------------------
Reporter: Nikolas N | Owner: nobody
Type: Bug | Status: closed
Component: Database layer | Version: 5.0
(models, ORM) |
Severity: Normal | Resolution: duplicate
Keywords: spatialite | Triage Stage:
| Unreviewed
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Nikolas N):

Thank you for all this in-depth info Natalia. I can't find specific info
about this Spatialite 5.0.1 bug - maybe it happens with 5.0 too, I am
really not familiar with Spatialite. I agree the patch matching the 5.0.1
version string is not ideal. For now I think I am able to work around this
by staying on Spatialite 4.x while waiting for 5.1 on Ubuntu 24.04.
--
Ticket URL: <https://code.djangoproject.com/ticket/35387#comment:4>