MySQL

Corrupt database

[justageek]

I've got a database that will not stay stable. It's not anything out of the norm. Just some text and int fields.

I've tried rebuilding the index using every combination in myisamchk and it will rebuild. The problem is that it goes corrupt again when I check it and gets tagged as crashed.

I've tried dumping the data into a .sql file and importing it again. Works fine but then goes oil side up again.

So, I tried it on a different machine and I still get the same thing.

The interesting thing is that I can still query the table even though it is crashed. At least sometimes I can.

Has anyone else seen this? If so what was the problem? I'm running 4.1.1a and haven't had any problems so far.

I would have thought that by importing the dump if there were any problems with the raw data it would fail but it doesn't.

JAG

[coopster]

>>So, I tried it on a different machine and I still get the same thing.

Is it the same version of MySQL too? 4.1.1a alpha?

If you run

myisamchk

while

mysqld

is updating the tables, you may get a warning that a table is corrupt even when it isn't.

Some MySQL Resources that may offer insight:
Disaster Prevention and Recovery [mysql.com]
MyISAM Table Problems [mysql.com]
What To Do If MySQL Keeps Crashing [mysql.com]

[justageek]

Is it the same version of MySQL too? 4.1.1a alpha?

Yep. Same version. Interestingly enough the original data came from a 4.0 version. It seems it was corrupt there as well. I tried every fix suggested from the [mysql.com...] page but nothing works. The database was shut down for the fixes but it still goes corrupt sometimes. When I get the corrupt message I can still get the data sometimes. Other times I can't. Really odd. I have to think it is in the data but I just can't see how.

JAG

[coopster]

Can you give details on the messages? Also, what platform are you running the server on?

[justageek]

I'm on W2K with all updates.
Version 4.1.1a-alph-max-nt MySQL.
Plenty of ram and disk space.

The error is 'key in wrong position' error or '*.myi file not found'. Could be either one.

It's test data so I'm not worried about the data so much as to why it is happening since this same table structure will be used in production.

The error indicates an index problem so I've destroyed and created it about 324 times throughout the day.

I even did a dump, dropped the table, re-created it from scratch, brought the data back and rebuilt the indexes.

After the rebuild there might only be 6 or so updates or selects and it goes corrupt. No inserts of new records.

The only thing that I've been able to find that might be the cause is I do have a fulltext index on a text field. I've dropped that now to see what happens.

I'm pulling my hair out on this one. The table has been fine for months and only has about 3k records and maybe 10 meg of data so it's very small.

JAG

[coopster]

It just may be a bug...

[bugs.mysql.com...]

[justageek]

Unfortunately I think you're right. There seems to be several bug reports with the full text index.

The odd thing about it is the length of time this db has been running without any problems. I dropped the full text index and I can at least get an OK back when I check it now. Maybe it'll run now. I'll post what happens.

Thanks for the help!

JAG

[justageek]

OK. It seems it is a full text index bug. It's odd because of the size of the db at only 3k records and another at 250k records. The larger one runs fine but the smaller one just wouldn't run even with the same structure.

JAG