Bug #16090 create SPATIAL INDEX in a table with UTF8 may cause the server crush
Submitted: 30 Dec 2005 3:18 Modified: 27 Jan 2006 17:10
Reporter: ming lu Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server Severity:S1 (Critical)
Version:5.0.12 OS:Microsoft Windows (windows2000 server)
Assigned to: CPU Architecture:Any

[30 Dec 2005 3:18] ming lu
Description:
create spatial index in a table with UTF8 character sets may cause the server crush. 
I do a same test with latin1 character sets, it works properly.

How to repeat:
drop database if exists testdb1;
create database testdb1;
use testdb1;
drop table if exists t1;
CREATE TABLE t1 (
  auto int(5) unsigned NOT NULL auto_increment,
  string char(100) default "hello" NOT NULL,
  tiny tinyint(4) DEFAULT '0' NOT NULL ,
  short smallint(6) DEFAULT '1' NOT NULL ,
  medium mediumint(8) DEFAULT '0' NOT NULL,
  long_int int(11) DEFAULT '0' NOT NULL,
  longlong bigint(13) DEFAULT '0' NOT NULL,
  real_float float(13,1) DEFAULT 0.0 NOT NULL,
  real_double double(16,4),
  utiny tinyint(3) unsigned DEFAULT '0' NOT NULL,
  ushort smallint(5) unsigned zerofill DEFAULT '00000' NOT NULL,
  umedium mediumint(8) unsigned DEFAULT '0' NOT NULL,
  ulong int(11) unsigned DEFAULT '0' NOT NULL,
  ulonglong bigint(13) unsigned DEFAULT '0' NOT NULL,
  time_stamp timestamp,
  date_field date,      
  time_field time,      
  date_time datetime,
  blob_col blob,
  tinyblob_col tinyblob,
  mediumblob_col mediumblob  not null default '',
  longblob_col longblob  not null default '',
  options enum('one','two','tree') not null ,
  flags set('one','two','tree') not null default '',
  PRIMARY KEY (auto),
  KEY (utiny),
  KEY (tiny),
  KEY (short),
  KEY any_name (medium),
  KEY (longlong),
  KEY (real_float),
  KEY (ushort),
  KEY (umedium),
  KEY (ulong),
  KEY (ulonglong,ulong),
  KEY (options,flags)
)engine=gssys;
insert into t1 values (10, 1,1,1,1,1,1,1,1,1,1,1,1,1,NULL,0,0,0,1,1,1,1,'one','one');
insert into t1 values (NULL,2,2,2,2,2,2,2,2,2,2,2,2,2,NULL,NULL,NULL,NULL,NULL,NULL,2,2,'two','two,one');
insert into t1 values (0,1/3,3,3,3,3,3,3,3,3,3,3,3,3,NULL,'19970303','10:10:10','19970303101010','','','','3',3,3);
insert into t1 values (0,-1,-1,-1,-1,-1,-1,-1,-1,-1,-1,-1,-1,-1,NULL,19970807,080706,19970403090807,-1,-1,-1,'-1',-1,-1);
insert into t1 values (0,-4294967295,-4294967295,-4294967295,-4294967295,-4294967295,-4294967295,-4294967295,-4294967295,-4294967295,-4294967295,-4294967295,-4294967295,-4294967295,NULL,0,0,0,-4294967295,-4294967295,-4294967295,'-4294967295',0,'one');
insert into t1 values (0,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,NULL,0,0,0,4294967295,4294967295,4294967295,'4294967295',0,0);
insert into t1 (tiny) values (1);

CREATE SPATIAL INDEX test3 on t1 ( string ) ;
the mysql server crush.

Note: the character sets i used is UTF8.
[30 Dec 2005 16:01] MySQL Verification Team
I was unable to repeat this issue. How you created a table with
engine=gssys ?

Thanks in advance.
[31 Dec 2005 1:37] ming lu
Sorry, the database engine should be 'innodb'.

I know that the spatial index can be supportted by MyISAM engine,  but the fatal error like server crush shouldn't occur when create the spatial index on other engines.
[27 Jan 2006 17:10] Jorge del Conde
I was unable to reproduce this under Win2K and XP using 5.0.18