MySQL Bugs: #36080: Bug when creating a new spatial index on geometry field

Bug #36080	Bug when creating a new spatial index on geometry field
Submitted:	15 Apr 2008 7:43	Modified:	16 Jun 2008 6:44
Reporter:	Aviv Levy	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: GIS	Severity:	S2 (Serious)
Version:	5.1.22 / 5.1.23	OS:	Any
Assigned to:	Alexey Botchkov	CPU Architecture:	Any
Tags:	Geometry, gis, keys, spatial index

Description:
when i tried to create new spatial index on a new table, i got the following error:

Incorrect sub part key; the used key part isn't a string, the used length is longer than the key part, or the storage engine doesn't support unique sub keys

no matter what i do, i returns. 
note: the table is in MyISam storage engine.

How to repeat:
create new table, add a geometry field.
create spatial index for the field. 
than the bug appears...

Thank you for a problem report. Please, send the exact, complete test case.

hi,
first, the exact version of the server is: mysql-essential-5.1.23-rc-win32

now, you can see the bug in the following scripts. both of them will get bug. 

thanks.

CREATE TABLE `xxx` (
  `ID` int(11) NOT NULL,
  `ogc_geom` geometry NOT NULL default '',
  PRIMARY KEY  (`ID`),
  SPATIAL KEY `Spatial` (`ogc_geom`(32))
) ENGINE=MyISAM DEFAULT CHARSET=hebrew COMMENT='InnoDB free: 3550208 kB';

INSERT INTO `xxx` (`ID`,`ogc_geom`) VALUES 
 (0, 0xE610000001060000000100000001030000000100000029000000C701EFA9859C414076850F967D444040D57960E47B9C4140315A1FFCB64440406C45EBCE6F9C414078E6CC9DEC4440407E3A424A559C41401945BE15004540406DBDEADE2E9C41401B4B9D28E744404028BA5BC6139C4140A58A54F3BA444040E4F0CBA7F19B41402D3479FB954440400B5C5679BF9B4140FE4F780A90444040EDC18C2CC19B414036C4254FDD44404076745782C09B41405B2176A40545404015BDD160F19B4140F28D895A294540404D965D6A0B9C41406B9519094B4540409FD4B0E7079C41403C61090F6B4540401093C22DF39B4140F82F4C4F7C45404079DBCD51DB9B4140FE468B4D72454040EF93E405C79B41406A8E90E4304540403998E7C99D9B41404E383D8B2A454040831B82B2749B41407BDA77932D4540407B4C263CAA9B4140581FA4F4934540405B2BC0CFA39B414088510E56AB454040A6941D97A79B4140648B3A4EB6454040A100045BAC9B4140DD7C6D93BF454040DAE393D0B19B41403E888624D6454040B7D19F6EB29B41407B8280EDE045404095F3DD4EB29B4140CC20BB15EB45404056C225CDF59B4140A060DFC10E4640409935403F1B9C4140BF1EFEC620464040F6751C494C9C41406B5D8FAD274640404C8D85BC6E9C4140EAA00A30294640409E36970E8F9C414092BC8D75304640406D08833D9E9C4140991DBF933A46404098F8AC30C29C414081C57F680A464040C125C791E49C4140B9CA5A32DC454040CD226B9CDE9C4140D4A44861C2454040E0593742C69C4140E421C4AD9A4540409F1F40BCBF9C4140196CDA5A6F45404073A82837D99C414003D3821838454040C5E2ACE2F89C4140ED668066FD44404067B9898DEB9C4140A5632480CC444040B9F54FF1B99C4140C7EDB41997444040C701EFA9859C414076850F967D444040),
 (1, 0xE61000000106000000010000000103000000010000006100000028E48E45EE9741402EA7B632874140404A65B78598974140A35159CD8B414040F3E8F7B566974140BFC2F3EF9A414040F7A1DB1538974140F92503D7AD414040FCA3360115974140263819B7D8414040EF2643C1E696414017D1F07AE7414040E4A31FBAB0964140EEF0BA9BEA414040B086CC188A964140AA7E49A1DF4140405F9E7415559641400E965971CE4140406C617CD6BE964140507D31473B4240407B9D78A6C4964140C78DBF4C41424040EC83D0BCCB96414024F0D79348424040800BB885EB964140B5EDD78270424040D329ADD528974140CC30AA0FBA424040788A32E730974140E1892543C242404038CE6EC239974140144CBFFABD42404072C335FB44974140E0A1F570B842404041E527A6549741401452C873B5424040E2AF232885974140870270D3A94240402ECF4F5896974140A60528B1A54240409136E1CDA1974140064DE033A9424040A1D0EA90AC974140B8DCF06AAC4240405057CF31F29741401AF3BD56BF42404002A55372FD97414073E1E5EAC44240401662B42406984140508E1581C74240407C160419109841400550EE08D1424040FB6CBB1121984140E61A2164E44240409DF119524B984140D07F97C1DA4240407A890FC4989841406EB9C869C642404040791ACCD2984140B909DA18C24240403A8E929CF598414002B710C8A1424040E339910313994140EA8BA36D814240409A263D772B994140CA3B471F78424040F0D8DE7332994140B7753BE46D424040B2B6FFA82F994140A366E6E94A4240409DE25EF42A9941405636C85C2E424040D6F6AF25709941400F6E3A7D374240406B4CBDDF5D9941403809908356424040D2285B73559941408D2B85209242404099351BDC57994140502087DED44240406BAF14245699414075493F27F4424040BB878D2969994140EEDC6CB31F434040A0E0613380994140BE6E13794A4340400F535195939941404763F82F6F43404037615C4388994140A20A60CD814340406B3AE41266994140486D1963AC434040B8851D25509941406BACA432E34340400CEB4AF257994140D60E09500A444040CF65CCAD6B9941404DDD48C829444040BA98E7B58C994140723FF9C942444040324B7F17AE99414026676DE354444040AB48F2EDCE994140806EDBA1464440400F3EAAA2E7994140C27D375231444040EA28734E019A4140E39F4C54EB43404053702C10179A4140980841F4A9434040061180BF2A9A4140E06A791990434040AD8869DB719A41405695D4C291434040F049DD868B9A4140FF08B248B8434040B6D9B81E959A4140ED549481E0434040D225A77BA39A4140FA5FB3F81B44404030959ED5D29A4140D607DFC034444040A6275AA2E49A41405360BFBA2F4440409817D67AF59A41406953AE7E264440405CCBD0AD029B41407B3620911944404079F71C8A0D9B4140090672F30644404030B761B9199B4140E30B7086F2434040681C5321129B4140D6909DB6D6434040C16F75300B9B41405F26324FAD434040269DDBFE069B414003E20EEF86434040F9BBA96AE79A4140382CC3F2594340408E731A45C69A4140A3585D423A434040A4A91618AA9A4140A95E61431E4340404992945D929A414089261A92FF42404069D621C8819A4140C4DCED8AE14240403C3E973A5A9A414054FF4F2CB9424040027AAE16309A4140EDF0806B954240402BB6565C389A414070246F62874240401CF32F895B9A4140D3E7EBE27F42404062CBF6ED6E9A41404969FEC86E42404073BA439A649A414041D7DBAE50424040B2B92A6B2B9A4140A58A4CF11A424040A6088CC7EF9941407C8EEB03D84140407319510FA99941403EBE32EC9A414040B0964A44819941409D76B05E9941404064816ED847994140290A0F37A8414040133090B05C994140B139FA46D5414040FF0EDE2975994140A1705A4B05424040D6E59000749941405A1CE4DA1042404082D140F4209941407E3A20EFFF414040A4313AC8F39841404AD0CA51F9414040D8D1885AE29841405D7E80E9DD414040B20F3115CD984140985C4624CD41404038D397799E9841409E048199E14140400AB736B380984140C259B961EC4140409D45793F68984140CEC4C1EAD7414040009A023F32984140E3CD0845A641404028E48E45EE9741402EA7B63287414040);

