Why does MySQL repair a table nightly after upgrade from 4.1 to 5.1?

I have a gigabyte sized MySQL MyISAM table that now every night during export for backup, goes into "repair with keycache" for a few hours.

This only started after I upgraded MySQL from 4.1 to 5.1 (I did it in steps from 4.1 to 5.0 to 5.1). Seems strange to blame 5.1 but it's the only change on the server.

There are plenty of other tables on this server that do not have this problem, though none even half as 开发者_如何学Clarge.

I tried cloning/replacing the table with "create table like" and "insert into blah select * from" but this has changed nothing. This is as good as export/import, right?

I even dropped the fulltext index entirely in an attempt to stop this behavior (or at least speed up the repair) without success.

Also tried doing a manual repair with myisamchk, no change in behavior.

What's really strange is this is a low activity database, with not many writes and reads per hour.

Can anyone give me insight into why this is happening and what else I might debug for more information?

adding these links for more research:

How To Avoid Repair With Keycache?

How can I avoid "repair with keycache" in MySQL?

Looks like I need to check myisam_max_sort_file_size

However that does not answer my original question of why it needs to repair every night in the first place.

Try running CHECK TABLE mytable FOR UPGRADE

http://dev.mysql.com/doc/refman/5.1/en/check-table.html

If you have a problem then do REPAIR TABLE mytable USE_FRM EXTENDED

http://dev.mysql.com/doc/refman/5.1/en/repair-table.html

Additionally, you can run this (in shell, outside of mysql)

myisamchk --force --recover --key_buffer_size=512M --sort_buffer_size=64M --read_buffer_size=8M --write_buffer_size=8M  /path/to/datadir/*/*.MYI

(you will need to specify path to MYI - index file)

http://dev.mysql.com/doc/refman/5.0/en/myisamchk.html