id summary reporter owner description type status component version severity resolution keywords cc stage has_patch needs_docs needs_tests needs_better_patch easy ui_ux 23153 1.7 migrations' temporary tables cause problems with gis Fanthomas90 Andrew Godwin "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" Bug closed GIS 1.7-rc-2 Release blocker fixed migrations, gis Accepted 0 0 0 0 0 0