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.
> All my systems have different OS's, versions of MySQL and PHP. They all
> have UPS's. The old 10.2 system (soon to be retired) has only a single
> drive. The new 10.4 system has mirrored drives on a RAID set- up. The
> 10.3 install (recently wiped) had the same set-up. I think I can rule
> out a hardware issue and a OS / MySQL issue. It even show up on tables
> I never interact with directly i.e. my Moveable Type db for my blogs. I
> create an entry and the next day it's dropped out of the database.
> The part that keeps scaring me is that I have seen this on 10.2, 10.3
> and 10.4 all with separate hardware. My recent build has 2 drive on a
> mirror. I find it hard to believe that I have been that unlucky to
> have drive failures / failed filesystems on ALL these machines.
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?
> I previously had a MySQL install on 10.2 Client under 3.x and never had
> an issue or any major problems at all. I upgraded to MySQL 4.x and
> have subsequently installed MySQL 4.x (from the supplied pkg's) on my
> 10.2 client, a 10.3 Server and 10.4 Server (Tiger) install.
> Well Apple does not really have a front end for MySQL at all. I've gone
> away from Apple's updates and install my own version from the source
> packages provided by MySQL.
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.
> The typical application here was standalone servers running MySQL,
> Apache and PHP 4/5 running a hand full of small websites. Things run
> along fine until with out warning my PHP / MySQL queries fail returning
> no data when there should be.
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?
> 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)?
> I then re-inserted my data again, ran a 'REPAIR
> TABLE...' directly from the client and the data seems to stick.
If I understand correctly, REPAIR TABLE finds no problems?
> I jump through these hoops each time and the problem seems to go away and
> then out of the blue this comes back. It's driving my crazy.
>
> Any ideas at all out there? Flush tables? A different repair system?
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?
> Dan T
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
