myisamchk -n corrupts spatial index?
Posted by:
P R
Date: August 25, 2009 05:45PM
Im new to using the spatial extensions in mysql.
Im having an issue with my spatial index (& table) being corrupted/rendered useless after running myisamchk -n (--sort-recover) in order to quickly rebuild the table's indexes (including the spatial index in question).
mysql version 5.0.84 on fedora 10.
basic method:
1)create empty table, with a spatial index defined
2)alter table, disable keys
3)populate table in bulk using "LOAD DATA INFILE", approx 14.5million rows, 4GB database table
4)run 'update' statement to populate my spatial 'point' column based on values in the table's lat,lng columns
5)run 'myisamchk -n' on table in order to quickly rebuild & re-enable the indexes
-- I initially tried simply executing 'alter table enable keys', but mysql REFUSED to 'repair with filesort', and instead kept running a (very, very slow) 'repair with keycache'. Regardless of how I configured my server variables & how much free disk space I have, mysql will simply not use 'repair with filesort' with the 'enable keys' statement (I submitted a post in the 'performance' forum related to this problem; if someone could also help me out with this issue, I'd really appreciate it)
after 'myisamchk -n' completes, everything appears normal, but running any&all spatial queries produce empty results, and if I try to run update statements on the table, mysql errors out & complains that the index file is 'incorrect' and the table is subsequently marked as crashed...
NOTE: using the 'enable keys'/'repair with keycache' method hasn't produced this problem, and the indexes & queries work after the process completes. However, the problem is that 'repair with keycache' takes unacceptably long to complete.
so, my questions are:
- has anyone else experienced (or can verify) this problem with myisamchk & a spatial index?
- could this be a bug, or is this by design (i.e.myisamchk is not designed to properly handle rebuilding spatial indexes) ???
- what other options can someone please recommend in order rebuild my spatial index within a reasonable amount of time???
again, keep in mind, 14.5 million rows @ 4GB data on disk...index file is approx. under 500MB. 4GB RAM and hundreds of GB of free space spread across 5 physical disks.
Edited 1 time(s). Last edit at 08/25/2009 05:50PM by P R.