mysql myisam 丢数据,在使用MyISAM的MySQL的REPAIR TABLE期间，什么会导致数据丢失？

The MySQL documentation for REPAIR TABLE states that

It is best to make a backup of a table

before performing a table repair

operation; under some circumstances

the operation might cause data loss.

Possible causes include but are not

limited to file system errors.

I'd like to know if there are any other causes of data loss besides file system errors. Has anyone seen this happen in the wild? How likely is it that the repair will lose data if there are no file system errors?

My specific situation is as follows. I have Sun T5120 server running Solaris 10 (SPARC) and am using MySQL 5.1.30. I have a table which uses the MyISAM engine which occasionally becomes corrupted. Some of the times that the table has been corrupted have been due to unexpected power outages on our development system which didn't have an UPS. I'm not sure that all corruption was due to power outages, so there may be some additional reasons that this is happening. Such as the causes listed here.

I'd like to setup an automatic repair solution in case these suspected "additional reasons" happen in the production system, or the production UPS fails. I could set a cron job to run mysqlcheck --auto-recover as is suggested in this answer, or I could modify my process that is inserting into that table to instantly perform the REPAIR TABLE EXTENDED command when it detects corruption. However, both of those approaches use REPAIR TABLE and are, thus, susceptible to data loss.

I could backup the table before attempting the repair, as the documentation suggests, but the table is rather large and I'm not sure I will have space available for the backup. I've done some searching, but haven't found any explanation of why REPAIR TABLE would cause data loss besides what is mentioned in the documentation. So is it likely that the repair will lose data when you have a sound file system, or is the documentation just being cautious?

解决方案

One of the listed possible causes in the MySQL manual is a bug in the software. You should read the release notes / change history from your version forwards to see if any bugs in the repair table code have been fixed, and also read the release notes for new versions as they come out.

Out of interest, how does your process that inserts data detect corruption ?

An additional measure you can take to protect against data loss is to take backups and enable the replication log. In the event of failure, you can restore from the backup and then use the replication logs to bring your database back to the state it was in when it crashed.

Ultimately, if this is a production system, you really need to sort out a good power supply, and also have a second machine acting as a replica.