Dan Tappin <[EMAIL PROTECTED]> wrote on 07/06/2005 11:26:13 AM: > 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 > I'm not a Mac guru but what you describe sounds like file system corruption. Can you do a diagnostic scan of your hard drives looking for bad sectors? You can move your data to another portion of the disk if you run an ALTER TABLE to make some trivial change. ALTER TABLE will create a new copy of the table somewhere else on the disk (applying your change) and drops the old table when it's through.
No, database clients *usually* never cause table corruption (there may be the rare occasion...). The maintenance of the table structures are the responsibility of the MySQL server, not the client. So I would discurage looking that way for long. The way you describe your system, it almost sounds as though you have physically shared the same files between all of your server. Doing that could cause the corruption you describe. Each server requires its OWN sets of files. Are there ANY other processes that may be attempting to directly write to your database files? Your data files should be protected and isolated from other user-type files and direct contact from any other process but your MySQL server. Another option may be to rebuild your databases by first dumping your data, removing and reinstalling your servers, then reloading your data (one at a time, of course). I would save that as an option of last resort. HTH... Shawn Green Database Administrator Unimin Corporation - Spruce Pine
