Context Navigation

← Previous Ticket
Next Ticket →

#23153 closed Bug (fixed)

1.7 migrations' temporary tables cause problems with gis

Reported by:	Fanthomas90	Owned by:	Andrew Godwin
Component:	GIS	Version:	1.7-rc-2
Severity:	Release blocker	Keywords:	migrations, gis
Cc:		Triage Stage:	Accepted
Has patch:	no	Needs documentation:	no
Needs tests:	no	Patch needs improvement:	no
Easy pickings:	no	UI/UX:	no

Description

I have some very weird issue with my gis (my backend is spatialite).

My model (boozter.core.models.Location) contains a PointField called "position" and whenever I try to create an instance of it by executing Location.objects.create(...) it fails with this error:

Traceback (most recent call last):
  File "/home/thomas/Dokumente/Softwareprojekte/boozter/boozter/backend/boozter/enduser/tests.py", line 13, in test_upvote_item
    test_models = TestModels()
  File "/home/thomas/Dokumente/Softwareprojekte/boozter/boozter/backend/boozter/core/tests/tests.py", line 80, in __init__
    self.location = self.create_location('Affenstall', self.city, self.company, self.location_contact)
  File "/home/thomas/Dokumente/Softwareprojekte/boozter/boozter/backend/boozter/core/tests/tests.py", line 123, in create_location
    location = Location.objects.create(
  File "/home/thomas/Dokumente/Softwareprojekte/boozter/ENV/local/lib/python2.7/site-packages/django/db/models/manager.py", line 92, in manager_method
    return getattr(self.get_queryset(), name)(*args, **kwargs)
  File "/home/thomas/Dokumente/Softwareprojekte/boozter/ENV/local/lib/python2.7/site-packages/django/db/models/query.py", line 370, in create
    obj.save(force_insert=True, using=self.db)
  File "/home/thomas/Dokumente/Softwareprojekte/boozter/ENV/local/lib/python2.7/site-packages/django/db/models/base.py", line 590, in save
    force_update=force_update, update_fields=update_fields)
  File "/home/thomas/Dokumente/Softwareprojekte/boozter/ENV/local/lib/python2.7/site-packages/django/db/models/base.py", line 618, in save_base
    updated = self._save_table(raw, cls, force_insert, force_update, using, update_fields)
  File "/home/thomas/Dokumente/Softwareprojekte/boozter/ENV/local/lib/python2.7/site-packages/django/db/models/base.py", line 699, in _save_table
    result = self._do_insert(cls._base_manager, using, fields, update_pk, raw)
  File "/home/thomas/Dokumente/Softwareprojekte/boozter/ENV/local/lib/python2.7/site-packages/django/db/models/base.py", line 732, in _do_insert
    using=using, raw=raw)
  File "/home/thomas/Dokumente/Softwareprojekte/boozter/ENV/local/lib/python2.7/site-packages/django/db/models/manager.py", line 92, in manager_method
    return getattr(self.get_queryset(), name)(*args, **kwargs)
  File "/home/thomas/Dokumente/Softwareprojekte/boozter/ENV/local/lib/python2.7/site-packages/django/db/models/query.py", line 920, in _insert
    return query.get_compiler(using=using).execute_sql(return_id)
  File "/home/thomas/Dokumente/Softwareprojekte/boozter/ENV/local/lib/python2.7/site-packages/django/db/models/sql/compiler.py", line 919, in execute_sql
    cursor.execute(sql, params)
  File "/home/thomas/Dokumente/Softwareprojekte/boozter/ENV/local/lib/python2.7/site-packages/django/db/backends/utils.py", line 65, in execute
    return self.cursor.execute(sql, params)
  File "/home/thomas/Dokumente/Softwareprojekte/boozter/ENV/local/lib/python2.7/site-packages/django/db/utils.py", line 94, in __exit__
    six.reraise(dj_exc_type, dj_exc_value, traceback)
  File "/home/thomas/Dokumente/Softwareprojekte/boozter/ENV/local/lib/python2.7/site-packages/django/db/backends/utils.py", line 65, in execute
    return self.cursor.execute(sql, params)
  File "/home/thomas/Dokumente/Softwareprojekte/boozter/ENV/local/lib/python2.7/site-packages/django/db/backends/sqlite3/base.py", line 485, in execute
    return Database.Cursor.execute(self, query, params)
OperationalError: no such table: main.idx_core_location__new_position

So this error seemed strange for two different reasons:

It works on a legacy database which was created by south and doesn't work on a newly created one (created by migrations)
I don't have anything called "idx" anywhere in my project. I soon noticed that this is because the table which django looks for is an index table which is needed by geodjango. So nothing to worry about.
Although core.Location does exist with the field "position", I wondered why there was a "_ _new" in the missing table. As far as I can understand, this is actually a mistake!

