Django's Spatialite backend throws errors with Spatialite 5.0.1

[Nikolas N]

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.

[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?

[Natalia Bidart]

David, would you have any advice on this report? Can you reproduce as well? Thank you!

[Natalia Bidart]

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

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