Doyley Posted February 15, 2006 Share Posted February 15, 2006 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! Quote Link to comment Share on other sites More sharing options...
fenway Posted February 15, 2006 Share Posted February 15, 2006 Strange indeed -- and all checks come back OK after the repair maintenance? Quote Link to comment Share on other sites More sharing options...
Doyley Posted February 15, 2006 Author Share Posted February 15, 2006 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. Quote Link to comment Share on other sites More sharing options...
fenway Posted February 16, 2006 Share Posted February 16, 2006 Well, you've got me -- I have no idea. Quote Link to comment Share on other sites More sharing options...
AndyB Posted February 16, 2006 Share Posted February 16, 2006 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? Quote Link to comment Share on other sites More sharing options...
fenway Posted February 16, 2006 Share Posted February 16, 2006 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. Quote Link to comment Share on other sites More sharing options...
wickning1 Posted February 17, 2006 Share Posted February 17, 2006 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. Quote Link to comment Share on other sites More sharing options...
Doyley Posted February 17, 2006 Author Share Posted February 17, 2006 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. Quote Link to comment Share on other sites More sharing options...
fenway Posted February 17, 2006 Share Posted February 17, 2006 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)? Quote Link to comment Share on other sites More sharing options...
Doyley Posted February 25, 2006 Author Share Posted February 25, 2006 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. Quote Link to comment Share on other sites More sharing options...
wickning1 Posted February 25, 2006 Share Posted February 25, 2006 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. Quote Link to comment Share on other sites More sharing options...
Doyley Posted February 25, 2006 Author Share Posted February 25, 2006 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! Quote Link to comment Share on other sites More sharing options...
wickning1 Posted February 25, 2006 Share Posted February 25, 2006 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. Quote Link to comment Share on other sites More sharing options...
Doyley Posted February 28, 2006 Author Share Posted February 28, 2006 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. Quote Link to comment Share on other sites More sharing options...
wickning1 Posted February 28, 2006 Share Posted February 28, 2006 Most likely the hard drive. Quote Link to comment Share on other sites More sharing options...
Doyley Posted March 2, 2006 Author Share Posted March 2, 2006 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 :( Quote Link to comment Share on other sites More sharing options...
fenway Posted March 2, 2006 Share Posted March 2, 2006 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. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.