Bug #26794 Adding an index with a prefix on a SPATIAL type breaks ALTER TABLE
Submitted: 2 Mar 2007 14:17 Modified: 27 Mar 2007 20:35
Reporter: Baron Schwartz (Basic Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: MyISAM storage engine Severity:S2 (Serious)
Version:5.0.38-BK, 5.0.22 OS:Linux (Linux)
Assigned to: Georgi Kodinov CPU Architecture:Any
Tags: qc, spatial index prefix

[2 Mar 2007 14:17] Baron Schwartz
Description:
I was experimenting with spatial indexes and got to a point where ALTER TABLE will not work anymore on this table.  I added a non-spatial index on a prefix of a spatial data type.

The server is the official binary downloaded from MySQL.

How to repeat:
The following table 'barontest' has some extra indexes and columns not relevant to this bug report, but I don't want to remove them in case they have something to do with this.  Once I added the key on g(50), I can't add or drop columns.

mysql> show create table barontest\G
*************************** 1. row ***************************
       Table: barontest
Create Table: CREATE TABLE `barontest` (
  `client` smallint(5) unsigned NOT NULL,
  `txt` varchar(500) NOT NULL default '',
  PRIMARY KEY  (`client`),
  KEY `client` (`client`),
  KEY `client_2` (`client`),
  KEY `txt` (`txt`),
  FULLTEXT KEY `txt_2` (`txt`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> alter table barontest add g geometry not null, add spatial index(g);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table barontest\G
*************************** 1. row ***************************
       Table: barontest
Create Table: CREATE TABLE `barontest` (
  `client` smallint(5) unsigned NOT NULL,
  `txt` varchar(500) NOT NULL default '',
  `g` geometry NOT NULL,
  PRIMARY KEY  (`client`),
  KEY `client` (`client`),
  KEY `client_2` (`client`),
  KEY `txt` (`txt`),
  SPATIAL KEY `g` (`g`(32)),
  FULLTEXT KEY `txt_2` (`txt`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> alter table barontest add key(g(50));
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table barontest\G
*************************** 1. row ***************************
       Table: barontest
Create Table: CREATE TABLE `barontest` (
  `client` smallint(5) unsigned NOT NULL,
  `txt` varchar(500) NOT NULL default '',
  `g` geometry NOT NULL,
  PRIMARY KEY  (`client`),
  KEY `client` (`client`),
  KEY `client_2` (`client`),
  KEY `txt` (`txt`),
  SPATIAL KEY `g` (`g`(32)),
  KEY `g_2` (`g`(50)),
  FULLTEXT KEY `txt_2` (`txt`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> alter table barontest add p point, add key(p), add spatial key(p);
ERROR 1170 (42000): BLOB/TEXT column 'g' used in key specification without a key length
mysql> alter table barontest add p point, add key(p(50)), add spatial key(p);
ERROR 1170 (42000): BLOB/TEXT column 'g' used in key specification without a key length
mysql> alter table barontest add p point;
ERROR 1170 (42000): BLOB/TEXT column 'g' used in key specification without a key length
mysql> alter table barontest drop client, drop txt;
ERROR 1170 (42000): BLOB/TEXT column 'g' used in key specification without a key length
[3 Mar 2007 7:11] Valeriy Kravchuk
Thank you for a bug report. Verified just as described with 5.0.38-BK on Linux:

openxs@suse:~/dbs/5.0> bin/mysql -uroot test
Reading 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 1
Server version: 5.0.38 Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE TABLE `barontest` (
    ->   `client` smallint(5) unsigned NOT NULL,
    ->   `txt` varchar(500) NOT NULL default '',
    ->   PRIMARY KEY  (`client`),
    ->   KEY `client` (`client`),
    ->   KEY `client_2` (`client`),
    ->   KEY `txt` (`txt`),
    ->   FULLTEXT KEY `txt_2` (`txt`)
    -> ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.01 sec)

mysql> alter table barontest add g geometry not null, add spatial index(g);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table barontest\G
*************************** 1. row ***************************
       Table: barontest
Create Table: CREATE TABLE `barontest` (
  `client` smallint(5) unsigned NOT NULL,
  `txt` varchar(500) NOT NULL default '',
  `g` geometry NOT NULL,
  PRIMARY KEY  (`client`),
  KEY `client` (`client`),
  KEY `client_2` (`client`),
  KEY `txt` (`txt`),
  SPATIAL KEY `g` (`g`(32)),
  FULLTEXT KEY `txt_2` (`txt`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> alter table barontest add key(g(50));
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table barontest\G
*************************** 1. row ***************************
       Table: barontest
Create Table: CREATE TABLE `barontest` (
  `client` smallint(5) unsigned NOT NULL,
  `txt` varchar(500) NOT NULL default '',
  `g` geometry NOT NULL,
  PRIMARY KEY  (`client`),
  KEY `client` (`client`),
  KEY `client_2` (`client`),
  KEY `txt` (`txt`),
  SPATIAL KEY `g` (`g`(32)),
  KEY `g_2` (`g`(50)),
  FULLTEXT KEY `txt_2` (`txt`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> alter table barontest add p point, add key(p), add spatial key(p);
ERROR 1170 (42000): BLOB/TEXT column 'g' used in key specification without a key
 length
mysql> alter table barontest add p point, add key(p(50)), add spatial key(p);
ERROR 1170 (42000): BLOB/TEXT column 'g' used in key specification without a key
 length
mysql> alter table barontest add p point;
ERROR 1170 (42000): BLOB/TEXT column 'g' used in key specification without a key
 length
mysql> alter table barontest add p int;
ERROR 1170 (42000): BLOB/TEXT column 'g' used in key specification without a key
 length
mysql> alter table barontest drop client;
ERROR 1170 (42000): BLOB/TEXT column 'g' used in key specification without a key
 length

We should not allow ALTER TABLE that makes further modifications of table structure impossible.
[12 Mar 2007 14:58] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/21742

ChangeSet@1.2434, 2007-03-12 16:57:00+02:00, gkodinov@magare.gmz +6 -0
  Bug #26794:
  Different set of conditions is used to verify
  the validity of index definitions over a GEOMETRY
  column in ALTER TABLE and CREATE TABLE. 
  The difference was on how sub-keys notion validity
  is checked.
  Fixed by extending the CREATE TABLE condition to
  support the cases allowed in ALTER TABLE.
  Made the SHOW CREATE TABLE not to display spatial
  indexes using the sub-key notion.
[12 Mar 2007 16:49] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/21749

ChangeSet@1.2483, 2007-03-12 18:48:51+02:00, gkodinov@magare.gmz +5 -0
  Bug #26794: 5.1 part
   It was syntactically correct to define 
   spatial keys over parts of columns (e.g.
   ALTER TABLE t1 ADD x GEOMETRY NOT NULL, 
     ADD SPATIAL KEY (x(32))).
   This may lead to undefined results and/or
   interpretation.
   Fixed by not allowing partial column 
   specification in a SPATIAL index definition.
[14 Mar 2007 9:42] Sergei Glukhov
ok to push
[14 Mar 2007 17:43] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/21915

ChangeSet@1.2435, 2007-03-14 15:53:45+02:00, gkodinov@magare.gmz +1 -0
  Bug #26794: fixed valgraind problem
[14 Mar 2007 17:43] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/21916

ChangeSet@1.2478, 2007-03-14 15:58:14+02:00, gkodinov@magare.gmz +1 -0
  Bug #26794: fixed valgrind warning
[17 Mar 2007 18:00] Alexey Botchkov
Pushed in 5.0.40, 5.1.17
[27 Mar 2007 19:02] Paul Dubois
Noted in 5.0.40, 5.1.17 changelogs.

5.0.40:
Prefix lengths for columns in SPATIAL indexes are no longer displayed
in SHOW CREATE TABLE output. mysqldump uses that statement, so if a
table with SPATIAL indexes containing prefixed columns is dumped and
reloaded, the index is created with no prefixes. (The full column 
width of each column is indexed.)

5.1.17:
Prefix lengths for columns in SPATIAL indexes no longer can be
specified. For tables created in older versions of MySQL that have
SPATIAL indexes containing prefixed columns, dumping and reloading
the table causes the indexes to be created with no prefixes. (The
full column width of each column is indexed.)
[27 Mar 2007 20:35] Baron Schwartz
In response to Paul's note, is it correct that the full column is indexed?  I thought some of the spatial types were BLOBs and had to be prefixed.  (I am pretty ignorant of how the spatial indexes really work).
[28 Mar 2007 7:17] Georgi Kodinov
The whole column is indexed with SPATIAL indexes. 
Otherwise some of the spatial data types are BLOBs indeed and they must be prefixed when indexed in non-SPATIAL indexes.