Bug #23578 Corruption prevents Optimize table from working properly with a spatial index
Submitted: 24 Oct 2006 9:42 Modified: 16 Jan 2007 6:06
Reporter: jb Caraus Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S1 (Critical)
Version:5.0.25, 5.1-BK, 5.0-BK OS:Linux (Centos)
Assigned to: Georgi Kodinov
Tags: block, corruption, INDEX, Optimize, spatial

[24 Oct 2006 9:42] jb Caraus
Description:
Mysql fails to "optimize the table" with a spatial index on a POINT type column. Each time I try to optimize the table, I get: "Can't read key block from filepos: 723761592142120..." 

How to repeat:
I've created a table with a POINT type column, i've added a spatial index on this column and i've populated the table.

Each time i try to optimize the table, i get a: "Can't read key block from filepos: 723761592142120..."
[24 Oct 2006 9:48] Shane Bester
jb, whats the output of "SHOW CREATE TABLE <tablename>" ?
[24 Oct 2006 9:55] jb Caraus
CREATE TABLE `geo_town` (
  `gt_id` mediumint(8) unsigned NOT NULL auto_increment,
  `gt_country` mediumint(8) unsigned default NULL,
  `gt_r1` mediumint(8) unsigned default NULL,
  `gt_r2` mediumint(8) unsigned default NULL,
  `gt_r3` mediumint(8) unsigned default NULL,
  `gt_lat` decimal(9,5) default NULL,
  `gt_long` decimal(9,5) default NULL,
  `gt_point` point NOT NULL,
  `gt_postal` varchar(10) NOT NULL,
  `gt_name` varchar(100) default NULL,
  PRIMARY KEY  (`gt_id`),
  KEY `gt_postal` (`gt_postal`),
  KEY `gt_country` (`gt_country`),
  SPATIAL KEY `sp_index` (`gt_point`(32)),
  FULLTEXT KEY `gt_name` (`gt_name`)
) ENGINE=MyISAM AUTO_INCREMENT=594297 DEFAULT CHARSET=latin1 PACK_KEYS=1 CHECKSUM=1 AUTO_INCREMENT=594297 ;
[24 Oct 2006 10:34] Shane Bester
I managed to get a corrupted table using 5.0.24a

mysql> check table geo_town extended;
+---------------+-------+----------+-----------------------------------------------+
| Table         | Op    | Msg_type | Msg_text                                      |
+---------------+-------+----------+-----------------------------------------------+
| test.geo_town | check | error    | Record at: 4888  Can't find key for index:  4 |
| test.geo_town | check | error    | Corrupt                                       |
+---------------+-------+----------+-----------------------------------------------+

Am doing more tests on latest version - will post another comment later.
[24 Oct 2006 10:43] Shane Bester
repeated on 5.0.28-BK

mysql> optimize table geo_town\G
*************************** 1. row ***************************
   Table: test.geo_town
      Op: optimize
Msg_type: error
Msg_text: Can't read key block from filepos: 71987225293750272
*************************** 2. row ***************************
   Table: test.geo_town
      Op: optimize
Msg_type: status
Msg_text: OK
2 rows in set, 1 warning (0.94 sec)

Will upload a complete testcase later.
[24 Oct 2006 11:05] Shane Bester
testcase for corruption

Attachment: bug23578.sql (text/x-delimtext), 8.70 KiB.

[24 Oct 2006 11:07] Shane Bester
verfied corruption on 5.0BK and 5.1BK. Import the testcase attached.
[24 Oct 2006 11:14] jb Caraus
Glad that you've been able to reproduce the error. Are there any ways to avoid this bug, or to fix it temporarely ?
[25 Oct 2006 7:40] Shane Bester
jb, no avoidance as far as I can tell. perhaps the developers would suggest something when looking at the testcase (privately uploaded)
[19 Dec 2006 13:04] 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/17159

ChangeSet@1.2349, 2006-12-19 15:04:26+02:00, gkodinov@macbook.gmz +3 -0
  Bug #23578: Corruption prevents Optimize table from working properly with a
  spatial index
   While executing OPTIMIZE TABLE on MyISAM tables the server re-creates the
   index file(s) in order to sort them physically by the key. This cannot be 
   done for R-tree indexes as it makes no sense.
   The server was not checking the type of the index and was accessing an 
   R-tree index as if it was a B-tree.
   Fixed by preventing sorting the index file if it contains an R-tree index.
[11 Jan 2007 10:12] Alexey Botchkov
fixed in 5.0.34, 5.1.15
[16 Jan 2007 6:06] Jon Stephens
Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html

Documented bugfix in 5.0.34 and 5.1.5 changelogs. Noted limitation in OPTIMIZE TABLE Syntax section of 5.0/5.1 Manual.