Opened 2 years ago

Closed 2 years ago

Last modified 2 years ago

#23030 closed Bug (fixed)

Geo model table rename (in migrations) with spatialite UNIQUE constraint failed

Reported by: Lucio Asnaghi Owned by: Claude Paroz
Component: GIS Version: 1.7-rc-1
Severity: Release blocker Keywords: gis spatialite sqlite geometry
Cc: Triage Stage: Accepted
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

I explain my working test:

  • create a virtualenv
  • install django 1.7c1
  • create a project
  • create an app and include to INSTALLED_APPS
  • change the database to use 'django.contrib.gis.db.backends.spatialite'
  • now go to models.py of the created app and type:
    from django.db import models
    from django.contrib.gis.db import models as geomodels
    from django.contrib.contenttypes.models import ContentType
    from django.contrib.contenttypes.fields import GenericForeignKey
    
    class Institute(models.Model):
        pass
    
    class Company(models.Model):
        pass
    
    class Motivation(models.Model):
        pass
    
    class Manumission(geomodels.Model):
        institute = models.ForeignKey(Institute, blank=True, null=True)
        company = models.ForeignKey(Company, blank=True, null=True)
        motivation = models.ForeignKey(Motivation, blank=True, null=True)
        elements = geomodels.GeometryField(null=True, blank=True)
        objects = geomodels.GeoManager()
    
  • then go into a shell and enter the virtualenv and issue:
    spatialite db.sqlite3 "SELECT InitSpatialMetaData();"
    ./manage.py makemigrations appname
    ./manage.py syncdb --noinput
    

The result is this:

SpatiaLite version ..: 3.0.1	Supported Extensions:
	- 'VirtualShape'	[direct Shapefile access]
	- 'VirtualDbf'		[direct DBF access]
	- 'VirtualXL'		[direct XLS access]
	- 'VirtualText'		[direct CSV/TXT access]
	- 'VirtualNetwork'	[Dijkstra shortest path]
	- 'RTree'		[Spatial Index - R*Tree]
	- 'MbrCache'		[Spatial Index - MBR cache]
	- 'VirtualSpatialIndex'	[R*Tree metahandler]
	- 'VirtualFDO'		[FDO-OGR interoperability]
	- 'SpatiaLite'		[Spatial SQL - OGC]
PROJ.4 version ......: Rel. 4.8.0, 6 March 2012
GEOS version ........: 3.4.2-CAPI-1.8.2 r3921
the SPATIAL_REF_SYS table already contains some row(s)
 InitSpatiaMetaData ()error:"table spatial_ref_sys already exists"
0
Migrations for 'gis':
  0001_initial.py:
    - Create model Company
    - Create model Institute
    - Create model Manumission
    - Create model Motivation
    - Add field motivation to manumission
Operations to perform:
  Apply all migrations: admin, contenttypes, gis, auth, sessions
