Opened 13 years ago

Closed 11 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)

in reply to:  description comment:1 by jbronn, 13 years ago

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 by Adam Nelson, 13 years ago

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 by Russell Keith-Magee, 13 years ago

Triage Stage: UnreviewedAccepted

comment:4 by Łukasz Rekucki, 13 years ago

Severity: Normal
Type: Bug

comment:5 by jbronn, 13 years ago

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 by Adam Nelson, 13 years ago

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 by Chris Spencer, 11 years ago

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 by Claude Paroz <claude@…>, 11 years ago

In 8fbc20b24bd14e22f3d9c0dd02781ddf1f2b5bd7:

Emphasized MyISAM pseudo-requirement for GeoDjango over MySQL

Refs #15295.

comment:9 by Claude Paroz <claude@…>, 11 years ago

In 44afe4460218c1207ca9d7b1495e3f60cb06d898:

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

Refs #15295.
Backport of 8fbc20b24 from master.

comment:10 by Claude Paroz, 11 years ago

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