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! Link to comment https://forums.phpfreaks.com/topic/3440-unable-to-find-file/ 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? Link to comment https://forums.phpfreaks.com/topic/3440-unable-to-find-file/#findComment-11792 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. Link to comment https://forums.phpfreaks.com/topic/3440-unable-to-find-file/#findComment-11801 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. Link to comment https://forums.phpfreaks.com/topic/3440-unable-to-find-file/#findComment-11825 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? Link to comment https://forums.phpfreaks.com/topic/3440-unable-to-find-file/#findComment-11830 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. Link to comment https://forums.phpfreaks.com/topic/3440-unable-to-find-file/#findComment-11834 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. Link to comment https://forums.phpfreaks.com/topic/3440-unable-to-find-file/#findComment-11860 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. Link to comment https://forums.phpfreaks.com/topic/3440-unable-to-find-file/#findComment-11861 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)? Link to comment https://forums.phpfreaks.com/topic/3440-unable-to-find-file/#findComment-11879 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. Link to comment https://forums.phpfreaks.com/topic/3440-unable-to-find-file/#findComment-12394 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. Link to comment https://forums.phpfreaks.com/topic/3440-unable-to-find-file/#findComment-12406 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! Link to comment https://forums.phpfreaks.com/topic/3440-unable-to-find-file/#findComment-12468 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. Link to comment https://forums.phpfreaks.com/topic/3440-unable-to-find-file/#findComment-12484 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. Link to comment https://forums.phpfreaks.com/topic/3440-unable-to-find-file/#findComment-12963 Share on other sites More sharing options...
wickning1 Posted February 28, 2006 Share Posted February 28, 2006 Most likely the hard drive. Link to comment https://forums.phpfreaks.com/topic/3440-unable-to-find-file/#findComment-12982 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 :( Link to comment https://forums.phpfreaks.com/topic/3440-unable-to-find-file/#findComment-13634 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. Link to comment https://forums.phpfreaks.com/topic/3440-unable-to-find-file/#findComment-13648 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.