[Django] #32935: Test suite fails with sqlite 3.36 and spatialite 5.
Django
-----------------------------------------+------------------------
Reporter: David Smith | Owner: nobody
Type: Uncategorized | Status: new
Component: Uncategorized | Version: 3.2
Severity: Normal | Keywords:
Triage Stage: Unreviewed | Has patch: 0
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-----------------------------------------+------------------------
When looking at support for GDAL3.3 I was having issues getting the test
suite to pass. The table below shows the scenarios I tried, I was able to
get the suite to pass with spatialite 4.3 but unable to get it to pass
with 5.0 until I dropped the sqlite version.
||= Python =||= sqlite =|| spatialite || GDAL || GEOS || Proj || Tests ||
|| 3.8.10 || 3.36.0 || 4.3.0a || 3.1.2 || 3.8.1 || 7.1.0 || PASS ||
|| 3.9.6 || 3.36.0 || 5.0.1 || 3.2.0 || 3.8.1 || 7.1.1 || FAIL ||
|| 3.9.6 || 3.36.0 || 5.0.1 || 3.3.1 || 3.9.1 || 8.0.1 || FAIL ||
|| 3.8.10 || 3.36.0 || 5.0.1 || 3.3.0 || 3.9.1 || 8.0.1 || FAIL ||
|| 3.9.6 || 3.36.0 || 5.0.1 || 3.2.2 || 3.9.1 || 8.0.0 || FAIL ||
|| 3.8.10 || 3.36.0 || 5.0.1 || 3.2.2 || 3.9.1 || 8.0.0 || FAIL ||
|| 3.9.2 || 3.35.0 || 5.0.1 || 3.2.2 || 3.9.1 || 8.0.0 || PASS ||
The tests were failing not for a GIS test, but on databse setup. For
exampe I had the same failure when runing the tests for `admin_inlines`.
The tests fail when setting up the database with this error.
{{{
File "/workspaces/django/django/db/backends/sqlite3/base.py", line 417, in
execute
return Database.Cursor.execute(self, query, params)
django.db.utils.OperationalError: error in trigger
ISO_metadata_reference_row_id_value_insert: no such column: rowid
}}}
At this point of failure `query` is `ALTER TABLE "new__django_admin_log"
RENAME TO "django_admin_log"`
I found that if I changed this
[https://github.com/django/django/blame/a56531ab1b772ee4ae94dd805f168761887354c6/django/contrib/gis/db/backends/spatialite/base.py#L77
line] back to the same behaviour as spatialite 4x then the suite will
pass.
I note that the [https://www.sqlite.org/draft/releaselog/current.html
release notes] for sqlite 3.36 say the following, which I'm assuming is
the cause of the issue here.
> An error is raised on any attempt to access the rowid of a VIEW or
subquery.
--
Ticket URL: <https://code.djangoproject.com/ticket/32935>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
Reporter: David Smith | Owner: nobody
Component: GIS | Version: 3.2
Severity: Normal | Resolution: needsinfo
Keywords: | Triage Stage: Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by Mariusz Felisiak):
* cc: Claude Paroz (added)
* resolution: => needsinfo
* status: new => closed
* component: Uncategorized => GIS
* type: Uncategorized => Bug
Comment:
Thanks for the report. I can reproduce this issue, I also confirmed that
SQLite 3.36 compiled with `-DSQLITE_ALLOW_ROWID_IN_VIEW` works fine.
However I don't think that Django is at fault because calling
`InitSpatialMetaDataFull(1)` is the [https://www.gaia-
gis.it/fossil/libspatialite/wiki?name=PROJ.6 recommended way] for
SpatiaLite 5.
Can you try to report this on their bug tracker or
[https://groups.google.com/g/spatialite-users the mailing list]?
--
Ticket URL: <https://code.djangoproject.com/ticket/32935#comment:1>
Django
Reporter: David Smith | Owner: nobody
Component: GIS | Version: 3.2
Severity: Normal | Resolution: needsinfo
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Comment (by David Smith):
Thank you for your guidance here. :-)
I posted on the mailing list and received a response from Sandro.
https://groups.google.com/g/spatialite-users/c/SnNZt4AGm_o
It seems to me that this confirms it is a bug in Spatialite and/or
sqlite3.36.
Do you think it's worth adding a note to this page in the
[https://docs.djangoproject.com/en/3.2/ref/contrib/gis/install/geolibs/
docs]?
--
Ticket URL: <https://code.djangoproject.com/ticket/32935#comment:2>
Django
Reporter: David Smith | Owner: nobody
Component: GIS | Version: 3.2
Severity: Normal | Resolution: needsinfo
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Comment (by Mariusz Felisiak):
Thanks.
> Do you think it's worth adding a note to this page in the docs?
I don't think it's necessary. This ticket should be enough.
--
Ticket URL: <https://code.djangoproject.com/ticket/32935#comment:3>
Django
Reporter: David Smith | Owner: nobody
Component: GIS | Version: 3.2
Severity: Normal | Resolution: needsinfo
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Comment (by Federico Capoano):
Thanks for opening this ticket and linking useful information.
I found this while adding trying to deploy OpenWISP using Django 4.0 on
Ubuntu 22.04, but I have seen that the same application runs fine with
Django 3.2 on Ubuntu 22.04, so I wanted to ask you: are you're sure it's
not a bug on the Django side? And if yes, why?
Is it because the latest version of GeoDjango ships some new feature which
wasn't available in Django 3.2 and now this feature is broken on
Spatialite 5?
Thanks in advance.
--
Ticket URL: <https://code.djangoproject.com/ticket/32935#comment:4>
Django
Reporter: David Smith | Owner: nobody
Component: GIS | Version: 3.2
Severity: Normal | Resolution: needsinfo
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Comment (by SunyataZero):
Hi all, i get the error
{{{
django.db.utils.OperationalError: error in trigger
ISO_metadata_reference_row_id_value_insert: no such column: rowid
}}}
when following the GeoDjango tutorial
https://docs.djangoproject.com/en/4.1/ref/contrib/gis/tutorial/
My setup:
* Django 4.1
* Ubuntu 22.04
* sqlite 3.37.2
* libsqlite3-mod-spatialite 5.0.1-2build2
I hope this helps
--
Ticket URL: <https://code.djangoproject.com/ticket/32935#comment:5>
Django
Reporter: David Smith | Owner: nobody
Component: GIS | Version: 3.2
Severity: Normal | Resolution: needsinfo
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Comment (by sheppard):
I can confirm this is broken on Ubuntu 22.04 with the default apt library
versions. Here's another comment from spatialite-users that explains
what is going on:
https://groups.google.com/g/spatialite-users/c/5rKuVlIzwZY/m/ydEa4ir_AAAJ
I think this means that InitSpatialMetaDataFull(1) basically will not work
on Ubuntu 22.04 until the next version of libsqlite3-mod-spatialite is
released and packaged. While this isn't strictly a bug in Django, I think
it would be appropriate and helpful to fall back to InitSpatialMetaData(1)
if the "ISO_metadata_reference_row_id_value_insert" error is detected.
Can this ticket be reopened?
In the meantime, here's a one-liner that can be executed before running
./manage.py migrate on Ubuntu 22.04.
{{{
./manage.py shell -c "import
django;django.db.connection.cursor().execute('SELECT
InitSpatialMetaData(1);')";
./manage.py migrate
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/32935#comment:6>
Django
Reporter: David Smith | Owner: nobody
Component: GIS | Version: 3.2
Severity: Normal | Resolution: needsinfo
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Comment (by Mariusz Felisiak):
> Can this ticket be reopened?
As far as I'm aware, changing to the `InitSpatialMetaData()` in Django is
not an option. According to the `SpatiaLite`'s docs:
> ''"InitSpatialMetaData is still maintained so to not break historical
compatibility, but **should no longer be used**."''
--
Ticket URL: <https://code.djangoproject.com/ticket/32935#comment:7>
Django
Reporter: David Smith | Owner: nobody
Component: GIS | Version: 3.2
Severity: Normal | Resolution: needsinfo
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Comment (by S. Andrew Sheppard):
Yes, InitSpatialMetaDataFull would remain as the preferred option. The
fallback would be only for systems like Ubuntu 22.04 where using
InitSpatialMetaDataFull per the docs is not an option, because the
implementation in spatialite 5.0.1 (the latest released version) is
incompatible with sqlite 3.36 (and newer versions). In spite of the stern
warning on the PROJ.6 page, other parts of the documentation explain that
InitSpatialMetaDataFull and InitSpatialMetaData are highly compatible:
https://www.gaia-gis.it/fossil/libspatialite/wiki?name=Upgrading+existing
+DB-files+to+5.0.0
Basically, it seems that the 5.0 schema created by InitSpatialMetaDataFull
is the same as the 4.0 schema created by InitSpatialMetaData, but with
additional tables needed to support librasterlite2. Many GeoDjango users
will probably be fine without raster support, so falling back to
InitSpatialMetaData seems like it should be reasonable for most cases.
That said, it is fair to argue that initializing a database with Django
and spatialite 5.0 should result in a full 5.0-compatible schema.
Fortunately, it appears that the CreateMissingSystemTables method also
described in that documentation does not break, so it could be used as
part of the fallback. Perhaps something like this would be appropriate:
{{{
cursor.execute("PRAGMA table_info(geometry_columns);")
if cursor.fetchall() == []:
if self.ops.spatial_version < (5,):
cursor.execute("SELECT InitSpatialMetaData(1)")
else:
try:
cursor.execute("SELECT
InitSpatialMetaDataFull(1)")
except OperationalError as e:
if "ISO_metadata_reference_row_id_value_insert" in
e.args[0]:
# Workaround for sqlite 3.36 and spatialite
5.0.1
cursor.execute("SELECT InitSpatialMetaData(1)")
cursor.execute("SELECT
CreateMissingSystemTables(1)")
else:
raise
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/32935#comment:8>
Django
Reporter: David Smith | Owner: nobody
Component: GIS | Version: 3.2
Severity: Normal | Resolution: needsinfo
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by Jon Janzen):
* cc: Jon Janzen (added)
--
Ticket URL: <https://code.djangoproject.com/ticket/32935#comment:9>
Django
Reporter: David Smith | Owner: nobody
Component: GIS | Version: 3.2
Severity: Normal | Resolution: needsinfo
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Comment (by Federico Capoano):
I think a workaround for systems which will ship spatialite 5 by default
is needed to avoid newcomers to get stuck on this.
Moreover, managing this workaround on CI systems on github CI or travis is
painful and a huge waste of time.
Can we reopen this ticket please? Will the maintainers accept a patch?
--
Ticket URL: <https://code.djangoproject.com/ticket/32935#comment:10>
Django
Reporter: David Smith | Owner: nobody
Component: GIS | Version: 3.2
Severity: Normal | Resolution: needsinfo
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Comment (by Alex Tomkins):
Replying to [comment:8 S. Andrew Sheppard]:
> That said, it is fair to argue that initializing a database with Django
and spatialite 5.0 should result in a full 5.0-compatible schema.
Fortunately, it appears that the CreateMissingSystemTables method also
described in that documentation does not break, so it could be used as
part of the fallback. Perhaps something like this would be appropriate:
Just upgraded a project to Django 4.2, and this doesn't work for me -
running `CreateMissingSystemTables(1)` results in the same problem:
{{{
django.db.utils.OperationalError: error in trigger
ISO_metadata_reference_row_id_value_insert: no such column: rowid
}}}
The two workarounds which don't result in errors, your one liner in an
earlier post:
{{{
./manage.py shell -c "import
django;django.db.connection.cursor().execute('SELECT
InitSpatialMetaData(1);')";
}}}
Or a custom DatabaseWrapper which extends from the spatialite version:
{{{
from django.contrib.gis.db.backends.spatialite import base
class DatabaseWrapper(base.DatabaseWrapper):
def prepare_database(self):
# Workaround for https://code.djangoproject.com/ticket/32935
with self.cursor() as cursor:
cursor.execute("PRAGMA table_info(geometry_columns);")
if cursor.fetchall() == []:
cursor.execute("SELECT InitSpatialMetaData(1)")
super().prepare_database()
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/32935#comment:11>
Django
Component: GIS | Version: 3.2
Severity: Normal | Resolution: needsinfo
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Comment (by Nikolas N):
I am still seeing this issue with these workarounds, using Python 3.12.2,
Django 4.2.11 and spatialite 5.0.1.
--
Ticket URL: <https://code.djangoproject.com/ticket/32935#comment:12>
Django
Component: GIS | Version: 3.2
Severity: Normal | Resolution: needsinfo
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by Jon Janzen):
--
Ticket URL: <https://code.djangoproject.com/ticket/32935#comment:13>
Django
Component: GIS | Version: 3.2
Severity: Normal | Resolution: needsinfo
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Comment (by Nikolas N):
workaround to github actions,
can someone please advise? My pull request is here:
https://github.com/ccnmtl/footprints/pull/2976
Specifically, I've added this step in my github actions script:
{{{
# https://code.djangoproject.com/ticket/32935
- name: Spatialite 5 django workaround
run: ./manage.py shell -c "import
InitSpatialMetaData(1);')";
}}}
{{{
django.db.utils.OperationalError: connection is bad: No such file or
directory
Is the server running locally and accepting connections on that
socket?
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/32935#comment:14>
Django
Severity: Normal | Resolution:
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by Nikolas N):
* resolution: needsinfo =>
* status: closed => new
--
Ticket URL: <https://code.djangoproject.com/ticket/32935#comment:15>
Django
Component: GIS | Version: 3.2
Severity: Normal | Resolution: needsinfo
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by Sarah Boyce):
* resolution: => needsinfo
* status: new => closed
Hi Nikolas, please don't reopen tickets.
If you want help debugging your CI, the best place to get answers is to
use one our [https://docs.djangoproject.com/en/dev/faq/help/#how-do-i-do-x
-why-doesn-t-y-work-where-can-i-go-to-get-help user support channels]. I'd
recommend using the forum 👍
--
Ticket URL: <https://code.djangoproject.com/ticket/32935#comment:16>
Django
Component: GIS | Version: 3.2
Severity: Normal | Resolution: needsinfo
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Comment (by Nikolas N):
Spatialite 5, not really specific to my CI setup, but thank you - I will
use the Django forum.
--
Ticket URL: <https://code.djangoproject.com/ticket/32935#comment:17>
Django
Component: GIS | Version: 3.2
Severity: Normal | Resolution: needsinfo
Has patch: 1 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Changes (by Nikolas N):
--
Ticket URL: <https://code.djangoproject.com/ticket/32935#comment:18>
Django
Component: GIS | Version: 3.2
Severity: Normal | Resolution: needsinfo
Has patch: 1 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Comment (by Nikolas N):
https://github.com/django/django/pull/18083
Basically just calling InitSpatialMetaData() rather than
InitSpatialMetaDataFull() specifically for spatialite 5.0.1, and now tests
run successfully.
--
Ticket URL: <https://code.djangoproject.com/ticket/32935#comment:19>
Django
Component: GIS | Version: 4.2
Severity: Normal | Resolution: needsinfo
Keywords: | Triage Stage: Unreviewed
Has patch: 1 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Changes (by Nikolas N):
--
Ticket URL: <https://code.djangoproject.com/ticket/32935#comment:20>
Django
Component: GIS | Version: 4.2
Severity: Normal | Resolution: needsinfo
Has patch: 1 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Comment (by Natalia Bidart):
#35387 is a dupe of this one.
--
Ticket URL: <https://code.djangoproject.com/ticket/32935#comment:21>