| Bug #21708 | Simple query on table with spatial index crashes the server | ||
|---|---|---|---|
| Submitted: | 18 Aug 2006 4:51 | Modified: | 18 Aug 2006 11:33 |
| Reporter: | David Kornmann | Email Updates: | |
| Status: | Can't repeat | Impact on me: | |
| Category: | MySQL Server | Severity: | S1 (Critical) |
| Version: | 5.0.17-nt | OS: | |
| Assigned to: | MySQL Verification Team | CPU Architecture: | Any |
| Tags: | crash, server, spatial index | ||
[18 Aug 2006 11:33]
MySQL Verification Team
Thank you for the bug report. The server version you are reporting it
was able for to create the table like 5.0.18:
c:\mysql\bin>mysql -uroot test
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.18-nt
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> CREATE TABLE `boundary` (
-> `ID` int(10) unsigned NOT NULL auto_increment,
-> `code` smallint(5) unsigned NOT NULL,
-> `Shape` geometry NOT NULL,
-> `mbr` polygon NOT NULL,
-> PRIMARY KEY (`ID`),
-> UNIQUE KEY `code` (`code`),
-> SPATIAL KEY `mbr` (`mbr`(32))
-> ) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC;
Query OK, 0 rows affected (0.13 sec)
however currently with version 5.0.24 (latest release) isn't more
supported:
c:\mysql\bin>mysql -uroot test
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.24-community-nt
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> CREATE TABLE `boundary` (
-> `ID` int(10) unsigned NOT NULL auto_increment,
-> `code` smallint(5) unsigned NOT NULL,
-> `Shape` geometry NOT NULL,
-> `mbr` polygon NOT NULL,
-> PRIMARY KEY (`ID`),
-> UNIQUE KEY `code` (`code`),
-> SPATIAL KEY `mbr` (`mbr`(32))
-> ) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC;
ERROR 1464 (HY000): The used table type doesn't support SPATIAL indexes
could you please upgrade and dump/restore as MyISAM table.
If you still get the crash, please re-open this bug report.
Thanks in advance.
Description: Some similarities with #18299, but somewhat different. I created a simple table that contains the following columns: * a column of type INT, UNSIGNED, AUTOINCREMENT that is used as primary key. * a column of type INT, UNSIGNED that is used to store a foreign key. * a column of type GEOMETRY that contains the MBRs of the geometry stored in a 4 th column. * a column of type GEOMETRY that contains polygons of 100+ vertices with no holes. Then I created a spatial index on the mbr column for fast search. The table contains roughly 20k entries. If I run the following query without the index, it returns values successfully. If I run it with when the index is created, it crashes the server. select b.id from boundary b where contains(b.mbr, geomfromtext('POINT(-122 40)') ) limit 5; The server error log gives the following information: 060816 20:29:21 InnoDB: Started; log sequence number 0 2588885876 060816 20:29:22 [Note] C:\Program Files\MySQL\MySQL Server 5.0\bin\mysqld-nt: ready for connections. Version: '5.0.17-nt' socket: '' port: 3306 MySQL Community Edition (GPL) 060817 12:23:41 InnoDB: Warning: using a partial-field key prefix in search. InnoDB: index `mbr` of table `coder/boundary`. Last data field length 34 bytes, InnoDB: key ptr now exceeds key end by 2 bytes. InnoDB: Key value in the MySQL format: 060817 12:38:25 InnoDB: Database was not shut down normally! InnoDB: Starting crash recovery. As soon as I delete the spatial index, the query works back again, but it very slow (not surprised about that) How to repeat: Here is the table definition: CREATE TABLE `boundary` ( `ID` int(10) unsigned NOT NULL auto_increment, `code` smallint(5) unsigned NOT NULL, `Shape` geometry NOT NULL, `mbr` polygon NOT NULL, PRIMARY KEY (`ID`), UNIQUE KEY `code` (`code`), SPATIAL KEY `mbr` (`mbr`(32)) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC I can also provide the content of mbr if you need it.