Bug #9645 Corrupted spatial index
Submitted: 5 Apr 2005 12:40 Modified: 31 Aug 2005 19:54
Reporter: [ name withheld ] Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: MyISAM storage engine Severity:S3 (Non-critical)
Version:4.1.7-debug OS:Linux (Linux 2.6.10, Solaris 8)
Assigned to: Alexey Botchkov CPU Architecture:Any
Tags: corruption, myisam, spatial

[5 Apr 2005 12:40] [ name withheld ]
Description:
When using a spatial index, the index becomes corrupted.  There are dozens of operations being performed on the table, updates, deletes, inserts.  It hasn't been possible to determine when the table becomes corrupted.  I've tried to use the binary logging to re-create the problem, but so far no luck.  A typical error message is...

mysql> check table querySupData extended;
+--------------------+-------+----------+----------------------------------------------------------------+
| Table              | Op    | Msg_type | Msg_text                                                       |
+--------------------+-------+----------+----------------------------------------------------------------+
| myOps.querySupData | check | warning  | Table is marked as crashed                                     |
| myOps.querySupData | check | error    | Found key at page 63488 that points to record outside datafile |
| myOps.querySupData | check | error    | Corrupt                                                        |
+--------------------+-------+----------+----------------------------------------------------------------+
3 rows in set (0.01 sec)

When running mysqld in debug an error is returned from line 664 of mi_check.c, and the trace file shows the following:

T@-1294939: | | | | | | | >chk_index
T@-1294939: | | | | | | | | buff: Memory: b2d091f0  Bytes: (756)
02 F4 28 30 34 41 50 52 30 35 4D 30 31 34 30 32 33 38 38 41 41 30 30 30 30 30
5F 30 31 30 30 30 30 31 41 41 30 39 30 30 30 31 33 77 61 72 70 20 20 20 00 05
78 00 8D 1B 34 37 36 41 41 30 30 30 30 30 5F 30 31 30 30 30 30 31 41 41 30 39
30 30 30 31 33 77 61 72 70 20 20 20 00 04 D6 C0 8D 1B 35 31 31 41 41 30 30 30
30 30 5F 30 31 30 30 30 30 31 41 41 30 39 30 30 30 31 33 77 61 72 70 20 20 20
00 02 49 00 8E 1A 36 34 41 41 30 30 30 30 30 5F 30 31 30 30 30 30 31 41 41 30
39 30 30 30 31 33 77 61 72 70 20 20 20 00 04 BC 80 8E 1A 38 35 41 41 30 30 30
30 30 5F 30 31 30 30 30 30 31 41 41 30 39 30 30 30 31 33 77 61 72 70 20 20 20
00 04 E0 C0 8D 1B 36 35 32 41 41 30 30 30 30 30 5F 30 31 30 30 30 30 31 41 41
30 39 30 30 30 31 33 77 61 72 70 20 20 20 00 05 15 40 8F 19 38 41 41 30 30 30
30 30 5F 30 31 30 30 30 30 31 41 41 30 39 30 30 30 31 33 77 61 72 70 20 20 20
00 05 1E 00 8E 1A 36 39 41 41 30 30 30 30 30 5F 30 31 30 30 30 30 31 41 41 30
39 30 30 30 31 33 77 61 72 70 20 20 20 00 02 4A 40 8E 1A 38 36 41 41 30 30 30
30 30 5F 30 31 30 30 30 30 31 41 41 30 39 30 30 30 31 33 77 61 72 70 20 20 20
00 04 2B 80 8D 1B 37 30 31 41 41 30 30 30 30 30 5F 30 31 30 30 30 30 31 41 41
30 39 30 30 30 31 33 77 61 72 70 20 20 20 00 02 B4 80 8F 19 32 41 41 30 30 30
30 30 5F 30 31 30 30 30 30 31 41 41 30 39 30 30 30 31 33 77 61 72 70 20 20 20
00 04 1B 40 8F 19 35 41 41 30 30 30 30 30 5F 30 31 30 30 30 30 31 41 41 30 39
30 30 30 31 33 77 61 72 70 20 20 20 00 04 3E 40 8E 1A 32 32 41 41 30 30 30 30
30 5F 30 31 30 30 30 30 31 41 41 30 39 30 30 30 31 33 77 61 72 70 20 20 20 00
04 4E 80 8F 19 37 41 41 30 30 30 30 30 5F 30 31 30 30 30 30 31 41 41 30 39 30
30 30 31 33 77 61 72 70 20 20 20 00 04 98 40 8F 19 38 41 41 30 30 30 30 30 5F
30 31 30 30 30 30 31 41 41 30 39 30 30 30 31 33 77 61 72 70 20 20 20 00 05 B7
C0 8E 1A 34 37 41 41 30 30 30 30 30 5F 30 31 30 30 30 30 31 41 41 30 39 30 30
30 31 33 77 61 72 70 20 20 20 00 05 CD 00 8E 1A 36 33 41 41 30 30 30 30 30 5F
30 31 30 30 30 30 31 41 41 30 39 30 30 30 31 33 77 61 72 70 20 20 20 00 05 49
C0 8D 1B 38 36 32 41 41 30 30 30 30 30 5F 30 31 30 30 30 30 31 41 41 30 39 30
30 30 31 33 77 61 72 70 20 20 20 00 01 D2 40 8E 1A 37 33 41 41 30 30 30 30 30
5F 30 31 30 30 30 30 31 41 41 30 39 30 30 30 31 33 77 61 72 70 20 20 20 00 01
DD 80
T@-1294939: | | | | | | | | error: message: Found key at page 107520 that points to record outside datafile
T@-1294939: | | | | | | | | >_myfree
T@-1294939: | | | | | | | | | enter: ptr: 0x8afdbf8
T@-1294939: | | | | | | | | <_myfree
T@-1294939: | | | | | | | | >_mymalloc
T@-1294939: | | | | | | | | | enter: Size: 64
T@-1294939: | | | | | | | | | exit: ptr: 0x8afdbf8
T@-1294939: | | | | | | | | <_mymalloc
T@-1294939: | | | | | | | | >Protocol::write
T@-1294939: | | | | | | | | <Protocol::write
T@-1294939: | | | | | | | | packet_header: Memory: b2d082f4  Bytes: (4)
5F 00 00 09
T@-1294939: | | | | | | | | test: page: 107520  record: 380160  filelength: 380160
T@-1294939: | | | | | | | | key: Memory: b2d08ce0  Bytes: (52)
28 30 34 41 50 52 30 35 4D 30 31 34 30 32 37 34 37 41 41 30 30 30 30 30 5F 30
31 30 30 30 30 31 41 41 30 39 30 30 30 31 33 77 61 72 70 20 20 20 00 05 CD 00
T@-1294939: | | | | | | | | new_in_page: Memory: b2d09447  Bytes: (39)
8E 1A 34 37 41 41 30 30 30 30 30 5F 30 31 30 30 30 30 31 41 41 30 39 30 30 30
31 33 77 61 72 70 20 20 20 00 05 CD 00
T@-1294939: | | | | | | | <chk_index

