| Bug #17877 | Corrupted spatial index | ||
|---|---|---|---|
| Submitted: | 2 Mar 2006 21:23 | Modified: | 11 Jul 2006 8:11 |
| Reporter: | Michael Jiang | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: MyISAM storage engine | Severity: | S2 (Serious) |
| Version: | 4.1.15-standard | OS: | Linux (RHEL3 (x86)) |
| Assigned to: | Ingo Strüwing | CPU Architecture: | Any |
| Tags: | corruption, myisam, spatial | ||
[3 Mar 2006 12:15]
Hartmut Holzgraefe
verified on 5.0bk:
single row test case:
DROP TABLE IF EXISTS `tempTable`;
CREATE TABLE `tempTable` (
`geometry` geometry NOT NULL default '',
SPATIAL KEY `gndx` (`geometry`(32))
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO tempTable (geometry) VALUES (
PolygonFromText('POLYGON((-18.6086111000 -66.9327777000, -18.6055555000
-66.8158332999, -18.7186111000 -66.8102777000, -18.7211111000 -66.9269443999,
-18.6086111000 -66.9327777000))'));
CHECK TABLE tempTable EXTENDED;
+----------------+-------+----------+--------------------------------------------+
| Table | Op | Msg_type | Msg_text |
+----------------+-------+----------+--------------------------------------------+
| test.tempTable | check | error | Record at: 0 Can't find key for index: 1 |
| test.tempTable | check | error | Corrupt |
+----------------+-------+----------+--------------------------------------------+
After this a 2nd INSERT reults in:
ERROR 145 (HY000): Table './test/tempTable' is marked as crashed and should be repaired
but when INSERTing the two rows right away and only checking the table after that all works fine
mysql> drop table tempTable;
Query OK, 0 rows affected (0.05 sec)
[14 Jun 2006 11:34]
Ingo Strüwing
A wrong comparison operator was used for table checking. The result was that it checked for non-matching spatial keys. This succeeded if at least two different keys were present, but failed if only the matching key was present. Unfortunately it is not checked if the index entry points to the record from which the key was generated. Otherwise a different check result would have been printed. So this test case does not show the existence of a bug in spatial index handling, just in checking. The following patch will fix the table checking.
[14 Jun 2006 12:02]
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/7628
[14 Jun 2006 19:58]
Ingo Strüwing
The first patch was not appropriate for non-unique indexes.
[15 Jun 2006 5:37]
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/7683
[28 Jun 2006 12:30]
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/8406
[29 Jun 2006 12:07]
Ingo Strüwing
Pushed to mysql-5.0-engines.
[6 Jul 2006 13:48]
Ingo Strüwing
Pushed to mysql-5.1-engines.
[8 Jul 2006 19:09]
Ingo Strüwing
CHECK TABLE could complain about a fully intact spatial index. A wrong comparison operator was used for table checking. The result was that it checked for non-matching spatial keys. This succeeded if at least two different keys were present, but failed if only the matching key was present. I fixed the key comparison. Pushed to 5.1.12 and 5.0.24 and 4.1.21.
[11 Jul 2006 8:11]
MC Brown
Noted in the 4.1, 5.0 and 5.1 changelogs:
Checking a spatial table (using <literal>CHECK
TABLE</literal>) with an index and only one row
would indicate a table corruption. (Bug #17877)
[13 Jul 2006 3:34]
Paul DuBois
5.0.x fix went to 5.0.25 instead.
[9 Oct 2006 2:34]
Jon Stephens
Updated changelog entries per Support request (JamesD).
Description: We try to use spatial index and have problem with table corruptions. I searched your bug library and found #9645 might be a fix for us. After we upgraded our MySQL DB from 4.1.9 to 4.1.15, the table still keeps corrupting. So I followed the statements in #9645 report, and did some test. Then I found the problem is still there. How to repeat: Following is copied from #9645 report: ========================================= DROP TABLE IF EXISTS `tempTable`; CREATE TABLE `tempTable` (`geometry` geometry NOT NULL default '',SPATIAL KEY `gndx` (`geometry`(32))) ENGINE=MyISAM DEFAULT CHARSET=latin1; INSERT INTO tempTable (geometry) VALUES (PolygonFromText('POLYGON((-18.6086111000 -66.9327777000, -18.6055555000 -66.8158332999, -18.7186111000 -66.8102777000, -18.7211111000 -66.9269443999, -18.6086111000 -66.9327777000))')); INSERT INTO tempTable (geometry) VALUES (PolygonFromText('POLYGON((-65.7402776999 -96.6686111000, -65.7372222000 -96.5516666000, -65.8502777000 -96.5461111000, -65.8527777000 -96.6627777000, -65.7402776999 -96.6686111000))')); INSERT INTO tempTable (geometry) VALUES (PolygonFromText('POLYGON((70.6624999999 34.7755555000, 70.6650000000 34.8924998999, 70.5519443999 34.8975000000, 70.5494443999 34.7808332999, 70.6624999999 34.7755555000))')); ============================================== If you perform all the insert, then it shows you a OK. But if you only perform the first insert, then do a check table extended. It shows table corrupted.