So this "new" comes from the way django does migrations. As far as I can see, it creates temporary tables with this addition "_ _new" (see db/backends/schema.py and db/backends/sqlite3/schema.py) in order to insert fields or alter anything in the schema.
It also seems that the same must happen to the gis indexes (idx_...) at some point when creating my model in the (initial) migration process.
While doing that it seems that it stores the correct table name (idx_core_location_position - which actually exists) in a table called geometry_columns, geometry_columns_auth, geometry_columns_statistics and geometry_columns_time. But it also stores the temporary name (idx_core_location_ _new_position) in geometry_columns_auth, geometry_columns_statistics and geometry_columns_time and doesn't delete it afterwards. Also the spatialite_history table contains the temporary table name.

This seems to be an actual bug! Sadly I have no clue where these entries are created, and why deleting them manually does not solve the issue. It also does not appear for a MultiPolygonField in another model - which is quite strange. I tried to recreate the issue in a smaller test project with just one model with a PointField, but this worked just fine.
Although the stack trace above is from a test case, it also crashes when I do it on a production database which was created by the 1.7 migrations.

My (relevant) libraries are:

Django 1.7 RC2
spatialite 4.1.1
proj.4 4.8.0
geos 3.4.2
sqlite3 3.8.2

I run everything on a Ubuntu 14.04 maschine

Change History (12)

comment:1 by Fanthomas90, 10 years ago

Component:	Migrations → GIS

comment:2 by Fanthomas90, 10 years ago

I now was able to reconstruct this error in a smaller sample.
My project is structured in the following way: projectfolder "gistest" with app "sample"
My settings.py looks like this:

"""
Django settings for gistest project.

For more information on this file, see
https://docs.djangoproject.com/en/dev/topics/settings/

For the full list of settings and their values, see
https://docs.djangoproject.com/en/dev/ref/settings/
"""

# Build paths inside the project like this: os.path.join(BASE_DIR, ...)
import os
BASE_DIR = os.path.dirname(os.path.dirname(__file__))


# Quick-start development settings - unsuitable for production
# See https://docs.djangoproject.com/en/dev/howto/deployment/checklist/

# SECURITY WARNING: keep the secret key used in production secret!
SECRET_KEY = 'k+=9bmpz)2+3v9(&+e6#x+iuqk!bz@g2!vl+fc7x$2blan+r9j'

# SECURITY WARNING: don't run with debug turned on in production!
DEBUG = True

TEMPLATE_DEBUG = True

ALLOWED_HOSTS = []


# Application definition

INSTALLED_APPS = (
    'django.contrib.admin',
    'django.contrib.auth',
    'django.contrib.contenttypes',
    'django.contrib.sessions',
    'django.contrib.messages',
    'django.contrib.staticfiles',
    'django.contrib.gis',
    'gistest.sample',
)

MIDDLEWARE_CLASSES = (
    'django.contrib.sessions.middleware.SessionMiddleware',
    'django.middleware.common.CommonMiddleware',
    'django.middleware.csrf.CsrfViewMiddleware',
    'django.contrib.auth.middleware.AuthenticationMiddleware',
    'django.contrib.auth.middleware.SessionAuthenticationMiddleware',
    'django.contrib.messages.middleware.MessageMiddleware',
    'django.middleware.clickjacking.XFrameOptionsMiddleware',
)

ROOT_URLCONF = 'gistest.urls'

WSGI_APPLICATION = 'gistest.wsgi.application'


# Database
# https://docs.djangoproject.com/en/dev/ref/settings/#databases

DATABASES = {
    'default': {
        'ENGINE' : 'django.contrib.gis.db.backends.spatialite',
        'NAME'   : 'gistest.db',
        'TEST': {
            'NAME'    : '/path/to/test.db',
        }
    }
}

# Internationalization
# https://docs.djangoproject.com/en/dev/topics/i18n/

LANGUAGE_CODE = 'en-us'

TIME_ZONE = 'UTC'

USE_I18N = True

USE_L10N = True

USE_TZ = True


# Static files (CSS, JavaScript, Images)
# https://docs.djangoproject.com/en/dev/howto/static-files/

STATIC_URL = '/static/'

My sample/models.py:

#!/usr/bin/env python
# -*- coding: utf-8 -*-

from django.db import models
from django.utils.translation import ugettext_lazy as _

from django.contrib.gis.db import models as gis_models

class Target(models.Model):
    pass

class Location(models.Model):
    """
    Represents a named location.
    """

    objects = gis_models.GeoManager()

    name = models.TextField(verbose_name=_(u'name'))

    position = gis_models.PointField(verbose_name=_(u'position'))

    fk = models.ForeignKey(Target, blank=True, null=True)

    def __unicode__(self):
        return self.name

