EC
1436
MySQLERRORNotableDDLHIGH confidence

Table type does not support SPATIAL indexes

Production Risk

Low — DDL error; table will not be created as specified.

What this means

ER_TABLE_CANT_HANDLE_SPKEYS (1436, SQLSTATE HY000) is returned when you attempt to create a SPATIAL index on a storage engine that does not support it.

Why it happens
  1. 1Creating a SPATIAL INDEX on an InnoDB table in MySQL versions before 5.7.5
  2. 2Using a storage engine (e.g., MEMORY, CSV) that does not support spatial indexes
How to reproduce
trigger — this will error
trigger — this will error
CREATE TABLE locations (
  id INT PRIMARY KEY,
  geom GEOMETRY NOT NULL,
  SPATIAL INDEX(geom)
) ENGINE=MEMORY;  -- MEMORY does not support SPATIAL

expected output

ERROR 1436 (HY000): The used table type doesn't support SPATIAL indexes

Fix

Switch to a supported storage engine

Switch to a supported storage engine
-- MyISAM and InnoDB (5.7.5+) support spatial indexes:
CREATE TABLE locations (
  id INT PRIMARY KEY,
  geom GEOMETRY NOT NULL SRID 4326,
  SPATIAL INDEX(geom)
) ENGINE=InnoDB;

Why this works

InnoDB supports spatial indexes from MySQL 5.7.5 onwards; MyISAM has supported them longer.

Version notes
MySQL 5.7.5+

InnoDB gained support for SPATIAL indexes in this version.

Sources
Official documentation ↗

MySQL 8.0 — 1436 ER_TABLE_CANT_HANDLE_SPKEYS

Content generated with AI assistance and reviewed for accuracy. Found an error? hello@errcodes.dev

← All MySQL errors