Forum Moderators: open

Message Too Old, No Replies

Can't fix corrupt MySQL table

         

heisters

1:47 pm on Jul 28, 2006 (gmt 0)

10+ Year Member



Hi all,

I have a MySQL (5.0.22) database for a MediaWiki installation that's beeing reported as corrupt. I've tried all the tricks in the manual to try to fix it, but nothing works.

I tried REPAIR TABLE, which reports the table is fixed, but then a CHECK TABLE returns


+-------------------------+-------+----------+------------------------------------+
¦ Table ¦ Op ¦ Msg_type ¦ Msg_text ¦
+-------------------------+-------+----------+------------------------------------+
¦ itdocs_wiki.searchindex ¦ check ¦ error ¦ Key in wrong position at page 4096 ¦
¦ itdocs_wiki.searchindex ¦ check ¦ error ¦ Corrupt ¦
+-------------------------+-------+----------+------------------------------------+
2 rows in set (0.00 sec)

So I logged out and stopped the MySQL daemon and ran both myisamchk -r and myisamchk -o to no avail:


# myisamchk -r searchindex.MYI
- recovering (with sort) MyISAM-table 'searchindex.MYI'
Data records: 92
- Fixing index 1
- Fixing index 2
- Fixing index 3
# invoke-rc.d mysql start
Starting MySQL database server: mysqld.
# mysqlcheck itdocs_wiki -p
...
itdocs_wiki.searchindex
error : Key in wrong position at page 8192
error : Corrupt
...
# invoke-rc.d mysql stop
Stopping MySQL database server: mysqld.
# myisamchk -o searchindex.MYI
- recovering (with keycache) MyISAM-table 'searchindex.MYI'
Data records: 92
# invoke-rc.d mysql start
Starting MySQL database server: mysqld.
# mysqlcheck itdocs_wiki -p
...
itdocs_wiki.searchindex
error : Key in wrong position at page 4096
error : Corrupt
...

So, I'm unsure how to proceed. Any ideas?

Thanks!

zCat

2:18 pm on Jul 28, 2006 (gmt 0)

10+ Year Member



I've experience similar, seemingly random failures [ * ] on MySQL's fulltext tables (including with Wikipedia data) before. IIRC (it's been a while, fortunately)
REPAIR TABLE name_of_table
was what brought the table back to life.

[ * ] one reason why I no longer use MySQL if I can avoid it

[edited by: zCat at 2:19 pm (utc) on July 28, 2006]

motorhaven

1:51 am on Jul 31, 2006 (gmt 0)

10+ Year Member Top Contributors Of The Month



Use:

repair table tablename use_frm;

This will repair even the most stubborn table. It copies the data file one record at a time to a temp table, then deletes the original data file, moves the temp table to be the new data file. Then it dumps the existing index file and rebuilds a new one from scratch.

I've yet to see a table it could not repair. Note that it can be time consuming on large tables.

heisters

2:34 pm on Jul 31, 2006 (gmt 0)

10+ Year Member



huh, I thought I'd already tried the use_frm flag, but it seems to have worked this time.

Thanks!