| 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: | |
| 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 | ||
[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.
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