Hi,
James Allen Wrote:
-------------------------------------------------------
> I am trying to add a spatial index to a table
> column named Location of type BLOB. If I try
> this:
>
> ALTER TABLE route ADD SPATIAL
> INDEX(Location);
>
> I get:
>
> #Error: BLOB/TEXT column 'Location' used in key
> specification without a key length
>
> But in the official docs for MySql 5.1 (the
> version I am using), it clearly says:
>
> "In MySQL 5.1, column prefix lengths are
> prohibited. The full width of each column is
> indexed."
>
> This surely says that I don't need to provide a
> prefix. I tried adding a prefix anyway like this:
>
> ALTER TABLE route ADD SPATIAL
> INDEX(Location(256));
>
> And I get:
>
> #Error: 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
>
> So what the heck is going on?? For info, I am
> using MySQL 5.1.37 community, and my table is
> MyISAM, this is the create statement:
>
> CREATE TABLE `climb`.`route` (
> `Id` int(11) NOT NULL,
> `Name` varchar(255) NOT NULL,
> `Location` blob,
> PRIMARY KEY (`Id`),
> ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
The error message is a bit misleading. What it is trying to tell you is that it's not possible to add a SPATIAL index to a BLOB column - it needs to be of the type GEOMETRY (as documented in the manual here:
http://dev.mysql.com/doc/refman/5.1/en/optimizing-spatial-analysis.html):
lenz@localhost:test > create table geom (g blob);
Query OK, 0 rows affected (0.16 sec)
lenz@localhost:test > describe geom;
+-------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------+------+-----+---------+-------+
| g | blob | YES | | NULL | |
+-------+------+------+-----+---------+-------+
1 row in set (0.12 sec)
lenz@localhost:test > alter table geom add spatial index(g);
ERROR 1170 (42000): BLOB/TEXT column 'g' used in key specification without a key length
lenz@localhost:test > drop table geom;
Query OK, 0 rows affected (0.00 sec)
lenz@localhost:test > create table geom (g geometry not null);
Query OK, 0 rows affected (0.01 sec)
lenz@localhost:test > describe geom;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| g | geometry | NO | | NULL | |
+-------+----------+------+-----+---------+-------+
1 row in set (0.00 sec)
lenz@localhost:test > alter table geom add spatial index(g);
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
lenz@localhost:test > describe geom;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| g | geometry | NO | MUL | NULL | |
+-------+----------+------+-----+---------+-------+
1 row in set (0.00 sec)
So it works, if you use the correct column type "geometry".
I've submitted a bug report about this - the error message should be less confusing:
http://bugs.mysql.com/46808
Bye,
LenZ
Lenz Grimmer - MySQL Community Relations Manager -
http://de.sun.com/
Sun Microsystems GmbH, Sonnenallee 1, 85551 Kirchheim-Heimstetten, DE
Geschaeftsfuehrer: Thomas Schroeder, Wolfgang Engels
Vorsitz d. Aufsichtsrates: Martin Haering AG Muenchen: HRB161028