"no such column: rowid" error with Django 4.2 and Spatialite 5.0.1

nikolas · April 17, 2024, 4:47pm

I am upgrading an application from Django 3.2 to 4.2, which uses postgis in production, and django.contrib.gis.db.backends.spatialite when running unit tests.

Running the tests on this application, I’m getting this error:

django.db.utils.OperationalError: error in trigger ISO_metadata_reference_row_id_value_insert: no such column: rowid
make: *** [django.mk:49: test] Error 1

This problem is documented in this ticket, and seems to be an issue with using Django 4.2 and Spatialite 5: #32935 (Test suite fails with sqlite 3.36 and spatialite 5.) – Django

One of the workarounds looks like executing this command in the spatialite database:

./manage.py shell -c "import django;django.db.connection.cursor().execute('SELECT InitSpatialMetaData(1);')";

However, after I run this command, I am still getting the no such column: rowid error. Has anyone else run into this? I am using Django 4.2.11, sqlite 3.42.0, libspatialite 5.0.1, python 3.12.2 on Fedora Linux.

sarahboyce · April 18, 2024, 6:00am

I think the easiest option available is in ⬆ django 4.2 by nikolas · Pull Request #2976 · ccnmtl/footprints · GitHub .github/workflows/build-and-test.yml, do not upgrade from ubuntu-20.04 to ubuntu-22.04.
Ubuntu 20.04 is using spatialite 4.3 when installing libsqlite3-mod-spatialite.

More fiddly options include:

in the action, upgrade to spatialite 5.1
in the action, recompile SQLite with -DSQLITE_ALLOW_ROWID_IN_VIEW

When Ubuntu 24.04 is released April 25th, libsqlite3-mod-spatialite will install spatialite 5.1 libsqlite3-mod-spatialite : Noble (24.04) : Ubuntu (launchpad.net)

smithdc1 · April 18, 2024, 6:15am

Hi @nikolas

Here’s a recent PR by @adamchainz which added a workaround for this issue. Maybe that could be used as inspiration for your project.

github.com

makinacorpus/django-geojson/blob/8ec70ccf966ca580621245647dd9de98bbecb80f/quicktest.py#L72


      
                  'django.contrib.messages.middleware.MessageMiddleware',
                  'django.contrib.sessions.middleware.SessionMiddleware',
              ],
              SECRET_KEY="not-secret",
              # django.VERSION >= (3, 2)
              DEFAULT_AUTO_FIELD="django.db.models.BigAutoField",
          )
          
          django.setup()
          
          # Workaround for incompatibility between SQLite 3.36+ and SpatiaLite 5,
          # as used on GitHub Actions. This change monkey patches
          # prepare_database() to avoid a call to InitSpatialMetaDataFull(). See:
          # https://code.djangoproject.com/ticket/32935
          # https://groups.google.com/g/spatialite-users/c/SnNZt4AGm_o
          from django.contrib.gis.db.backends.spatialite.base import DatabaseWrapper
          
          def prepare_database(self):
              super(DatabaseWrapper, self).prepare_database()
              with self.cursor() as cursor:
                  cursor.execute("PRAGMA table_info(geometry_columns);")

nikolas · April 18, 2024, 11:12am

Thank you for these options - Our production systems are running Spatialite 5.0.1 so I would prefer a solution that works with that. If I can’t get that working I suppose I will figure out how to upgrade everything to Spatialite 5.1.

It would be much simpler to just accept the Django patch I submitted, though (Fixed #32935 -- Call InitSpatialMetaData(1) on spatialite 5.0.1. by nikolas · Pull Request #18083 · django/django · GitHub). I think I will most likely just create a custom database wrapper based on that patch.

Topic		Replies	Views
Can not execute tests with spatialite as database backend Using Django	1	554	November 22, 2020
Django Raw queryerror Using Django	5	1108	June 16, 2021
"django.db.utils.OperationalError: no such function: lwgeom_version" Django Version 3.2.16, Spatialite Version 4.2.0 on Windows 10 Mystery Errors	0	762	December 16, 2022
MakeMigrations with SpatiaLite and GeoDjango Using the ORM	4	33	July 19, 2024
Error integrating with MySQL view: "Unknown column in 'field list'" Using the ORM	6	6758	September 30, 2023

"no such column: rowid" error with Django 4.2 and Spatialite 5.0.1

Related topics