Bug #60298 Geometry columns/spatial indexes unsupported in partitioned tables
Submitted: 1 Mar 2011 22:33 Modified: 3 Mar 2011 19:32
Reporter: Kevin Martin Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Partitions Severity:S3 (Non-critical)
Version:5.1+ OS:Any
Assigned to: CPU Architecture:Any
Tags: spatial myisam partition
Triage: Needs Triage: D4 (Minor)

[1 Mar 2011 22:33] Kevin Martin
Description:
Attempts to create partitioned MyISAM tables containing geometry columns fail with a "not supported" message:

mysql> CREATE TABLE geop2 ( `id` INT NOT NULL AUTO_INCREMENT, `point` POINT NOT NULL, PRIMARY KEY ( `id` ) ) ENGINE=MyISAM PARTITION BY LINEAR HASH( `id` ) 
PARTITIONS 40;

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

There's no reference to this restriction here:
http://dev.mysql.com/doc/refman/5.1/en/partitioning-limitations-storage-engines.html

Nor within the overview (http://dev.mysql.com/doc/refman/5.1/en/partitioning-overview.html) which states:

"For creating partitioned tables, you can use most storage engines that are supported by your MySQL server; the MySQL partitioning engine runs in a separate layer and can interact with any of these. In MySQL 5.1, all partitions of the same partitioned table must use the same storage engine; for example, you cannot use MyISAM for one partition and InnoDB for another. However, there is nothing preventing you from using different storage engines for different partitioned tables on the same MySQL server or even in the same database."

I'd imagine the docs and error message should be updated if this is not meant to be supported, or, ideally, support for geometry columns within partitioned MyISAM tables should be implemented.

How to repeat:
Attempt to create a partitioned table containing a geometry column:

CREATE TABLE geop2 ( `id` INT NOT NULL AUTO_INCREMENT, `point` POINT NOT NULL, PRIMARY KEY ( `id` ) ) ENGINE=MyISAM PARTITION BY LINEAR HASH( `id` ) 
PARTITIONS 40;

Suggested fix:
Implement support for geometry columns within partitioned tables. With any luck this is already in place and the error message is being incorrectly returned.

Failing that, update docs and error message to explicitly disallow this situation.
[2 Mar 2011 4:06] Valeriy Kravchuk
Indeed, this is what we have even in latest 5.5:

macbook-pro:5.5 openxs$ bin/mysql -uroot testReading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.5.11-debug Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> CREATE TABLE geop2 ( `id` INT NOT NULL AUTO_INCREMENT, `point` POINT NOT NULL, PRIMARY
    -> KEY ( `id` ) ) ENGINE=MyISAM PARTITION BY LINEAR HASH( `id` ) 
    -> PARTITIONS 40;
ERROR 1178 (42000): The storage engine for the table doesn't support GEOMETRY
[2 Mar 2011 20:41] Kevin Martin
It looks like geometry support for partitioned tables is explicitly disallowed within sql/ha_partition.h, line 773:

"[...] HA_CAN_GEOMETRY, HA_CAN_FULLTEXT, HA_CAN_SQL_HANDLER, HA_DUPLICATE_POS, HA_CAN_INSERT_DELAYED, HA_PRIMARY_KEY_REQUIRED_FOR_POSITION is disabled until further investigated."

Has the time for this investigation come? It would be advantageous to have this support and, though I haven't investigated much, I'd imagine it should come almost automatically through the manner that partitioning has been implemented.
[3 Mar 2011 19:31] Kevin Martin
Allows geometry columns within partitioned tables

Attachment: partitionGeometry.patch (application/octet-stream, text), 372 bytes.

[3 Mar 2011 19:32] Kevin Martin
The supplied patch modifies ha_partition.h to allow geometry column support within partitioned tables. Previously this support was disabled no matter whether the base table supported geometry.

Initial tests of this support on a partitioned table were successful.
[3 Mar 2011 20:45] Mattias Jonsson
I also did some tests like yours and stumbled upon a bug in the latest mysql-trunk, I will retry it in 5.1 or 5.5