Verified with 5.1 bk tree.

mysql> CREATE TABLE `xxx` (
    ->   `ID` int(11) NOT NULL,
    ->   `ogc_geom` geometry NOT NULL default '',
    ->   PRIMARY KEY  (`ID`),
    ->   SPATIAL KEY `Spatial` (`ogc_geom`(32))
    -> ) ENGINE=MyISAM;
ERROR 1089 (HY000): Incorrect prefix key; the used key part isn't a string, the used length is longer than the key part, or the storage engine doesn't support unique prefix keys

Without default value:

mysql> CREATE TABLE `xxx` (   `ID` int(11) NOT NULL,   `ogc_geom` geometry NOT NULL,   PRIMARY KEY  (`ID`),   SPATIAL KEY `Spatial` (`ogc_geom`(32)) ) ENGINE=MyISAM;
ERROR 1089 (HY000): Incorrect prefix key; the used key part isn't a string, the used length is longer than the key part, or the storage engine doesn't support unique prefix keys

By the nature of the Spatial key it cannot be created on a part of a field.
So 'SPATIAL KEY `Spatial` (`ogc_geom`(32))' is truly illegal.
Otherwise 'SPATIAL KEY `Spatial` (`ogc_geom`)' - without prefix length - worked for me.

http://dev.mysql.com/doc/refman/5.1/en/create-index.html says this about SPATIAL column prefixes:

"
Spatial indexes (created using SPATIAL INDEX):

In MySQL 5.1, column prefix lengths are prohibited. The full width of each column is indexed.
"