Jump to content


Photo

Unable to find file


  • Please log in to reply
16 replies to this topic

#1 Doyley

Doyley
  • Members
  • PipPipPip
  • Advanced Member
  • 69 posts

Posted 15 February 2006 - 06:48 PM

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!

#2 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 15 February 2006 - 07:11 PM

Strange indeed -- and all checks come back OK after the repair maintenance?
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#3 Doyley

Doyley
  • Members
  • PipPipPip
  • Advanced Member
  • 69 posts

Posted 15 February 2006 - 09:39 PM

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.

#4 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 16 February 2006 - 07:47 PM

Well, you've got me -- I have no idea.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#5 AndyB

AndyB
  • Staff Alumni
  • Advanced Member
  • 5,465 posts
  • LocationToronto

Posted 16 February 2006 - 08:27 PM

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?
Legend has it that reading the manual never killed anyone.
My site

#6 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 16 February 2006 - 10:09 PM

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.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#7 wickning1

wickning1
  • Members
  • PipPipPip
  • Advanced Member
  • 405 posts

Posted 17 February 2006 - 02:29 PM

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.

#8 Doyley

Doyley
  • Members
  • PipPipPip
  • Advanced Member
  • 69 posts

Posted 17 February 2006 - 02:37 PM

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.

#9 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 17 February 2006 - 08:58 PM

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)?
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#10 Doyley

Doyley
  • Members
  • PipPipPip
  • Advanced Member
  • 69 posts

Posted 25 February 2006 - 01:28 AM

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.

#11 wickning1

wickning1
  • Members
  • PipPipPip
  • Advanced Member
  • 405 posts

Posted 25 February 2006 - 08:46 AM

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.

#12 Doyley

Doyley
  • Members
  • PipPipPip
  • Advanced Member
  • 69 posts

Posted 25 February 2006 - 09:42 PM

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!

#13 wickning1

wickning1
  • Members
  • PipPipPip
  • Advanced Member
  • 405 posts

Posted 25 February 2006 - 11:49 PM

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.

#14 Doyley

Doyley
  • Members
  • PipPipPip
  • Advanced Member
  • 69 posts

Posted 28 February 2006 - 08:10 AM

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.

#15 wickning1

wickning1
  • Members
  • PipPipPip
  • Advanced Member
  • 405 posts

Posted 28 February 2006 - 12:47 PM

Most likely the hard drive.

#16 Doyley

Doyley
  • Members
  • PipPipPip
  • Advanced Member
  • 69 posts

Posted 02 March 2006 - 07:18 PM

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 :(

#17 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 02 March 2006 - 07:40 PM

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.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users