Running migrations:
  Applying contenttypes.0001_initial... OK
  Applying auth.0001_initial... OK
  Applying admin.0001_initial... OK
  Applying gis.0001_initial...Traceback (most recent call last):
  File "./manage.py", line 10, in <module>
    execute_from_command_line(sys.argv)
  File "/home/x/djangofault/lib/python2.7/site-packages/django/core/management/__init__.py", line 385, in execute_from_command_line
    utility.execute()
  File "/home/x/djangofault/lib/python2.7/site-packages/django/core/management/__init__.py", line 377, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File "/home/x/djangofault/lib/python2.7/site-packages/django/core/management/base.py", line 288, in run_from_argv
    self.execute(*args, **options.__dict__)
  File "/home/x/djangofault/lib/python2.7/site-packages/django/core/management/base.py", line 337, in execute
    output = self.handle(*args, **options)
  File "/home/x/djangofault/lib/python2.7/site-packages/django/core/management/base.py", line 532, in handle
    return self.handle_noargs(**options)
  File "/home/x/djangofault/lib/python2.7/site-packages/django/core/management/commands/syncdb.py", line 27, in handle_noargs
    call_command("migrate", **options)
  File "/home/x/djangofault/lib/python2.7/site-packages/django/core/management/__init__.py", line 115, in call_command
    return klass.execute(*args, **defaults)
  File "/home/x/djangofault/lib/python2.7/site-packages/django/core/management/base.py", line 337, in execute
    output = self.handle(*args, **options)
  File "/home/x/djangofault/lib/python2.7/site-packages/django/core/management/commands/migrate.py", line 160, in handle
    executor.migrate(targets, plan, fake=options.get("fake", False))
  File "/home/x/djangofault/lib/python2.7/site-packages/django/db/migrations/executor.py", line 62, in migrate
    self.apply_migration(migration, fake=fake)
  File "/home/x/djangofault/lib/python2.7/site-packages/django/db/migrations/executor.py", line 96, in apply_migration
    migration.apply(project_state, schema_editor)
  File "/home/x/djangofault/lib/python2.7/site-packages/django/db/migrations/migration.py", line 107, in apply
    operation.database_forwards(self.app_label, schema_editor, project_state, new_state)
  File "/home/x/djangofault/lib/python2.7/site-packages/django/db/migrations/operations/fields.py", line 37, in database_forwards
    field,
  File "/home/x/djangofault/lib/python2.7/site-packages/django/contrib/gis/db/backends/spatialite/schema.py", line 82, in add_field
    super(SpatialiteSchemaEditor, self).add_field(model, field)
  File "/home/x/djangofault/lib/python2.7/site-packages/django/db/backends/sqlite3/schema.py", line 143, in add_field
    self._remake_table(model, create_fields=[field])
  File "/home/x/djangofault/lib/python2.7/site-packages/django/db/backends/sqlite3/schema.py", line 125, in _remake_table
    self.alter_db_table(model, temp_model._meta.db_table, model._meta.db_table)
  File "/home/x/djangofault/lib/python2.7/site-packages/django/contrib/gis/db/backends/spatialite/schema.py", line 95, in alter_db_table
    "new_table": self.quote_name(new_db_table),
  File "/home/x/djangofault/lib/python2.7/site-packages/django/db/backends/schema.py", line 98, in execute
    cursor.execute(sql, params)
  File "/home/x/djangofault/lib/python2.7/site-packages/django/db/backends/utils.py", line 81, in execute
    return super(CursorDebugWrapper, self).execute(sql, params)
  File "/home/x/djangofault/lib/python2.7/site-packages/django/db/backends/utils.py", line 65, in execute
    return self.cursor.execute(sql, params)
  File "/home/x/djangofault/lib/python2.7/site-packages/django/db/utils.py", line 94, in __exit__
    six.reraise(dj_exc_type, dj_exc_value, traceback)
  File "/home/x/djangofault/lib/python2.7/site-packages/django/db/backends/utils.py", line 65, in execute
    return self.cursor.execute(sql, params)
  File "/home/x/djangofault/lib/python2.7/site-packages/django/db/backends/sqlite3/base.py", line 485, in execute
    return Database.Cursor.execute(self, query, params)
django.db.utils.IntegrityError: UNIQUE constraint failed: geometry_columns.f_table_name, geometry_columns.f_geometry_column

In fact the migration created seems strange (why the 3rd foreign key is issued separately, so a table rename is needed):

# -*- coding: utf-8 -*-
from __future__ import unicode_literals

from django.db import models, migrations
import django.contrib.gis.db.models.fields


class Migration(migrations.Migration):

    dependencies = [
    ]

    operations = [
        migrations.CreateModel(
            name='Company',
            fields=[
                ('id', models.AutoField(verbose_name='ID', serialize=False, auto_created=True, primary_key=True)),
            ],
            options={
            },
            bases=(models.Model,),
        ),
        migrations.CreateModel(
            name='Institute',
            fields=[
                ('id', models.AutoField(verbose_name='ID', serialize=False, auto_created=True, primary_key=True)),
            ],
            options={
            },
            bases=(models.Model,),
        ),
        migrations.CreateModel(
            name='Manumission',
            fields=[
                ('id', models.AutoField(verbose_name='ID', serialize=False, auto_created=True, primary_key=True)),
                ('elements', django.contrib.gis.db.models.fields.GeometryField(srid=4326, null=True, blank=True)),
                ('company', models.ForeignKey(blank=True, to='gis.Company', null=True)),
                ('institute', models.ForeignKey(blank=True, to='gis.Institute', null=True)),
            ],
            options={
            },
            bases=(models.Model,),
        ),
        migrations.CreateModel(
            name='Motivation',
            fields=[
                ('id', models.AutoField(verbose_name='ID', serialize=False, auto_created=True, primary_key=True)),
            ],
            options={
            },
            bases=(models.Model,),
        ),
        migrations.AddField(
            model_name='manumission',
            name='motivation',
            field=models.ForeignKey(blank=True, to='gis.Motivation', null=True),
            preserve_default=True,
        ),
    ]

