[Django] #23153: 1.7 migrations' temporary tables cause problems with gis
Django
-------------------------------+-----------------------------
Reporter: TomLottermann | Owner: nobody
Type: Bug | Status: new
Component: Migrations | Version: 1.7-rc-2
Severity: Normal | Keywords: migrations, gis
Triage Stage: Unreviewed | Has patch: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------+-----------------------------
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:
1. It works on a legacy database which was created by south and doesn't
work on a newly created one (created by migrations)
2. 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.
3. 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
--
Ticket URL: <https://code.djangoproject.com/ticket/23153>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
Reporter: TomLottermann | Owner: nobody
Type: Bug | Status: new
Severity: Normal | Resolution:
Keywords: migrations, gis | Triage Stage: Unreviewed
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by TomLottermann):
* needs_better_patch: => 0
* component: Migrations => GIS
* needs_tests: => 0
* needs_docs: => 0
--
Ticket URL: <https://code.djangoproject.com/ticket/23153#comment:1>
Django
Reporter: TomLottermann | Owner: nobody
Type: Bug | Status: new
Severity: Normal | Resolution:
Easy pickings: 0 | UI/UX: 0
Comment (by TomLottermann):
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
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.
--
Ticket URL: <https://code.djangoproject.com/ticket/23153#comment:2>
Django
Reporter: TomLottermann | Owner: nobody
Type: Bug | Status: new
Severity: Release blocker | Resolution:
Keywords: migrations, gis | Triage Stage: Unreviewed
Easy pickings: 0 | UI/UX: 0
Changes (by TomLottermann):
* severity: Normal => Release blocker
--
Ticket URL: <https://code.djangoproject.com/ticket/23153#comment:3>
Django
Reporter: TomLottermann | Owner: nobody
Type: Bug | Status: new
Severity: Release blocker | Resolution:
Easy pickings: 0 | UI/UX: 0
Comment (by andrewgodwin):
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.
--
Ticket URL: <https://code.djangoproject.com/ticket/23153#comment:4>
Django
Reporter: TomLottermann | Owner: nobody
Type: Bug | Status: new
Severity: Release blocker | Resolution:
Easy pickings: 0 | UI/UX: 0
Comment (by claudep):
I have worked a bit on Spatialite migrations recently, and ''might'' be
able to handle this, however probably not before a week.
--
Ticket URL: <https://code.djangoproject.com/ticket/23153#comment:5>
Django
Reporter: TomLottermann | Owner: andrewgodwin
Type: Bug | Status: assigned
Component: GIS | Version: 1.7-rc-2
Severity: Release blocker | Resolution:
Easy pickings: 0 | UI/UX: 0
Changes (by andrewgodwin):
* owner: nobody => andrewgodwin
* status: new => assigned
--
Ticket URL: <https://code.djangoproject.com/ticket/23153#comment:6>
Django
Reporter: TomLottermann | Owner: andrewgodwin
Component: GIS | Version: 1.7-rc-2
Severity: Release blocker | Resolution: fixed
Keywords: migrations, gis | Triage Stage: Unreviewed
Easy pickings: 0 | UI/UX: 0
Changes (by Andrew Godwin <andrew@…>):
* status: assigned => closed
* resolution: => fixed
Comment:
In [changeset:"b508c1c317c026eeec82b04142d9e3956192619c"]:
{{{
#!CommitTicketReference repository=""
revision="b508c1c317c026eeec82b04142d9e3956192619c"
Fixed #23153: Properly recreate spatialite triggers on alter table
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/23153#comment:7>
Django
Reporter: TomLottermann | Owner: andrewgodwin
Type: Bug | Status: closed
Component: GIS | Version: 1.7-rc-2
Severity: Release blocker | Resolution: fixed
Easy pickings: 0 | UI/UX: 0
Comment (by Andrew Godwin <andrew@…>):
In [changeset:"6ebfff7f667f8e30afc6fb32a4349b6d0a4aa4ba"]:
{{{
#!CommitTicketReference repository=""
revision="6ebfff7f667f8e30afc6fb32a4349b6d0a4aa4ba"
[1.7.x] Fixed #23153: Properly recreate spatialite triggers on alter table
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/23153#comment:8>
Django
Reporter: TomLottermann | Owner: andrewgodwin
Component: GIS | Version: 1.7-rc-2
Severity: Release blocker | Resolution:
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Changes (by timo):
* status: closed => new
* resolution: fixed =>
* stage: Unreviewed => Accepted
Comment:
This is causing "table already exists" test failures on spatialite.
--
Ticket URL: <https://code.djangoproject.com/ticket/23153#comment:9>
Django
Reporter: TomLottermann | Owner: andrewgodwin
Component: GIS | Version: 1.7-rc-2
Severity: Release blocker | Resolution:
Keywords: migrations, gis | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Comment (by andrewgodwin):
Can I get more information? All tests pass locally on spatialite, and
Jenkins seems down at the moment.
--
Ticket URL: <https://code.djangoproject.com/ticket/23153#comment:10>
Django
Reporter: TomLottermann | Owner: andrewgodwin
Component: GIS | Version: 1.7-rc-2
Severity: Release blocker | Resolution:
Keywords: migrations, gis | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Comment (by charettes):
@andrewgodwin, Jenkins has moved to [http://djangoci.com/job/django-
master/database=spatialite,python=python2.7/ djangoci.com].
--
Ticket URL: <https://code.djangoproject.com/ticket/23153#comment:11>
Django
Reporter: TomLottermann | Owner: andrewgodwin
Component: GIS | Version: 1.7-rc-2
Severity: Release blocker | Resolution: fixed
Keywords: migrations, gis | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Changes (by timo):
* status: new => closed
* resolution: => fixed
Comment:
In 5f6558f82f980ff1326ae0f71b4cdaa545c6f4c2"
Stop errors on older Spatialite versions that miss some tables
--
Ticket URL: <https://code.djangoproject.com/ticket/23153#comment:12>