MySQL Bugs: #46946: myisamchk -n corrupts table with spatial index

Bug #46946	myisamchk -n corrupts table with spatial index
Submitted:	26 Aug 2009 20:30	Modified:	27 Sep 2009 7:57
Reporter:	P R	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5+	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	corruption, gis, INDEX, myisam, MyISAMchk, Repair, spatial

Description:
running 'myisamchk -n' on a table with a spatial index appears to succeed, but in reality creates a spatial index that doesnt function properly & any attempt to run updates on the table will error out and mark it as crashed.

How to repeat:
1) create empty table, with geometry 'point' column, and also a spatial index on that point column

2)disable keys on the table
3)bulk load the table using 'LOAD DATA INFILE...'
4)run update statement to populate the point column:
'update gis_table set loc=pointfromwkb(point(lat,lon))'
- this assumes 2 additional table columns of type 'double' for lat & lon values

5)instead of running 'alter table ... enable keys' which will force a keycache repair due to the presence of the spatial index, use 'myisamchk -n' instead to force the filesort repair (see also bug #46945)...

- myisamchk will appear to succeed without any problems, yet pre-tested queries will produce empty sets when using the index, and attempting to re-run the 'update' statement from above on the point column will report an error about the index file not being correct, and subsequently mark the table as crashed.

Suggested fix:
fix internal functionality of the filesort repair mechanism(s) so that it supports repairing spatial indices correctly.

this seems related to the fact that filesort repairs have been purposely disabled for tables with spatial indices (see bug #46945).

marked as serious because it greatly affects user's ability to recover/repair tables with GIS data.

Thank you for the report.

I can not repeat described behavior. Please indicate accurate version of MySQL server you run and provide output of SHOW CREATE TABLE problem_table

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".