Page 1 of 1

Getting to many MySQL file corrupt errors

Posted: Wed Apr 29, 2015 8:51 am
by bowlesj
Hi, Before I market my website I am populating a few database files manually. So I am adding maybe 100 records a day to some of these files. I am getting a lot of MySQL (MyIsam) corruptions that I am fixing with the "myisamchk -r" command. I read that MySQL databases corrupt easily so at first I did not think much of it. But now I am realizing that I am getting them way more than would seem normal. In other words if I add 100 records I can be sure that I will need to do some fixes. So now I am thinking maybe I have done something wrong. I did some google searches for trouble shooting MySQL errors and that went nowhere. One of the errors suggested the file was not properly closed. So I tried a bunch of google searches thinking I needed a close command after every access of the tables (somewhere I read that I don't need to do this). Anyway, these google searches did not find a close statement. I even found a list of MySQL statements and a second list of MySQL commands and did not find a close statement when searching the lists. So for now (as a temp measure) I have created a batch file to fix all the files regardless of whether they need fixing or not. Years back I wrote a bash script which had a menu and I could select menu options but I can't remember how I did that so I just do them all for now. Learning how to create a menu like that again is on my to-do list somewhere - LOL - down a ways - LOL. Is this number of errors normal? Maybe I need to start inspecting the MySQL log? Does anyone have any suggestions?

Thanks,
John

P.S. from the link below I just read " If you get a lot of these errors, without mysqld having died unexpectedly just before, then something is wrong and needs to be investigated further."
https://dev.mysql.com/doc/refman/5.5/en ... ption.html

I also found this. Just starting to read it.
https://dev.mysql.com/doc/refman/5.5/en ... -case.html

Re: Getting to many MySQL file corrupt errors

Posted: Wed Apr 29, 2015 8:58 am
by Celauran
Honestly, you'd be better off using InnoDB than MyISAM for a number of reasons (locking, transactions, foreign keys, etc). The latter is notorious for corruptions. Some things I've read suggest repeated frequent corruptions may be a sign of a failing hard drive. Probably not likely, but worth looking at all the same.

Re: Getting to many MySQL file corrupt errors

Posted: Wed Apr 29, 2015 9:19 am
by bowlesj
Thanks Celauran, Failing hard drive? I actually would not be that surprised. My hosting company is not the greatest.

The other day I had a problem where I could not add a record even after fixing the files and running the check without any errors. I solved it by backing up the database and returning it to a test database first to find that fixed it then I bit the bullet and returned it to my live database and it worked. Actually after fixing the files this morning I can't get the webpage to read one of the tables. I get an error saying "Incorrect key file for table 'mytable.MYI'; try to repair it". I tried repairing it but I get the same error even with a successful repair. So I have a fresh backup from this morning before any problems. I guess I will have to return it (did that and it worked).

Can I drop use phpMyAdmin to drop and return a single table rather than all the database tables? (I think I answered this. I tried it on the local host and it appears it can't be done).

Re: Getting to many MySQL file corrupt errors

Posted: Fri May 01, 2015 3:31 pm
by bowlesj
I wanted to know what was causing the problem so I went to http://forums.mysql.com/ and a specialist helped me out. He didn't know what was causing the problem but he also recommended I switch to InnoDB format for an additional reason. He figured it would solve the problem so I did it and it worked. He said that InnoDB is much more robust. He also looked at the logs and wanted me to make some updates to the MySql installation. He said much better. I don't know why but it sounded good to me :-)

Much earlier because I was having some problems I started running myisamchk daily then when it showed errors every day I ran it during the day to discover that any insert was immediately creating an error. I think that saved loosing any work. So one of my Google searches discovered mysqlcheck for InnoDB. He said.
You don't really need to run mysqlcheck....so often. You could run it once a week maybe and add the --optimize flag which will defrag your table files and reorder your primary indexes, but is only beneficial if your tables are going through lots of changes such as inserts and deletes. It's your call.