List:General Discussion« Previous MessageNext Message »
From:Quentin Gouedard Date:June 7 2007 1:21pm
Subject:Re: recurring table corruption problems (MyISAM)
View as plain text  
I recompiled my sql with "nocona" instead of k8 and the problem persists.

Now, I found this:
which definitely sounds similar to my problems.
I'm gonna give a shot to disabling delayed_key_write.

On 6/6/07, Quentin Gouedard <quenting@stripped> wrote:
> Hello,
> So I have this somewhat busy mysql box (300qps - 1100 qps) holding a bunch
> (20) of myISAM tables.
> The Box is Core2Duo 2.4 / 4GB RAM / Gentoo64 / MySQL 5.0.38
> There are a number of scripts accessing those tables, both located on the
> same machine and on others.
> Every day, 1 to 3 tables get corrupted. Some of these tables are accessed
> from the other servers, but some tables get corrupted which are only
> accessed locally too.
> Most of the time I do a REPAIR, I lose some rows in the battle. Imagine
> what the result is when some of those tables are like "customers" or contain
> other vital information.
> Sometimes, REPAIR returns that the table status is OK, when CHECK returns
> that the table needs to be repaired. In such cases, i need to shutdown the
> server, myisamchk -r the table, and restart mysql. Sometimes even that
> doesn't change the CHECK output, and i need somehow to re-run a REPAIR to
> see the table finally fixed.
> MYSQL docs state for possible reasons for corruption:
> [quote] 1- The mysqld process being killed in the middle of a write.
> 2- Unexpected shutdown of the computer (for example, if the computer is
> turned off).
> 3- A hardware error such as faulty memory/cpu/motherboard and or hard
> drive.
> 4- You are using an external program (like myisamchk) on a live table.
> 5- A software bug in the MySQL or MyISAM code[/quote]
> 1 -> tables get corrupted without killed processes
> 2 -> server never gets unexpectedly shutdown
> 3 -> my hosts' monitoring system says ahrdware is fine (although i don't
> know what that means).
> 4 -> never do that without shutting mysql down first
> 5 -> I'm using a somewhat recent version so if it was this it'd be really
> curious.
> The software I use is a popular one for which no such problems have been
> reported before, so I doubt it's query/PHP related.
> The fact it occurs specifically on one out of 7 machines I have running
> "similar" configurations makes me think about an environmental problem
> rather than an application-layer one.
> Some more info if that may help :
> Typical errors showing up (error codes vary as you can see):
> 070502  3:27:45 [ERROR] /usr/sbin/mysqld: Table 'my_table' is marked as
> crashed and should be repaired
> 070502  4:23:30 [ERROR] Got error 127 when reading table
> './mysite/my_table'
> 070502 17:55:58 [ERROR] /usr/sbin/mysqld: Incorrect key file for table
> './mysite/my_table2.MYI'; try to repair it
> 070521 11:48:48 [ERROR] /usr/sbin/mysqld: Incorrect key file for table
> './mysite/my_table2.MYI'; try to repair it
> 070521 11:48:53 [ERROR] Got error 134 when reading table
> './mysite/my_table2'
> 070521 22:54:41 [ERROR] /usr/sbin/mysqld: Incorrect key file for table
> './mysite/my_table2.MYI'; try to repair it
> 070521 22:54:41 [ERROR] Got error 126 when reading table
> './mysite/my_table2'
> Another weird thing is that the errors do not always show up in the
> mysqld.err log file.
> Sometimes I get on-screen errors mentionning crashed tables, which are not
> reported in the log file, making me wonder how much more often it occurs.
> Would  mysqladmin variables help helping me ?
> And now on to the questions :
> 1) Are there any known problems to running mysql 5 on gentoo 64 bits ?
> 2) Could delay_key_write ON be the reason for this corruption, or setting
> it off minimise the chances of it happenning ?
> 3) How can I debug / add logs to give me a clue about the corruption
> origin ? What would you do to find out the problem / solve it ?
> 4) Are there settings that minimize / maximize table corruption risks ?
> 5) I realized today that the "march" option in my /etc/make.conf file is
> set to "k8" (AMD64 processors), when I use Core2Duo (for which recommended
> "march" is "nocona"). Could having compiled mysql with this parameter be the
> reason for the table corruptions ?
> Thanks !

recurring table corruption problems (MyISAM)Quentin Gouedard6 Jun
  • Re: recurring table corruption problems (MyISAM)Quentin Gouedard7 Jun