GeoDjango model creation fails for MySQL during tests

[Adam Nelson]

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)

[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?

[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.

[jbronn]

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',
        }
    },
}

[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.

[Chris Spencer]

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.

[Claude Paroz <claude@…>]

In 8fbc20b24bd14e22f3d9c0dd02781ddf1f2b5bd7:

Emphasized MyISAM pseudo-requirement for GeoDjango over MySQL

Refs #15295.

[Claude Paroz <claude@…>]

In 44afe4460218c1207ca9d7b1495e3f60cb06d898:

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

Refs #15295.
Backport of 8fbc20b24 from master.

[Claude Paroz]

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