For some reason the record is equal to the data file length?

How to repeat:
Haven't written a repeatable test case.

Suggested fix:
None
[5 Apr 2005 14:46] MySQL Verification Team
Not enough information was provided for us to be able
to handle this bug. Please re-read the instructions at
http://bugs.mysql.com/how-to-report.php

If you can provide more information, feel free to add it
to this bug and change the status back to 'Open'.

Thank you for your interest in MySQL.
[8 Apr 2005 12:22] [ name withheld ]
The following should reproduce the problem:

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 a "check table tempTable extended;" after the first record is inserted it returns an OK status, but after the 2nd record it returns "corrupted".
[8 Apr 2005 18:42] MySQL Verification Team
mysql> 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))'));
Query OK, 1 row affected (0.00 sec)

mysql> check table tempTable extended;
+----------------+-------+----------+----------------------------------------------+
| Table          | Op    | Msg_type | Msg_text                                     |
+----------------+-------+----------+----------------------------------------------+
| test.tempTable | check | error    | Record at: 108  Can't find key for index:  1 |
| test.tempTable | check | error    | Corrupt                                      |
+----------------+-------+----------+----------------------------------------------+
2 rows in set (0.01 sec)

Thank you for the feedback.
[20 Apr 2005 12:43] [ name withheld ]
I don't know if this helps, but here are some other symptoms that may be related to this bug.  We've seen problems where the spatial index becomes corrupted and select statements using that index return an incomplete set of data.  As a check we've had the select statements ignore the index, and get more records returned.  It might be that the index is corrupted before the mi_check.c::chk_index notices the problem.  Also we've noticed that other indexes (non-spatial) have become corrupted, but we haven't been able to repeat those as easily.  We were wondering if some of these problems might be related to using myISAM tables?  Do you know if the spatial extensions will be available in other table types? Thanks.
[4 Aug 2005 16:38] 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/internals/27898
[27 Aug 2005 15:25] Alexey Botchkov
pushed into 4.1.15
[31 Aug 2005 19:54] Paul DuBois
Noted in 4.1.15 changelog.