On Jul 24, 2005, at 9:13 AM, Michael Stassen wrote:
Dan Tappin wrote (quotes from several posts, my thoughts
interspersed):
> I am running into repeatable table corruption with MySQL 4.x on
Mac OS X
> 10.x.
Many people, myself included, are running mysql 4.x on OS X without
reporting this sort of problem, so the trick is to find out what
your installations have that the rest of us don't have.
This is also part of the frustration. If Macintouch or the OS X
server admin list was full of MySQL issues I would actually feel better.
Repeated table corruption often points to an underlying hardware or
OS issue, as others have pointed out, but as you have the same
behavior on a variety of platforms, I think you are right that this
is unlikely in your case. The question is, what do your systems
have in common? In particular, what do they have in common that
working systems don't have?
That's the big frustration. I'm also thinking now that I am the
common thread i.e operator error.
You've said you install using supplied pkg's, but you've also said
you install from the source. Let's be precise: Are you installing
MySQL's pre-compiled binary distribution for OS X (the installer
package which comes as a .dmg), or are you compiling from source?
I expect you are using the installer package, but if you are
building from source, you should post your configure options. Both
methods work, but if you are compiling it yourself, it could be
something different in your configuration which is causing your
problem.
Sorry to be clear I'm using the installer package - 4.1.13 as of
today on both suspect systems.
To be clear, your queries succeed, in that they do not return
errors from mysql, but they do not return expected data, correct?
Do the same queries produce the same result from the mysql client?
What makes you think that is table corruption, rather than missing
data? Corrupt tables usually produce error messages to that
effect. (See the manual for a list <http://dev.mysql.com/doc/mysql/
en/repair.html>. Also see <http://dev.mysql.com/doc/mysql/en/
corrupted-myisam-tables.html>.)
> As a temporary fix I created a cron job to run:
>
> /usr/local/mysql/bin/myisamchk -ov
>
> on all my tables every 4 hours.
You are shutting down mysqld every 4 hours? Or are you running
myisamchk while the server is running?
While running.
> This fixes the data issues but the problem is I am losing data:
>
> - recovering (with keycache) MyISAM-table '/var/mysql/data/
xxxxxxx.MYI'
> Data records: 17
> Data records: 14
>
> Now this was last night at midnight. I recall manually adding the 3
> records that evening and the previous cron job had only 14
records. It
> seems that the fix is flushing my new data down the toilet.
>
> Here is the full myisamchk output on the subject table (after
adding my
> data back in):
<snip>
> Poof!... gone again.
So, myisamchk is dropping *recently added* data (rows inserted
since the previous invocation of myisamchk)?
Yes... even when I wasn't running a regular myisamchk. For example:
I have the movable type blog installed on my 10.4 Server system. I
create a new blog entry or a new category. Later that hour, day or
week subsequent queries will fail and I'll get the ever wonderfull
'got error 127 from storage engine' error. Manual SELECT * FROM
mt_category etc will trigger this. A myisamchk will only seem to
work once I run a REPAIR TABLE mt_category etc.
The recommended method is to run CHECK TABLE in the client, then
REPAIR TABLE if necessary <http://dev.mysql.com/doc/mysql/en/
repair.html>, "If you are using MySQL 3.23.16 and above, you can
(and should) use the CHECK TABLE and REPAIR TABLE statements to
check and repair MyISAM tables."
That same page also states, "If you are going to repair a table
from the command line [using myisamchk], you must first stop the
mysqld server. Note that when you do mysqladmin shutdown on a
remote server, the mysqld server is still alive for a while after
mysqladmin returns, until all queries are stopped and all keys have
been flushed to disk."
Another manual page <http://dev.mysql.com/doc/mysql/en/crash-
recovery.html> says, "If you run mysqld with --skip-external-
locking...", the default on OS X, "...you can't reliably use
myisamchk to check a table when mysqld is using the same table."
It goes on to say, "If you use myisamchk to repair or optimize
tables, you must always ensure that the mysqld server is not using
the table (this also applies if you are using --skip-external-
locking). If you don't take down mysqld, you should at least do a
mysqladmin flush-tables before you run myisamchk. Your tables may
become corrupted if the server and myisamchk access the tables
simultaneously."
I am suspicious that you are creating your own problem. You report
that mysqld is not crashing, nor is it complaining of crashed/
corrupted tables. Your only symptom is missing data, so you run
myisamchk, which is deleting recently added rows. My guess is that
your cron job, meant to fix the problem, is actually the source of
the problem. Does your cron job shut down mysqld before running
myisamchk on your tables? Before the every-4-hour cron job, did
you have a regular (nightly? weekly?) job running myisamchk?
Hmmm... interesting... OK... I will suspend my cron myisamchk runs.
I have moved my non-fulltext search required tables to Innodb... I'm
wondering if I should move them back?
I will shut down the server and run a full myisamchk, re-start the
the server and run a REPAIR TABLE ... on all tables an then watch my
system.
Dan T
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
