I am running into repeatable table corruption with MySQL 4.x on Mac
OS X 10.x.
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.
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. As a temporary fix I created
a cron job to run:
/usr/local/mysql/bin/myisamchk -ov
on all my tables every 4 hours. This fixes the data issues but the
problem is I am loosing 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 I the fix is flushing my new data down the toilet.
The only common thread I can see is that I use MacSQL (an old version
2.6.3) for running test queries etc rather than the CLI. Can a mysql
client cause this corruption?
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.
Here is the full myisamchk output on the subject table (after adding
my data back in):
Checking MyISAM file: /var/mysql/data/xxxxxxxxxxxx.MYI
Data records: 18 Deleted blocks: 2
- check file-size
myisamchk: error: Size of datafile is: 876 Should be: 1160
- check record delete-chain
myisamchk: error: record delete-link-chain corrupted
- check key delete-chain
- check index reference
- check data record references index: 1
myisamchk: error: Found key at page 1024 that points to record
outside datafile
- check record links
myisamchk: error: Record-count is not ok; is 15 Should be: 18
myisamchk: warning: Found 0 deleted space. Should be 80
myisamchk: warning: Found 0 deleted blocks Should be: 2
myisamchk: warning: Found 15 parts Should be:
20 parts
MyISAM-table '/var/mysql/data/oilfielddepot_r6/subnavbar.MYI' is
corrupted
Fix it using switch "-r" or "-o"
so I run: myisamchk -vr /var/mysql/data/xxxxxxxxxxxxxx.MYI
- recovering (with sort) MyISAM-table '/var/mysql/data/
xxxxxxxxxxxxxxxxxx.MYI'
Data records: 18
- Fixing index 1
- Searching for keys, allocating buffer for 45 keys
- Dumping 15 keys
Data records: 15
Poof!... gone again. I then re-inserted my data again, ran a 'REPAIR
TABLE...' directly from the client and the data seems to stick. I
jump thought 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?
Dan T
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]