September 8th, 2012, 09:42 AM
Table corruption woes
I'm hoping my problem is resolved but I really don't like how its come about and was curious of anyone had any input.
I have a large mysql isam table, 1.3 gig with about 1.4 million rows.
We had to add a new column so we did a alter table, it took forever (like 4 hours) but after it finished the new field was there and the table was ok.
Then we started getting corruption, specifically
Incorrect key file for table './db/table.MYI'
Every time I took the db down and repaired the table it work, then once it came back up all you would have to do is a dozen or so updates or inserts and then you would get above error. I did "repair" and also "safe recover" and the results were the same, everything checked out and was fine for a little while.
After a few days of this, I did a repair and with a good table did a dump and then turned around and imported that in.
That has seemed to solve my problem. What bothers me is the MYI file is now more then twice the size (700meg vs 1.6 gig) and if this really fixed my problem then what was happening before when myisamchk would repair and the table be ok for for just a few mins?