Jump to content

Archived

This topic is now archived and is closed to further replies.

Doyley

Unable to find file

Recommended Posts

Hi all,

We are having problems with our mysql database. Random sites display the message "Unable to open file blahblah.MYI"

Now I know normally this can be sorted by repairing the table. This helps temporarily but the problem then appears somewhere else.

I created a php script to go through every table in every database and repair then optimize each table. I run that but we are still getting the random errors.

Does anybody have any ideas?

Thanks!

Share this post


Link to post
Share on other sites
Strange indeed -- and all checks come back OK after the repair maintenance?

Share this post


Link to post
Share on other sites
Yeah all seems fine when I run the repair.

We had a similar problem a few months back but it wasnt as bad as this. It seemed to sort itself out.

Its extremely frustrating as im sure you can imagine.

Share this post


Link to post
Share on other sites
Well, you've got me -- I have no idea.

Share this post


Link to post
Share on other sites
Assuming this isn't a dedicated server you control, what does the host have to say?

Just a question that one of the MySQL gurus can answer. What error message/erratic behaviour would a database generate when the MySQL server is tragically overloaded?

Share this post


Link to post
Share on other sites
I guess anything's possible if the server is being thrashed constantly, but IMHO, there would be far more obvious problems than simply the odd table index failing.

Share this post


Link to post
Share on other sites
That's not normal behavior even if you're overloaded.

These are the repairs I would attempt, in order:

Try dumping all the data into a backup file with mysqldump and then use it to create fresh tables with all the same data.

Try dropping the whole database and recreating it from backup.

Try reinstalling mysql.

Try another hard drive.

Try another server.

Share this post


Link to post
Share on other sites
Thanks for the advice all.

It is a dedicated server. We are getting Error no 24.

I will try the dump and recreate the tables but there are about a hundred databases and several of them are huge so it may take a while.

Share this post


Link to post
Share on other sites
I'm not a proponent of giving up and re-installing everything -- mainly since it doesn't yield an explanation for the behaviour you describe.

Are these MyISAM tables or InnoDB tables? Is there nothing suspicious in the error log? What version of MySQL are you running (there are some known bugs regarding errno 24)?

Share this post


Link to post
Share on other sites
Thanks for all your help folks... Im dumping the databases now...this will take a while as they are all pretty big.

I think I know how it has happened... My fault... We moved the databases from another server a while back and I was lazy and just copied the folders from the mysql data folder to the data folder on the new server. It worked but I think over time it has caused errors.

D'oh, I wont be as lazy in future and do things the proper way.

Ill let you know how I get on for future reference.

Share this post


Link to post
Share on other sites
I suspected that you'd done something like that, that's why I recommended the dump & restore. Theoretically you should be able to copy the files, but there are several known issues when you do it between different versions, especially between 4 and 5. The bigger the version jump, the more likely to have problems.

Share this post


Link to post
Share on other sites
They were the same mysql versions.

Anyway, I dumped the DB, removed MySQL and reinstalled everything. This hasnt helped.

I noticed when restoring the data that a few of the databases have invalid column names such as "fulltext" "date" etc etc. They errored out when I was trying to restore them. Could these be the cause of the problem?

Thanks!

Share this post


Link to post
Share on other sites
They are reserved words, they need to be backticked (`columnname`). That will not cause random instability though. I'm leaning towards hardware failure on this one, but you can try a fresh MySQL installation.

Share this post


Link to post
Share on other sites
Yeah I completely removed MySQL and all the folders etc when doing the database dump but that hasnt helped.

What hardware would cause them sort of errors?

Many thanks for all you help with this.

Share this post


Link to post
Share on other sites
Ive contacted the technical support team that manage our server and they have said there is nothing wrong with the hardware and it will be a software fault :(

Share this post


Link to post
Share on other sites
Going back to an earlier post -- you said Error 24. That's mysql complaining about "too many open files". This sounds like there aren't enough file descriptors. I suggest you following the directions in [a href=\"http://dev.mysql.com/doc/refman/5.0/en/not-enough-file-handles.html\" target=\"_blank\"]this relevant refman page[/a] and take it from there. These are easy things to try and change, so go for it, and report back.

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.