Opened 4 years ago

Closed 2 years ago

#15295 closed Bug (fixed)

GeoDjango model creation fails for MySQL during tests

Reported by: adamnelson Owned by: nobody
Component: GIS Version: master
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 4 years ago by jbronn

  • Needs documentation unset
  • Needs tests unset
  • Patch needs improvement unset

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 4 years ago by adamnelson

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 4 years ago by russellm

  • Triage Stage changed from Unreviewed to Accepted

comment:4 Changed 4 years ago by lrekucki

  • Severity set to Normal
  • Type set to Bug

comment:5 Changed 4 years ago by jbronn

  • Easy pickings unset
  • Resolution set to wontfix
  • Status changed from new to closed
  • 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 4 years ago by adamnelson

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 2 years ago by Cerin

  • Resolution wontfix deleted
  • Status changed from closed to new

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

In 8fbc20b24bd14e22f3d9c0dd02781ddf1f2b5bd7:

Emphasized MyISAM pseudo-requirement for GeoDjango over MySQL

Refs #15295.

comment:9 Changed 2 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 2 years ago by claudep

  • Resolution set to fixed
  • Status changed from new to closed

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