Opened 12 years ago

Closed 10 years ago

#15295 closed Bug (fixed)

GeoDjango model creation fails for MySQL during tests

Reported by: Adam Nelson Owned by: nobody
Component: GIS Version: dev
Severity: Normal Keywords:
Cc: Triage Stage: Accepted
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

When running runtests.py:

Failed to install index for geoapp.Country model: (1464, "The used table type doesn't support SPATIAL indexes")
Failed to install index for geoapp.City model: (1464, "The used table type doesn't support SPATIAL indexes")
Failed to install index for geoapp.State model: (1464, "The used table type doesn't support SPATIAL indexes")
Failed to install index for geoapp.Track model: (1464, "The used table type doesn't support SPATIAL indexes")
Failed to install index for geoapp.Feature model: (1464, "The used table type doesn't support SPATIAL indexes")
Failed to install index for geoapp.MinusOneSRID model: (1464, "The used table type doesn't support SPATIAL indexes")
Failed to install index for relatedapp.Location model: (1464, "The used table type doesn't support SPATIAL indexes")
Failed to install index for relatedapp.Parcel model: (1464, "The used table type doesn't support SPATIAL indexes")
Failed to install index for layermap.County model: (1464, "The used table type doesn't support SPATIAL indexes")
Failed to install index for layermap.CountyFeat model: (1464, "The used table type doesn't support SPATIAL indexes")
Failed to install index for layermap.City model: (1464, "The used table type doesn't support SPATIAL indexes")
Failed to install index for layermap.Interstate model: (1464, "The used table type doesn't support SPATIAL indexes")
Failed to install index for layermap.CityBase model: (1464, "The used table type doesn't support SPATIAL indexes")

This is because MySQL InnoDB does not support SPATIAL indexes. Options:

  1. create a new variable for spatial_index that is set to true if using the MySQL backend with InnoDB
  2. or wrap table creation around a try...except that inserts spatial_index=False
  3. or do one of these for GeoDjango generally (not just the tests)

Change History (10)

comment:1 in reply to:  description Changed 12 years ago by jbronn

Replying to adamnelson:

This is because MySQL InnoDB does not support SPATIAL indexes. Options:

  1. create a new variable for spatial_index that is set to true if using the MySQL backend with InnoDB
  2. or wrap table creation around a try...except that inserts spatial_index=False
  3. or do one of these for GeoDjango generally (not just the tests)

How about not attempting to run the GeoDjango test suite with InnoDB in the first place? What does your settings file look like when you ran runtests.py?

comment:2 Changed 12 years ago by Adam Nelson

It works fine if I don't use InnoDB I presume. However, I'd like to use InnoDB in my environment so it would be best if the tests pass.

comment:3 Changed 12 years ago by Russell Keith-Magee

Triage Stage: UnreviewedAccepted

comment:4 Changed 12 years ago by Łukasz Rekucki

Severity: Normal
Type: Bug

comment:5 Changed 12 years ago by jbronn

Easy pickings: unset
Resolution: wontfix
Status: newclosed
UI/UX: unset

GeoDjango is not supported on MySQL when InnoDB is used. If you want the tests to pass, ensure the correct options are in your settings when testing, for example:

TEST_RUNNER='django.contrib.gis.tests.GeoDjangoTestSuiteRunner'

DATABASES = {
    'default': {
        'ENGINE': 'django.contrib.gis.db.backends.mysql',
        'NAME': 'geodjango',
        'USER': 'geodjango',
        'OPTIONS': {
            'init_command': 'SET storage_engine=MyISAM',
        }
    },
}

comment:6 Changed 12 years ago by Adam Nelson

It's not efficient to use GeoDjango with range queries on InnoDB (because of the lack of SPATIAL indexes) but it works exactly the same as MyISAM with regards to point() fields.

I'm fine keeping this ticket closed, but GeoDjango does work with InnoDB and in addition it's not documented anywhere that GeoDjango is not supported on InnoDB.

comment:7 Changed 10 years ago by Chris Spencer

Resolution: wontfix
Status: closednew

Please re-consider this bug. It effects a lot more than just tests. As most people use Innodb with MySQL, this effectively makes all django.contrib.gis functionality incredibly awkward to most MySQL users, if not completely inaccessible. Slower functionality due to missing indexes on Innodb is preferrable to no functionality whatsoever.

It should at least be mentioned in the docs somewhere that GeoDjango has not support for InnoDB. I wasted several hours going over the docs and through the setup only to realize I couldn't use any of it with my pre-existing InnoDB database when my initial model migrations blew up.

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

In 8fbc20b24bd14e22f3d9c0dd02781ddf1f2b5bd7:

Emphasized MyISAM pseudo-requirement for GeoDjango over MySQL

Refs #15295.

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

In 44afe4460218c1207ca9d7b1495e3f60cb06d898:

[1.5.x] Emphasized MyISAM pseudo-requirement for GeoDjango over MySQL

Refs #15295.
Backport of 8fbc20b24 from master.

comment:10 Changed 10 years ago by Claude Paroz

Resolution: fixed
Status: newclosed

Hopefully, the commit above addresses concerns about lack of documentation on this issue. I'm not sure we can/will do more.

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