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: | |
Category: | MySQL Server | Severity: | S1 (Critical) |
Version: | 5.0.25, 5.1-BK, 5.0-BK | OS: | Linux (Centos) |
Assigned to: | Georgi Kodinov | CPU Architecture: | Any |
Tags: | block, corruption, INDEX, Optimize, spatial |
[24 Oct 2006 9:42]
jb Caraus
[24 Oct 2006 9:48]
MySQL Verification Team
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]
MySQL Verification Team
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]
MySQL Verification Team
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]
MySQL Verification Team
testcase for corruption
Attachment: bug23578.sql (text/x-delimtext), 8.70 KiB.
[24 Oct 2006 11:07]
MySQL Verification Team
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]
MySQL Verification Team
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.