MySQL Bugs: #104253: Allow using spatial index and geometry types with partitioned tables

Bug #104253	Allow using spatial index and geometry types with partitioned tables
Submitted:	8 Jul 2021 14:16	Modified:	9 Jul 2021 5:47
Reporter:	Hurelhuyag M	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Partitions	Severity:	S4 (Feature request)
Version:	8.0.25, 5.7.34	OS:	Ubuntu
Assigned to:		CPU Architecture:	x86
Tags:	Geometry, gis, partition, spatial

Description:
I'm trying to create a spatial index on a partitioned table. Like this:

CREATE TABLE data (
   deviceId BIGINT UNSIGNED NOT NULL,
   createdAt DATETIME NOT NULL,
   location POINT NOT NULL,
   PRIMARY KEY (deviceId, createdAt),
   SPATIAL KEY (location)
)Engine=InnoDB PARTITION BY HASH ( deviceId ) PARTITIONS 1024;

But MySQL 8.0.25 returning error message:

ERROR 1178 (42000): The storage engine for the table doesn't support GEOMETRY

How to repeat:
1. Launch MySQL 8.0.25
2. CREATE DATABASE test;
3. Create a table with SPATIAL INDEX and partition.

CREATE TABLE data (
   deviceId BIGINT UNSIGNED NOT NULL,
   createdAt DATETIME NOT NULL,
   location POINT NOT NULL,
   PRIMARY KEY (deviceId, createdAt),
   SPATIAL KEY (location)
)Engine=InnoDB PARTITION BY HASH ( deviceId ) PARTITIONS 1024;

4. This error message should return:

ERROR 1178 (42000): The storage engine for the table doesn't support GEOMETRY

Suggested fix:
Enabling or allowing partitioning and spatial index on the same table.

tags updated

Hello Hurelhuyag M,

Thank you for the feature request.
Imho manual list this as a limitation - Quoting from manual "Spatial columns.  Columns with spatial data types such as POINT or GEOMETRY cannot be used in partitioned tables. " - https://dev.mysql.com/doc/refman/8.0/en/partitioning-limitations.html

regards,
Umesh