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

• Submitted: 19 Aug 13:34
• Modified: 19 Aug 15:39
• Reporter: Lenz Grimmer
• Status: Verified
• Category: Server: GIS
• Severity: S3 (Non-critical)
• Version: 5.1.36/5.4
• OS: Any
• Tags: gis, spatial, error
• Triage: Triaged: D4 (Minor)

[19 Aug 13: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 15:39] Miguel Solorzano

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 >