Test suite fails with sqlite 3.36 and spatialite 5.

[David Smith]

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 ​line back to the same behaviour as spatialite 4x then the suite will pass.

I note that the ​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.

[Mariusz Felisiak]

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 ​recommended way for SpatiaLite 5.

Can you try to report this on their bug tracker or ​the mailing list?

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

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

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

[>SunyataZero on GitLab<]

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

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

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

[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 possible with the default packages, 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

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

[Alex Tomkins]

Replying to 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 to avoid running InitSpatialMetaDataFull:

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

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

[Nikolas N]

I am updating a project to Django 4.2 and I'm having trouble adding this 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 django;django.db.connection.cursor().execute('SELECT InitSpatialMetaData(1);')";

Which fails with the error:

django.db.utils.OperationalError: connection is bad: No such file or directory
	Is the server running locally and accepting connections on that socket?

[Sarah Boyce]

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 ​user support channels. I'd recommend using the forum 👍

[Nikolas N]

The larger problem here seems specific to the combination of Django 4.2 and Spatialite 5.0.1, not really related to my CI setup, but thank you - I will use the Django forum.

[Nikolas N]

For anyone following this issue, this patch fixes things in my scenario: ​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.

[Natalia Bidart]

#35387 is a dupe of this one.