Lost table structure file (MYI)

[dmittner]

So I came to work this morning and instantly get hammered by a problem. That alone is quite ordinary, but the catch is that I'm having trouble figuring this one out, so here I am.

I have a table by the name of 'users' which is apprently unusable due to the apparent loss of it's .MYI file. When I try to access the table, it claims it can't find users.MYD, but upon checking the files, it's present and .MYI is not -- so I'm continuing under the assumption that it can't find .MYD due to the missing .MYI file.

So my question is thus: What would cause that file to be deleted? Access is limited on the server and root access even moreso, so I can't imagine it being manually deleted. Could it have been automatically removed if it breached it's set max keyfile length? But the even more important question is: can I correct the problem without losing all the information in the table?

Any help is greatly appreciated.

Thanks,
Dave Mittner

[Spookster]

http://www.mysql.com/doc/en/Repair.html

[dmittner]

Originally posted by Spookster
http://www.mysql.com/doc/en/Repair.html

I saw that page prior to posting here, but it didn't have anything that helps. The myisamchk tool seems to require the .MYI file to even begin assessing table problems, so since I don't have that file to begin with, that tool is unusable.

Unfortunately time didn't allow for extended research so I ended up just dropping what was left of that table and created it new. We just cut our losses and have begun population of it as normal.

[Spookster]

one word comes to mind here....ummmm. Backups are good.

[dmittner]

Originally posted by Spookster
one word comes to mind here....ummmm. Backups are good.

That's three words, unless you meant the "ummmm", which isn't even a word. But I agree. This happened on a system I just recently inhereted and I haven't yet had the opportunity (or means) to create regular backups. Needless to say, this little incident has helped me convince my superiors that changes are needed.

[johnnyb]

So, I'm having the same problem. However, it appears that the problem can be fixed by restarting the MySQL Server. This is somewhat of a pain as you may imagine and I would like to simply make it so my .MYI files stay intact.

Anyone have any idea what is causing them to dissapear?

Thanks in advance,

John

[raf]

what mySQL version are you running ?

[johnnyb]

4.0.18-standard

[raf]

Hmm. Been looking around a bit and this problem seems to be quite common. All sources say the same : run a repair or a myisamchk.

This http://dev.vikingnetwerkz.com/module...upt-table.html was the best doc i ould find. Contains a lott of links to additiobal info. Hope it helps.

[johnnyb]

Thanks. I read the page and I think it'll help. I've also discovered the MySQL Logs and I'm going to try to find out what the problem is next time it happens.

John