I am all most ready to give up on MySQL at this point. I'm still
getting regular table corruption on multiple installs of OS X.
I went as far as reporting it as a bug:
http://bugs.mysql.com/bug.php?id=12066
They seem to want more info but my requests for clarification have
gone unanswered.
So I'm trying a few obscure things:
1.) Move the MySQL data dir
The default data dir was /usr/local/mysql/data/
I moved this to /var/mysql/data because subsequent MySQL installs
messed with the previous path (i.e. creating a symlink from the
latest install to /usr/local/mysql)
I have copied the entire mysql data dir to /usr/local/data
Just a hunch that it's some issue with being in /var/ On my previous
MySQL systems I simply moved the data contents to the new location
after each upgrade and restarted the server. I never had any issues
here (it was also on MySQL 3.x)
2.) Try Innodb
I have changed one of the tables that consistently gives my trouble
to Innodb. I will continue to watch for corruption.
Given that can anyone point out any big things to note about innodb
tables. I've read that they are in general better than myisam tables
but you loose the fulltext index option. Is there a typical work
around for this?? I typically only have the odd tables that needs a
full text search capability. I think what I have seen was to create
a dummy myisam table with just the key and fields from the innodb
table and add a fulltext index to search against. It would just be a
matter of keeping the tables synced.
I am also wondering about back-ups. The docs seem to indicate that
you can still run mysqldump... but how does this effect restoring.
In my case I can afford to shutdown the server in the wee hours of
the morning and back-up the data dir manually.
Any pointers would be appreciated.
Thanks,
Dan T
On Jul 6, 2005, at 10:26 AM, [EMAIL PROTECTED] wrote:
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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