My sample/tests.py:

#!/usr/bin/env python
# -*- coding: utf-8 -*-

from django.test import TestCase

from django.contrib.auth.models import User

from gistest.sample.models import Location

class LocationTestCase(TestCase):
    def test_location(self):
        location = Location.objects.create(
            name="name",
            position='POINT(1 1)'
        )

Now after having created the initial migrations, executing manage.py test throws this exception:

======================================================================
ERROR: test_location (gistest.sample.tests.LocationTestCase)
----------------------------------------------------------------------
Traceback (most recent call last):
  File "/home/thomas/Dokumente/Softwareprojekte/boozter/django17gistest/gistest/gistest/sample/tests.py", line 14, in test_location
    position='POINT(1 1)'
  File "/home/thomas/Dokumente/Softwareprojekte/boozter/ENV/local/lib/python2.7/site-packages/django/db/models/manager.py", line 92, in manager_method
    return getattr(self.get_queryset(), name)(*args, **kwargs)
  File "/home/thomas/Dokumente/Softwareprojekte/boozter/ENV/local/lib/python2.7/site-packages/django/db/models/query.py", line 370, in create
    obj.save(force_insert=True, using=self.db)
  File "/home/thomas/Dokumente/Softwareprojekte/boozter/ENV/local/lib/python2.7/site-packages/django/db/models/base.py", line 590, in save
    force_update=force_update, update_fields=update_fields)
  File "/home/thomas/Dokumente/Softwareprojekte/boozter/ENV/local/lib/python2.7/site-packages/django/db/models/base.py", line 618, in save_base
    updated = self._save_table(raw, cls, force_insert, force_update, using, update_fields)
  File "/home/thomas/Dokumente/Softwareprojekte/boozter/ENV/local/lib/python2.7/site-packages/django/db/models/base.py", line 701, in _save_table
    update_pk = bool(meta.has_auto_field and not pk_set)
  File "/home/thomas/Dokumente/Softwareprojekte/boozter/ENV/local/lib/python2.7/site-packages/django/db/models/base.py", line 735, in _do_insert
    using=using, raw=raw)
  File "/home/thomas/Dokumente/Softwareprojekte/boozter/ENV/local/lib/python2.7/site-packages/django/db/models/manager.py", line 92, in manager_method
    return getattr(self.get_queryset(), name)(*args, **kwargs)
  File "/home/thomas/Dokumente/Softwareprojekte/boozter/ENV/local/lib/python2.7/site-packages/django/db/models/query.py", line 920, in _insert
    return query.get_compiler(using=using).execute_sql(return_id)
  File "/home/thomas/Dokumente/Softwareprojekte/boozter/ENV/local/lib/python2.7/site-packages/django/db/models/sql/compiler.py", line 919, in execute_sql
    cursor.execute(sql, params)
  File "/home/thomas/Dokumente/Softwareprojekte/boozter/ENV/local/lib/python2.7/site-packages/django/db/backends/utils.py", line 65, in execute
    return self.cursor.execute(sql, params)
  File "/home/thomas/Dokumente/Softwareprojekte/boozter/ENV/local/lib/python2.7/site-packages/django/db/utils.py", line 94, in __exit__
    six.reraise(dj_exc_type, dj_exc_value, traceback)
  File "/home/thomas/Dokumente/Softwareprojekte/boozter/ENV/local/lib/python2.7/site-packages/django/db/backends/utils.py", line 65, in execute
    return self.cursor.execute(sql, params)
  File "/home/thomas/Dokumente/Softwareprojekte/boozter/ENV/local/lib/python2.7/site-packages/django/db/backends/sqlite3/base.py", line 485, in execute
    return Database.Cursor.execute(self, query, params)
OperationalError: no such table: main.idx_sample_location__new_position

I believe that this comes from the fact, that alter_db_table (from contrib/gis/db/backends/sqlite/schema.py) is executed, which just renames the index table. I can imagine, that spatialite does not update the necessary references and therefore comes up with this error, when trying to insert something to the model table.

Instead one could execute remove_geometry_metadata() before renaming the actual model table and execute part of column_sql() in order to create it again.

Does anybody have any input on that idea?

I stumbled across #23030 which might have had similar reasons.

comment:3 by Fanthomas90, 10 years ago

Severity:	Normal → Release blocker

comment:4 by Andrew Godwin, 10 years ago

I can't immediately work out the issue here, but I suspect we might have to say that alteration isn't supported with spatialite - after all, Django is offering features that Spatialite itself does not. Rather a drastic change, but if you're developing GIS applications it's not too much an ask to say you should use PostGIS.