Also, base=(models.Model,) of 'Manumission' model does not respect django.contrib.gis.db.Model.

Attachments (2)

spatialite_migrations_fix.patch (1.2 KB) - added by Lucio Asnaghi 2 years ago.
This works with migrations
spatialite_migrations_regression.patch (2.7 KB) - added by Lucio Asnaghi 2 years ago.
It's possible to showcase the error with this test

Download all attachments as: .zip

Change History (13)

comment:1 Changed 2 years ago by Lucio Asnaghi

Needs documentation: unset
Needs tests: unset
Patch needs improvement: unset

Test case should be shortened to:

from django.db import models
from django.contrib.gis.db.models import GeometryField

class Last(models.Model):
    pass

class First(models.Model):
    fk = models.ForeignKey(Last, blank=True, null=True)
    objs = GeometryField(null=True, blank=True)

comment:2 Changed 2 years ago by Lucio Asnaghi

It seems that geometry_columns spatialite table is populated with 2 tables

  Applying gis.0001_initial...
SELECT AddGeometryColumn('gis_first', 'objs', 4326, 'GEOMETRY', 2, 0)
SELECT CreateSpatialIndex("gis_first", "objs")
SELECT AddGeometryColumn('gis_first__new', 'objs', 4326, 'GEOMETRY', 2, 0)
SELECT CreateSpatialIndex("gis_first__new", "objs")

UPDATE geometry_columns SET f_table_name = "gis_first" WHERE f_table_name = "gis_first__new"

then the update is obviously failing cause in the geometry_columns table there are already 2 entries...

comment:3 Changed 2 years ago by Lucio Asnaghi

Summary: Migration code not working with GeometryField and more than 2 foreign keysGeo model table rename (in migrations) with spatialite UNIQUE constraint failed

comment:4 Changed 2 years ago by Lucio Asnaghi

Has patch: set

comment:5 Changed 2 years ago by Tim Graham

Needs tests: set
Triage Stage: UnreviewedAccepted

Could you add a regression test?

comment:6 Changed 2 years ago by Lucio Asnaghi

well, actually my patch don't work as expected, there are some edge cases that make it not viable solution.

regarding regression test, i'm not that practical with writing django test actually, but i can see what i can do.

Changed 2 years ago by Lucio Asnaghi

This works with migrations

comment:7 Changed 2 years ago by Lucio Asnaghi

last patch will work correctly with migrations without leaving the database in a inconsistent state.

i don't know if DatabaseSchema.alter_db_table is called also outside of migrations, so i don't know if it will work in any corner case.

will try to write a test for this

Changed 2 years ago by Lucio Asnaghi

It's possible to showcase the error with this test

comment:8 Changed 2 years ago by Claude Paroz

Owner: changed from nobody to Claude Paroz
Status: newassigned

comment:9 Changed 2 years ago by Claude Paroz

Needs tests: unset

comment:10 Changed 2 years ago by Claude Paroz <claude@…>

Resolution: fixed
Status: assignedclosed

In 8c30df15f17c180fbfb3e378c5469c63cde6599b:

Fixed #23030 -- Properly handled geometry columns metadata during migrations

Thanks kunitoki for the report and initial patches.

comment:11 Changed 2 years ago by Claude Paroz <claude@…>

In ddb5674945725ec8f6e6336f73d3d56e331f34ae:

[1.7.x] Fixed #23030 -- Properly handled geometry columns metadata during migrations

Thanks kunitoki for the report and initial patches.
Backport of 8c30df15f1 from master.

Note: See TracTickets for help on using tickets.
Back to Top