Bug #30284 spatial key corruption
Submitted: 7 Aug 2007 19:53 Modified: 15 Jan 2008 16:30
Reporter: Shane Bester (Platinum Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: MyISAM storage engine Severity:S2 (Serious)
Version:5.0.45,5.1.22BK OS:Any
Assigned to: Alexey Botchkov CPU Architecture:Any
Tags: corruption, myisam, spatial

[7 Aug 2007 19:53] Shane Bester 
Description:
Server version: 5.1.22-beta Source distribution

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

mysql> drop table if exists `t1`;
Query OK, 0 rows affected, 1 warning (0.44 sec)

mysql> create table `t1` (`a` geometry not null, spatial index(`a`))engine=myisam;
Query OK, 0 rows affected (1.42 sec)

mysql> alter table `t1` add index(`a`(1));
Query OK, 0 rows affected (3.35 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> insert into `t1`(`a`) values (GeomFromWKB(0x0101000000000000000000F03F000000000000F03f));
Query OK, 1 row affected (1.11 sec)

mysql> insert into `t1`(`a`) values (GeomFromWKB(0x0101000000000000000000F03F000000000000F03f));
Query OK, 1 row affected (0.33 sec)

mysql> check table `t1` extended;
+---------+-------+----------+------------------------------------------------+
| Table   | Op    | Msg_type | Msg_text                                       |
+---------+-------+----------+------------------------------------------------+
| test.t1 | check | error    | Key 2 doesn't point at same records that key 1 |
| test.t1 | check | error    | Corrupt                                        |
+---------+-------+----------+------------------------------------------------+
2 rows in set (0.25 sec)

How to repeat:
drop table if exists `t1`;
create table `t1` (`a` geometry not null, spatial index(`a`))engine=myisam;
alter table `t1` add index(`a`(1));
insert into `t1`(`a`) values (GeomFromWKB(0x0101000000000000000000F03F000000000000F03f));
insert into `t1`(`a`) values (GeomFromWKB(0x0101000000000000000000F03F000000000000F03f));
check table `t1` extended;

Suggested fix:
.
[12 Sep 2007 1:10] MySQL Verification Team 
nicer testcase:
----------------

drop table if exists rainbow_spatial;
create table rainbow_spatial(col1 geometry not null,col15 geometrycollection not null,spatial index(col15),index(col1(15)))engine=myisam;
insert into rainbow_spatial set col15 = GeomFromText('POINT(6 5)');
insert into rainbow_spatial set col15 = GeomFromText('POINT(6 5)');
check table rainbow_spatial extended;
[19 Nov 2007 7:08] 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/38030

ChangeSet@1.2696, 2007-11-19 11:03:03+04:00, holyfoot@mysql.com +3 -0
  Bug #30284 spatial key corruption.
  
  SPATIAL key is fine actually, but the chk_key() function
  mistakenly returns error. It tries to compare checksums
  of btree and SPATIAL keys while the checksum for the SPATIAL isn't
  calculated (always 0). Same thing with FULLTEXT keys is handled
  using full_text_keys counter, so fixed by counting both
  SPATIAL and FULLTEXT keys in that counter.
[23 Nov 2007 6:28] Alexander Barkov 
The patch http://lists.mysql.com/commits/38030 is ok to push.
[14 Dec 2007 8:13] Bugs System 
Pushed into 4.1.24
[14 Dec 2007 8:15] Bugs System 
Pushed into 5.0.54
[14 Dec 2007 8:19] Bugs System 
Pushed into 5.1.23-rc
[14 Dec 2007 8:22] Bugs System 
Pushed into 6.0.5-alpha
[15 Jan 2008 16:30] MC Brown 
A note has been added to the 4.1.24, 5.0.54, 5.1.23, 6.0.5 changelogs: 

Tables with a GEOMETRY column could be marked as corrupt if you added a non-SPATIAL index on a GEOMETRY column.