Bug #46808 Misleading error message when trying to create a SPATIAL index on a BLOB column

• Submitted: 19 Aug 2009 11:34
• Modified: 1 Aug 2012 11:17
• Reporter: Lenz Grimmer
• Status: Duplicate
• Category: MySQL Server: GIS
• Severity: S3 (Non-critical)
• Version: 5.1.36/5.4
• OS: Any
• CPU Architecture: Any
• Tags: error, gis, spatial

[19 Aug 2009 11:34] Lenz Grimmer

Description:
When trying to create a spatial index on a column that is not of the "geometry" type, I get a misleading error message: "BLOB/TEXT column 'g' used in key specification without a key length" instead of a clear indication that the column type is wrong.

How to repeat:
lenz@localhost:test > create table geom (g blob) engine=MyISAM;
Query OK, 0 rows affected (0.01 sec)

lenz@localhost:test > describe geom;
+-------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------+------+-----+---------+-------+
| g     | blob | YES  |     | NULL    |       |
+-------+------+------+-----+---------+-------+
1 row in set (0.00 sec)

lenz@localhost:test > show create table geom;
+-------+-------------------------------------------------------------------------+
| Table | Create Table                                                            |
+-------+-------------------------------------------------------------------------+
| geom  | CREATE TABLE `geom` (
  `g` blob
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+-------+-------------------------------------------------------------------------+
1 row in set (0.00 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

Suggested fix:
Provide a more descriptive error message, stating that the index creation failed because of the column not being of type "geometry".

[19 Aug 2009 13:39] MySQL Verification Team

Thank you for the bug report. Verified as described.

c:\dbs>c:\dbs\5.4\bin\mysql -uroot --port=3540 --prompt="mysql 5.4 >"
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.4.4-alpha-Win X64 Source distribution

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

mysql 5.4 >create database h;
Query OK, 1 row affected (0.00 sec)

mysql 5.4 >use h
Database changed
mysql 5.4 >create table geom (g blob) engine=MyISAM;
Query OK, 0 rows affected (0.14 sec)

mysql 5.4 >alter table geom add spatial index(g);
ERROR 1170 (42000): BLOB/TEXT column 'g' used in key specification without a key length
mysql 5.4 >

[1 Aug 2012 11:17] Alexander Barkov

This bug was earlier fixed by the patch for:
Bug#50574 5.5.x allows spatial indexes on non-spatial columns

mysql> drop table if exists t1; create table t1 (g blob) engine=MyISAM; alter table t1 add spatial index(g);
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.04 sec)

ERROR 1687 (42000): A SPATIAL index may only contain a geometrical type column